Data Engineering · 4 min read
PostgreSQL 19 Goes GA with Online REPACK and Plan Advice
PostgreSQL 19 hits GA with online REPACK, plan advice, and WAIT FOR LSN; Lakebase ships it day-one. REPACK has a hard limit near 104 million concurrent tuple changes.
Key takeaways
- PostgreSQL 19 reached GA and is available on Lakebase the same day.
- REPACK CONCURRENTLY fails if more than ~104 million tuples are updated or deleted during the repack.
- PostgreSQL 19.1 is anticipated in February; many teams plan production upgrades around it.
- Six major features including SQL/PGQ were reverted between June and September 2026 before release.
- pg_plan_advice and pg_stash_advice let teams stabilize plans without touching application query text.

PostgreSQL 19 reached general availability today, and managed Postgres provider Lakebase made it available to customers the same day — a turnaround that often takes weeks on other managed services. Several Lakebase engineers are PostgreSQL committers and contributors who worked on the release directly, covering query plan advice, shared memory, locale handling, and release management. To use it, customers create a new Lakebase project and select PostgreSQL 19 as the version.
There is no single headline feature here. Version 19 is an operational release aimed at problems database teams already know: bloated tables, unstable query plans, replica lag, incomplete statistics, and maintenance that is hard to run safely in production. The common thread is control — more ways to intervene when defaults fall short, without giving up reliability.
What does REPACK change for table maintenance?
PostgreSQL's multiversion concurrency control (an UPDATE creates a new row version, a DELETE leaves a dead tuple) means vacuuming makes space reusable but does not always return it to the OS or restore physical row order. The traditional fixes — VACUUM FULL and CLUSTER — take an ACCESS EXCLUSIVE lock for the whole operation, blocking normal access.
REPACK reclaims space like VACUUM FULL and, with USING INDEX, physically orders rows like CLUSTER. With CONCURRENTLY, it rebuilds the table while reads and writes continue, capturing ongoing changes via logical decoding (streaming row changes out of the write-ahead log). It takes an ACCESS EXCLUSIVE lock only briefly at the end, though that phase can stretch on a busy table.
The constraints are real and data teams should plan for them:
- Concurrent repack creates an extra replication slot, needing headroom under max_repack_replication_slots, plus a non-deferrable primary key or suitable replica identity.
- It needs temporary disk for a full copy of the table and its indexes, potentially plus a sort file up to the table's size.
- The upstream implementation fails if more than roughly 104 million tuples are updated or deleted in the target table during the repack.
- It is not MVCC-safe: transactions with snapshots taken before the repack began can see the table as empty afterward.
How does plan advice stabilize bad query plans?
The cost-based optimizer picks plans from per-table statistics, which cannot capture cross-table correlations. If enterprise customers are 0.1% of customers but generate 30% of orders, the planner may pick a nested loop that probes an index repeatedly, when a hash join would be better.
PostgreSQL 19 adds two components: pg_plan_advice describes planner decisions as advice, applies supplied advice, and reports whether it was followed; pg_stash_advice stores advice by query ID and applies it automatically. EXPLAIN reports "matched" when the plan complies — but matched does not mean better; teams must still evaluate against the actual workload. Unlike inline optimizer hints, advice lives outside application query text.
What else landed in 19?
- Autovacuum can vacuum indexes with parallel workers, controlled by autovacuum_max_parallel_workers, and a new scoring system prioritizes tables needing vacuum or analysis.
- New views: pg_stat_lock, pg_stat_recovery, and pg_stat_autovacuum_scores.
- EXPLAIN (ANALYZE, IO) reports asynchronous I/O activity; pg_stat_wal, EXPLAIN (ANALYZE, WAL), and vacuum logs report full-page-image bytes written.
- Logical replication gaps close: the first valid replication slot raises effective_wal_level to logical at runtime; FOR ALL SEQUENCES synchronizes sequences; CREATE PUBLICATION ... FOR ALL TABLES EXCEPT (...) excludes named tables; CREATE SUBSCRIPTION ... SERVER targets a postgres_fdw server; and retain_dead_tuples plus track_commit_timestamp detect update_deleted conflicts, counted in pg_stat_subscription_stats.
- WAIT FOR LSN blocks a standby until replay reaches a requested WAL position, enabling read-your-writes without polling or sleeps — the client or pooler still carries the LSN.
- postgres_fdw can import remote statistics via ANALYZE, falling back to row sampling if import fails.
- INSERT ... ON CONFLICT DO SELECT gives a get-or-create pattern returning both existing and new rows, replacing the DO UPDATE SET col = EXCLUDED.col workaround that wrote a new row version on every conflict.
- COPY TO can emit JSON, one object per line by default or a single array with FORCE_ARRAY; COPY FROM does not accept it.
- Smaller items: new btrim(), lower(), replace(), split_part() variants; random(min, max) for date and timestamp types; IGNORE NULLS / RESPECT NULLS in window functions; io_method = worker with auto-scaling I/O workers; faster foreign-key checks.
Sequence synchronization matters for migrations: copy_data = true copies initial sequence values at subscription creation, but later changes are not streamed, so refresh sequences immediately before cutover.
What was cut before release?
Several features were reverted after reviewers found correctness or design problems: SQL/PGQ property graph queries (reverted September 7, 2026), UPDATE/DELETE ... FOR PORTION OF (September 15, 2026), MERGE PARTITIONS and SPLIT PARTITION (August 27, 2026), GROUP BY ALL (July 17, 2026), parallel pg_dumpall (June 18, 2026), and the pg_get_*_ddl() functions (September 13, 2026).
Should data teams upgrade now?
Lakebase itself recommends a measured approach: check changed defaults and compatibility behaviors, and confirm extension availability on 19 before planning a migration — not all extensions supported on PostgreSQL 18 are available yet. The community typically ships the first minor release a few months after a major; PostgreSQL 19.1 is anticipated in February, and many teams time production upgrades to it. That pattern, the company notes, is "just as true here" as for any .0 release.
docs.databricks.com (Original)
More from Priya Raman
Show full bio
Correspondent covering regulation, policy and their impact on Analytics Digest is a professional daily at Analytics Digest.
14 articles