Databases

PostgreSQL 19: What's New and What to Check Before Upgrading

Concurrent REPACK, plan advice, parallel autovacuum and restart-free logical replication, plus the breaking changes to check before you upgrade.

·
6 min read
·
AI-assisted

AI tooling disclosure

Generative AI materially contributed to this article.

Human review confirmed by the author.

PostgreSQL 19: What's New and What to Check Before Upgrading

PostgreSQL 19 was released on November 19, 2026. The theme is operating Postgres at scale with less downtime: online table rebuilds, planner control, faster autovacuum, and easier replication. This post covers what shipped, what changed under you, and how to upgrade safely.

Headline features

1. REPACK: online bloat removal

REPACK replaces VACUUM FULL and CLUSTER (both remain for compatibility). It reclaims space and can reorder a table to match an index (REPACK ... USING INDEX). With CONCURRENTLY, reads and writes continue while the table is rebuilt; an ACCESS EXCLUSIVE lock is held only briefly at the end to swap in the new files.

REPACK (CONCURRENTLY) orders;

Progress shows in the new pg_stat_progress_repack view. Know the caveats before using it in production:

  • Not MVCC-safe. After the repack commits, the table can appear empty to transactions whose snapshot predates it.

  • CONCURRENTLY has prerequisites. It does not work on partitioned tables, UNLOGGED tables, materialized views, or tables lacking a primary key or index-based replica identity, and it cannot run inside a transaction block.

  • It uses logical decoding and a replication slot from the new max_repack_replication_slots pool (default 5, changeable only at server start). If no slot is free, the command fails.

  • It can fail if other sessions run DDL on the table meanwhile, or if roughly 104 million or more rows are concurrently updated or deleted.

  • Resources: you need free disk space for a full copy of the table and its indexes, and REPACK counts as one long-running transaction for wraparound purposes, which can hold back VACUUM on other tables.

2. Plan control: pg_plan_advice and pg_stash_advice

Postgres has long avoided Oracle-style hints. The new pg_plan_advice extension offers a middle path: a small "plan advice" language to stabilize a plan or experiment with alternatives. Its companion, pg_stash_advice, stores advice keyed by query ID and applies it automatically, so you can fix a regressed query without touching application code. Both are contrib modules that must be loaded (LOAD 'pg_plan_advice' per session, or shared_preload_libraries; the docs recommend preloading pg_stash_advice and restarting). The docs warn that applying advice can add a noticeable planning cost even when the plan doesn't change, so use it only where needed.

3. Autovacuum that keeps up

  • Parallel index vacuuming in autovacuum, via autovacuum_max_parallel_workers (default 0, so opt-in), the per-autovacuum-worker limit for the index vacuuming and cleanup phases. A per-table autovacuum_parallel_workers storage parameter can lower it for individual tables. The actual count is also capped by max_parallel_workers, so size that first.

  • Prioritization scoring decides which tables go first, tunable with new autovacuum_*_score_weight settings and visible in pg_stat_autovacuum_scores.

  • Queries can set all-visible bits. Table scans now mark pages in the visibility map, leaving less work for later vacuums.

Performance

  • Foreign-key checks are faster. The release announcement cites bulk inserts nearly 2x faster in testing.

  • Async I/O scales itself. With io_method = worker (default), the worker pool now grows and shrinks between io_min_workers and io_max_workers. These replace the removed io_workers.

  • Planner improvements: NOT IN converts to an anti-join when NULLs are provably absent, aggregation can happen before joins (eager aggregation), and several join and NULL-handling simplifications were added.

  • Other gains: SIMD-accelerated COPY FROM (text/CSV), radix sort, parallel TID range scans, and NOTIFY waking only relevant listeners.

Replication and high availability

  • Logical replication without a restart. With wal_level = replica, creating the first logical slot automatically raises the effective level to logical. Check it with effective_wal_level.

  • Sequence replication. Publish with CREATE PUBLICATION ... FOR ALL SEQUENCES so subscribers stay in step, which should simplify future online upgrades. New subscriptions copy initial values, and ALTER SUBSCRIPTION ... REFRESH SEQUENCES re-syncs drift. The docs state that sequence synchronization requires a publisher already running 19, so it does not help an 18 → 19 move; copy sequence values yourself at cutover.

  • Publication and subscription extras: an EXCEPT clause for FOR ALL TABLES, CREATE SUBSCRIPTION ... SERVER to reuse foreign-server connection settings, and retain_dead_tuples for detecting update/delete conflicts.

  • Read-your-writes on standbys with WAIT FOR LSN:

-- On the primary, in a separate statement AFTER your write has committed:
SELECT pg_current_wal_insert_lsn();   -- e.g. 0/3000060

-- On the standby, before reading:
WAIT FOR LSN '0/3000060' WITH (TIMEOUT '500ms');
SELECT * FROM orders WHERE id = 42;

Capture the LSN after COMMIT, not in the same statement as the write; the target must be at or after the commit record. Always set a TIMEOUT, since without one the wait is indefinite (NO_THROW returns a status instead of an error on timeout). WAIT cannot run once a transaction holds a snapshot.

  • postgres_fdw can import remote statistics during ANALYZE (import_stats option) instead of sampling rows.

Developer features

  • "Get or create" in one round trip: INSERT ... ON CONFLICT DO SELECT ... RETURNING returns the existing row on conflict (optionally locking it).

INSERT INTO tags (name) VALUES ('postgres')
ON CONFLICT (name) DO SELECT
RETURNING id, old.id IS NULL AS created;

RETURNING and a conflict target are both required. The command tag counts selected rows too, so use old.id IS NULL (via RETURNING old, available since 18) to tell whether a row was actually created. Identity values are still consumed on conflicts, so expect gaps.

  • IGNORE NULLS / RESPECT NULLS for lead(), lag(), first_value(), last_value(), nth_value().

  • COPY TO ... (FORMAT json), including a single array via FORCE_ARRAY, plus COPY TO on partitioned tables, COPY FROM with ON_ERROR SET_NULL, and skipping multiple header lines.

  • jsonpath string methods such as lower(), upper(), replace(), split_part(), and btrim().

  • Smaller additions: random(min, max) for dates and timestamps, ALTER CONSTRAINT ... [NOT] ENFORCED for CHECK constraints, error_on_null(), bytea/uuid casts, a 64-bit oid8 type, and PL/Python event triggers.

Observability

  • New views: pg_stat_lock, pg_stat_recovery, pg_stat_autovacuum_scores, pg_stat_progress_repack.

  • EXPLAIN (ANALYZE, IO) reports asynchronous I/O (auto_explain.log_io for logging), and WAL volume from full-page images is now reported in pg_stat_wal, EXPLAIN, and vacuum/analyze logs.

  • log_lock_waits is now on by default, and wraparound warnings begin at 100 million transactions remaining (was 40 million).

Security

  • SNI support: one server can present different TLS certificates per hostname, configured in the new pg_hosts.conf.

  • MD5 passwords now trigger a warning on successful login (silence with md5_password_warnings). Move to SCRAM.

  • Password expiry warnings via password_expiration_warning_threshold (default 7 days).

  • RADIUS authentication is removed. The release notes describe UDP-based RADIUS as unfixably insecure.

  • OAuth improvements: custom pg_hba.conf options for validators and an oauth_ca_file libpq parameter.

Breaking changes and upgrade gotchas

Change

Impact

io_workers and escape_string_warning removed

Leftover lines in config can stop the server from starting

standard_conforming_strings always on

Setting it to off is an error; dumps made by old tools with off won't load correctly. Use 19's pg_dump

RADIUS removed

radius entries in pg_hba.conf are invalid

JIT off by default

Enable it where large analytical queries benefit

max_locks_per_transaction default 64 → 128

Lock capacity per setting effectively halved; double any custom value

TOAST default compression lz4 (if built with it)

Affects newly written values only

wal_compression = on picks the best available method

Was always pglz before

Parallel vacuum workers share one cost limit

Throttled parallel vacuums may run slower

json_array(query) with no rows returns []

Was NULL

inet/cidr default index opclass → built-in GiST

pg_upgrade refuses clusters with btree_gist inet/cidr indexes

Newlines in database/role/tablespace names; MULE_INTERNAL encoding

Both blocked or removed; fix before upgrading

Renamed monitoring items

sync_error_count → sync_table_error_count; BufferPin wait event type → Buffer (event → BufferCleanup); BufferContent split into three events

Text search, pg_trgm, fuzzystrmatch, stemmers

Behavior changes for some locales/languages; reindex affected indexes and recompute stored tsvector values

READ ONLY status passed to postgres_fdw

Read-only transactions can no longer cause writes via foreign sessions

What did not make it

The release slipped from its usual September/October window to November 19 after late-cycle bug reports led committers to revert roughly 8 to 10 significant features. Reverted features that beta-era articles (and the Beta 1 announcement) still describe include:

  • SQL/PGQ property graph queries

  • GROUP BY ALL

  • Online enabling/disabling of data checksums (use pg_checksums on a stopped cluster as before)

  • UPDATE/DELETE ... FOR PORTION OF

  • Partition MERGE/SPLIT

Most are expected to be retried for PostgreSQL 20. If you tested against a beta or release candidate, re-verify anything that depended on them.

Upgrading

  1. Patch 18 first (18.7 is current) and apply its post-update notes.

  2. Audit for the blockers above: inet/cidr btree_gist indexes, odd object names, MULE_INTERNAL, RADIUS, removed settings (including per-role/per-database ones, which a third-party upgrade test found pg_upgrade --check does not catch).

  3. Install 19 builds of every extension you use.

  4. Rehearse on a copy: pg_upgrade --check, then a real run. Logical replication is the low-downtime alternative.

  5. Afterward: run vacuumdb --all --analyze-in-stages, update extensions, rebuild affected text-search indexes, and update dashboards for renamed views and wait events.

Also note that PostgreSQL 14 stopped receiving fixes on November 12, 2026, so anyone still on it has a clear reason to plan a move.

Verdict

PostgreSQL 19 is a strong operations release: REPACK (CONCURRENTLY), plan advice, parallel autovacuum, and restart-free logical replication each remove a long-standing pain point. The upgrade is routine for most clusters, but the removed settings, new defaults, and renamed catalog items deserve a rehearsal first.

Sources: the PostgreSQL 19 release announcement and release notes; the PostgreSQL 19 documentation for REPACK, INSERT, WAIT, pg_stash_advice, vacuum and replication settings, and logical replication of sequences; and The Register's report and Snowflake's engineering blog on the reverted features.

Comments(0)

Join the discussion by logging into your account.

No comments yet. Be the first to comment!

Samod Alex

Passionate developer sharing knowledge about modern web technologies and best practices.

Subscribe to Samod Alex's Newsletter

Direct email dispatches when new stories are published. Zero algorithms.