3 Architecture

Architecture, catalogs, replication, execution, I/O, roles, and server internals.

GASource pages: PDF 13-31Original PDF on GitHub

PDF page 13

3.1.1 Modified catalogs

The following catalogs have been changed.

Catalog name	Description
pg_stat_progress_cluster	Traces the execution status of CLUSTER.
pg_stat_progress_create_index	Traces the execution status of CREATE INEDX.
pg_stat_gssapi	Showing information about GSSAPI usage.
pg_statistic_ext_data	Holds data for extended planner statistic

Table 4 Added system catalogs

Catalog name	Description
column_column_usage	Column information that depends on a specific column (stores information on a table with generated columns).

Table 5 Added information_schema catalog

Catalog name	Added column	Data type	Description
*1	oid	oid	Change attribute to regular column.
pg_attribute	attgenerated	char	’s’ for generated column.
pg_collation	collisdeterministic	boolean	Is the collation deterministic?
pg_statistic	stacol[1-5]	oid	Collation information.
pg_stat_database	checksum_failures	bigint	Number of blocks for which a checksum error was detected.
	checksum_last_failure	timestamp with time zone	Last timestamp for which checksum error was detected.
pg_stat_replication	reply_time	timestamp with time zone	Message time from standby instance.
pg_stat_ssl	client_serial	numeric	Client certificate serial number.
	issuer_dn	text	Client certificate issuer DN.

Table 6 System catalogs with columns added

PDF page 14

*1 There are many corresponding catalogs.

Catalog name	Deleted column	Description
pg_attrdef	adsrc	A human-readable representation of the default value.
pg_class	relhasoids	Whether the table is WITH OIDS specified.
pg_constraint	consrc	A human-readable representation of the expression.
pg_statistic_ext	stxndistinct	Move to pg_statistics_ext_data
	stxdependencies	Move to pg_statistics_ext_data

Table 7 System catalogs with columns deleted

Catalog name	Modified Column	Description
pg_shadow	valuntil	Data type changed to timestamp with time zone.
pg_stat_ssl	client_dn	Column name changed from ’lientdn'.

Table 8 System catalogs with column modified

Among the modified system catalogs, the details of the major catalogs are described below.

Pg_stat_progress_cluster catalog

The pg_stat_progress_cluster catalog can trace the execution status of CLUSTER statements or VACUUM FULL statements.

Column name	Data type	Description
pid	integer	Process ID of backend.
datid	oid	OID of the database to which this backend is connected.
datname	name	Name of the database to which this backend is connected.
relid	oid	OID of the table being clustered.
command	text	The command that is running.
phase	text	Current processing phase.
cluster_index_relid	oid	OID of the index being used.
heap_tuples_scanned	bigint	Number of heap tuples scanned.
heap_tuples_written	bigint	Number of heap tuples written.
heap_blks_total	bigint	Total number of heap blocks in the table.
heap_blks_scanned	bigint	Number of heap blocks scanned.
index_rebuild_count	bigint	Number of indexes rebuilt.

Table 9 pg_stat_progress_cluster catalog

PDF page 15

Pg_stat_progress_create_index catalog

The pg_stat_progress_create_index catalog can trace the execution status of CREATE INDEX statement. The status is also reflected when the REINDEX statement is executed.

Column name	Data type	Description
pid	integer	Process ID of backend.
datid	oid	OID of the database to which this backend is connected.
datname	name	Name of the database to which this backend is connected.
relid	oid	OID of the table on which the index is being created.
index_relid	oid	OID of the index.
command	text	Executed SQL statement name.
phase	text	Current processing phase of index creation.
lockers_total	bigint	Total number of lockers to wait for.
lockers_done	bigint	Total number of lockers already waited for.
current_locker_pid	bigint	Process ID of the locker currently being waited for.
blocks_total	bigint	Total number of the blocks to be processed.
blocks_done	bigint	Total number of the blocks already processed.
tuples_total	bigint	Total number of tuples to be processed.
tuples_done	bigint	Number of tuples already processed.
partitions_total	bigint	Total number of partitions.
partitions_done	bigint	Number of partitions on which the index has been completed.

Table 10 pg_stat_progress_create_index catalog

PDF page 16

Example 8 Refer pg_stat_progress_create_index catalog

postgres=> SELECT * FROM pg_stat_progress_create_index ;
-[ RECORD 1 ]------+---------------------------------------
pid                | 13233
datid              | 16385
datname            | postgres
relid              | 16386
index_relid       | 0
phase              | building index: loading tuples in tree
lockers_total      | 0
lockers_done       | 0
current_locker_pid | 0
blocks_total       | 0
blocks_done        | 0
tuples_total       | 10000000
tuples_done        | 6207353
partitions_total   | 0
partitions_done    | 0

Pg_stat_replication catalog

The reply_time column has been added to the pg_stat_replication catalog. This column stores the sending time of the last reply message received from the standby instance. This column can only be displayed by the SUPERUSER attribute or the holder of the pg_monitor role.

PDF page 17

Example 9 Refer pg_stat_replication catalog

postgres=# SELECT * FROM pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid              | 12497
usesysid         | 10
usename          | postgres
application_name | walreceiver
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 2019-10-03 20:13:15.551032-04
backend_xmin     |
state            | streaming
sent_lsn         | 0/3000060
write_lsn        | 0/3000060
flush_lsn        | 0/3000060
replay_lsn       | 0/3000060
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2019-10-03 20:15:45.68363-04

Pg_indexes catalog

The pg_indexes catalog now includes partitioned indexes. Specifically, the relkind column in the pg_class catalog now includes an index of ‘I’.

Pg_stat_database catalog

The checksum_failures column and checksum_last_failure column in the pg_stat_database catalog are now updated when a block checksum error is detected. The value of checksum_failures column is updated each time when a checksum error is detected, so it does not indicate the actual number of blocks corrupted.

PDF page 18

Example 10 Refer pg_stat_database catalog

postgres=# SELECT * FROM data1 ;
psql: WARNING:  page verification failed, calculated checksum 27311
but expected 12320
ERROR:  invalid page in block 0 of relation base/13567/16384
postgres=#
postgres=# SELECT datname, checksum_failures , checksum_last_failure
FROM pg_stat_database WHERE datname='postgres' ;
-[ RECORD 1 ]-----+------------------------------
datname           | postgres
checksum_failures | 1
checksum_last_failure | 2019-10-03 20:06:23.747741-04

In PostgreSQL 12, a tuple whose datid column is 0 has been added. This is database-independent
global object information.

Pg_stat_gssapi catalog

The pg_stat_gssapi view showing information about GSSAPI usage on this connection. It can be joined to pg_stat_activity or pg_stat_replication on the pid column to get more details about the connection.

Column name	Data type	Description
pid	integer	Process ID of backend.
gss_authenticated	boolean	True if GSSAPI authentication was used.
principal	text	Principal used to authenticate this connection.
encrypted	boolean	True if GSSAPI encryption is in use.

Table 11 pg_stat_gssapi catalog

Pg_statistic_ext_data catalog

This catalog holds data for extended planner statistics defined in pg_statistic_ext.

PDF page 19

Column name	Data type	Description
stxoid	oid	Reference of pg_statistic_ext catalog
stxdndistinct	pg_ndistinct	N-distinct counts
stxddependencies	pg_dependencies	Functional dependency statistics
stxdmcv	pg_mcv_list	MCV (most-common values) list statistics

Table 12 pg_statistic_ext_data catalog

3.1.2 Abolition of recovery.conf file

The recovery.conf file used to build standby instances in a streaming replication environment, and for recovery from backup has been obsolete. Various settings in the recovery.conf file have been integrated into the postgresql.conf file. Some parameters have been changed.

recovery.conf	postgresql.conf	Note
standby_modetrigger_file	promote_trigger_file	Discontinued.

Table 13 Modified parameters

In case of recovery, create a recovery.signal file in the database cluster and start the instance. This file is deleted when the recovery process is complete. In a streaming replication environment, create a standby.signal file in the database cluster on slave instances. This file is deleted when promoted to primary. If the recovery.conf file exists in the database cluster, instance startup fails.

PDF page 20

Example 11 Deploy recovery.conf file and start instance

$ ls data/recovery.conf
data/recovery.conf
$ pg_ctl -D data start
waiting for server to start....2019-10-03 21:55:53.749 EDT [18397]
LOG:  starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
2019-10-03 21:55:53.750 EDT [18397] LOG:  listening on IPv6 address
"::1", port 5432
2019-10-03 21:55:53.750 EDT [18397] LOG:  listening on IPv4 address
"127.0.0.1", port 5432
2019-10-03 21:55:53.755 EDT [18397] LOG:  listening on Unix socket
"/tmp/.s.PGSQL.5432"
2019-10-03 21:55:53.771 EDT [18398] LOG:  database system was shut
down at 2019-10-03 21:55:09 EDT
2019-10-03 21:55:53.771 EDT [18398] FATAL:  using recovery command
file "recovery.conf" is not supported
2019-10-03 21:55:53.772 EDT [18397] LOG:  startup process (PID
18398) exited with exit code 1
2019-10-03 21:55:53.772 EDT [18397] LOG:  aborting startup due to
startup process failure
2019-10-03 21:55:53.773 EDT [18397] LOG:  database system is shut
down
 stopped waiting
pg_ctl: could not start server
Examine the log output.
$

3.1.3 Instance startup log

The version number is now output to the log at instance startup. If the parameter logging_collector is specified as “on”, it is output to the log file.

PDF page 21

Example 12 Instance startup log

$ pg_ctl -D data start
waiting for server to start....2019-10-03 21:36:22.445 EDT [17730]
LOG:  starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
2019-10-03 21:36:22.447 EDT [17730] LOG:  listening on IPv6 address
"::1", port 5432
2019-10-03 21:36:22.447 EDT [17730] LOG:  listening on IPv4 address
"127.0.0.1", …

3.1.4 Maximum number of connections

The number of connections for replication is now obtained using the max_wal_senders parameter without depending on the max_connections parameter. As a result of this modification, information of max_wal_senders has been added to the output of the pg_controldata command.

Example 13 Output of pg_controldata command

$ pg_controldata -D data | grep max_wal_senders
max_wal_senders setting:              10

3.1.5 Parallel query enhancement

Parallel queries now work even if the transaction isolation level of the session is SERIALIZABLE. The following example uses the auto_explain module to output the execution plan of the SQL statement executed in the transaction of the SERIALIZABLE isolation level.

PDF page 22

Example 14 Execute query

postgres=# LOAD 'auto_explain' ;
LOAD
postgres=# SET auto_explain.log_min_duration = 0 ;
SET
postgres=# BEGIN ISOLATION LEVEL SERIALIZABLE ;
BEGIN
postgres=# SELECT COUNT(*) FROM data1 ;
  count
---------
 1000000
(1 row)
postgres=# COMMIT ;
COMMIT

Example 15 Execution plan on PostgreSQL 11

2019-10-03 21:52:48.785 JST [1789] LOG:  duration: 45.472 ms  plan:
        Query Text: SELECT COUNT(*) FROM data1 ;
        Aggregate  (cost=17906.00..17906.01 rows=1 width=8)
          ->  Seq Scan on data1  (cost=0.00..15406.00 rows=1000000
width=0)

Example 16 Execution plan of PostgreSQL 12

2019-10-03 21:39:09.319 EDT [17764] LOG:  duration: 94.678 ms  plan:
        Query Text: SELECT COUNT(*) FROM data1;
        Finalize Aggregate  (cost=10633.55..10633.56 rows=1 width=8)
          ->  Gather  (cost=10633.33..10633.54 rows=2 width=8)
                Workers Planned: 2
                ->  Partial Aggregate  (cost=9633.33..9633.34 rows=1
width=8)
                      ->  Parallel Seq Scan on data1  (cost=0.00..8591.67
rows=416667 width=0)

PDF page 23

3.1.6 Jsonb type and GIN index

The “jsonb @@ jsonpath” and “jsonb @? jsonpath” operators have been added to the GIN index created for the jsonb type. The operator class of GIN index can be used either jsonb_ops or json_path_ops. The following is the execution plan of the SELECT statement described in the manual.

Example 17 jsonb type and GIN index

postgres=> CREATE INDEX idxgin ON api USING GIN (jdoc) ;
CREATE INDEX
postgres=> EXPLAIN (COSTS OFF) SELECT jdoc->'guid', jdoc->'name' FROM
api WHERE jdoc @@ '$.tags[*] == "qui"' ;
                            QUERY PLAN
------------------------------------------------------------------
 Bitmap Heap Scan on api
   Recheck Cond: (jdoc @@ '($."tags"[*] == "qui")'::jsonpath)
   ->  Bitmap Index Scan on idxgin
         Index Cond: (jdoc @@ '($."tags"[*] == "qui")'::jsonpath)
(4 rows)
postgres=> EXPLAIN (COSTS OFF) SELECT jdoc->'guid', jdoc->'name' FROM
api WHERE jdoc @@ '$.tags[*] ? (@ == "qui")' ;
                             QUERY PLAN
--------------------------------------------------------------------
 Bitmap Heap Scan on api
   Recheck Cond: (jdoc @@ '$."tags"[*]?(@ == "qui")'::jsonpath)
   ->  Bitmap Index Scan on idxgin
         Index Cond: (jdoc @@ '$."tags"[*]?(@ == "qui")'::jsonpath)
(4 rows)

3.1.7 Wait Event

The following changes were made to the wait event output to the wait_event column of the pg_stat_activity catalog.

PDF page 24

Wait event name	Description	Modify
BackendRandomLock	Waiting to generate a random number.	Deleted
GSSOpenServer	Waiting for connect to GSSAPI server.	Added
CheckpointDone	Waiting for a checkpoint to complete.	Added
CheckpointStart	Waiting for a checkpoint to start.	Added
Promote	Waiting for standby promotion.	Added
WALSync	Waiting for a WAL file to reach stable storage.	Added

Table 14 Modified wait events

3.1.8 ECPG

The following features have been added to ECPG.

Bytea data type

The variable of type bytea is defined in the DECLARE SECTION and can be used for data input/output.

Example 18 DECLARE bytea

{
    EXEC SQL BEGIN DECLARE SECTION ;
        bytea data[1024] ;
        short data_ind = 0 ;
    EXEC SQL END DECLARE SECTION ;
    memset(data.arr, 0, sizeof(data.arr)) ;
    data.len = 0 ;
    EXEC SQL SELECT col1 INTO :data FROM data1 ;
    for (i = 0; i < data.len; i++)
    {
        printf("data[%d] = %c\n", i, data.arr[i]) ;
    }
}

PDF page 25

PREPARE AS statement

It is now possible to write SQL statements directly by PREPARE AS statement.

Example 19 PREPARE AS statement

{
    EXEC SQL BEGIN DECLARE SECTION ;
        int ivar1 = 100;
    int ivar2 = 200;
    EXEC SQL END DECLARE SECTION ;
  EXEC SQL PREPARE pre_name(INT, INT) AS INSERT INTO prepare1
VALUES ($1, $2);
    EXEC SQL EXECUTE pre_name(:ivar1, :ivar2);
}

3.1.9 Pluggable Storage Engine

The basic specifications for using multiple storage engines have been decided. The access method for the table specifies the TYPE TABLE clause in the CREATE ACCESS METHOD statement. Syntax

CREATE ACCESS METHOD am_name TYPE TABLE HANDLER handler_name

The default storage engine is specified by the default_table_access_method parameter. The default value for this parameter is “heap”. As a result of this modification, it has been added tuple to the pg_am catalog.

PDF page 26

Example 20 Refer pg_am catalog

postgres=# SELECT amname, amhandler, amtype FROM pg_am ;
 amname |      amhandler       | amtype
--------+----------------------+--------
 heap   | heap_tableam_handler | t
 btree  | bthandler            | i
 hash   | hashhandler          | i
 gist   | gisthandler          | i
 gin    | ginhandler           | i
 spgist | spghandler           | i
 brin   | brinhandler          | i
(7 rows)

Specify engine when creating table

To specify a storage engine for a table, specify the USING clause in the CREATE TABLE statement. It can also be specified with the CREATE TABLE AS SELECT statement or the CREATE MATERIALIZED VIEW statement.

Example 21 CREATE TABLE statement

postgres=> CREATE TABLE data1(c1 NUMERIC, c2 VARCHAR(10)) USING
heap ;
CREATE TABLE
postgres=> CREATE TABLE data2 USING heap AS SELECT * FROM data1 ;
SELECT 100000
postgres=> CREATE MATERIALIZED VIEW mview1 USING heap AS
                 SELECT COUNT(*) cnt FROM data1 ;
SELECT 1

Display table definition by psql command

When the “\d+ table_name” command is executed, the storage engine name of the table is output.

PDF page 27

Example 22 \d+ command output

postgres=> \d+ data1
                                          Table "public.data1"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats
target | Description
--------+-----------------------+-----------+----------+---------+----------+---
-----------+-
 c1     | numeric               |           |          |         | main     |           |
 c2     | character varying(10) |           |          |         | extended |
|
Access method: heap

 Specifying on for the psql variable HIDE_TABLEAM (default value: 'off') can suppress the output
of the Access Method item.

Example 23 \d+ command output

postgres=> \set HIDE_TABLEAM on
postgres=> \d+ data1
                                          Table "public.data1"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats
target | Description
--------+-----------------------+-----------+----------+---------+----------+--------
------+----------
 c1     | numeric               |           |          |         | main     |           |
 c2     | character varying(10) |           |          |         | extended |           |

Display of access method by psql command

Table access methods are also displayed with the \dA command. Previously, only the index access method was output.

PDF page 28

Example 24 \dA command output

postgres=> \dA
List of access methods
  Name  | Type
--------+-------
 brin   | Index
 btree  | Index
 gin    | Index
 gist   | Index
 hash   | Index
 heap   | Table
 spgist | Index
(7 rows)

3.1.10 Pg_hba.conf file

The following new features have been added to the pg_hba.conf file.

New parameter of clientcert item

A new setting “verfy-full” can be specified for the clientcert parameter. This option also ensures that the certificate’s “cn” (common name) matches the username or proper mapping.

GSSAPI authentication encryption option

Generic Security Standard Application Programming Interface (GSSAPI) with encryption option is available for client authentication. An entry of hostgssenc / hostnogssenc can be specified in the pg_hba.conf file. In order to enable GSSAPI authentication, it is necessary to specify –with-gssapi as an option of “configure” command at installation.

3.1.11 Text search

Supported languages for text search increased. It was 16 languages in PostgreSQL 11 but has increased to 22 languages in PostgreSQL 12.

PDF page 29

Example 25 Text search configurations

postgres=# \dF
               List of text search configurations
   Schema   |    Name    |              Description
------------+------------+---------------------------------------
 pg_catalog | arabic     | configuration for arabic language
<< new
 pg_catalog | danish     | configuration for danish language
 pg_catalog | dutch      | configuration for dutch language
 pg_catalog | english    | configuration for english language
 pg_catalog | finnish    | configuration for finnish language
 pg_catalog | french     | configuration for french language
 pg_catalog | german     | configuration for german language
 pg_catalog | hungarian  | configuration for hungarian language
 pg_catalog | indonesian | configuration for indonesian language << new
 pg_catalog | irish      | configuration for irish language      << new
 pg_catalog | italian    | configuration for italian language
 pg_catalog | lithuanian | configuration for lithuanian language << new
 pg_catalog | nepali     | configuration for nepali language
 pg_catalog | norwegian  | configuration for norwegian language  << new
 pg_catalog | portuguese | configuration for portuguese language
 pg_catalog | romanian   | configuration for romanian language
 pg_catalog | russian    | configuration for russian language
 pg_catalog | simple     | simple configuration
 pg_catalog | spanish    | configuration for spanish language
 pg_catalog | swedish    | configuration for swedish language
 pg_catalog | tamil      | configuration for tamil language     << new
 pg_catalog | turkish    | configuration for turkish language
(22 rows)

PDF page 30

3.1.12 Libpq API

The following functions have been added to the C language interface to PostgreSQL.

PQresultMemorySize

“size_t PQresultMemorySize (const PGresult * res)” API has been added. This function returns the amount of memory allocated by PGresult. It can be used to manage application memory.

GetForeignDataWrapperExtended

“ForeignDataWrapper * GetForeignDataWrapperExtended (Oid fwdid, bits16 flags)” API has been added.

GetForeignServerExtended

“ForeignServer * GetForeignServerExtended (Oid fwdid, bits16 flags)” API has been added.

3.1.13 Transaction ID

The 64-bit transaction ID is available. API “GetTopFullTransactionId”, “GetTopFullTransactionIdIfAny”, “GetCurrentFullTransactionId” and “GetCurrentFullTransactionIdIfAny” are provided. However, the current heap does not use them.

3.1.14 Client environment variable

Environment variables PG_COLOR and PG_COLORS have been added. Specify whether to use the color of the diagnostic message in PG_COLOR. Possible values are always, auto or never. PG_COLORS is specified by combining the category of escape sequence and code with an equal sign (=). When specifying multiple categories, separate them with a colon (:).

Category	Default value	Note
error	01;31
warning	01;35
locus	01

Table 15 Environment variable PG_COLORS settings

PDF page 31

3.1.15 Client connection string

Tcp_user_timeout indicating TCP timeout has been added to the client connection string.