PostgreSQL 19: less friction, more control

Start with the server you actually have

Before reading a release article, ask the server which release you are actually running. SELECT 1 is also a pleasantly boring connectivity test: a result beats a spinning status indicator.

The version returned below belongs to the sandbox, not to the release covered by the article. The SQL role is student; ownership of the prepared demo database gives you room to experiment without administrative credentials.

Look for: one row, a real server version, and the expected role. Keep that baseline when you compare an upgrade later.

SELECT 1 AS terminal_is_alive, current_setting('server_version') AS actual_version, current_user AS sql_role;

One row with terminal_is_alive = 1, the installed server version, and student.

REPACK — reclaim space without a maintenance blackout

Deleting rows does not mean the table's file immediately gets smaller. Regular vacuum makes dead-row space reusable; a rewrite can return unused space to the operating system. That distinction matters when a table has grown far beyond its current workload.

PostgreSQL 19 adds native REPACK, bringing table rewrites and index-based physical ordering under one command. The concurrent form lets ordinary reads and writes continue. It still requires capacity planning: extra storage, WAL, eligible replica identity, and appropriate privileges. It is not MVCC-safe for older snapshots; concurrent is not a promise of zero operational impact. See the REPACK command and restrictions.

PostgreSQL 19 reference · requires a 19 server:

REPACK (CONCURRENTLY) events;

Here, delete 400 demo rows, vacuum, and inspect the surviving count and allocation. This demonstrates ordinary vacuum on the current image. It does not implement concurrent repacking. Re-running the delete removes zero additional rows; the count remains 600.

Look for: fewer live rows without assuming a smaller file. On a real upgrade rehearsal, measure rewrite duration, spare disk, WAL growth, and behavior around long transactions.

DELETE FROM events WHERE id > 600;

VACUUM (ANALYZE) events;

SELECT count(*) AS surviving_events, pg_size_pretty(pg_total_relation_size('events')) AS allocated_size FROM events;

DELETE 400 on the first run, VACUUM, then 600 surviving events. Size varies.

Autovacuum — give the urgent tables a turn

With many tables, vacuum has a scheduling problem as well as a cleaning problem. A large number of dead rows, a high churn ratio, and an old transaction horizon describe different kinds of urgency.

Version 19 introduces an autovacuum scoring system and parallel workers for index work. Table scans can also mark eligible pages all-visible. The routine vacuuming documentation explains how these changes fit into maintenance; the release notes name the new score weights and monitoring view.

The experiment uses invented backlog numbers, not PostgreSQL's scoring formula. It ranks by transaction age while showing churn alongside it. Notice that the table most urgent for freezing is not the table with the highest churn.

Look for: old_quiet first for freezing, tiny_busy highest for churn. Your production test should check whether high-priority tables receive attention and whether parallel index work changes CPU or I/O pressure.

WITH backlog(table_name, dead_rows, live_rows, xid_age) AS (
  VALUES ('tiny_busy', 900, 100, 1000), ('large_calm', 2000, 100000, 2000), ('old_quiet', 10, 1000, 900000)
)
SELECT table_name, round(dead_rows::numeric / live_rows, 2) AS churn_ratio,
       xid_age, rank() OVER (ORDER BY xid_age DESC) AS freeze_priority
FROM backlog ORDER BY freeze_priority;

old_quiet has freeze_priority 1; tiny_busy has the highest churn_ratio.

Plan advice — make the important query predictable

A critical query can become a critical incident when its plan changes. Start by retaining a plan that you can explain: scan type, row estimates, buffer activity, and where the time goes.

The new pg_plan_advice module can generate and apply constraints on planner choices. pg_stash_advice can persist advice keyed to query identifiers. These are supplied modules that need installation/loading configuration, not magic hints that work everywhere. Read plan advice and stored advice.

PostgreSQL 19 reference · after loading the module:

LOAD 'pg_plan_advice';
EXPLAIN (PLAN_ADVICE, COSTS OFF)
SELECT sum(amount) FROM orders WHERE customer_id = 1;

Our runnable version captures a plain plan on the current image. Advice can help preserve a proven plan, but data changes can make the same constraints harmful. Keep a baseline, review the supplied-advice feedback, and have a way to remove stale advice.

Look for: the aggregate and the access path underneath it. This small dataset is for reading a plan, not proving a performance improvement.

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT sum(amount) FROM orders WHERE customer_id = 1;

An Aggregate plan, scan details, buffer information, and execution timing.

Logical replication — remember the next ID, too

A migration that copies all your rows can still surprise you when the next insert asks for an ID. Table contents and sequence state are different pieces of the cutover.

PostgreSQL 19 adds sequence synchronization capabilities to logical replication, publication exclusions, and automatic logical-WAL enablement when configured wal_level is replica. Sequence synchronization has explicit subscription operations and conditions; do not assume a row stream continuously keeps every sequence perfectly current. See subscription behavior and publication syntax.

PostgreSQL 19 reference · administrative setup on a 19 server:

CREATE PUBLICATION app_pub FOR ALL TABLES EXCEPT scratch_events;
ALTER SUBSCRIPTION app_sub REFRESH SEQUENCES;

The terminal only compares the highest original order ID (1–500) with a separate local sequence. No publisher or subscriber is created. Each call to nextval advances the sequence, even if a surrounding transaction later rolls back.

Look for: maximum row ID 500 and a next value starting at 501. On a migration rehearsal, check tables, sequences, excluded objects, and the first post-cutover insert separately.

SELECT max(id) AS copied_max_id FROM orders WHERE id <= 500;

SELECT nextval('order_numbers') AS next_order_id;

copied_max_id = 500; next_order_id starts at 501 and increases on every run.

WAIT — read your writes on a replica

A write succeeds on the primary; the next screen reads from a replica and shows the old value. Replication is working, but the application has not stated the consistency it needs.

PostgreSQL 19's WAIT FOR LSN gives that read an explicit gate. Obtain an LSN at or after the write's commit, wait for replay on the standby, then read. Write or flush progress alone is not replay. Use a bounded timeout and handle failure. Run the wait before acquiring a snapshot or locks; a failover also requires checking timeline relevance. The WAIT documentation covers these constraints.

sequenceDiagram
  participant App
  participant Primary
  participant Replica
  App->>Primary: Write and commit
  App->>Primary: Obtain post-commit LSN
  Primary-->>App: Target LSN
  App->>Replica: WAIT FOR LSN with timeout
  Replica-->>App: Replay reached target
  App->>Replica: Read with a fresh snapshot

PostgreSQL 19 reference · on a standby, using the actual target:

WAIT FOR LSN '0/120' WITH (MODE 'standby_replay', TIMEOUT '2s');

Our SQL uses synthetic LSNs to model the gate. There is no second server and no actual wait. The first sample is behind, the second is past the target.

Look for: false then true. The important application behavior is what happens when the gate times out: retry, fall back to the primary, or explain the delay.

WITH replica_progress(replayed, target) AS (
  VALUES ('0/100'::pg_lsn, '0/120'::pg_lsn), ('0/130'::pg_lsn, '0/120'::pg_lsn)
)
SELECT replayed, target, replayed >= target AS safe_to_read,
       greatest(target - replayed, 0) AS bytes_remaining
FROM replica_progress;

First row is false with 32 bytes remaining; second is true with 0 remaining.

I/O — see the work behind the wait

“Slow query” is a symptom. Did it wait for storage, process too many rows, or do both? PostgreSQL 19 adds more visibility into asynchronous I/O through EXPLAIN (ANALYZE, IO); worker-based I/O can scale between configured minimum and maximum worker counts. Read-ahead also receives more attention. See I/O resource settings and EXPLAIN.

PostgreSQL 19 reference:

EXPLAIN (ANALYZE, IO, BUFFERS)
SELECT count(*) FROM events WHERE length(payload) > 100;

For today's sandbox, use BUFFERS without the new IO option. ANALYZE actually executes the query; our query is read-only. A tiny, warm table and an emulator are poor tools for estimating production storage gains.

Look for: buffer activity and rows processed. In your rehearsal, use representative data and record both first-run and warm-cache results. Compare useful work and I/O alongside elapsed time.

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT count(*) FROM events WHERE length(payload) > 100;

600 qualifying rows after the cleanup experiment, or 1000 before it; buffer counts and timings vary.

Foreign keys — faster inserts, same contract

Most application schemas have relationships. Every child-row insert must verify that its parent exists, so foreign-key checking is on the hot path for an ordinary order insert.

The Beta 1 announcement reported up to roughly twice the insert performance for workloads involving foreign-key checks. That is a workload-specific result, not a promise that every INSERT becomes twice as fast. Beta 4 also includes fixes in this area.

Insert a demo order, then join it to the customer. The ON CONFLICT clause makes the experiment repeatable. An insert referencing a nonexistent customer would still be rejected: faster checking preserves the constraint's job.

Look for: order 1001 attached to Ada. To evaluate the upgrade, replay realistic batches with the same relationships, transaction sizes, and contention. Do not turn off constraints to make the comparison look better.

INSERT INTO orders VALUES (1001, 1, 42) ON CONFLICT (id) DO NOTHING;

SELECT o.id, c.name, o.amount FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.id = 1001;

INSERT 0 1 on the first run, then 1001 | Ada | 42.

What did not make the cut

Two earlier headlines no longer belong on the PostgreSQL 19 feature list: online enabling/disabling of data checksums and SQL/PGQ property-graph queries. Both were reverted in Beta 4.

Online checksum changes would have made an operational task easier, but they are not a capability you should plan to use in this release. Graph-query syntax should likewise stay out of a PostgreSQL 19 migration dependency list.

The first command reads the sandbox's checksum setting. The second returns editorial labels entered in this article, not a feature-detection query. A server's catalog cannot tell you why a beta feature was removed.

Look for: the installed checksum setting and two removed-feature rows. Release notes are a moving document until release; use the final notes as the gate for your final upgrade checklist.

SHOW data_checksums;

SELECT feature, status FROM (VALUES ('online checksum toggling', 'removed in Beta 4'), ('SQL/PGQ property graphs', 'removed in Beta 4')) AS release_watch(feature, status);

Current checksum setting, followed by two explicitly labelled editorial rows.