---
title: "PostgreSQL 19 vs 18: Native REPACK, pg_plan_advice, Graph Myth & Production Upgrade Guide"
description: "Complete engineering comparison of PostgreSQL 19 vs 18: native REPACK (CONCURRENTLY), pg_plan_advice plan stability, ON CONFLICT DO SELECT, auto-scaling Async I/O, why SQL/PGQ graph queries were reverted before Beta 4, and breaking pg_upgrade traps."
author: Aleksei Aleinikov
date: 2026-11-20
lang: en
tags: [postgresql 19 vs 18, postgresql 19, postgresql 19 features, postgresql repack concurrently, pg_plan_advice, on conflict do select, postgresql 19 upgrade]
canonical: https://www.alekseialeinikov.com/en/blog/topics/data/postgresql-19-vs-18-features-performance-upgrade-guide
source: alekseialeinikov.com
---

# 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.](https://media.alekseialeinikov.com/data/7b3e9a4c1d8f/cover-1200.9b8409540c5d46bc.webp)

> [!NOTE]
> **Verified against PostgreSQL 19.0 release notes.** Every GUC name, SQL syntax addition, reverted beta feature (`SQL/PGQ`), and `pg_upgrade` blocker below is verified directly against the [official PostgreSQL 19.0 release notes](https://www.postgresql.org/docs/release/19.0/) and primary PostgreSQL contributor reports.

> [!KEY]
> **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](https://www.alekseialeinikov.com/en/blog/topics/data/postgresql-in-docker-production), 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)`**:

```sql
-- 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.](https://media.alekseialeinikov.com/data/7b3e9a4c1d8f/postgresql-19-repack-locks-1200.a88b100f215b0cfd.webp)

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`)](https://www.alekseialeinikov.com/en/blog/topics/data/postgres-index-types-which-to-use-btree-gin-brin-benchmark).

## 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`**:

```sql
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:

```sql
-- 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.](https://media.alekseialeinikov.com/data/7b3e9a4c1d8f/postgresql-19-vs-18-defaults-1200.3a1a1e29fdc47b39.webp)

- **`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?](https://www.alekseialeinikov.com/en/blog/topics/data/do-you-need-a-graph-database-2026).

## 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.](https://media.alekseialeinikov.com/data/7b3e9a4c1d8f/postgresql-19-upgrade-checklist-1200.1355e0c24ea6b1a2.webp)

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](https://www.alekseialeinikov.com/en/blog/topics/data/postgresql-in-docker-production)), 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.
