PostgreSQL 19 measured on a two-node Patroni lab
Overview
PostgreSQL 19 run on a live primary and standby: lock capacity halves, WAIT holds a replica, REPACK reports progress, and JIT defaults off.
PostgreSQL 19 bootstrapped on our two-node Patroni cluster with no Patroni or template change, so we used the pair as a bench: one primary, one streaming standby, every new-version claim testable on both. The sharpest change we measured is the lock table. At max_locks_per_transaction=32, stock 18 took 3,633 exclusive table locks before out of shared memory, and stock 19 took 1,950.
WAIT held a standby until a missing write replayed, sequence values followed a subscriber, REPACK returned bytes and reported progress while it ran, and six I/O workers appeared on their own during a parallel scan. Stock 19 also changes three defaults an existing cluster inherits: JIT off, the lock default doubled to compensate, and TOAST on lz4.
Checked 20 November 2026.
The scenario
The lab is a copy of our own patroni-ha-dockerized (opens in a new tab) repository, two nodes on Patroni 4.1.5 with etcd3 and an HAProxy front. We built the image from the official postgres:<version> tag plus Patroni from pip, and both arms used the cluster template we ship. The images came from the pull-date tags, 18.7 and 19.0.
The pair came up on timeline 1 with one leader and one streaming replica:
$ patronictl -c /etc/patroni/patroni.yml list
+ Cluster: maborak (7710231585137373258) -+----+-------------+-----+------------+-----+
| Member | Host | Role | State | TL | Receive LSN | Lag | Replay LSN | Lag |
+--------+----------+---------+-----------+----+-------------+-----+------------+-----+
| db1 | db1:5431 | Leader | running | 1 | | | | |
| db2 | db2:5431 | Replica | streaming | 1 | 1/30B9F490 | 0 | 1/30B9F490 | 0 |
+--------+----------+---------+-----------+----+-------------+-----+------------+-----+
The same state from SQL, with the template’s settings in place:
$ psql -c "SELECT version();" -c "SELECT usename, application_name, client_addr, state, sync_state, sent_lsn, replay_lsn FROM pg_stat_replication;"
version
--------------------------------------------------------------------------------------------------------------------------
PostgreSQL 19.0 (Debian 19.0-1.pgdg13+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)
usename | application_name | client_addr | state | sync_state | sent_lsn | replay_lsn
------------+------------------+---------------+-----------+------------+------------+------------
replicator | db2 | 192.168.240.6 | streaming | async | 1/30B9F490 | 1/30B9F490
(1 row)
SHOW on the 19 leader (name, setting, source)
name | setting | source
---------------------------+---------+--------------------
data_checksums | on | default
default_toast_compression | lz4 | default
effective_wal_level | replica | default
io_max_workers | 8 | default
io_method | worker | default
io_min_workers | 2 | default
jit | on | configuration file
log_lock_waits | on | configuration file
max_locks_per_transaction | 512 | command line
wal_compression | zstd | configuration file
wal_level | replica | command line
docker compose -p pg19lab ps
NAME SERVICE STATUS
lab-db1 db1 Up 7 hours (healthy)
lab-db2 db2 Up 7 hours (healthy)
lab-etcd1 etcd1 Up 7 hours (healthy)
lab-etcd2 etcd2 Up 7 hours (healthy)
lab-etcd3 etcd3 Up 7 hours (healthy)
lab-haproxy haproxy Up 7 hours (healthy)
Patroni’s releases.rst (opens in a new tab) records 4.1.5 as compatible with 14.24 through 18.6, and PostgreSQL 19 reached general availability on 2026-11-19. The 19 beta1 parameter list arrived in 5ae67a7b (opens in a new tab) (2026-06-11), which is in 4.1.5; b02b3c69 (opens in a new tab) (2026-10-06) followed the beta4 data-checksum change and is not.
Plumbing needed a detour in the lab copy. The generator lists SUPPORTED_POSTGRES_VERSIONS="15 16 17 18", so make generate refuses 19; the lab bypassed the list with a sed of the build args. And create_databases.sh has carried a bare CREATE EXTENSION ... line since commit 9d48eb3 (2026-08-25), which bash runs as a command and fails post_init; we fixed it in the lab copy only. Neither is a PostgreSQL change.
We did not run failover trials, the backup tools stayed down, and the path we tested is a fresh bootstrap, not pg_upgrade from 18 (the upgrade-planning checklist covers that path).
What the release changed
| Release item | What the lab returned |
|---|---|
| Lock allocation | 3,633 / 1,950 / 3,902 / 6,606 locks; default 64 → 128 |
WAIT |
standby success in 0.061–0.233 ms; 2,004 ms timeout with replay paused |
effective_wal_level |
replica → logical → replica with a slot, no restart |
| Sequence replication | subscriber sequence 1 → 3 after REFRESH SEQUENCES |
REPACK |
68.7 MB → 34.1 MB; 391 MB → 260 MB in 1.46 s |
REPACK (CONCURRENTLY) |
195 MB → 131 MB heap, 45/45 writes survived |
| Autovacuum scoring | score 171.3; 1 parallel worker; started_by visible |
| I/O workers | 6 during a parallel scan, bounds 2–8 |
pg_plan_advice |
PLAN_ADVICE block with join and scan directives |
| SQL surface | 19 statements, from ON CONFLICT DO SELECT to oid8 |
| Defaults | JIT off, locks 128, TOAST lz4; 480 kB → 160 kB |
The lock table halves at the same setting
The release changed the default max_locks_per_transaction from 64 to 128, and its release notes (opens in a new tab) say: “Lock size allocation has changed, so effectively settings must now be doubled to match their capacity in previous releases.” We measured the rule on stock images with no Patroni and max_connections=25, using the method from Patroni’s issue #3760 (opens in a new tab): create 12,000 empty tables, then lock them one by one in a single transaction until out of shared memory (SQLSTATE 53200). Three repetitions per configuration, identical each time:
| Configuration | Locks before the error |
|---|---|
| postgres:18, max_locks_per_transaction=32 | 3,633 |
| postgres:19, max_locks_per_transaction=32 | 1,950 |
| postgres:19, max_locks_per_transaction=64 | 3,902 |
| postgres:18, max_locks_per_transaction=64 (control) | 6,606 |
The 19 build at 32 holds 0.54 of the 18 build’s capacity at the same setting, and doubling the setting on 19 landed 7 percent above the 18-at-32 point rather than exactly on it: capacity depends on more than max_locks_per_transaction. On the 18 side, 64 gave 6,606, where a pure doubling of 3,633 would give 7,266. The nominal arithmetic (max_locks times max_connections) predicts 800 entries in both arms, so the usable number is what the server accepted.
The locking loop and its inputs (postgres:18 = 18.7, postgres:19 = 19.0, pulled 2026-11-20; 12,000 empty tables created first, one CREATE TABLE per statement)
DO $x$
DECLARE
i int; n int := 0;
BEGIN
FOR i IN 1..12000 LOOP
BEGIN
EXECUTE format('LOCK TABLE t_%s IN ACCESS EXCLUSIVE MODE', i);
n := n + 1;
EXCEPTION WHEN SQLSTATE '53200' THEN
RAISE NOTICE 'locks_before_error=%', n;
RETURN;
END;
END LOOP;
RAISE NOTICE 'locks_before_error=% (no error)', n;
END $x$;
The template sets max_locks_per_transaction: 512 explicitly, and SHOW on both arms reported it with source command line, so the cluster runs with the template’s number on 19 as well. By the rule, the template’s 512 buys roughly half of what it bought on 18; we did not measure at 512, because the table count at the shipped PG_MAX_CONNECTIONS=200 would run to six figures, and the stock-build measurement carries the ratio.
Patroni passes --max_locks_per_transaction=64 on the postmaster command line when an operator sets nothing (CMDLINE_OPTIONS in patroni/postgresql/config.py), and the template’s 512 avoids that. A Patroni cluster on 19 without the parameter runs at roughly half of a stock 19 server. Patroni’s tracker reports larger absolute numbers because that context runs 100 connections; the ratios agree. Patroni has not merged the fix (#3762 (opens in a new tab)) as of 20 November.
WAIT holds a standby until the write arrives
WAIT is what read-your-writes against a replica needs. It waits for WAL to be written, flushed or replayed up to an LSN and reports success, timeout or not in recovery; the documentation (opens in a new tab) lists standby_replay, standby_write, standby_flush and primary_flush. On the lab standby, a row written on the primary and its pg_current_wal_flush_lsn() were enough. All three standby modes returned success against the reached target, in 0.233, 0.096 and 0.061 ms, and the row was there afterwards.
We paused replay on the standby, wrote a second row, and ran WAIT FOR LSN ... WITH (TIMEOUT '2s', NO_THROW): it returned timeout after 2,004 ms while the row count stayed at one. Resuming replay let the same statement return success in 0.222 ms, with the row visible. A future LSN behaved the same way, timing out at 2,004 ms.
Statuses on the two nodes
standby, reached LSN, standby_replay success 0.233 ms
standby, reached LSN, standby_write success 0.096 ms
standby, reached LSN, standby_flush success 0.061 ms
primary, reached LSN, primary_flush success
primary, reached LSN, standby_replay not in recovery
standby, primary_flush ERROR: recovery is in progress
standby, future LSN, TIMEOUT '2s' timeout 2004.041 ms
standby, replay paused, TIMEOUT '2s' timeout 2003.039 ms
The mode check runs before the wait, so NO_THROW does not cover asking a standby for primary_flush; that still raises. It must also be top-level: the same LSN inside a DO block failed. Through the HAProxy read frontend on 5432, WAIT returned success and pg_is_in_recovery() returned true, so a read through the frontend can use WAIT without a direct node connection. The PgBouncer read pool was not exercised.
Logical replication without a restart, and sequences
Creating the first logical slot raised effective_wal_level from replica to logical without a restart, and dropping the slot returned it to replica. The template keeps wal_level: replica, so a stack that grows a logical consumer picks up the heavier WAL level and sheds it again.
Sequence values now replicate too, and we ran the whole path with a stock 19 subscriber. Rows replicated first: the subscriber had 1|a, 2|b, 3|c while its own seqdemo_id_seq sat at 1, is_called = f. ALTER SUBSCRIPTION ... REFRESH SEQUENCES moved it to 3, is_called = t, and pg_get_sequence_data() reported the new page LSN. The publisher needed two publications, one FOR TABLE and one FOR ALL SEQUENCES. The synopsis (opens in a new tab) allows a table list or ALL SEQUENCES, and the mixed form is a syntax error. CREATE PUBLICATION ... FOR ALL TABLES EXCEPT (TABLE x) worked, and pg_stat_subscription_stats now separates sync_seq_error_count from sync_table_error_count.
REPACK returns bytes and reports progress
REPACK replaces VACUUM FULL and CLUSTER, which the release notes retain for compatibility. Our first measurement used a million rows: 68,681,728 bytes, unchanged after deleting half and vacuuming, then 34,136,064 bytes after REPACK, with a new relfilenode. At scale, a 6-million-row table held 391 MB in 50,000 pages; after deleting 2 million rows, REPACK (VERBOSE) rewrote it to 260 MB in 33,334 pages, 1.46 s of work. The new pg_stat_progress_repack (opens in a new tab) view follows a run phase by phase. On a 2-million-row table it reported seq scanning heap with heap_tuples_scanned climbing from 430,320 to 1,762,836 and heap_blks_scanned from 3,587 to 14,691, then rebuilding index at 2,000,045 tuples. REPACK ... USING INDEX reported index scanning heap instead.
REPACK (CONCURRENTLY) kept the table writable. On a table built with 3 million rows and a third deleted, it took the heap from 195 MB to 131 MB and the index from 64 MB to 43 MB while holding ShareUpdateExclusiveLock. We issued 45 inserts during the run; all succeeded and were present after the swap. REPACK obeys the oldest snapshot like VACUUM. When an older snapshot was pinned, it reported the dead rows as “nonremovable” and copied them.
Autovacuum gets a score, and parallel index cleanup
Autovacuum now ranks tables with a score instead of a single threshold. Five weights, one each for vacuum, insert, analyze, freeze and multixact freeze, default to 1, and pg_stat_autovacuum_scores (opens in a new tab) exposes the result. Our 8-million-row table with 2.67 million dead tuples scored 171.3, with vacuum_score 28.5, analyze_score 171.3, do_vacuum = t, do_analyze = t.
Parallel index cleanup is opt-in: autovacuum_max_parallel_workers defaults to 0. The per-table autovacuum_parallel_workers storage parameter also exists, and VACUUM (VERBOSE, PARALLEL 2) on a table with two indexes reported “launched 1 parallel vacuum worker for index cleanup (planned: 1)”, plus memory usage, which VACUUM (VERBOSE) now prints. pg_stat_progress_vacuum gained started_by and mode; we saw manual|normal|scanning heap for our own vacuum and autovacuum|normal|vacuuming indexes for the daemon’s.
I/O workers scale themselves
The old io_workers variable is gone, replaced by io_min_workers, io_max_workers, io_worker_idle_timeout and io_worker_launch_interval. Stock 19 reports 2 and 8 for the bounds, a 100 ms launch interval and a 1-minute idle timeout, with io_method = worker. During a five-way parallel scan of the 8-million-row table, pg_stat_activity showed six backends of type io worker, inside those bounds and without configuration.
EXPLAIN (ANALYZE, IO) reports the asynchronous side: each scan node printed Prefetch: avg=1.00 max=1 capacity=956. pg_stat_io lists io worker next to client backend.
Planner advice in EXPLAIN
pg_plan_advice (opens in a new tab) is a module, not an extension: the library is in the image, LOAD 'pg_plan_advice' enables it for the session, and EXPLAIN (PLAN_ADVICE) then prints the plan as a small program. On a join between the two lab tables it appended:
Generated Plan Advice:
JOIN_ORDER(r a)
MERGE_JOIN_PLAIN(a)
INDEX_ONLY_SCAN(r public.repack_conc_pkey a public.av_idx1)
GATHER((a r))
pg_stash_advice, the companion that stores advice per query ID, is an extension in pg_available_extensions and installed without complaint. The module stays off unless loaded.
The SQL surface we ran
| Change | What the lab returned |
|---|---|
INSERT ... ON CONFLICT DO SELECT ... RETURNING |
returned the existing row for a duplicate key, and FOR UPDATE works |
COPY ... TO ... (FORMAT json) |
one JSON object per row; FORCE_ARRAY wraps them in one array |
COPY ... FROM ... (HEADER 3, ON_ERROR set_null) |
skipped three header lines, loaded three rows, turned a bad integer into NULL with a NOTICE |
IGNORE NULLS on window functions |
lag and nth_value skipped NULLs |
GROUP BY with a target-list subquery |
the constant subquery was accepted |
ALTER TABLE ... CHECK ... NOT ENFORCED |
constraint recorded with convalidated = f, and a violating row inserted |
virtual column SET EXPRESSION |
the column’s CHECK survived, and 5 became 15 |
GRANT ... GRANTED BY |
the grant succeeded and appeared for the grantee |
COPY straight from a partitioned table |
partitions written without a SELECT wrapper |
oid8 |
18446744073709551615 |
bytea to uuid cast |
00112233-4455-6677-8899-aabbccddeeff |
regdatabase |
'postgres' returned OID 5 |
tid_block / tid_offset |
(1,2) returned 1 and 2 |
| jsonpath string methods | ltrim removed the leading spaces, split_part returned b, upper returned MIXED |
base64url / base32hex encodings |
AQI and 0410==== |
random(date, date) and timestamp variants |
returned values inside the given range |
range_minus_multi |
[1,10) minus [3,5) returned [1,3) and [5,10) |
error_on_null |
returned its argument, and raised “null value not allowed” on NULL |
IS JSON on a domain |
true for valid JSON; the domain’s CHECK rejected the invalid value |
Defaults that move under an existing cluster
We ran the same SHOW script on stock 18.7 and 19.0. These are the differences an upgrade brings without a configuration change:
| Setting or behaviour | 18.7 | 19.0 |
|---|---|---|
| max_locks_per_transaction | 64 | 128 |
| jit | on | off |
| default_toast_compression | pglz | lz4 |
| log_lock_waits | off | on |
| io_workers | 3 | removed |
| io_min_workers / io_max_workers | (absent) | 2 / 8 |
wal_compression with SET ... = 'on' |
reports pglz | reports zstd |
SET standard_conforming_strings = off |
accepted | error: non-standard string literals are not supported |
SET escape_string_warning = off |
accepted | unrecognized parameter |
| Buffer wait events | BufferPin, BufferContent | BufferCleanup, BufferShared, BufferShareExclusive, BufferExclusive |
| database name with a newline | accepted | rejected |
COPY FROM ... WHERE ctid ... |
accepted | rejected: system column |
| GiST default opclass for inet | gist_inet_ops (btree_gist) | inet_ops (core GiST) |
| TOAST size, 40 rows of compressible text | 480 kB | 160 kB |
The 3x TOAST difference comes from the default compression. JIT is the other default that reaches this stack, because the template sets jit: on; 19 turns it off globally after the cost estimates proved unreliable, and the template overrides that. log_lock_waits on by default will put more lines in the log of a busy cluster. wal_compression: on in the template selected pglz on 18 and now selects zstd, which SHOW confirms.
Three compatibility changes matter at an upgrade. standard_conforming_strings is forced on, so a dump from an older pg_dump that turned it off will not load. escape_string_warning is gone. The wait-event renames break dashboards that filter on BufferPin or BufferContent. pg_stat_lock gives the lock manager its own view: after a controlled wait, it reported one relation wait of 2,244.4 ms and fastpath_exceeded 0.
A role with VALID UNTIL two days out produced “role password will expire soon” and “will expire in 2 days” on a SCRAM login; the threshold is password_expiration_warning_threshold, default 7d, and it is postmaster-only. The md5 warning does not reach this stack because its pg_hba is SCRAM-only, and the removed RADIUS support was never configured here.
Sources
- PostgreSQL, PostgreSQL 19 Released! (opens in a new tab), 19 November 2026. Feature summary; I/O and JIT defaults.
- PostgreSQL, Release Notes 19 (opens in a new tab), 19 November 2026. Lock-size allocation change, defaults,
WAIT,REPACK, sequence replication, new views. - PostgreSQL, WAIT (opens in a new tab), 19 November 2026. Modes, timeout and status values.
- PostgreSQL, REPACK (opens in a new tab), 19 November 2026. Options, locking and snapshot behaviour.
- PostgreSQL, CREATE PUBLICATION (opens in a new tab) and ALTER SUBSCRIPTION (opens in a new tab), 19 November 2026.
ALL SEQUENCES,EXCEPT,REFRESH SEQUENCES. - PostgreSQL, pg_plan_advice (opens in a new tab), 19 November 2026. Module loading and
PLAN_ADVICE. - PostgreSQL, Monitoring statistics (opens in a new tab), 19 November 2026.
pg_stat_lock,pg_stat_recovery,pg_stat_progress_repack,pg_stat_autovacuum_scores. - Patroni, releases.rst (opens in a new tab), checked 20 November 2026. 4.1.5 released 2026-08-12; compatibility through 18.6.
- Patroni, issue #3760 (opens in a new tab) and pull request #3762 (opens in a new tab), checked 20 November 2026. The 19 lock-table default and its proposed fix.
- Patroni, commit 5ae67a7b (opens in a new tab) (11 June 2026) and commit b02b3c69 (opens in a new tab) (6 October 2026). PostgreSQL 19 beta parameter lists.
- Patroni, config.py at v4.1.5 (opens in a new tab), checked 20 November 2026.
CMDLINE_OPTIONSdefault formax_locks_per_transaction. - Maborak, patroni-ha-dockerized (opens in a new tab), checked 20 November 2026. Lab repository, template parameters, generator and hook script.
- Docker, postgres official image (opens in a new tab), tags 18 and 19, pulled 20 November 2026.