# PostgreSQL 19 features I'm excited about

> PostgreSQL 19 features I'm excited about: planner advisor framework, DDL extraction, online REPACK, pg_stat_recovery, parallel autovacuum, sequence sync, and 64-bit MultiXact.

Tianzhou | 2026-04-30 | Source: https://www.bytebase.com/blog/postgres-19-features-im-excited-about/

---

> **Note:** This post is based on the [PostgreSQL 19 release notes draft](https://www.postgresql.org/docs/devel/release-19.html). The feature set firms up at beta freeze; we will update this post as PG 19 progresses through beta and RC.

| Update History | Comment          |
| -------------- | ---------------- |
| 2026/04/30     | Initial version. |

PostgreSQL 18 shipped asynchronous I/O as raw capability. PostgreSQL 19 is the operational counterpart, and the items below are the ones I'm most excited about. In-core plan advice closes the longest-running gap in the planner. DDL extraction lands in core. `REPACK` and checksums move out of the maintenance window. Lock contention and standby recovery state get first-class system views. The 32-bit MultiXact ceiling that has caused production outages is gone. Autovacuum becomes parallel and observable.

## pg_plan_advice

PostgreSQL's planner is excellent at the average case and occasionally wrong at the long tail. Skewed distributions, stale statistics, and many-table joins can push it onto an operationally unacceptable plan. The workarounds lived outside core: [pg_hint_plan extension](https://github.com/ossc-db/pg_hint_plan), application-level `SET enable_*` flags, third-party plan-locking extensions — each with its own semantics and lifecycle.

In February 2026, [Clerk traced an outage](https://clerk.com/blog/2026-02-19-system-outage-postmortem) to exactly this pattern: a column whose values were 99.9996% NULL, an `ANALYZE` whose sample happened to contain only NULLs, and a planner that concluded the column was 100% NULL. The plan that followed assumed zero non-null rows where there were over 17,000.

PostgreSQL 19 brings the answer inside core, in two layers.

**`pg_plan_advice`** introduces a planner-advisor framework. It generates a plan-advice string that captures the relevant plan choice — join order, scan method, etc. — and exposes a hook for other modules to inject advice at planning time. The advice can be passed inline via `EXPLAIN (PLAN_ADVICE '...')` for one-shot use.

**`pg_stash_advice`** persists plan-advice strings keyed by `(stash_name, queryId)` in dynamic shared memory and injects them automatically at planning time via the `pg_plan_advice` hook. One write. Effective across all sessions.

```sql
-- 1. Load the extension (must already be in shared_preload_libraries)
CREATE EXTENSION pg_stash_advice;

-- 2. Create a stash
SELECT pg_create_advice_stash('events_stash');

-- 3. Find the queryId
EXPLAIN (VERBOSE) SELECT * FROM events WHERE tenant_id = 42;
-- → Query Identifier: 9876543210

-- 4. Stash the advice for that queryId
SELECT pg_set_stashed_advice('events_stash', 9876543210,
    'INDEX_SCAN(events events_tenant_idx)');

-- 5. Activate the stash in this session
SET pg_stash_advice.stash_name = 'events_stash';

-- 6. Verify — the planner now picks the indexed path
EXPLAIN (COSTS OFF) SELECT count(*) FROM events
  WHERE tenant_id = 42 AND created_at > now() - interval '7 days';
-- → Index Scan using events_tenant_idx
```

Scope is controlled by the `pg_stash_advice.stash_name` GUC — `ALTER DATABASE`, `ALTER ROLE`, or session-level `SET`.

**Why it's useful:** Plan stability without application changes. One write to a stash takes effect across every session that runs the matching `queryId`, with no `pg_hint_plan` or third-party tooling. The right use case is a small set of queries with known planner regressions on production data. Applying advice has a cost even when it does not change the plan, so this is not a pattern to apply broadly across a workload.

## DDL extraction function

PostgreSQL has never shipped a way to pull the `CREATE` statement for a database, role, or tablespace out of the catalog. The data is there. The DDL is not. Every team that needs it writes its own extractor: a shell loop around `pg_dump --schema-only`, a hand-rolled catalog query inside a migration tool, a schema-management platform like Bytebase that reads the catalog directly and assembles the DDL. The implementations differ. The maintenance burden does not — every major Postgres release brings new catalog columns and new attribute encodings to absorb.

PostgreSQL 19 replaces those bespoke layers with a stable, in-core function-call contract. Three functions, one option shape, returning the DDL as text:

```sql
SELECT pg_get_database_ddl('mydb'::regdatabase);
SELECT pg_get_role_ddl('myrole'::regrole);
SELECT pg_get_tablespace_ddl('mytbs');
```

All three accept `pretty := true`. `pg_get_database_ddl()` additionally accepts `owner := false` and `tablespace := false` to suppress those clauses when the target environment differs.

**Why it's useful:** A stable, in-core contract replaces every team's bespoke extractor. Migration prep, CI/CD schema diffs, audit snapshots of role grants and database properties, embedded calls from schema-management tools — all stop parsing `pg_dump` output.

These functions complement `pg_dump` rather than replace it: single-object only, no dependency ordering, no indexes, triggers, or constraints. Permissions are scoped: `CONNECT` on the database, `USAGE` on the tablespace, superuser or self for roles.

## Online maintenance

Two long-standing maintenance pain points shrink. Both used to need a window.

**`REPACK` and `REPACK CONCURRENTLY`.** A single in-core command consolidates `VACUUM FULL`, `CLUSTER`, and the third-party `pg_repack` extension. With `CONCURRENTLY`, the rebuild runs without an `ACCESS EXCLUSIVE` lock — concurrent reads and writes proceed against the original heap while the new one is built and switched. `max_repack_replication_slots` controls the slot pool that backs the concurrent variant.

```sql
REPACK TABLE my_table;
REPACK TABLE my_table CONCURRENTLY;
REPACK INDEX my_index CONCURRENTLY;
```

**Online data checksum enable/disable.** Toggling checksums no longer requires stopping the cluster and running `pg_checksums` against a halted data directory. The change applies progressively in the running cluster.

**Why it's useful:** Both items remove a class of "schedule a window, hope nothing breaks" operations.

`REPACK CONCURRENTLY` deserves the same caution as any logical-decoding-backed feature: it consumes a replication slot, so a slow consumer or stuck transaction will hold WAL. Pin `max_repack_replication_slots` to a reasonable upper bound and monitor slot lag during long repacks. Online checksum toggling is the safer of the two — failure mode is a clean abort, not a locked-out cluster.

## Observability

Two diagnostic gaps close in 19. Lock history and standby recovery state get first-class system views.

### Locks

`pg_stat_lock` exposes per-lock-type statistics: counts and waits broken out by lock mode. `pg_locks` shows the snapshot. `pg_stat_lock` shows the history.

`log_lock_waits` is on by default. Long lock waits are now logged out of the box. Threshold and noise floor are unchanged — operators no longer need to flip the GUC in every new install.

**Why it's useful:** The combination matters more than either piece alone. `pg_locks` answers "who is blocked right now." `pg_stat_lock` answers "where is contention concentrated over time." The first informs immediate intervention. The second informs schema and indexing decisions. Build a dashboard query against `pg_stat_lock` filtered by relation and mode — most contention surprises sit in two or three hot tables.

### Recovery

Standby monitoring meant calling functions in sequence — `pg_last_wal_replay_lsn()`, `pg_last_wal_receive_lsn()`, `pg_get_wal_replay_pause_state()`, `pg_last_xact_replay_timestamp()` — and reconciling the results. Each call was its own snapshot. The answer to "where is this standby right now?" was a composite, not a reading.

PostgreSQL 19 adds `pg_stat_recovery`, a single system view that snapshots all of it from shared memory in one read. Fields cover:

- Last replayed WAL position and timeline
- End position and timeline of the WAL record currently being replayed
- Current WAL chunk start time
- Promotion trigger state
- Last commit/abort timestamp — the freshness signal for replication lag
- Recovery pause state

```sql
SELECT * FROM pg_stat_recovery;
```

The view returns no rows on a primary. Read access requires the `pg_read_all_stats` role.

**Why it's useful:** The atomic snapshot is the part that matters. Previously, when alerts fired on "replica too far behind," distinguishing real lag from a query that raced with a state transition meant correlating timestamps across multiple function calls. One view collapses that into a coherent reading for dashboards and runbooks. The current-WAL-chunk-start-time field has no prior function equivalent — it diagnoses standbys stuck mid-record rather than between records.

## Logical replication: sequence sync

Logical replication has reproduced table data faithfully since 10. It has not reproduced the sequences backing `SERIAL` and `IDENTITY` columns. Promote a subscriber to primary and the next `INSERT` triggers a primary-key violation against rows the application thought it owned.

PostgreSQL 19 closes the gap. Publications gain sequence support, a new `REFRESH SEQUENCES` subcommand triggers the copy, and a `sequencesync` worker batches the work.

```sql
CREATE PUBLICATION upgrade_pub FOR ALL TABLES, ALL SEQUENCES;

ALTER SUBSCRIPTION upgrade_sub REFRESH SEQUENCES;
```

`pg_subscription_rel.srsubstate` exposes per-sequence progress (`i` = INIT, `r` = READY).

**Why it's useful:** Zero-downtime upgrades and failover get safer. The behavioral catch is that sequences sync only when `REFRESH SEQUENCES` runs — they do not track continuously. Build the call into cutover runbooks, immediately before promoting the subscriber. For the deeper walkthrough including `pg_subscription_rel` query patterns, see [Postgres 19 Feature Preview: Sequence Synchronization for Logical Replication](/blog/postgres-19-feature-preview-logical-replication-sequence).

## MultiXact ceiling, eliminated

PostgreSQL tracks row-level locks shared across transactions in MultiXact structures. The pointer into the member array — MultiXactOffset — was 32 bits, capping total members at roughly 4 billion. Transaction-ID wraparound is well-known and widely monitored. MultiXact member exhaustion is not. It does not show up on standard XID-consumption dashboards. High-concurrency workloads with foreign keys can hit it in days, not years.

In May 2025, [Metronome experienced four separate outages](https://metronome.com/blog/root-cause-analysis-postgresql-multixact-member-exhaustion-incidents-may-2025) from this exact ceiling during a data migration, requiring hours of emergency vacuuming on a 30TB cluster.

PostgreSQL 19 widens MultiXact members to 64 bits. The ceiling is gone — not raised, eliminated.

**Why it's useful:** The "vacuum or die" emergency for member exhaustion is gone. The aggressive anti-wraparound vacuum that ran specifically against MultiXact member space is no longer needed; routine vacuum still matters for disk reclamation. Upgrading to 19 rewrites the `pg_multixact` SLRU files via `pg_upgrade` automatically; size the upgrade window accordingly on clusters with large multixact histories. Full background and the Metronome incident analysis: [Postgres 19 Feature Preview: 64-bit MultiXactOffset](/blog/postgres-19-feature-preview-64bit-multixactoffset).

## Vacuum

Two long-standing complaints land together. Autovacuum can use parallel workers. The `pg_stat_*` views expose enough state that diagnosing a slow vacuum no longer relies on guesswork.

**Parallel autovacuum.** `autovacuum_max_parallel_workers` enables parallel index processing for autovacuum runs. Per-table tuning lives in the new `autovacuum_parallel_workers` storage parameter. Manual `VACUUM (PARALLEL N)` has worked since 13; autovacuum has been single-threaded throughout.

**Autovacuum priority is now visible.** `pg_stat_autovacuum_scores` exposes _why_ a given table is queued ahead of another. A family of `autovacuum_*_score_weight` GUCs (covering freeze, vacuum, vacuum-insert, multixact-freeze, and analyze pressure) lets operators tune the priority function. Before 19, autovacuum picked tables in discovery order; tuning amounted to changing thresholds and hoping.

**`pg_stat_progress_vacuum` gains two columns.** `started_by` distinguishes `manual` / `autovacuum` / `autovacuum_wraparound`. `mode` distinguishes `normal` / `aggressive` / `failsafe`. Routine sweep or emergency, in one column.

**Planned vs launched parallel workers logged.** `VACUUM (VERBOSE)` output and autovacuum logs gain two numbers: how many parallel workers the operation planned, and how many it actually launched. The two diverge often. `max_parallel_maintenance_workers`, `min_parallel_index_scan_size`, the eligible-index count, and runtime worker availability all gate the count. Before 19, when launched fell below planned, no log evidence told you which gate closed.

**Why it's useful:** Faster autovacuum on heavily-indexed tables, with the feedback loop to verify it actually ran the way you expect. Parallel autovacuum gates on the same conditions as manual `VACUUM (PARALLEL N)` — eligible-index count, `min_parallel_index_scan_size`, available worker slots — so verify workers actually launch.

Investigation order changes too. The old loop — raise `maintenance_work_mem`, lower `autovacuum_vacuum_cost_delay`, hope — was guesswork because the inputs were not observable. The new sequence:

1. Read `pg_stat_autovacuum_scores` to confirm priority.
2. Check the autovacuum log for planned vs launched workers. If the gap is large, the bottleneck is gating, not throughput.
3. Watch `pg_stat_progress_vacuum.mode` for `failsafe` events. A failsafe cluster needs priority weights tuned, not more workers.

## Closing thoughts

`pg_plan_advice` is the one I'm most excited about, and it closes another long-standing gap with Oracle and SQL Server. `pg_plan_advice` defines an in-core hook for injecting planner advice, which is the foundation a proper SQL Plan Management (SPM) module can be built on — automatic baseline capture, plan evolution, regression detection, the pieces Oracle SPM and SQL Server's Query Store users have taken for granted for years. PostgreSQL 19 doesn't ship SPM itself, but for the first time it ships the substrate that lets the community build one without forking the planner.

The rest of the list is the kind of steady, hard-won progress that keeps PostgreSQL ahead in production. Vacuum and MultiXact are the same [(debatable) architectural choice](https://www.cs.cmu.edu/~pavlo/blog/2023/04/the-part-of-postgresql-we-hate-the-most.html) surfacing as different problems — to implement MVCC, PostgreSQL keeps a separate version of every row an `UPDATE` touches, and a background process cleans them up. Until something like [OrioleDB](https://www.orioledb.com/) — a storage engine that implements MVCC via undo log instead of duplicating tuples — lands in core, we will keep duct-taping.

## Further Readings

- [Postgres 19 release notes draft](https://www.postgresql.org/docs/devel/release-19.html)
- [The Part of PostgreSQL We Hate the Most](https://www.cs.cmu.edu/~pavlo/blog/2023/04/the-part-of-postgresql-we-hate-the-most.html)
- [Clerk: February 2026 outage postmortem caused by query plan flip](https://clerk.com/blog/2026-02-19-system-outage-postmortem)
- [Metronome: May 2025 PostgreSQL MultiXact member exhaustion](https://metronome.com/blog/root-cause-analysis-postgresql-multixact-member-exhaustion-incidents-may-2025)