PostgreSQL 19 vs 18: Native REPACK, pg_plan_advice, Graph Myth & Production Upgrade Guide

If PostgreSQL 18 was the storage-speed release that brought Asynchronous I/O (io_uring and worker threads) to Postgres, PostgreSQL 19 is the operational release DBAs and backend teams have been asking for since 2018.

Instead of forcing teams to bolt on external extensions for basic online maintenance or pray that a major upgrade won’t flip a critical join plan, PostgreSQL 19 ships native REPACK (CONCURRENTLY), pg_plan_advice, INSERT ... ON CONFLICT DO SELECT, parallel index autovacuum, and auto-scaling Async I/O straight in core — alongside a handful of default changes and pg_upgrade blockers that will break a blind config copy from v18.

PostgreSQL 19 vs 18 editorial cover: faceted sapphire-blue optical glass PostgreSQL elephant atop a two-tiered brushed stainless-steel pedestal in bright studio daylight.

The short verdict: should you upgrade from 18 to 19? Yes for high-churn OLTP clusters suffering from table/index bloat (REPACK (CONCURRENTLY) + parallel index autovacuum) and write-heavy APIs (ON CONFLICT DO SELECT). Just audit postgresql.conf first — leaving v18’s io_workers parameter in place triggers a FATAL startup failure, jit is now off by default, and SQL/PGQ graph queries did NOT make the final cut.

PostgreSQL 19 vs 18 at a Glance

Most upgrade decisions come down to whether the delta justifies a pg_upgrade maintenance window. Compared directly against PostgreSQL 18, version 19 shifts the needle across operations, developer SQL, and defaults:

Area PostgreSQL 18 PostgreSQL 19 Production Impact
Async I/O (AIO) Fixed io_workers (io_uring / workers, ~3× cold scan boost) Auto-scaling io_min_workers / io_max_workers + EXPLAIN (ANALYZE, IO) Adapts to bursty cloud storage; old io_workers GUC removed
Online Table Compaction VACUUM FULL (blocks all R/W) or external pg_repack Native REPACK & REPACK (CONCURRENTLY) Zero-downtime bloat removal in core
Autovacuum Sequential index cleanup per table Parallel index autovacuum + multi-factor bloat/age scoring Cuts maintenance lag on large multi-index tables
Query Plan Stability External pg_hint_plan or SQL rewrites Core pg_plan_advice + pg_stash_advice Pin JOIN_ORDER / HASH_JOIN without touching app code
Upsert / Get-or-Create ON CONFLICT DO NOTHING + second SELECT INSERT ... ON CONFLICT DO SELECT [FOR UPDATE] RETURNING Single atomic round-trip, fewer race conditions
JIT Default jit = on (since v12) jit = off Stops random JIT latency spikes on short OLTP queries
TOAST Compression pglz default lz4 default (default_toast_compression) Faster JSON/text compression & decompression
Lock Diagnostics log_lock_waits = off log_lock_waits = on Immediate visibility into >1s lock contention
Logical Replication Restart needed to change wal_level Enable from wal_level = replica without restart + sequence sync Zero-downtime CDC onboarding & cleaner failover

1. Native REPACK (CONCURRENTLY): Goodbye pg_repack?

For years, reclaiming severe table bloat in production meant choosing between two bad options: VACUUM FULL / CLUSTER (which grabs an ACCESS EXCLUSIVE lock and freezes all reads and writes for minutes or hours) or installing and operating external pg_repack / pg_squeeze extensions.

PostgreSQL 19 brings online compaction into core SQL with REPACK and REPACK (CONCURRENTLY):

-- Online compaction without blocking production reads or writes
REPACK (CONCURRENTLY) orders;
-- Reorder table physically along an index (replaces blocking CLUSTER)
REPACK (CONCURRENTLY) orders USING INDEX orders_created_at_idx;
Lock timeline comparison between VACUUM FULL, pg_repack, and PostgreSQL 19 native REPACK (CONCURRENTLY): VACUUM FULL blocks all reads and writes with ACCESS EXCLUSIVE, whereas REPACK (CONCURRENTLY) copies under SHARE UPDATE EXCLUSIVE, streams live writes via a temporary logical replication slot, and holds ACCESS EXCLUSIVE only for a split-second swap.
Lock timeline comparison between VACUUM FULL, pg_repack, and PostgreSQL 19 native REPACK (CONCURRENTLY): VACUUM FULL blocks all reads and writes with ACCESS EXCLUSIVE, whereas REPACK (CONCURRENTLY) copies under SHARE UPDATE EXCLUSIVE, streams live writes via a temporary logical replication slot, and holds ACCESS EXCLUSIVE only for a split-second swap.

Under the hood, REPACK (CONCURRENTLY) works like a battle-tested online migration:

  1. Acquires a lightweight SHARE UPDATE EXCLUSIVE lock so normal SELECT, INSERT, UPDATE, and DELETE traffic keeps flowing.
  2. Opens a temporary logical replication slot to capture concurrent writes happening during the copy.
  3. Replays the delta and takes ACCESS EXCLUSIVE only for a split second at the very end to swap the table files.

Add parallel index autovacuum and PostgreSQL 19’s new weighted autovacuum scoring (balancing table age, bloat ratio, and time since last cleanup), and massive multi-index tables stay healthy far longer before needing a repack at all — especially when paired with the right index types (B-tree, GIN, BRIN).

2. pg_plan_advice: Zero-Code Plan Stability Across Upgrades

Every experienced DBA knows the worst part of major PostgreSQL upgrades: 99% of queries get faster, and one critical reporting query flips from a Hash Join to a nested loop after ANALYZE, saturating CPU until someone ships an emergency code hotfix.

PostgreSQL 19 introduces pg_plan_advice and pg_stash_advice directly in the official distribution. Instead of rewriting ORM-generated SQL, DBAs can attach explicit plan advice (JOIN_ORDER, HASH_JOIN, SEQ_SCAN, INDEX_SCAN) keyed by query identifier so the planner honors the intended strategy without changing application code.

3. INSERT ... ON CONFLICT DO SELECT: Atomic Get-or-Create

Previously, the ubiquitous get-or-create pattern (idempotent user/session/tag lookup) forced developers into ugly workarounds: run INSERT ... ON CONFLICT DO NOTHING RETURNING *, get zero rows on conflict, then issue a second SELECT (or risk subtransaction bloat from BEGIN ... EXCEPTION WHEN unique_violation).

PostgreSQL 19 closes that gap with INSERT ... ON CONFLICT DO SELECT:

INSERT INTO api_clients (external_id, tier)
VALUES ('tenant-42', 'pro')
ON CONFLICT (external_id) DO SELECT FOR UPDATE
RETURNING id, tier, created_at;

You get one atomic server round-trip, optional row-level locking (FOR UPDATE / FOR SHARE), and zero wasted XIDs or subtransaction overhead on high-concurrency endpoints.

PostgreSQL 19 also cleans up native JSON exports with COPY ... TO ... (FORMAT JSON) (streaming NDJSON/JSON Lines, or a single JSON array with FORCE_ARRAY), replacing fragile row_to_json() workarounds:

-- Stream rows as NDJSON, or wrap as a single JSON array with FORCE_ARRAY
COPY (SELECT id, tier FROM api_clients) TO STDOUT (FORMAT JSON, FORCE_ARRAY);

(Note: FORMAT JSON is supported for COPY TO exports only, not COPY FROM imports yet.)

4. New Production Defaults: jit = off, lz4 TOAST & Live Logical Replication

PostgreSQL 19 quietly changes several defaults that directly affect latency, CPU, and observability:

Comparison of PostgreSQL 18 vs PostgreSQL 19 default settings and operational behavior: auto-scaling AIO workers, jit defaulting to off, LZ4 default TOAST compression, log_lock_waits enabled by default, parallel index autovacuum, and live logical replication without restart.
Comparison of PostgreSQL 18 vs PostgreSQL 19 default settings and operational behavior: auto-scaling AIO workers, jit defaulting to off, LZ4 default TOAST compression, log_lock_waits enabled by default, parallel index autovacuum, and live logical replication without restart.
  • jit = off by default: Turned on by default since PostgreSQL 12, LLVM JIT compilation too often misestimated costs on short OLTP queries, adding compilation overhead for zero gain. Disabling it out of the box makes OLTP latency more predictable — if you run heavy OLAP aggregations, benchmark jit = on explicitly after upgrading.
  • default_toast_compression = lz4: New toasted values (jsonb, large text) now default to LZ4 instead of pglz for noticeably faster compression and decompression with minimal ratio trade-off. Existing toasted rows stay untouched until rewritten.
  • log_lock_waits = on & per-process log_min_messages: Lock waits exceeding deadlock_timeout (1s default) are now logged automatically, and log_min_messages accepts a comma-separated list of process types (so you can raise verbosity for checkpointer without flooding logs from every backend).
  • Logical replication without restart + sequences: Clusters running wal_level = replica can now activate logical replication dynamically without bouncing the server, and sequence values are replicated logically — removing a long-standing failover headache.
  • Faster foreign key checks & auto-scaling AIO: Bulk writes with foreign keys see substantial speedups (up to ~2× in write-heavy test cases), while io_min_workers / io_max_workers dynamically scale Async I/O workers and EXPLAIN (ANALYZE, IO) exposes exact I/O behavior.

5. The PostgreSQL 19 Graph Myth: Why SQL/PGQ Was Pulled

If you searched for PostgreSQL 19 features, you probably saw early blog posts showcasing CREATE PROPERTY GRAPH and GRAPH_TABLE (SQL/PGQ, SQL:2023 graph queries on top of relational tables).

Here is the real status: although SQL/PGQ was committed during early v19 development and appeared in early betas, core committers reverted roughly 47 commits ahead of Beta 4 to avoid locking PostgreSQL into premature catalog complexity and edge-case bugs.

SQL/PGQ did NOT ship in PostgreSQL 19.0 — the earliest target is now PostgreSQL 20. If you need graph workloads today, use Apache AGE or check whether a dedicated engine actually makes sense in Do You Need a Graph Database?.

Breaking Changes & pg_upgrade Pre-Flight Checklist (18 → 19)

Before pointing pg_upgrade at your production v18 cluster, check these six blockers and deprecations:

Pre-flight checklist for upgrading from PostgreSQL 18 to PostgreSQL 19: purge removed io_workers and escape_string_warning parameters, drop legacy btree_gist inet/cidr indexes, check for CR/LF in object names, regenerate dumps for forced standard_conforming_strings, and plan migration off MD5 and removed UDP RADIUS auth.
Pre-flight checklist for upgrading from PostgreSQL 18 to PostgreSQL 19: purge removed io_workers and escape_string_warning parameters, drop legacy btree_gist inet/cidr indexes, check for CR/LF in object names, regenerate dumps for forced standard_conforming_strings, and plan migration off MD5 and removed UDP RADIUS auth.
  1. Purge io_workers and escape_string_warning from postgresql.conf: PostgreSQL 18’s io_workers parameter was replaced by io_min_workers / io_max_workers. Leaving io_workers or escape_string_warning in your config triggers a FATAL: configuration file contains errors startup failure.
  2. Audit btree_gist indexes on inet / cidr: pg_upgrade aborts if your v18 cluster still has indexes built on the legacy gist_inet_ops or gist_cidr_ops opclasses. Drop or recreate them with the safer built-in opclasses first.
  3. Check object names for \r or \n: Database, role, and tablespace names containing carriage returns or newlines are now rejected and will halt pg_upgrade (and MULE_INTERNAL encoding has been removed).
  4. Regenerate old dumps with v19 pg_dump: standard_conforming_strings is now permanently forced on; legacy dumps that toggle it off will fail to restore cleanly. Also reindex pg_trgm / tsvector indexes if locale rules apply.
  5. Re-test OLAP queries with jit = off: Explicitly set jit = on per database or role if analytical queries regress.
  6. Security cleanup (MD5 & RADIUS): Successful MD5 logins now emit server warnings (md5_password_warnings, alongside a new 7-day password_expiration_warning_threshold) — migrate to SCRAM-SHA-256 — and UDP RADIUS authentication has been removed completely.

Should You Upgrade Now?

  • Upgrade early if you run write-heavy OLTP clusters on PostgreSQL 17/18 (or containerized setups like PostgreSQL in Docker), battle table bloat (REPACK (CONCURRENTLY)), or maintain high-concurrency upserts (ON CONFLICT DO SELECT).
  • Wait for 19.1 / 19.2 on ultra-conservative financial cores — and use the time to strip io_workers from IaC templates, migrate MD5 roles to SCRAM-SHA-256, and stage pg_plan_advice baselines against production query logs.

Originally published at alekseialeinikov.com