I am working on publishing the English version of “PostgreSQL 9.6 Performance Story” on Amazon. Below is a preview of Chapter 1.
This section will explain several characteristics of the PostgreSQL structure and PostgreSQL.
The physical structure of PostgreSQL is very simple. Shared memory, and very few background processes and data files. (See Figure 1-1)
Figure 1-1. PostgreSQL structure
The most important elements in shared memory are Shared Buffer and WAL buffers.
The purpose of Shared Buffer is to minimize DISK IO. To achieve this, the following items must be satisfied. This section is covered in ‘Chapter 2. Understanding Shared Buffer’
- You need to access very large (tens, hundreds of gigabytes) buffers quickly.
- You should minimize contention when many users access it at the same time.
- Frequently used blocks must be in the buffer for as long as possible.
The WAL buffer is a buffer that temporarily stores changes to the database. The contents stored in the WAL buffer are written to the WAL file at a predetermined point in time. From a backup and recovery point of view, WAL buffers and WAL files are very important. However, the subject of this book is somewhat different. Therefore, this book does not cover.
PostgreSQL has four process types.
- Postmaster (Daemon) Process
- Background Process
- Backend Process
- Client Process
The Postmaster process is the first process started when you start PostgreSQL. At startup, performs recovery, initialize shared memory, and run background processes. It also creates a backend process when there is a connection request from the client process. (See Figure 1-2)
Figure 1-2. Process relationship diagram
If you check the relationships between processes with the pstree command, you can see that the Postmaster process is the parent process of all processes. (For clarity, I added the process name and argument after the process ID)
$ pstree -p 1125 postgres(1125) /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data ─┬─postgres(1249) postgres: logger process ├─postgres(1478) postgres: checkpointer process ├─postgres(1479) postgres: writer process ├─postgres(1480) postgres: wal writer process ├─postgres(1481) postgres: autovacuum launcher process ├─postgres(1482) postgres: archiver process ├─postgres(1483) postgres: stats collector process
The list of background processes required for PostgreSQL operation is as follows. (See Table 1-1) Other processes except Autovacuum launcher are easily found in ORACLE.
Table 1-1. List of PostgreSQL background processes
|logger||Write the error message to the log file.|
|checkpointer||When a checkpoint occurs, the dirty buffer is written to the file.|
|writer||Periodically writes the dirty buffer to a file.|
|wal writer||Write the WAL buffer to the WAL file.|
|autovacuum launcher||Fork autovacuum worker when vacuum is needed. Vacuum is described in ‘Chapter 4. Understanding Vacuum’.|
|archiver||When in Archive log mode, copy the WAL file to the specified directory.|
|stats collector||DBMS usage statistics such as session execution information (pg_stat_activity) and table usage statistical information (pg_stat_all_tables) are collected.|
The maximum number of backend processes is set by the max_connections parameter, and the default value is 100. The backend process performs the query request of the user process and then transmits the result. Some memory structures are required for query execution, which is called local memory. The main parameters associated with local memory are:
Space used for sorting, bitmap operations, hash joins, and merge joins. The default setting is 4 MiB.
Space used for Vacuum and CREATE INDEX. The default setting is 64 MiB.
Space used for temporary tables. The default setting is 8 MiB.
Let’s look at some of the things you need to understand the database structure.
Things related to the database
- PostgreSQL consists of several databases. This is called a database cluster.
- When initdb () is executed, template0, template1, and postgres databases are created.
- The template0 and template1 databases are template databases for user database creation and contain the system catalog tables.
- The list of tables in the template0 and template1 databases is the same immediately after initdb (). However, the template1 database can create objects that the user needs.
- The user database is created by cloning the template1 database.
Things related to the tablespace
- The pg_default and pg_global tablespaces are created immediately after initdb ().
- If you do not specify a tablespace at the time of table creation, it is stored in the pg_dafault tablespace.
- Tables managed at the database cluster level are stored in the pg_global tablespace.
- The physical location of the pg_default tablespace is $PGDATA\base.
- The physical location of the pg_global tablespace is $PGDATA\global.
- One tablespace can be used by multiple databases. At this time, a database-specific subdirectory is created in the table space directory.
- Creating a user tablespace creates a symbolic link to the user tablespace in the $PGDATA\tblspc directory.
Things related to the table
1. There are three files per table.
2. One is a file for storing table data. The file name is the OID of the table.
3. One is a file to manage table free space. The file name is OID_fsm.
4. One is a file for managing the visibility of the table block. The file name is OID_vm.
5. The index does not have a _vm file. That is, OID and OID_fsm are composed of two files.
Note The file name at the time of table and index creation is OID, and OID and pg_class.relfilenode are the same at this point. However, when a rewrite operation (Truncate, CLUSTER, Vacuum Full, REINDEX, etc.) is performed, the relfilenode value of the affected object is changed, and the file name is also changed to the relfilenode value. You can easily check the file location and name by using pg_relation_filepath (‘<object name>’).
template0, template1, postgres database
Let’s take a look at the tests we’ve done.
If you query the pg_database view after initdb (), you can see that the template0, template1, and postgres databases have been created.
select oid, datname, datistemplate, datallowconn from pg_database order by 1; oid | datname | datistemplate | datallowconn -------+-----------+---------------+-------------- 1 | template1 | t | t 13321 | template0 | t | f 13322 | postgres | f | t
- Through the datistemplate column, you can see that the template0 and template1 databases are database for template for user database creation.
- The datlowconn column indicates whether the database can be accessed. Since the template0 database can not be accessed, the contents of the database can not be changed either.
- The reason for providing two databases for the template is that the template0 database is the initial state template and the template1 database is the template added by the user.
- The postgres database is the default database created using the template1 database. If you do not specify a database at connection time, you will be connected to the postgres database.
The database is located under the $PGDATA/base directory. The directory name is the database OID number.
[postgres@pgserver data]$ ls -l $PGDATA/base drwx------. 2 postgres postgres 8192 Nov 4 19:34 1 drwx------. 2 postgres postgres 8192 Nov 4 19:34 13321 drwx------. 2 postgres postgres 8192 Nov 4 19:34 13322
Create user database
The user database is created by cloning the template1 database. To verify this, create a user table T1 in the template1 database. After creating the mydb01 database, check that the T1 table exists.
-- Create a T1 table in the template database. template1=# create table t1 (c1 integer); -- Create the mydb01 database. postgres=# create database mydb01; -- After connecting to mydb01 database, check if T1 table exists. mydb01=# \d t1 Table "public.t1" Column | Type | Modifiers --------+---------+----------- c1 | integer |
See Figure 1-3.
Figure 1-3. Relationship between Template Database and User Database
If you query pg_tablespace after initdb (), you can see that the pg_default and pg_global tablespaces have been created.
postgres=# select oid, * from pg_tablespace; oid | spcname | spcowner | spcacl | spcoptions ------+------------+----------+--------+------------ 1663 | pg_default | 10 | | 1664 | pg_global | 10 | |
- The location of the pg_default tablespace is $PGDATA\base. There is a subdirectory by database OID in this directory. (See Figure 1-4)
[postgres@pgserver ~]$ ls -l $PGDATA/base drwx------. 2 postgres postgres 8192 Nov 4 20:06 1 drwx------. 2 postgres postgres 8192 Nov 4 20:02 13321 drwx------. 2 postgres postgres 8192 Nov 4 20:02 13322 drwx------. 2 postgres postgres 8192 Nov 4 20:06 16396
Figure 1-4. Pg_default tablespace and database relationships from a physical configuration perspective
The pg_global tablespace is a tablespace for storing data to be managed at the ‘database cluster’ level.
- For example, tables of the same type as the pg_database table provide the same information whether they are accessed from any database. (See Figure 1-5)
- The location of the pg_global tablespace is $PGDATA\global.
Figure 1-5. Relationship between pg_global tablespace and database
Create user tablespace
Create a user tablespace.
postgres=# create tablespace myts01 location '/data01';
The pg_tablespace shows that the myts01 tablespace has been created.
postgres=# select oid, * from pg_tablespace; oid | spcname | spcowner | spcacl | spcoptions -------+------------+----------+--------+------------ 1663 | pg_default | 10 | | 1664 | pg_global | 10 | | 24576 | myts01 | 10 | |
Symbolic links in the $PGDATA/pg_tblspc directory point to tablespace directories.
[postgres@pgserver ~]$ ls -l $PGDATA/pg_tblspc lrwxrwxrwx. 1 postgres postgres 7 Nov 8 15:10 24576 -> /data01
Connect to the postgres and mydb01 databases and create the table.
[postgres@pgserver ~]$ psql -p 5436 -d postgres -- Create a table. postgres=# create table t1 (c1 integer) tablespace myts01; -- Check the OID. postgres=# select oid from pg_class where relname='t1'; oid ------- 24577 [postgres@pgserver ~]$ psql -p 5436 -d mydb01 -- Create a table. mydb01=# create table t1 (c1 integer) tablespace myts01; -- Check the OID. mydb01=# select oid from pg_class where relname='t1'; oid ------- 24580
If you look up the /data01 directory after creating the table, you will see that the postgres and mydb01 database OID directories have been created and that each directory contains the same files as the T1 table OIDs.
[postgres@pgserver ~]$ ls -Rl /data01 /data01: drwx------. 4 postgres postgres 30 Nov 8 15:19 PG_9.6_201608131 /data01/PG_9.6_201608131: drwx------. 2 postgres postgres 18 Nov 8 15:18 13322 drwx------. 2 postgres postgres 18 Nov 8 15:19 16396 /data01/PG_9.6_201608131/13322: -rw-------. 1 postgres postgres 40960 Nov 8 15:40 24577 /data01/PG_9.6_201608131/16396: -rw-------. 1 postgres postgres 40960 Nov 8 15:41 24580
How to change tablespace location
PostgreSQL specifies a directory when creating tablespaces. Therefore, if the file system where the directory is located is full, the data can no longer be stored. To solve this problem, you can use the volume manager. However, if you can not use the volume manager, you can consider changing the tablespace location. The order of operation is as follows.
-- Shutdown PostgreSQL. /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data stop -- Copy the myts01 tablespace to the new file system. [postgres@pgserver data01]$ cp -rp /data01/PG* /data02 -- Check the contents of the pg_tblspc directory. [postgres@pgserver pg_tblspc]$ ls -l lrwxrwxrwx. 1 postgres postgres 7 Nov 8 15:52 24576 -> /data01 -- Delete the symbolic link. [postgres@pgserver pg_tblspc]$ rm 24576 -- Create a new symbolic link. [postgres@pgserver pg_tblspc]$ ln -s /data02 24576 -- Confirm the contents [postgres@pgserver pg_tblspc]$ ls -l lrwxrwxrwx. 1 postgres postgres 7 Nov 8 15:53 24576 -> /data02 -- Startup PostgreSQL. /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start
Note Tablespaces are also very useful in environments that use partition tables. Because you can use different tablespaces for each partition table, you can more flexibly cope with file system capacity problems.
What is Vacuum?
Vacuum does the following:
- Gathering table and index statistics
- Reorganize the table
- Clean up tables and index dead blocks
- Frozen by record XID to prevent XID Wraparound
#1 and #2 are generally required for DBMS management. But #3 and #4 are necessary because of the PostgreSQL MVCC feature. (This is discussed in detail in ‘Chapter 4. Understanding Vacuum’)
Differences between ORACLE and PostgreSQL
The biggest difference I think is the MVCC model and the existence of a shared pool. This is also a feature of PostgreSQL. (See Table 1-2)
Table 1-2. The biggest difference between ORACLE and PostgreSQL
|MVCC model implementation method||UNDO Segment||Store previous record within block|
|Shared Pool||existence||it does not exist|
Differences in the MVCC model
To increase concurrency, you must follow the principle that “read operations do not block write operations and write operations should not block read operations”. To implement this principle, a Multi Version Concurrency Control (MVCC) is required. ORACLE uses UNDO segments to implement MVCC. On the other hand, PostgreSQL uses a way to store previous records in a block. This section is covered in ‘Chapter 4. Understanding Vacuum’.
PostgreSQL does not provide a shared pool. This is somewhat embarrassing for users familiar with ORACLE. Shared Pool is a very important and essential component in ORACLE.
PostgreSQL provides the ability to share SQL information at the process level instead of the Shared Pool. In other words, if you execute the same SQL several times in one process, it will hard-parse only once. This part is described in Chapter 3. Understanding Optimizer’.
The PostgreSQL structure is very simple. From a physical point of view, it consists of shared memory, processes, and data files. The main components of shared memory are Shared Buffer and WAL buffers.
Process is divided into Postmaster process, background process, backend process and client process.
Background processes exist for error logging, buffer writes, WAL buffer writes, and there is a process for autovacuum. The backend process executes the query requested by the client process, and there is a local memory area for performing the task.
PostgreSQL can create multiple databases, and clone the contents of the template database when creating a user database.
From a physical point of view, the relationship between a database and a tablespace is the way in which tablespaces are located in the table space directory.
The pg_global tablespace stores data that must be managed at the database cluster level.
Chapter 1 gives a brief overview of PostgreSQL architecture.