3 Architecture

PDF page 20

3.1.1 Modified System catalogs

The following catalogs have been changed. [d540a02, 84ad713, c037471, 3662839, 6566133, e3ce2de, c591300, ae4fdde, 90189ee, 216a784, e0b0142, e056c55, 0c67946, a4b4cc1]

PDF page 21

Details about the add the pg_stat_io views are described below. [a9c70b4, 8aaa04b, ac8d53d, ac8d53d, 0ecb87e, 093e5c5]

PDF page 22

Column name Data type Description backend_type text Type of backend process. The following values are output background worker client backend walsender standalone backend autovacuum worker autovacuum launcher background writer startup checkpointer etc. object text Type of target relation (relation, temp relation) context text Type of I/O operation (normal, vacuum, bulkread, bulkwrite) reads bigint Number of reads of the size indicated by the io_bytes column read_time double Total loading time precision writes bigint Number of writes of the size indicated in the io_bytes column write_time double Total write time precision writebacks bigint Number of units of size op_bytes which the process requested the kernel write out to storage. writeback_time double Total writeback time precision extend_time double Total Extended Time precision op_bytes bigint Number of bytes for one I/O processing hits bigint Number of cache hits evictions bigint Number of times written out from shared or local buffers so that they can be used for other purposes

PDF page 23

postgres=> SELECT backend_type, context, reads, writes FROM pg_stat_io WHERE
 backend_type='autovacuum worker' ;
   backend_type    | context  | reads | writes
-------------------+----------+-------+--------
 autovacuum worker | bulkread |     0 |      0
 autovacuum worker | normal   |   548 |      0
 autovacuum worker | vacuum   |   145 |      0
(3 rows)

Parameter track_io_timing should be set to 'on' to output read_time column, write_time column,
extend_time column and fsync_time column. Execute the pg_stat_reset_shared function to reset the
contents of the view.

postgres=# SELECT pg_stat_reset_shared('io') ;
pg_stat_reset_shared
----------------------
(1 row)

PDF page 24

3.1.2 Logical Replication Enhancements

The following features have been added to Logical Replication. [216a784, 3662839, 4826759, 1e10d49, ecb6965, c3afe8c, 89e46da, 0fdab27, 5de94a0, 9f2213a, 2666975, c9f7f92]

Parallel apply

In previous versions, the large transactions were applied after being saved to a temporary file. PostgreSQL 16 now allows transaction deltas to be applied directly by multiple worker processes. To enable this feature, specify ‘parallel’ for the option streaming in the CREATE SUBSCRIPTION statement. Along with this, the substream column of the pg_subscription catalog is changed from bool type to char type, and ‘p’ is output when ‘parallel’ is specified for the stream option of the CREATE SUBSCRIPTION statement. The maximum number of worker processes a subscription uses is determined by the parameter max_parallel_apply_workers_per_subscription.

postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'port=5432 dbname=postgres'
PUBLICATION pub1 WITH (streaming=parallel) ;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION
postgres=# SELECT subname, substream FROM pg_subscription ;
 subname | substream
---------+-----------
 sub1    | p
(1 row)

Origin option for SUBSCRIPTION

The CREATE /ALTER SUBSCRIPTION statements now accept the ‘origin’ option. This parameter specifies the type of change to request to PUBLICATION. A suborigin column has been added to the pg_subscription catalog to store this option information.

PDF page 25

Setting the origin option to ’none’ allows bi-directional replication for the same table with some restrictions.

postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'port=5432 dbname=postgres
user=postgres' PUBLICATION pub1 WITH (origin=none) ;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION
postgres=# SELECT subname, suborigin FROM pg_subscription ;
 subname | suborigin
---------+-----------
 sub1    | none
(1 row)

Run_as_owner option for SUBSCRIPTION

In previous versions of logical replication, SUBSCRIPTION updated the table with the privileges of the SUBSCRIPTION owner. By default, in PostgreSQL 16, apply is done with the privileges of the table owner. Setting the run_as_owner option to ’true’ allows the same behavior as in previous versions. The subrunasowner column has been added to the pg_subscription catalog to store the information of this option.

BINARY transfer for initial data synchronization

When both PUBLICATION / SUBSCRIPTION are PostgreSQL 16 or higher and binary option of the SUBSCRIPTION is enabled, initial data is now transferred in binary format. The following example is the COPY statement log executed for initial data synchronization on the PUBLICATION side.

LOG:  statement: COPY public.data1 (c1, c2) TO STDOUT WITH (FORMAT binary)
LOG:  statement: COMMIT

Index with REPLICA IDENTITY FULL

Even in an environment where REPLICA IDENTITY and primary keys cannot be used at the publisher, the subscriber can use indexes other than primary keys and REPLICA IDENTITY. The

PDF page 26

indexes that can be used are B-tree indexes, not partial indexes, and require at least one column reference.

SUBSCRIPTION creation role

General users who are granted the pg_create_subscription role will be able to create SUBSCRIPTIONs. However, the password is required in the CONNECTION clause. Only users with the SUPERUSER attribute can turn off the password_required attribute. The subpasswordrequired column has been added to the pg_subscription catalog with this option.

postgres=# GRANT pg_create_subscription TO demo ;
GRANT ROLE
postgres=# \connect postgres demo
You are now connected to database "postgres" as user "demo".
postgres=> CREATE SUBSCRIPTION sub1 CONNECTION 'port=5432 dbname=postgres'
PUBLICATION pub1 ;
ERROR:  password is required
DETAIL:  Non-superusers must provide a password in the connection string.
postgres=> CREATE SUBSCRIPTION sub1 CONNECTION 'port=5432 dbname=postgres
 user=postgres' PUBLICATION pub1 WITH (password_required=false) ;
ERROR:  password_required=false is superuser-only
HINT:  Subscriptions with the password_required option set to false may only be
created or modified by the superuser.

Logical decoding on standby instance

Logical decoding can now be executed on the standby instance. The pg_create_logical_replication_slot function can be executed on the standby instance. To speed up the creation of logical replication slots on the standby instance, the pg_log_standby_snapshot function can be executed on the primary instance.

PDF page 27

postgres=# SELECT pg_log_standby_snapshot() ;
 pg_log_standby_snapshot
-------------------------
 0/5000098
(1 row)

Replication mode

Added the parameter debug_logical_replication_streaming that allows changing the transfer mode for logical decoding. The default value is ‘buffered’. This parameter can have the following values:

Value Description

Conflict detection

Add confl_active_logicalslot column to the pg_stat_database_conflicts view to detect conflicts such as when WAL required for a subscriber is dropped on a standby instance. This column outputs the number of uses canceled in logical replication slots.

ALTER TABLE REPLICA IDENTITY statement

The ALTER TABLE REPLICA IDENTITY USING statement now succeeds even if the index is INVALID. This is a fix to avoid errors due to the order in which the pg_dump command outputs table definitions.

PDF page 28

3.1.3 Parallel Query Enhancements

There are more syntaxes and functions for parallel query execution. [11c2d6f, 16fd03e]

Parallel Hash Full Join

Parallel query is now supported for RIGHT OUTER JOIN and FULL OUTER JOIN syntax. This behavior can be controlled with the parameter enable_parallel_hash.

postgres=> EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM data1 d1 FULL OUTER JOIN
data2 d2 USING (c1) ;
                         QUERY PLAN
-------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 2
         ->  Partial Aggregate
               ->  Parallel Hash Full Join
                     Hash Cond: (d1.c1 = d2.c1)
                     ->  Parallel Seq Scan on data1 d1
                     ->  Parallel Hash
                           ->  Parallel Seq Scan on data2 d2
(9 rows)

Functions

Aggregation processing can now be executed in parallel when executing the string_agg and array_agg functions. Partial Aggregate is output in the execution plan when the parallel query is executed.

PDF page 29

postgres=> EXPLAIN SELECT string_agg(c2, ':') FROM data1 ;
                                       QUERY PLAN
------------------------------------------------------------------------------
Finalize Aggregate  (cost=11614.56..11614.57 rows=1 width=32)
   ->  Gather  (cost=11614.34..11614.55 rows=2 width=32)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=10614.34..10614.35 rows=1 width=32)
               ->  Parallel Seq Scan on data1  (cost=0.00..9572.67 rows=416667
width=6)
(5 rows)

3.1.4 Configuration Files

Some configuration files have been written better. [8fea868, efb6f4a, a54b658, efb6f4a]

Regular expression for database name and username

The pg_hba.conf file now supports regular expressions for database names and user names. Usernames and database names starting with a slash (/) are considered regular expressions.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    /^demodb[1-3]   all             192.168.1.0/24          scram-sha-256
host    demodb4         /^user.*$       192.168.1.0/24          scram-sha-256

Regular expressions can also be used in database user entries in the pg_ident.conf file.

Include another file

The pg_hba.conf and the pg_ident.conf files can now include other files just as the postgresql.conf file can.

PDF page 30

$ cat pg_hba.conf
include pg_hba_1.conf
include_if_exists pg_hba_2.conf
include_dir hba_dir

The file_name column that indicates the configuration file name has been added to the
pg_hba_file_rules and the pg_ident_file_mappings catalogs.

Pg_ident.conf file

The same setting as in the pg_hba.conf file is allowed for the PostgreSQL user name part as follows.

• ‘all’ for all database users

• Member check using plus (+)

• Database username regular expression

3.1.5 ICU Locale

Enhanced the ability to use the ICU locale. [f3a01af, fcb21b3, c1f1c1f, 5cd1a5a, c45dc7f, 30a53b7, cd42785, 0d21d4b]

Conversion of ICU local ‘C’

If “C” is specified for the ICU locale, it will be converted to “en-US-u-va-posix”.

PDF page 31

postgres=# CREATE DATABASE icu1 TEMPLATE template0 LOCALE_PROVIDER icu
ICU_LOCALE 'C' ;
NOTICE:  using standard form "en-US-u-va-posix" for ICU locale "C"
CREATE DATABASE
postgres=# \l icu1
List of databases
-[ RECORD 1 ]-----+-----------------
Name              | icu1
Owner             | postgres
Encoding          | UTF8
Locale Provider   | icu
Collate           | C
Ctype             | C
ICU Locale        | en-US-u-va-posix
ICU Rules         |
Access privileges |

Link with locale provider

The ICU library is also by default when building from source code. The –with-icu option of the ‘configure’ command has been abolished, and the –without-icu option has been added to specify when not using ICU. However, if only –no-locale is specified in the initdb command, LIBC will be used as the locale provider as in previous versions.

Default encoding

The default encoding when using the ICU locale is UTF8.

Specify custom rules

Custom rules can now be specified for the ICU locales. Add RULES clause to the CREATE COLLATION statement and the ICU_RULES clause to the CREATE DATABASE statement to specify rules. See the URL below for the detailed syntax of the rules. https://unicode-org.github.io/icu/userguide/collation/customization/

PDF page 32

Syntax 1 - CREATE COLLATION / CREATE DATABASE statement

CREATE COLLATION [ IF NOT EXISTS ] name (
  [ RULES = rules ]
)
CREATE DATABASE name
[ WITH ] [ ICU_RULES [=] icu_rules ]

In the example below, a unique rule “&a < g” is added to the locale “en_US” when creating the database. The table to which the rule is applied is output in the order of apple, green, and bird when sorted.

postgres=# CREATE DATABASE en_db1 LOCALE_PROVIDER icu ICU_LOCALE 'en_US'
ICU_RULES '&a < g' TEMPLATE template0 ;
CREATE DATABASE
postgres=# \connect en_db1 postgres
You are now connected to database "en_db1" as user "postgres".
en_db1=# CREATE TABLE data1(c1 text) ;
CREATE TABLE
en_db1=# INSERT INTO data1 values ('apple'),('bird'),('green') ;
INSERT 0 3
en_db1=# SELECT c1 FROM data1 ORDER BY c1 ;
  c1
-------
 apple
 green
 bird
(3 rows)

The contents of custom rules can be checked in the daticurules column of the pg_database catalog,
the collicurules column of the pg_collation catalog, and the object definition of the psql command.

PDF page 33

postgres=# \l en_db1
List of databases
-[ RECORD 1 ]-----+---------
Name              | en_db1
Owner             | postgres
Encoding          | UTF8
Locale Provider   | icu
Collate           | C
Ctype             | C
ICU Locale        | en_US
ICU Rules         | &a < g
Access privileges |

New ICU locale

Add UNICODE to the ICU locale.

postgres=> SELECT * FROM pg_collation WHERE collname = 'unicode' ;
-[ RECORD 1 ]-------+--------
oid                 | 963
collname            | unicode
collnamespace       | 11
collowner           | 10
collprovider        | i
collisdeterministic | t
collencoding        | -1
collcollate         |
collctype           |
colliculocale       | und
collicurules        |
collversion         | 153.80

PDF page 34

3.1.6 Predefined Roles

PostgreSQL 16 has been added the following predefined roles: [c3afe8c, 6e2775e]

3.1.7 Wait Events

The following wait events have been added or changed. [7bae3bb, fce003c, 216a784, 5a3a953, d8cd0c6, 92daeca, 8fba928]

PDF page 35

3.1.8 Triggers

The following enhancements have been implemented for triggers: [3b00a94, 93f2349]

TRUNCATE Trigger

The TRUNCATE triggers are now executed when the TRUNCATE statement is executed for a foreign table.

Event Trigger

Execution of the ALTER MATERIALIZED VIEW statement now executes event triggers.

3.1.9 Log files

Add information that is printed to the log in some cases. [62c46ee, d977ffd, 71cb84e]

Checkpoint

The LSN information is added to the log when the parameter log_checkpoints is set to ‘on’.

LOG:  checkpoint starting: time
LOG:  checkpoint complete: wrote 44 buffers (0.3%); 0 WAL file(s) added, 0
removed, 0 recycled; write=4.134 s, sync=0.005 s, total=4.143 s; sync files=11,
longest=0.002 s, average=0.001 s; distance=258 kB, estimate=258 kB;
lsn=0/153EC70, redo lsn=0/153EC38

Auto vacuum

Add frozen table information to autovacuum logs.

PDF page 36

LOG:  automatic vacuum of table "postgres.public.data1": index scans: 1
        pages: 0 removed, 541 remain, 541 scanned (100.00% of total)
        tuples: 50000 removed, 50000 remain, 0 are dead but not yet removable
        removable cutoff: 750, which was 0 XIDs old when operation ended
        new relfrozenxid: 747, which is 1 XIDs ahead of previous value
        frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
        avg read rate: 0.414 MB/s, avg write rate: 0.829 MB/s
…

WAL

The LSN information is now outputted in error messages that occur while validating WAL headers.

3.1.10 Kerberos Credential Delegation

Supports GSSAPI/Kerberos authentication delegated by the client to the server. This allows users who authenticate to PostgreSQL with Kerberos credentials to delegate their credentials to PostgreSQL. The server will now be able to connect to other services using the delegated credentials. GSSAPI delegate acceptance is enabled by changing the parameter gss_accept_delegation to ‘on’. Sessions with delegated GSSAPI credentials can be seen in the credentials_delegated column of the pg_stat_gssapi view. This new feature adds the gssdeleg to the libpq connection string. The default setting value is ‘disable’. Specify ’enable’ in the connection string to enable GSS authentication delegation. The environment variable PGGSSDELEG can also be used instead of the connection string. This setting can also be used with the postgres_fdw module, the dblink module, etc. Add PQconnectionUsedGSSAPI as an API to check the authentication method. [6633cfb, 1f9f6aa]

Syntax 2 - PQconnectionUsedGSSAPI function

int PQconnectionUsedGSSAPI(const PGconn *conn);

3.1.11 Libpq

The following extensions have been implemented in libpq, the API that using PostgreSQL. [3a465cc, 36f40ce, 7f5b198, 8eda731, 9fcdf2c, 419a8dd, 19d8e23]

PDF page 37

Connection string require_auth

Add a connection string require_auth that specifies the list of required authentication methods. For this parameter, specify the following connection methods separated by commas (,). If there are no authentication methods that the server can provide, the connection will fail. This connection string can also be specified with the environment variable PGREQUIREAUTH.

$ grep demo data/pg_hba.conf
host   all             demo        127.0.0.1/32         scram-sha-256
$ export PGREQUIREAUTH=scram-sha-256
$ psql -h 127.0.0.1 -d postgres -U demo
Password for user demo:
psql (16.0)
Type "help" for help.
postgres=> \q
$ psql -h 127.0.0.1 -d postgres -U postgres
psql: error: connection to server at "127.0.0.1", port 5432 failed:
authentication method requirement "scram-sha-256" failed: server did not
complete authentication

Connection string sslcertmode

The connection string sslcertmode determines if the server will send the certificate from the client. This connection string can also be specified in the environment variable PGSSLCERTMODE.

PDF page 38

Connection string load_balance_hosts

The connection string load_balance_hosts automatically selects a specific instance from the destination list. This connection string can also be specified in the environment variable PGLOADBALANCEHOSTS. If the destination does not exist, another destination is automatically selected.

$ psql "load_balance_hosts=random host=localhost,localhost port=5432,5433
dbname=postgres user=postgres" -c "\conninfo"
You are connected to database "postgres" as user "postgres" on host "localhost"
(address "::1") at port "5432".
$ psql "load_balance_hosts=random host=localhost,localhost port=5432,5433
dbname=postgres user=postgres" -c "\conninfo"
You are connected to database "postgres" as user "postgres" on host "localhost"
(address "::1") at port "5433".

Connection string sslrootcert

The connection string sslrootcert can now specify ‘system’ as its configuration value. This configuration value loads the system’s trusted CA root certificate for certificate validation.

PDF page 39

COPY statement callback

The CopySendEndOfRow API now supports a callback function. This feature helps the extensions execute the COPY TO statements. A similar callback function is already provided for the COPY FROM statements.

Hook

Add a hook to change the password specified in ldapbindpasswd in the pg_hba.conf file for LDAP authentication. Update the ldap_password_hook defined in the src/backend/libpq/auth.c file.

Index Access Method

Add “bool amsummarizing;” flag to IndexAmRoutine struct. This flag indicates whether the access method should summarize tuples.

3.1.12 PL/pgSQL

Add PG_ROUTINE_OID to get OID of self-procedure. [d3d53f9]

postgres=> CREATE OR REPLACE FUNCTION current_function()
RETURNS regprocedure AS $$
DECLARE
fnc_oid regprocedure;
BEGIN
GET DIAGNOSTICS fnc_oid = PG_ROUTINE_OID;
RETURN fnc_oid;
END; $$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=> SELECT current_function() ;
  current_function
--------------------
 current_function()
(1 row)

3.1.13 MATERIALIZED VIEW

Supports Predicate Lock to prevent serialization anomaly when executing the REFRESH MATERIALIZED VIEW CONCURRENTLY statement. [4335155]

PDF page 40

3.1.14 WAL sender process

The database name of the data provider (PUBLICATION side) is now output as the process name of the WAL sender process in the logical replication environment. [af20515]

$ ps -ef|grep walsender | grep -v grep
postgres   23488   23127  0 18:10 ?        00:00:00 postgres: walsender
postgres demodb [local] START_REPLICATION

3.1.15 GIN Index cost

CPU-based cost is now taken into account as part of the GIN index cost calculation. This increases the cost estimate for using GIN indexes. [cd9479a]

3.1.16 Meson

Meson (https://mesonbuild.com/) is now available as a build system. The meson.build file is located in each directory in the source code. [e692727]

3.1.17 UNICODE

The supported Unicode version has been changed from 14.0.0 to 15.0.0. [1091b48]

3.1.18 LLVM

Supports LLVM 15. This change will also be backported to previous versions. [c2ae01f]

3.1.19 Extension

Add the @extschema:{name}@ and no_relocate options to extensions. @extschema:{name}@ can extend the existing @extschema@ functionality and insert an extended schema name. The no_relocate option specifies a list of extension names that this extension depends on. [72a5b1f]

PDF page 41

CREATE FUNCTION dep_req2() RETURNS text
BEGIN ATOMIC
SELECT @extschema:test_ext_req_schema1@.dep_req1() || ' req2';
END;

relocatable = true
requires = 'test_ext_req_schema1, test_ext_req_schema2'
no_relocate = 'test_ext_req_schema1'

3.1.20 Reduction of WAL output

Reduced amount of WAL output for FREEZE operations. [9e54059]

3.1.21 SIMD support

The following processes now support SIMD expansion and are faster. SIMD supported architectures are Intel Streaming SIMD Extensions (SSE2) and ARM Advanced SIMD (NEON). [b6ef167, 121d2d3, 56f2c7b, 82739d4, 37a6e5d]

• ASCII string check

• JSON string check

• Snapshot processing and array search