3 Architecture

PDF page 18

Describes the improved architecture and features in PostgreSQL 18.

3.1.1 Modified System catalogs

The following catalogs and views have been changed. [12227a1, 32d3ed8, f0d1127, 559efce, 17cc5f6, 7054186, e7a9496, 02a8d0c, a0f1fce, ca87c41, f92c854, 30a6ed0, 75eb976, a051e71, 83ea6c5, bb8dff9, ac0e331, 2421e9a, 99f8f3f, 60f566b, 89f908a, 73eba50, 8cc139b, 0d6c477]

PDF page 19

PDF page 20

PDF page 21

Pg_aios view

The pg_aios view outputs the status of the currently used asynchronous I/O handle. [60f566b]

Pg_shmem_allocations_numa view

This view shows how shared memory segments are distributed among NUMA nodes. [8cc139b]

PDF page 22

postgres=# SELECT * FROM pg_shmem_allocations_numa ;
                      name                      | numa_node |   size
------------------------------------------------+-----------+-----------
 multixact_offset                               |         0 |    139264
 subtransaction                                 |         0 |    270336
 notify                                         |         0 |    139264
 Shared Memory Stats                            |         0 |    319488
 serializable                                   |         0 |    270336
 PROCLOCK hash                                  |         0 |      4096
…

NOT NULL constraint

NOT NULL constraints on a table columns are now included in the pg_constraint catalog. The constraint name is automatically generated from the table and column names. The value of the contype column of the NOT NULL constraint is specified as ’n’. [14e87ff]

postgres=> CREATE TABLE data1(col1 INT NOT NULL, col2 VARCHAR(10)) ;
CREATE TABLE
postgres=> \d+ data1
                                                 Table "public.data1"
 Column |         Type          | Collation | Nullable | Default | Storage …
--------+-----------------------+-----------+----------+---------+---------…
 col1   | integer               |           | not null |         | plain   …
 col2   | character varying(10) |           |          |         | extended…
Not-null constraints:
    "data1_col1_not_null" NOT NULL "col1"
Access method: heap
postgres=> SELECT conname FROM pg_constraint WHERE contype='n' AND
conname LIKE 'data1%' ;
      conname
---------------------
 data1_col1_not_null
(1 row)

PDF page 23

Pg_stat_{all|user|sys}_tables view

VACUUM and ANALYZE execution times have been added to the pg_stat_{all|user|sys}_tables view. The manually executed and automatically executed cases are cumulated, respectively. [30a6ed0]

postgres=> ANALYZE data1 ;
ANALYZE
postgres=> VACUUM data1 ;
VACUUM
postgres=> SELECT relname, total_vacuum_time, total_autovacuum_time,
 total_analyze_time, total_autoanalyze_time FROM pg_stat_user_tables ;
-[ RECORD 1 ]----------+------
relname                | data1
total_vacuum_time      | 1826
total_autovacuum_time  | 14607
total_analyze_time     | 125
total_autoanalyze_time | 1475

3.1.2 Logical Replication Enhancements

The following new features have been implemented for logical replication

Log output when conflicts occur

Information about conflicts that occur when updating against the standby database are now output to the log. The reason for the conflict is output in the format “conflict={reason}”. The parameter track_commit_timestamp must be set to ‘on’ on the subscriber side (default value ‘off’) to detect the conflict reasons update_differ and delete_differ. [9758174, 73eba50]

PDF page 24

ERROR:  conflict detected on relation "public.data1": conflict=insert_exists
DETAIL:  Key already exists in unique index "data1_pkey", modified in
transaction 776.
        Key (col1)=(100); existing local tuple (100, standby1); remote tuple
(100, primary1).
CONTEXT:  processing remote data for replication origin "pg_24577" during
message type "INSERT" for replication target relation "public.data1" in
transaction 761, finished at 0/3059248

PDF page 25

Propagation of Generated Columns

The option PUBLISH_GENERATED_COLUMNS has been added to the CREATE PUBLICATION statement to determine whether the values of generated columns (GENERATED ALWAYS AS STORED specified columns) are to be replicated. The default value for this option is ‘none’, which means that the values of the generated columns will not be propagated. To propagate generated columns, specify ‘stored’ for this option. The value of the specified option is stored in the pubgencols_type column of the pg_publication catalog. [7054186, 745217a, 7054186, e65dbc9, 117f9f3]

postgres=> CREATE TABLE data1(col1 INT PRIMARY KEY,
col2 INT GENERATED ALWAYS AS (col1*2) STORED, col3 VARCHAR(10)) ;
CREATE TABLE
postgres=> CREATE PUBLICATION pub1 FOR TABLE data1(col1, col2, col3)
WITH (publish_generated_columns = stored) ;
CREATE PUBLICATION
postgres=> SELECT pubname, pubgencols FROM pg_publication ;
-[ RECORD 1 ]----
pubname    | pub1
pubgencols | s
postgres=> INSERT INTO data1(col1, col3) VALUES (100, 'data1') ;
INSERT 0 1

postgres=> CREATE TABLE data1(col1 INT PRIMARY KEY, col2 INT,
col3 VARCHAR(10)) ;
CREATE TABLE
postgres=> CREATE SUBSCRIPTION sub1 CONNECTION 'port=5432 dbname=postgres'
PUBLICATION pub1 ;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION
postgres=> SELECT * FROM data1 ;
 col1  | col2  |  col3
-------+-------+-------
   100 |   200 | data1
(1 row)

PDF page 26

3.1.3 Optimizer Statistics Management

Functions to set and clear optimizer statistics for tables and columns are now provided. Options have been added to the pg_dump, pg_dumpall, and pg_upgrade commands to use this function to migrate statistics. See “3.4 Utilities” for additional command options.

Pg_restore_relation_stats function

The pg_restore_relation_stats function sets the optimizer statistics for a table. It returns false in case of minor errors. [d32d146]

Syntax 1 - Pg_restore_relatoin_stats function

boolean pg_restore_relation_stats(VARIADIC kwargs "any")

postgres=> SELECT * FROM pg_catalog.pg_restore_relation_stats(
        'version', '180000'::integer,
        'schemaname', 'public',
        'relname', 'data1',
        'relpages', '5406'::integer,
        'reltuples', '1e+06'::real,
        'relallvisible', '0'::integer,
        'relallfrozen', '0'::integer) ;
pg_restore_relation_stats
---------------------------
 t
(1 row)

Pg_clear_relation_stats function

The function pg_clear_relation_stats clears the optimizer statistics for the specified table. Executing this function resets the corresponding column in the pg_class catalog. [e839c8e, 650ab8a]

Syntax 2 - Pg_clear_relation_stats function

void pg_clear_relation_stats(schemaname text, relname text)

PDF page 27

postgres=> SELECT pg_clear_relation_stats('public', 'data1') ;
 pg_clear_relation_stats
-------------------------
(1 row)
postgres=> SELECT relname, reltuples FROM pg_class WHERE relname='data1' ;
 relname | reltuples
---------+-----------
 data1   |        -1
(1 row)

Pg_restore_attribute_stats function

The function pg_restore_attribute_stats sets the column optimizer statistics. It returns false in case of minor errors. [d32d146]

Syntax 3 - Pg_restore_attribute_stats function

boolean pg_restore_attribute_stats(VARIADIC kwargs "any")

postgres=> SELECT * FROM pg_catalog.pg_restore_attribute_stats(
        'version', '180000'::integer,
        'schemaname', 'public',
        'relname', 'data1',
        'attname', 'col1',
        'inherited', 'f'::boolean,
        'null_frac', '0'::real,
        'avg_width', '4'::integer,
        'n_distinct', '-1'::real) ;
 pg_restore_attribute_stats
----------------------------
 t
(1 row)

PDF page 28

Pg_clear_attribute_stats function

The function pg_clear_attribute_stats clears the optimizer statistics for the specified column. Executing this function resets the corresponding column in the pg_stats catalog. [ce207d2, 650ab8a]

Syntax 4 - Pg_clear_attribute_stats function

void pg_clear_attribute_stats(schemaname text, relname text, attname text,
inherited boolean)

postgres=> SELECT pg_clear_attribute_stats('public', 'data1', 'col1', true) ;
-[ RECORD 1 ]------------+-
pg_clear_attribute_stats |

3.1.4 Roles and Permissions

The role pg_signal_autovacuum_worker has been added as a predefined role. This role allows signaling to the Autovacuum worker process. [ccd3802]

3.1.5 I/O Processing

PostgreSQL 18 adds large enhancements to the Storage I/O.

Streaming I/O coverage enhancement

Streaming I/O, introduced in PostgreSQL 17, is now more widely applicable. Streaming I/O is used in the following cases

• VACUUM statement for a table [9256822, c3e775e, 9256822, c3e775e]

• CREATE DATABASE STRATEGY=WAL_LOG statement [8720a15]

• VACUUM for B-Tree index [c5c239e]

• VACUUM for GiST index [69273b8]

• VACUUM for SP-GiST index [e215166]

• Amcheck module [043799f]

• Autoprewarm module [d9c7911]

PDF page 29

Providing Asynchronous I/O

The infrastructure for using asynchronous I/O is now in place. The parameter io_method has been added to specify the method used for asynchronous I/O. The default value of this parameter is ‘worker’, which performs asynchronous I/O by an I/O worker process; the number of I/O worker processes started is determined by the parameter io_workers (default value 3). If using Linux’s io_uring, specify io_uring for this parameter. [da72269, 55b454d, 247ce06, c325a76]

$ ps -ef | grep 'io worker' | grep -v grep
postgres  148065  148063  0 10:08 ?        00:00:00 postgres: io worker 0
postgres  148066  148063  0 10:08 ?        00:00:00 postgres: io worker 2
postgres  148067  148063  0 10:08 ?        00:00:00 postgres: io worker 1

Prefetch support

Prefetching is now possible with PostgreSQL running on macOS. [6654bb9]

3.1.6 Locale Provider

The locale PG_UNICODE_FAST has been added to the builtin locale provider. This locale uses code point sort order and conforms to Unicode’s full case mapping. However, conversions may cause changes in the number of characters, such as “ß” being lowercased to “ss”. [d3d0983, 286a365]

PDF page 30

$ initdb --locale-provider=builtin --builtin-locale=PG_UNICODE_FAST
--encoding=utf8 data
…
The database cluster will be initialized with this locale configuration:
  locale provider:   builtin
  default collation: PG_UNICODE_FAST
…

postgres=# CREATE DATABASE fastuni1 LOCALE_PROVIDER builtin
LOCALE 'PG_UNICODE_FAST' lc_collate 'C' LC_CTYPE 'C' ENCODING 'UTF8'
TEMPLATE template0 ;
CREATE DATABASE

3.1.7 OAUTH SASL

OAUTHBearer/SASL can now be used for database user authentication. To use OAUTHBearer/SASL authentication, specify ‘oauth’ in the pg_hba.conf authentication method. To use this authentication, the client module uses the libcurl library, so a binary with –with-libcurl to the ‘configure’ command is required. The parameter oauth_validator_libraries specifies the validator libraries. If this parameter is not specified, authentication by the oauth authentication method will be failed. [b3f0be7]

3.1.8 Faster hash joins

Deform processing during hash join execution has been optimized. [adf97c1]

PDF page 31

postgres=> EXPLAIN (ANALYZE, BUFFERS OFF) SELECT * FROM data1, data2
WHERE data1.col1 = data2.col1 ;
                               QUERY PLAN
------------------------------------------------------------------------------
Hash Join  (cost=32789.00..65469.01 rows=1000000 width=20) (actual time=99.8…
   Hash Cond: (data1.col1 = data2.col1)
   ->  Seq Scan on data1  (cost=0.00..15406.00 rows=1000000 width=10) (act …
   ->  Hash  (cost=15406.00..15406.00 rows=1000000 width=10) (actual time=99…
         Buckets: 262144  Batches: 8  Memory Usage: 7179kB
         ->  Seq Scan on data2  (cost=0.00..15406.00 rows=1000000 width=10) (…
 Planning Time: 0.165 ms
 JIT:
   Functions: 10
   Options: Inlining false, Optimization false, Expressions true, Deforming
true
   Timing: Generation 0.228 ms (Deform 0.112 ms), Inlining 0.000 ms,
Optimization
 0.153 ms, Emission 3.116 ms, Total 3.497 ms
 Execution Time: 389.868 ms
(12 rows)

3.1.9 Libpq

The following APIs have been added and enhanced.

Connection string

The minimum / maximum protocol version and SSL connection log file name have been added to the connection string. Corresponding environment variables have also been added. [285613c, 2da74d8]

PDF page 32

Other API

  The following APIs have been added. [4b99fed, cdb6b0f, fef6da9]

3.1.10 Access Method

The following enhancements have been added to the index access methods. [56fead4, af4002b, ce62f2f, af4002b]

3.1.11 NUMA

The basic NUMA environment-aware API, SQL functions and pg_shmem_allocations_numa view have been added. The –with-libnuma option to the ‘configure’ command is required to enable this feature. [65c298f, 8cc139b]

PDF page 33

postgres=> SELECT pg_numa_available() ;
 pg_numa_available
-------------------
 t
(1 row)

3.1.12 Accelerated numerical calculations

Acceleration of the following basic calculations and CRC32C functions are implemented.

• Faster numeric multiplication [c4e4422, c4e4422, ca481d3]

• Faster division of numeric types [9428c00]

• Intel AVX-512 use of CRC32C functions [3c6e8c1]

3.1.13 Scope of Use of Unique Key Constraints

Previously, only B-Tree indexes could be used for foreign keys, materialized views, and partition keys, but now other index access methods can be used to create indexes as long as they are unique. [bfe21b7, 9d6db8b, f278e1f]

3.1.14 Hook

The following hooks for the EXPLAIN statement have been added. [fd02bf7, 50ba65e]

• Hook explore_per_plan_hook for EXPLAIN statement

• Hook explain_per_node_hook for per-node execution

• Hook for option validation, explore_validate_options_hook