--- title: "PostgreSQL 19: What's New and What to Check Before Upgrading" author: "Samod Alex" published: "2026-11-20T06:16:50.838Z" updated: "2026-11-20T06:16:50.838Z" url: "https://zyvop.com/postgresql-19-what-s-new-and-what-to-check-before-upgrading-y4d2z" canonical: "https://zyvop.com/postgresql-19-what-s-new-and-what-to-check-before-upgrading-y4d2z" tags: ["Upgrades","postgresql","Logical Replication","Database Administration","PostgreSQL 19"] --- # 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. ![Cover image]() 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. ```sql 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`: ```sql -- 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). ```sql 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.