{"schemaVersion":"1.0","type":"TechArticle","types":["Article","TechArticle"],"slug":"postgresql-19-what-s-new-and-what-to-check-before-upgrading-y4d2z","url":"https://zyvop.com/postgresql-19-what-s-new-and-what-to-check-before-upgrading-y4d2z","title":"PostgreSQL 19: What's New and What to Check Before Upgrading","subtitle":"Concurrent REPACK, plan advice, parallel autovacuum and restart-free logical replication, plus the breaking changes to check before you upgrade.","tldr":"PostgreSQL 19 arrived on November 19, 2026, adding REPACK (CONCURRENTLY), pg_plan_advice, parallel autovacuum, restart-free logical replication and WAIT FOR LSN. Here’s what shipped, what was reverted, and the breaking changes to check before you upgrade.","keywords":["Upgrades","postgresql","Logical Replication","Database Administration","PostgreSQL 19","Databases"],"entities":["Samod Alex","Upgrades","postgresql","Logical Replication","Database Administration","PostgreSQL 19","Databases","ZyVOP"],"keyTakeaways":["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."],"headings":["Headline features","1. REPACK: online bloat removal","2. Plan control: pg_plan_advice and pg_stash_advice","3. Autovacuum that keeps up","Performance","Replication and high availability","Developer features","Observability","Security","Breaking changes and upgrade gotchas","What did not make it","Upgrading","Verdict"],"outboundLinks":["https://www.postgresql.org/about/news/postgresql-19-released-3449/","https://www.postgresql.org/docs/19/release-19.html","https://www.theregister.com/databases/2026/10/16/postgres-19-delayed-after-ai-reviewers-find-more-bugs-than-humans-can-fix/5303471","https://www.snowflake.com/en/blog/engineering/postgresql-19-release-delay-feature-reverts/"],"contentText":"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 Patch 18 first (18.7 is current) and apply its post-update notes. 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). Install 19 builds of every extension you use. Rehearse on a copy: pg_upgrade --check, then a real run. Logical replication is the low-downtime alternative. 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.","contentHash":"sha256:1182e4acd1080f789b22e2a553cf3634b5a9916c3dd3dea14453cfd08eb0fbb9","authorName":"Samod Alex","authorUrl":"https://zyvop.com/author/samod","authorSameAs":[],"category":"Databases","tags":["Upgrades","postgresql","Logical Replication","Database Administration","PostgreSQL 19"],"audience":"Developers, software engineers, and students learning Databases","tone":"Instructional, practical, code-first","readingTimeMinutes":6,"wordCount":1470,"faqs":null,"primaryTopic":"Databases","publishedAt":"2026-11-20T06:16:50.838Z","updatedAt":"2026-11-20T06:16:50.838Z","canonicalUrl":"https://zyvop.com/postgresql-19-what-s-new-and-what-to-check-before-upgrading-y4d2z"}