# Architecture > Architecture, catalogs, replication, execution, I/O, roles, and server internals. --- LLMS index: [llms.txt](/en/llms.txt) ---
GASource pages: PDF 20-41Original PDF on GitHub

PDF page 20

#### 3.1.1 Modified System catalogs {#pg16-3-1-1-modified-system-catalogs} The following catalogs have been changed. [[d540a02](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d540a02a724b9643205abce8c5644a0f0908f6e3), [84ad713](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=84ad713cf85aeffee5dd39f62d49a1b9e34632da), [c037471](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c037471832e1ec3327f81eebbd8892e5c1042fe0), [3662839](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=366283961ac0ed6d89014444c6090f3fd02fce0a), [6566133](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6566133c5f52771198aca07ed18f84519fac1be7), [e3ce2de](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e3ce2de09d814f8770b2e3b3c152b7671bcdb83f), [c591300](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c591300a8f54d9711157d9a8866f022a257ec4ee), [ae4fdde](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ae4fdde1352fa6b2c9123e91435efafc78c370a0), [90189ee](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=90189eefc1e11822794e3386d9bafafd3ba3a6e8), [216a784](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=216a784829c2c5f03ab0c43e009126cbb819e9b2), [e0b0142](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e0b0142959066f3a9de1c6867f4ec8d41d104f33), [e056c55](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e056c557aef4006c3dfbf8a4b94b7ae88eb9fd67), [0c67946](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0c679464a837079acc75ff1d45eaa83f79e05690), [a4b4cc1](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a4b4cc1d60f7e8ccfcc8ff8cb80c28ee411ad9a9)] **Table 7.** Add system catalogs and views | View name | Description | | --- | --- | | pg_stat_io | Provides detailed I/O statistics for database clusters. | **Table 8.** System catalogs\/views with additional columns | View name | Add column | Data type | Description | | --- | --- | --- | --- | | pg_auth_members | oid | oid | Object ID | | | inherit_option | boolean | Allow inheritance | | | set_option | boolean | Allow SET ROLE Statement | | pg_collation | collicurules | text | ICU custom rule | | pg_database | daticurules | text | ICU custom rule | | pg_hba_file_rules | rule_number | integer | Rule number | | | file_name | text | Configuration file name | | pg_ident_file_mappings | map_number | integer | Mapping number | | | file_name | text | Configuration file name | | pg_prepared_statements | result_types | regtype[] | Types of columns returned by the statement | | pg_replication_slots | conflicting | boolean | Conflict with recovery | | pg_stat_database_conflicts | confl_active_logicalslot | bigint | Number of logical slot uses that have been canceled | | pg_stat_gssapi | credentials_delegated | boolean | Is it a GSSAPI delegated session? | | pg_stat_subscription | leader_pid | integer | The ID of the parallel apply reader process | | pg_stat_*_indexes | last_idx_scan | timestamp with time zone | Index access last time | | pg_stat_*_tables | last_seq_scan | timestamp with time zone | Sequential access last time | | | last_idx_scan | timestamp with time zone | Index access last time | | | n_tup_newpage_upd | bigint | Number of rows that successor versions have moved to new heap pages | | pg_subscription | suborigin | text | Type of request to send data to PUBLICATION | | | subpasswordrequir ed | boolean | Password required for authentication | | | subrunasowner | boolean | Execution by SUBSCRIPTION owner |

PDF page 21

**Table 9.** System catalogs\/views with modified output | View name | Description | | --- | --- | | pg_attribute | The data type of attndims, attstattarget, attinhcount columns changed from integer to smallint. Also, the order of the columns has changed. | | pg_constraint | The data type of the coninhcount column has changed from integer to smallint. | | pg_database | -2 (invalid) is now output in the datconnlimit column. | | pg_locks | 'applytransaction' is now output in the locktype column. | | pg_stat_activity | Now standalone backends are output in the backend_type column. | | pg_stat_subscription | A tuple is created immediately after the SUBSCRIPTION is created. In previous versions, it was created when the first stat was received. | | pg_subscription | Substream column changed from bool type to char type. 'p' is output when applying in parallel. | Details about the add the pg_stat_io views are described below. [[a9c70b4](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a9c70b46dbe152e094f137f7e6ba9cd3a638ee25), [8aaa04b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8aaa04b32d790da595684de58ae4fc2db96becff), [ac8d53d](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ac8d53dae5ae2914aeb022dc514826f71c7206e6), [ac8d53d](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ac8d53dae5ae2914aeb022dc514826f71c7206e6), [0ecb87e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0ecb87e1fa07a733494e35636e55807b0f3fc1ed), [093e5c5](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=093e5c57d506783a95dd8feddd9a3f2651e1aeba)]

PDF page 22

**Table 10.** The pg\_stat\_io view | Column name | Data type | Description | | --- | --- | --- | | reuses | bigint | Number of times an I/O operation reused an existing ring buffer outside of shared buffers | | fsyncs | bigint | Number of fsync system calls executed | | fsync_time | double precision | fsync system call execution time | | stats_reset | timestamp with time zone | view reset time | 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

**Example 16.** Reference to the pg\_ident\_file\_mapping view\. ```text 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) ``` ```text 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. ``` **Example 17.** Reset of the pg\_stat\_io view ```text postgres=# SELECT pg_stat_reset_shared('io') ; pg_stat_reset_shared ---------------------- (1 row) ```

PDF page 24

#### 3.1.2 Logical Replication Enhancements {#pg16-3-1-2-logical-replication-enhancements} The following features have been added to Logical Replication. [[216a784](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=216a784829c2c5f03ab0c43e009126cbb819e9b2), [3662839](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=366283961ac0ed6d89014444c6090f3fd02fce0a), [4826759](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=482675987bcdffb390ae735cfd5f34b485ae97c6), [1e10d49](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1e10d49b65d6c26c61fee07999e4cd59eab2b765), [ecb6965](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ecb696527c01908d54b7a7aa2bd9179585b46459), [c3afe8c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c3afe8cf5a1e465bd71e48e4bc717f5bfdc7a7d6), [89e46da](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=89e46da5e511a6970e26a020f265c9fb4b72b1d2), [0fdab27](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0fdab27ad68a059a1663fa5ce48d76333f1bd74c), [5de94a0](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5de94a041ed7a51b571db2030ba87600c7fc6262), [9f2213a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9f2213a7c575bae43a2d41abc8b60770066ca81c), [2666975](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=26669757b6a7665c1069e77e6472bd8550193ca6), [c9f7f92](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c9f7f926484d69e2806e35343af7e472fadfede7)] ##### Parallel apply {#pg16-p24-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. **Example 18.** Setting for parallel apply ```text 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 {#pg16-p24-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. **Table 11.** The origin option setting | Value | Description | Note | | --- | --- | --- | | none | SUBSCRIPTION requests only non-origin related changes to PUBLICATION | | | any | PUBLICATION will send all changes to SUBSCRIPTION regardless of origin | Default value |

PDF page 25

Setting the origin option to 'none' allows bi-directional replication for the same table with some restrictions. **Example 19.** The origin option setting ```text 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 {#pg16-p25-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 {#pg16-p25-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. **Example 20.** Log of the COPY statements executed in initial data synchronization ```sql LOG: statement: COPY public.data1 (c1, c2) TO STDOUT WITH (FORMAT binary) LOG: statement: COMMIT ``` ##### Index with REPLICA IDENTITY FULL {#pg16-p25-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 {#pg16-p26-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. **Example 21.** SUBSCRIPTION creation role ```text 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 {#pg16-p26-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

**Example 22.** Execute pg\_log\_standby\_snapshot function \(Primary instance\) ```text postgres=# SELECT pg_log_standby_snapshot() ; pg_log_standby_snapshot ------------------------- 0/5000098 (1 row) ``` ##### Replication mode {#pg16-p27-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: **Table 12.** Replication mode setting \(PUBLISHER side\) | buffered | changed data reaches | | --- | --- | | immediate | Streaming occurs if the 'streaming' option of the CREATE SUBSCRIPTION statement is enabled. | Value Description **Table 13.** Replication mode setting \(SUBSCRIBER side\) | Value | Description | | --- | --- | | buffered | If the 'streaming' option is set to 'parallel', send data to parallel workers through a shared memory queue. | | immediate | Serializes all data to a file and notifies parallel workers to apply it at the end of the transaction. | ##### Conflict detection {#pg16-p27-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 {#pg16-p27-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 {#pg16-3-1-3-parallel-query-enhancements} There are more syntaxes and functions for parallel query execution. [[11c2d6f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=11c2d6fdf5af1aacec9ca2005543f1b0fc4cc364), [16fd03e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=16fd03e95)] ##### Parallel Hash Full Join {#pg16-p28-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. **Example 23.** Parallel Hash Full Join execution plan ```text 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 {#pg16-p28-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

**Example 24.** Execute string\_agg function ```text 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 {#pg16-3-1-4-configuration-files} Some configuration files have been written better. [[8fea868](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8fea86830e1d40961fd3cba59a73fca178417c78), [efb6f4a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=efb6f4a4f9b627b9447f5cd8e955d43a7066c30c), [a54b658](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a54b658ce77b6705eb1f997b416c2e820a77946c), [efb6f4a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=efb6f4a4f9b627b9447f5cd8e955d43a7066c30c)] ##### Regular expression for database name and username {#pg16-p29-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. **Example 25.** Pg\_hba\.conf file with regular expressions ```bash # 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 ``` ```text Regular expressions can also be used in database user entries in the pg_ident.conf file. ``` ##### Include another file {#pg16-p29-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

**Table 14.** Add syntaxes | Syntax | Description | | --- | --- | | includefile_name | Include the file | | include_if_existsfile_name | Include the file if it exists | | include_dirdirectory_name | Include all files with extension .conf under the specified directory | **Example 26.** Settings to include files ```bash $ cat pg_hba.conf include pg_hba_1.conf include_if_exists pg_hba_2.conf include_dir hba_dir ``` ```text 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 {#pg16-p30-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 {#pg16-3-1-5-icu-locale} Enhanced the ability to use the ICU locale. [[f3a01af](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f3a01af29b1941ca7937a45504ab10ef5f99cdff), [fcb21b3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fcb21b3acdcb9a60313325618fd7080aa36f1626), [c1f1c1f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c1f1c1f87fd685981c45da528649c700b6ba0655), [5cd1a5a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5cd1a5af4d17496a58678c8eb7ab792119c2d723), [c45dc7f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c45dc7ffbba2cb0bf180a0b9edadc22769143e7a), [30a53b7](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=30a53b792959b36f07200dae246067b3adbcc0b9), [cd42785](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=cd427859745af4574d75e37ec7d427ccd2b8b24a), [0d21d4b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0d21d4b9bc1f9da9dda29e5c4db0c6dd45408aaa)] ##### Conversion of ICU local 'C' {#pg16-p30-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

**Example 27.** Conversion of ICU locale \&\#x27\;C\&\#x27\; ```text 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 {#pg16-p31-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 {#pg16-p31-default-encoding} The default encoding when using the ICU locale is UTF8. ##### Specify custom rules {#pg16-p31-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/](https://unicode-org.github.io/icu/userguide/collation/customization/)

PDF page 32

#### Syntax 1 - CREATE COLLATION / CREATE DATABASE statement {#pg16-syntax-1} ```sql 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. **Example 28.** Specifying custom rules ```text 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) ``` ```text 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

**Example 29.** Check custom rules ```text 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 {#pg16-p33-new-icu-locale} Add UNICODE to the ICU locale. **Example 30.** Check UNICODE locale ```text 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 {#pg16-3-1-6-predefined-roles} PostgreSQL 16 has been added the following predefined roles: [[c3afe8c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c3afe8cf5a1e465bd71e48e4bc717f5bfdc7a7d6), [6e2775e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6e2775e4d4e47775f0d933e4a93c148024a3bc63)] **Table 15.** Add predefined roles | Role name | Description | | --- | --- | | pg_create_subscription | Allows creation of SUBSCRIPTION | | pg_use_reserved_connections | Connections assigned to parameter reserved_connections can be used | #### 3.1.7 Wait Events {#pg16-3-1-7-wait-events} The following wait events have been added or changed. [[7bae3bb](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7bae3bbf62d63cdd49ae4ca4a851cef0cdbe6ab5), [fce003c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fce003cfde219b7016140f83f67ebcfdf75aa0dc), [216a784](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=216a784829c2c5f03ab0c43e009126cbb819e9b2), [5a3a953](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5a3a95385bd5a8f1a4fd50545b7efe9338581899), [d8cd0c6](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d8cd0c6c95c0120168df93aae095df4e0682a08a), [92daeca](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=92daeca45df6551dd85f92f7369eaa57a35fb8a9), [8fba928](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8fba928fd78856712f69d96852f8061e77390fda)] **Table 16.** Add Wait Events | Event name | Type | Description | | --- | --- | --- | | DSMAllocate | IO | Waiting for dynamic shared memory allocation | | LogicalApplySendData | IPC | Waiting to complete sending data to parallel apply workers | | LogicalParallelApplyMain | Activity | Waiting for parallel application | | LogicalParallelApplyStateChange | Activity | Waiting for Parallel Apply status change | | LogicalRepLauncherDSA | LWLock | Waiting for access to the DSA memory allocator in the launcher process | | LogicalRepLauncherHash | LWLock | Waiting for access to the shared hash table of the launcher process | | RelationMapReplace | IO | Waiting for replacement of relation map file. Changed from RelationMapSync | | SLRUFlushSync | IO | Waiting for SLRU data to reach storage during checkpoint or database shutdown. | | SpinDelay | Timeout | pg_usleep execution wait time | **Table 17.** Renamed Wait Event | Old name | New name | Note | | --- | --- | --- | | HashGrowBatchesAllocate | HashGrowBatchesReallocate | | | HashGrowBucketsAllocate | HashGrowBucketsReallocate | |

PDF page 35

#### 3.1.8 Triggers {#pg16-3-1-8-triggers} The following enhancements have been implemented for triggers: [[3b00a94](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3b00a944a9b3847fb02dae7c9ea62fe0b211b396), [93f2349](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=93f2349c36a7555a81d5e26edf9a0213fb3d323a)] ##### TRUNCATE Trigger {#pg16-p35-truncate-trigger} The TRUNCATE triggers are now executed when the TRUNCATE statement is executed for a foreign table. ##### Event Trigger {#pg16-p35-event-trigger} Execution of the ALTER MATERIALIZED VIEW statement now executes event triggers. #### 3.1.9 Log files {#pg16-3-1-9-log-files} Add information that is printed to the log in some cases. [[62c46ee](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=62c46eee2279eb0300ab7ffe393d0d0dcfafb157), [d977ffd](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d977ffd923d207164eef78ed107d5293aee6c660), [71cb84e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=71cb84ec69a38444c48bd8d3b5451b2da157848b)] ##### Checkpoint {#pg16-p35-checkpoint} The LSN information is added to the log when the parameter log_checkpoints is set to 'on'. **Example 31.** Checkpoint log ```text 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 {#pg16-p35-auto-vacuum} Add frozen table information to autovacuum logs.

PDF page 36

**Example 32.** Auto vacuum log ```sql 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 {#pg16-p36-wal} The LSN information is now outputted in error messages that occur while validating WAL headers. #### 3.1.10 Kerberos Credential Delegation {#pg16-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6633cfb21691840c33816a6dacaca0b504efb895), [1f9f6aa](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1f9f6aa491986ed4e65851e827d021e9c784fd42)] #### Syntax 2 - PQconnectionUsedGSSAPI function {#pg16-syntax-2} ```sql int PQconnectionUsedGSSAPI(const PGconn *conn); ``` #### 3.1.11 Libpq {#pg16-3-1-11-libpq} The following extensions have been implemented in libpq, the API that using PostgreSQL. [[3a465cc](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3a465cc6783f586096d9f885c3fc544d82eb8f19), [36f40ce](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=36f40ce2dc66f1a36d6a12f7a0352e1c5bf1063e), [7f5b198](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7f5b19817eaf38e70ad1153db4e644ee9456853e), [8eda731](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8eda7314652703a2ae30d6c4a69c378f6813a7f2), [9fcdf2c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9fcdf2c787ac6da330165ea3cd50ec5155943a2b), [419a8dd](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=419a8dd8142afef790dafd91ba39afac2ca48aaf), [19d8e23](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=19d8e2308bc51ec4ab993ce90077342c915dd116)]

PDF page 37

##### Connection string require_auth {#pg16-p37-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. **Table 18.** Connection string require\_auth | Authentication method | Description | | --- | --- | | password | Password authentication | | md5 | Authentication with MD5 password | | gss | GSSAPI authentication | | sspi | SSPI authentication | | scram-sha-256 | SCRAM-SHA-256 authentication | | none | No required authentication method | **Example 33.** Setting the required authentication method ```text $ 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 {#pg16-p37-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

**Table 19.** Setting value | Value | Description | | --- | --- | | allow | Client certificates will be sent if the server requests them. This behavior is the same as in previous versions and is the default value. | | disable | Refuse to send even if a client certificate is available. | | require | No client certificate is sent and the connection fails. It can be useful for troubleshooting. | ##### Connection string load_balance_hosts {#pg16-p38-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. **Table 20.** Setting value | Value | Description | | --- | --- | | random | Randomly selects a connection point from multiple specified hosts and ports. | | disable | Disables random selection of destinations (default). | **Example 34.** Random selection of connection destination ```bash $ 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 {#pg16-p38-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 {#pg16-p39-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 {#pg16-p39-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 {#pg16-p39-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 {#pg16-3-1-12-pl-pgsql} Add PG_ROUTINE_OID to get OID of self-procedure. [[d3d53f9](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d3d53f955cf6ad755ba3682577e0f6fa10106438)] **Example 35.** Get PG\_ROUTINE\_OID ```text 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 {#pg16-3-1-13-materialized-view} Supports Predicate Lock to prevent serialization anomaly when executing the REFRESH MATERIALIZED VIEW CONCURRENTLY statement. [[4335155](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=43351557d0d2b9c5e20298b5fee2849abef86aff)]

PDF page 40

#### 3.1.14 WAL sender process {#pg16-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=af205152ef57cf54730c38342878cb6b9f8ce7a1)] **Example 36.** The WAL sender process name ```bash $ 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 {#pg16-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=cd9479af2af25d7fa9bfd24dd4dcf976b360f077)] #### 3.1.16 Meson {#pg16-3-1-16-meson} Meson ([https://mesonbuild.com/](https://mesonbuild.com/)) is now available as a build system. The meson.build file is located in each directory in the source code. [[e692727](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e6927270cd18d535b77cbe79c55c6584351524be)] #### 3.1.17 UNICODE {#pg16-3-1-17-unicode} The supported Unicode version has been changed from 14.0.0 to 15.0.0. [[1091b48](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1091b48cd761abc7f697fa9c4e018c46763b46fb)] #### 3.1.18 LLVM {#pg16-3-1-18-llvm} Supports LLVM 15. This change will also be backported to previous versions. [[c2ae01f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c2ae01f695b1605bc5e3908ff52b24fce6636caa)] #### 3.1.19 Extension {#pg16-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=72a5b1fc880481914da2d4233077438dd87840ca)]

PDF page 41

**Example 37.** Part of the test\_ext\_req\_schema2\-\-1\.0\.sql ```sql CREATE FUNCTION dep_req2() RETURNS text BEGIN ATOMIC SELECT @extschema:test_ext_req_schema1@.dep_req1() || ' req2'; END; ``` **Example 38.** Part of the test\_ext\_req\_schema3\.control ```text relocatable = true requires = 'test_ext_req_schema1, test_ext_req_schema2' no_relocate = 'test_ext_req_schema1' ``` #### 3.1.20 Reduction of WAL output {#pg16-3-1-20-reduction-of-wal-output} Reduced amount of WAL output for FREEZE operations. [[9e54059](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9e5405993c1e242ae6fb7561353e437241244ac1)] #### 3.1.21 SIMD support {#pg16-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b6ef167564905e8dab8af1b1dba0997ec61204ca), [121d2d3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=121d2d3d70ecdb2113b340c5f3b99a61341291af), [56f2c7b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=56f2c7b58bda00ea5ecc56a93467ed9eae6b3db7), [82739d4](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=82739d4a80f2eeb2f96b56477bb04e463ea24800), [37a6e5d](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=37a6e5df3713498a21942dae2ed3122bba5b9f50)] - ASCII string check - JSON string check - Snapshot processing and array search