
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.

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 auditpostgresql.conffirst — leaving v18’sio_workersparameter in place triggers aFATALstartup failure,jitis nowoffby 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 writesREPACK (CONCURRENTLY) orders; -- Reorder table physically along an index (replaces blocking CLUSTER)REPACK (CONCURRENTLY) orders USING INDEX orders_created_at_idx;
Under the hood, REPACK (CONCURRENTLY) works like a battle-tested online migration:
- Acquires a lightweight
SHARE UPDATE EXCLUSIVElock so normalSELECT,INSERT,UPDATE, andDELETEtraffic keeps flowing. - Opens a temporary logical replication slot to capture concurrent writes happening during the copy.
- Replays the delta and takes
ACCESS EXCLUSIVEonly 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 UPDATERETURNING 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_ARRAYCOPY (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:

jit = offby 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, benchmarkjit = onexplicitly after upgrading.default_toast_compression = lz4: New toasted values (jsonb, largetext) now default toLZ4instead ofpglzfor noticeably faster compression and decompression with minimal ratio trade-off. Existing toasted rows stay untouched until rewritten.log_lock_waits = on& per-processlog_min_messages: Lock waits exceedingdeadlock_timeout(1s default) are now logged automatically, andlog_min_messagesaccepts a comma-separated list of process types (so you can raise verbosity forcheckpointerwithout flooding logs from everybackend).- Logical replication without restart + sequences: Clusters running
wal_level = replicacan 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_workersdynamically scale Async I/O workers andEXPLAIN (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:

- Purge
io_workersandescape_string_warningfrompostgresql.conf: PostgreSQL 18’sio_workersparameter was replaced byio_min_workers/io_max_workers. Leavingio_workersorescape_string_warningin your config triggers aFATAL: configuration file contains errorsstartup failure. - Audit
btree_gistindexes oninet/cidr:pg_upgradeaborts if your v18 cluster still has indexes built on the legacygist_inet_opsorgist_cidr_opsopclasses. Drop or recreate them with the safer built-in opclasses first. - Check object names for
\ror\n: Database, role, and tablespace names containing carriage returns or newlines are now rejected and will haltpg_upgrade(andMULE_INTERNALencoding has been removed). - Regenerate old dumps with v19
pg_dump:standard_conforming_stringsis now permanently forcedon; legacy dumps that toggle itoffwill fail to restore cleanly. Also reindexpg_trgm/tsvectorindexes if locale rules apply. - Re-test OLAP queries with
jit = off: Explicitly setjit = onper database or role if analytical queries regress. - Security cleanup (
MD5&RADIUS): SuccessfulMD5logins now emit server warnings (md5_password_warnings, alongside a new 7-daypassword_expiration_warning_threshold) — migrate toSCRAM-SHA-256— and UDPRADIUSauthentication 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.2on ultra-conservative financial cores — and use the time to stripio_workersfrom IaC templates, migrateMD5roles toSCRAM-SHA-256, and stagepg_plan_advicebaselines against production query logs.
Originally published at alekseialeinikov.com


