3 Architecture

PDF page 17

Describes the improved architecture and features in PostgreSQL 19.

3.1.1 Changed System Catalogs

The following catalogs and views have been changed. [228c370, 167ed80, fd5a1a0, deb6744, b71bae4, a5b5432, a850be2, 185e304, d3b6183, 96b3784, f9a09aa, f6a4c49, 3edaf29, 76b7872, e68b6ad, 5db6a34, ab40db3, 0d78952, f3c9e34, c257ba8, a36164e, 307447e, fb80f38, ac58465, 723619e, 8fe315f, 2f094e7, 3b88e50, 4019f72, 01d485b, 87f61f0]

PDF page 18

PDF page 19

PDF page 20

PDF page 21

View pg_dsm_registry_allocations

The pg_dsm_registry_allocations view provides information about Dynamic Shared Memory (DSM). This view can only be accessed by users with the SUPERUSER privilege or who are members of the pg_read_all_stats role. [167ed80]

postgres=# SELECT * FROM pg_dsm_registry_allocations ;
    name     |  type   | size
-------------+---------+------
 autoprewarm | segment |   44
(1 row)

View pg_stat_lock

The pg_stat_lock view provides cumulative information on lock durations. This view is accessible to all users. [4019f72]

PDF page 22

postgres=# SELECT locktype, waits, wait_time, fastpath_exceeded FROM
pg_stat_lock ;
     locktype     | waits | wait_time | fastpath_exceeded
------------------+-------+-----------+-------------------
 relation         |     1 |      1000 |                 0
 extend           |     0 |         0 |                 0
 frozenid         |     0 |         0 |                 0
 page             |     0 |         0 |                 0
 tuple            |     0 |         0 |                 0
 transactionid    |     0 |         0 |                 0
 virtualxid       |     0 |         0 |                 0
 spectoken        |     0 |         0 |                 0
 object           |     0 |         0 |                 0
 userlock         |     0 |         0 |                 0
 advisory         |     0 |         0 |                 0
 applytransaction |     0 |         0 |                 0
(12 rows)

View pg_stat_progress_data_checksums

The pg_stat_progress_data_checksums view allows real-time monitoring of the progress of checksum activation. [f19c0ec]

PDF page 23

postgres=# SELECT * FROM pg_stat_progress_data_checksums
WHERE datname='postgres' ;
-[ RECORD 1 ]---+---------
pid             | 155779
datid           | 5
datname         | postgres
phase           | enabling
databases_total | 0
databases_done  | 0
relations_total | 293
relations_done  | 0
blocks_total    | 162163
blocks_done     | 64146

View pg_stat_progress_repack

The pg_stat_progress_repack view allows real-time monitoring of the execution status of the REPACK statements. [ac58465]

PDF page 24

postgres=> SELECT * FROM pg_stat_progress_repack ;
-[ RECORD 1 ]--------+------------------
pid                  | 53203
datid                | 5
datname              | postgres
relid                | 16390
command              | REPACK
phase                | seq scanning heap
repack_index_relid   | 0
heap_tuples_scanned  | 4141595
heap_tuples_inserted | 4141595
heap_tuples_updated  | 0
heap_tuples_deleted  | 0
heap_blks_total      | 54055
heap_blks_scanned    | 22387
index_rebuild_count  | 0

PDF page 25

View pg_stat_recovery

The pg_stat_recovery view provides the recovery status of the standby database. This view can be retrieved by users with the SUPERUSER privilege or who have the pg_monitor role. [01d485b]

postgres=# SELECT * FROM pg_stat_recovery ;
-[ RECORD 1 ]------------+-----------------------------
promote_triggered        | f
last_replayed_read_lsn   | 0/030005D8
last_replayed_end_lsn    | 0/03000610
last_replayed_tli        | 1
replay_end_lsn           | 0/03000610
replay_end_tli           | 1
recovery_last_xact_time  | 2026-06-04 14:43:09.97718+09
current_chunk_start_time | 2026-06-04 14:45:43.83114+09
pause_state              | not paused

3.1.2 Logical Replication Enhancements

The following new features have been implemented for logical replication

PDF page 26

Sequence Replication

Sequence replication is now supported in logical replication. On the publication side, create the publication by specifying the ALL SEQUENCES clause. The names of the sequences contained in the publication are stored in the pg_publication_sequences view. [5509055]

postgres=# CREATE PUBLICATION pub1 FOR ALL SEQUENCES ;
CREATE PUBLICATION
postgres=# SELECT * FROM pg_publication_sequences ;
 pubname | schemaname | sequencename
---------+------------+--------------
 pub1    | public     | seq01
(1 row)

On the subscriber side, create a subscription after first creating the sequences to be replicated. The
target sequences are stored in the pg_subscription_rel view.

postgres=# CREATE SUBSCRIPTION sub1 CONNECTION
'host=dbsvr1 port=5432 dbname=postgres' PUBLICATION pub1 ;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION
postgres=# SELECT * FROM pg_subscription_rel ;
 srsubid | srrelid | srsubstate |  srsublsn
---------+---------+------------+------------
   16390 |   16389 | r          | 0/030659C0
(1 row)

To synchronize sequence values, execute the ALTER SUBSCRIPTION REFRESH SEQUENCES
statement on the subscriber side.

PDF page 27

postgres=# SELECT setval('seq01', 1) ;
 setval
--------
      1
(1 row)
postgres=# ALTER SUBSCRIPTION sub1 REFRESH SEQUENCES ;
ALTER SUBSCRIPTION
postgres=# SELECT nextval('seq01') ;
 nextval
---------
      11
(1 row)

SERVER clause in the CREATE SUBSCRIPTION statement

Previously, the connection information for connecting to the PUBLICATION was specified directly in the CONNECTION clause of the CREATE SUBSCRIPTION statement. In PostgreSQL 19, it is now possible to specify an existing SERVER object in the SERVER clause. [8185bb5]

postgres=# CREATE EXTENSION postgres_fdw ;
CREATE EXTENSION
postgres=# CREATE SERVER dbsvr1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS
(host 'dbsvr1', port '5432', dbname 'postgres') ;
CREATE SERVER
postgres=# CREATE USER MAPPING FOR PUBLIC SERVER dbsvr1 OPTIONS
(user 'postgres', password '<<Password>>') ;
CREATE USER MAPPING
postgres=# CREATE SUBSCRIPTION sub1 SERVER dbsvr1 PUBLICATION pub1 ;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION

PDF page 28

Specifying Exception Tables

The EXCEPT clause can be specified for the PUBLICATION that uses the ALL TABLES clause to designate tables to be excepted. The EXCEPT clause is specified in the CREATE PUBLICATION or ALTER PUBLICATION statement. The prexcept column has been added to the pg_publication_rel catalog to indicate exception tables. [5984ea8, fd36606, 493f8c6]

postgres=# CREATE PUBLICATION pub1 FOR ALL TABLES EXCEPT (TABLE extable1) ;
CREATE PUBLICATION
postgres=# SELECT * FROM pg_publication_rel ;
  oid  | prpubid | prrelid | prexcept | prqual | prattrs
-------+---------+---------+----------+--------+---------
 16414 |   16413 |   16402 | t        |        |
(1 row)

Subscription option retain_dead_tuples

The retain_dead_tuples option has been added to subscriptions. Enabling this option causes the subscriber to retain deleted tuples until the remote transaction is completed. When setting the retain_dead_tuples option to “true”, the track_commit_timestamp parameter must also be set to “true” (default: “false”). [228c370]

postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'port=5432 dbname=postgres'
PUBLICATION pub1 WITH (RETAIN_DEAD_TUPLES = TRUE) ;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION
postgres=# SELECT subname, subretaindeadtuples FROM pg_subscription ;
-[ RECORD 1 ]-------+-----
subname             | sub1
subretaindeadtuples | t

Subscription option max_retention_duration

The max_retention_duration option has been added to subscriptions. This option sets the maximum retention period for non-deletable transaction IDs in environments where the retain_dead_tuples

PDF page 29

option is enabled. Once this threshold is exceeded, the apply worker stops retaining conflict detection information. The setting for this option can be verified in the submaxretention column of the pg_subscription catalog. [a850be2]

postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'port=5432 dbname=postgres'
PUBLICATION pub1 WITH (MAX_RETENTION_DURATION = 10, RETAIN_DEAD_TUPLES =
TRUE) ;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION
postgres=# SELECT subname, submaxretention, subretentionactive FROM
pg_subscription ;
 subname | submaxretention | subretentionactive
---------+-----------------+--------------------
 sub1    |              10 | t
(1 row)

Detection of Memory Usage for Decoding

The mem_exceeded_count column has been added to the pg_stat_replication_slots view. This column indicates the number of times the memory used for logical decoding exceeded the logical_decoding_work_mem parameter. [d3b6183]

postgres=# SELECT slot_name, mem_exceeded_count FROM
pg_stat_replication_slots ;
-[ RECORD 1 ]------+-----
slot_name          | sub1
mem_exceeded_count | 0

Conflict Detection

The update_deleted conflict can now be detected when an update on a subscriber conflicts with a local deletion. To use this feature, the retain_dead_tuples option must be enabled on the subscriber. [fd5a1a0]

PDF page 30

Automatic WAL Level Change

Even if the wal_level parameter is set to its default value of “replica”, the WAL output level is automatically changed to “logical” when a logical replication slot is created. The internal parameter effective_wal_level has been added to indicate this state. When all logical replication slots are deleted, the value of the effective_wal_level parameter reverts to its original state. [67c2097]

postgres=# SHOW wal_level ;
 wal_level
-----------
 replica
(1 row)
postgres=# SHOW effective_wal_level ;
 effective_wal_level
---------------------
 replica
(1 row)
postgres=# SELECT
 pg_create_logical_replication_slot('slot1', 'test_decoding') ;
slot_name |    lsn
-----------+------------
 slot1     | 0/95033BD8
(1 row)
postgres=# SHOW effective_wal_level ;
 effective_wal_level
---------------------
 logical
(1 row)

3.1.3 Partition Table Enhancements

The following new features have been implemented in the partition table.

PDF page 31

Merging Partitions

It is now possible to merge multiple partitions into a single partition using an ALTER TABLE statement on RANGE or LIST partitioned tables. Specify the existing partitions in the MERGE PARTITIONS clause and specify the name of the new partition to be created as the destination in the INTO clause. [f2e4cc4, 713e553]

Syntax 1 - MERGE PARTITIONS clause

ALTER TABLE table_name MERGE PARTITIONS (partition#1, partition#2, …)
INTO new_partition

The following example merges partitions part1v1 and part1v2 into the part1v12 partition.

postgres=> CREATE TABLE part1(c1 INT PRIMARY KEY, c2 VARCHAR(10))
PARTITION BY RANGE(c1) ;
CREATE TABLE
postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES
FROM (0) TO (1000000) ;
CREATE TABLE
postgres=> CREATE TABLE part1v2 PARTITION OF part1 FOR VALUES
FROM (1000000) TO (2000000) ;
CREATE TABLE
postgres=> INSERT INTO part1 VALUES (generate_series(1, 1999999), 'data1') ;
INSERT 0 1999999
postgres=> ALTER TABLE part1 MERGE PARTITIONS (part1v1, part1v2) INTO part1v12 ;
ALTER TABLE

RANGE partitions whose data ranges are not contiguous cannot be merged.

PDF page 32

postgres=> CREATE TABLE part2(c1 INT PRIMARY KEY, c2 VARCHAR(10))
PARTITION BY RANGE(c1) ;
CREATE TABLE
postgres=> CREATE TABLE part2v1 PARTITION OF part2 FOR VALUES
FROM (0) TO (1000000) ;
CREATE TABLE
postgres=> CREATE TABLE part2v2 PARTITION OF part2 FOR VALUES
FROM (1000000) TO (2000000) ;
CREATE TABLE
postgres=> CREATE TABLE part2v3 PARTITION OF part2
FOR VALUES FROM (2000000) TO (3000000) ;
CREATE TABLE
postgres=> ALTER TABLE part2 MERGE PARTITIONS (part2v1, part2v3) INTO part2v13 ;
ERROR:  cannot merge partition "part2v3" together with partition "part2v1"
DETAIL:  The lower bound of partition "part2v3" is not equal to the upper bound
of partition "part2v1"
HINT:  ALTER TABLE ... MERGE PARTITIONS requires the partition bounds to be
adjacent.

Partition Splitting

It is now possible to split a partition into multiple partitions using the ALTER TABLE statement on RANGE/LIST partition tables. Specify the source partition in the SPLIT PARTITION clause of the ALTER TABLE statement, and specify the destination partitions and partitioning values in the INTO clause. [4b3d173, 713e553]

Syntax 2 - SPLIT PARTITION clause

ALTER TABLE table_name SPLIT PARTITION old_partition INTO
(PARTITION partition#1 VALUES …,
 PARTITION parttiotn#2 VALUES …)

PDF page 33

postgres=> CREATE TABLE part3(c1 INT PRIMARY KEY, c2 VARCHAR(10))
PARTITION BY RANGE(c1) ;
CREATE TABLE
postgres=> CREATE TABLE part3v1 PARTITION OF part3 FOR VALUES
FROM (0) TO (2000000) ;
CREATE TABLE
postgres=> INSERT INTO part3 VALUES (generate_series(1,1999999), 'data1') ;
INSERT 0 1999999
postgres=> ALTER TABLE part3 SPLIT PARTITION part3v1 INTO
(PARTITION part3v2 FOR VALUES FROM (0) TO (1000000),
PARTITION part3v3 FOR VALUES FROM (1000000) TO (2000000)) ;
ALTER TABLE

3.1.4 SQL Property Graph

PostgreSQL 19 introduces the PROPERTY GRAPH object, which provides graph query features. This feature implements Property Graph Queries (SQL/PGQ) - ISO/IEC 9075-16:2023. PROPERTY GRAPH objects are created using the CREATE PROPERTY GRAPH statement. It creates a table that serves as the source for graph queries and maps it to a PROPERTY GRAPH. VERTEX and NODE, as well as EDGE and RELATIONSHIP, have the same meanings. This document does not provide detailed instructions on how to use it. [2f094e7]

Syntax 3 - Creating a Property Graph

CREATE PROPERTY GRAPH name
[{VERTEX|NODE} TABLES (vertex_table_definition [, …])]
[{EDGE|RELATIONSHIP} TABLES (edge_table_definition [, …])]

PDF page 34

postgres=> CREATE TABLE member (member_id INT PRIMARY KEY, member_name TEXT) ;
CREATE TABLE
postgres=> CREATE TABLE follow (member_id1 INT, member_id2 INTEGER,
PRIMARY KEY(member_id1, member_id2)) ;
CREATE TABLE
postgres=> CREATE PROPERTY GRAPH graph1
VERTEX TABLES (member KEY (member_id) PROPERTIES (member_id, member_name))
EDGE TABLES (follow
SOURCE KEY (member_id1) REFERENCES member(member_id)
DESTINATION KEY (member_id2) REFERENCES member(member_id)) ;

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2f094e7ac691abc9d2fe0f4dcf0feac4

CREATE PROPERTY GRAPH

a6ce1d9c
Several catalogs with names beginning with "pg_prograph" have been added for the Property Graph.
Use the GRAPH_TABLE function to query the Property Graph.

postgres=> SELECT * FROM GRAPH_TABLE (graph1 MATCH
(mem1 IS member)-[follow1 IS follow]->(mem2 IS member)
-[follow2 IS follow]->(mem3 IS member)
WHERE mem1.member_id = mem3.member_id COLUMNS
(mem1.member_id, mem2.member_id)) ;
 member_id | member_id
-----------+-----------
       100 |       200
       200 |       100
(2 rows)

3.1.5 SSL/SNI

PostgreSQL now supports SSL Server Name Indication (SNI) on the server side. This feature is enabled by setting the ssl_sni parameter to “on” (the default value is “off”). To configure SNI, use the pg_hosts.conf file in the database cluster. In the pg_hosts.conf file, enter the following entries, one per line. [4f43302]

PDF page 35

3.1.6 Dynamic Checksum Configuration

It is now possible to enable or disable block checksums while the instance is running. To enable checksums, execute the pg_enable_data_checksums function; to disable them, execute the pg_disable_data_checksums function. Enabling checksums is executed by a background worker process. When enabling checksums, the pg_enable_data_checksums function allows specifying the cost_delay parameter (default value 0) and the cost_limit parameter (default value 100), which enables checksums in a behavior similar to VACUUM cost delay. Checksum enablement executes in the background, and progress can be tracked via the pg_stat_progress_data_checksums view. [f19c0ec]

PDF page 36

postgres=# SHOW data_checksums ;
-[ RECORD 1 ]--+---
data_checksums | on
postgres=# SELECT pg_disable_data_checksums() ;
-[ RECORD 1 ]-------------+-
pg_disable_data_checksums |
postgres=# SHOW data_checksums ;
-[ RECORD 1 ]--+----
data_checksums | off
postgres=# SELECT pg_enable_data_checksums(1, 110) ;
-[ RECORD 1 ]------------+-
pg_enable_data_checksums |
postgres=# SHOW data_checksums ;
-[ RECORD 1 ]--+---
data_checksums | inprogress-on

3.1.7 Roles

The pg_read_all_data and pg_write_all_data functions now support reading and writing large objects. In previous versions, the error “ERROR: permission denied for large object {OID}” occurred. [d981976]

PDF page 37

postgres=# SELECT lo_create(-1) ;
 lo_create
------------
 4294967295
(1 row)
postgres=# SELECT lo_put(4294967295, 1, E'\\xaa') ;
 lo_put
--------
(1 row)
postgres=# GRANT pg_read_all_data TO user1 ;
GRANT ROLE
postgres=# \connect postgres user1
You are now connected to database "postgres" as user "user1".
postgres=> SELECT lo_get(4294967295) ;
 lo_get
--------
 \x00aa
(1 row)

3.1.8 Libpq

The following APIs have been added or enhanced.

Connection string servicefile

The servicefile option has been added to the connection string to specify a service file. The

PGSERVICEFILE environment variable can still be specified as previously.  [092f3c6]

PDF page 38

$ psql "servicefile=/tmp/serviceA.conf service=service1"
psql (19beta1)
Type "help" for help.
postgres=#

Connection string oauth_ca_file

The oauth_ca_file option has been added to the connection string to specify an OAuth certificate. The PGOAUTHCAFILE environment variable is now available in non-debug modes as well. [9933681]

Retrieving Version Information

The get_pg_version function, which retrieves version information from the PG_VERSION file, has been added. This function is used by commands such as pg_resetwal, pg_combinebackup, pg_createsubscriber, and pg_upgrade. [cd0be13]

Other API

The following APIs have been added. [fe07100, 7508169, e533524, b8d7685]

3.1.9 I/O Processing

These are the improvements to I/O processing in PostgreSQL 19.

PDF page 39

Automatic Scaling of Asynchronous I/O Workers

The number of I/O workers now automatically increases or decreases in response to load. The number of active I/O workers is determined by the following parameters: As the I/O queue grows, the number of workers increases; if a worker remains idle for a certain period of time, it is stopped. [d1c01b7]

Enhanced Streaming I/O

PostgreSQL 19 has enhanced the scope of streaming I/O.

• VACUUM for BRIN index [a6eac22]

• Data deleting reads for hash index [bfa3c4f]

• Index reads in the Contrib module bloom [4c910f3, d841ca2]

• Index reads in the Contrib module pgstattuple [213f007]

Improvements to asynchronous I/O processing

When using io_uring with I/O methods, the behavior has been changed so that synchronous I/O is used for reads of four blocks or fewer. [a9ee668, 8ca147d, f63ca33]

3.1.10 Automatic Vacuum

The following features have been added to the automatic VACUUM processing.

Using Parallel Workers

It is now possible to use parallel workers during automatic VACUUM operations. Automatic VACUUM performs the same processing as the VACUUM (PARALLEL) statement. The number of parallel workers is determined by the autovacuum_max_parallel_workers parameter. The default value for this parameter is 0. Refer to the automatic VACUUM log to verify that parallel workers are launched.

PDF page 40

LOG:  automatic vacuum of table "postgres.public.data1": index scans: 1
…
index scan needed: 5406 pages from table (100.00% of total) had 500000 dead …
parallel workers: index vacuum: 1 planned, 1 launched in total
…

Prioritization by scoring

PostgreSQL 19 introduced the scoring system that determines the execution order of automatic VACUUM operations. It prioritizes tables that deviate the most from the thresholds used to trigger VACUUM and ANALYZE operations. Scores are determined by the newly added pg_stat_autovacuum_scores view. For example, if 100 tuples are updated by an UPDATE statement and the VACUUM threshold is 80, the score will be 1.25. The actual score is determined by multiplying this value by the following parameters, which represent their respective weights. [87f61f0]

The structure of the pg_stat_autovacuum_scores view is as follows.

PDF page 41

postgres=> SELECT * FROM pg_stat_autovacuum_scores WHERE relname='data1' ;
-[ RECORD 1 ]-------+------------------
relid               | 16385
schemaname          | public
relname             | data1
score               | 9.99000999000999
xid_score           | 2e-08
mxid_score          | 0
vacuum_score        | 4.997501249375312
vacuum_insert_score | 0
analyze_score       | 9.99000999000999
do_vacuum           | t
do_analyze          | t
for_wraparound      | f

3.1.11 Hook

The following hooks have been added for the planner.

PDF page 42

Hook planner_setup_hook, planner_shutdown_hook

The planner_setup_hook hook, which is executed when the PlannerGlobal object is initialized, and the planner_shutdown_hook hook, which is executed immediately before the object is destroyed, have been added. [94f3ad3, 0f4c8d3]

Hook joinrel_setup_hook, join_path_setup_hook

The joinrel_setup_hook hook, which is executed when creating table join relations (joinrel), and the join_path_setup_hook hook, which is executed when adding paths to a joinrel, have been added. [4020b37]

3.1.12 Faster processing

Performance improvements have been implemented through changes to processor instructions and sorting algorithms.

Sorting

Some sorting algorithms have been changed from Quick sort to Radix sort. If there are fewer than 40 items, Quick sort will be used. [ef3c3cf]

Internal functions hex_encode / hex_decode

The hex_encode and hex_decode functions now support SIMD instructions for the x86-64 and Aarch64 architectures. The larger the input value, the more effective the performance improvement is expected to be. [ec8719c]

Page Checksum

If available, AVX2 instructions are used to calculate the page checksum. [5e13b0f]

CRC32C

If the Crypto Extension is available on an Arm v8 processor, it is used for CRC32C calculations. [fbc57f2]

COPY FROM statement

Performance has been improved by using SIMD instructions to check for the presence of special characters. [e0a3a3f]

PDF page 43

3.1.13 Log Output

The byte count for full-page images is now included in the logs generated by the automatic ANALYZE operation. [ad25744]

LOG:  automatic analyze of table "postgres.public.data1"
        avg read rate: 1.486 MB/s, avg write rate: 0.255 MB/s
        buffer usage: 10994 hits, 35 reads, 6 dirtied
        WAL usage: 14 records, 3 full page images, 27293 bytes, 24576 full page
image bytes, 0 buffers full
        system usage: CPU: user: 0.03 s, system: 0.01 s, elapsed: 0.18 s

3.1.14 OAuth Authentication

The following new features have been added to the OAuth authentication feature.

File pg_hba.conf Settings

It is now possible to specify OAuth validator information in the pg_hba.conf options. The following is an example of pg_hba.conf from the manual. [b977bd3]

hostssl  postgres   admin  0.0.0.0/0  oauth issuer=https://admin.example.com \
                                            scope="pg-admin openid email" \
                                            map=oauth-email \
                                            validator=my_validator \
                                            validator.foo="admin access" \
     validator.bar=https://magic.example.com

Validator Error Output

The following parameters have been added to control how errors generated by the validator are logged. [d438a36]

PDF page 44

3.1.15 Wait Events

The following wait events have been added. [6c5c393, e05a24c, 7a39f43]