What's new in Postgres 19

Postgres has never had a great built-in way to shrink a bloated table. VACUUM identifies dead space for reuse, but doesn't return it to the operating system. VACUUM FULL and CLUSTER do, but they lock the table for the whole rewrite. For a decade, operators have been forced to reach for extensions like pg_repack and pg_squeeze.

Postgres 19 finally brings that capability into core. Among many, the three most notable changes in Postgres 19 are:

• REPACK rewrites a bloated table, online
• JIT disabled by default
• The query planner is smarter, sometimes

Let's take a look!

REPACK: Keep Your Database Moving

Before Postgres 19, two commands could rewrite a table to reclaim space from dead tuples. VACUUM FULL compacts the table while CLUSTER both compacts and orders it. Both, however, hold an ACCESS EXCLUSIVE lock on the table for the entire rewrite.

REPACK, the headline feature of Postgres 19, absorbs the functionality of VACUUM FULL and CLUSTER and adds an online mode to keep your table accepting reads and writes while the rewrite occurs.

To demonstrate REPACK we need a bloated table. Here's how to create one with six million rows, and then cause 70% of them to churn into dead tuples.

DROP TABLE IF EXISTS t;
CREATE TABLE t (
    id      bigint PRIMARY KEY,
    val     integer NOT NULL,
    payload text NOT NULL
);

INSERT INTO t (id, val, payload)
SELECT g, g % 1000, repeat('a', 120)
FROM generate_series(1, 6000000) AS g;

UPDATE t SET payload = payload || 'x' WHERE val < 700;   -- churns 4.2M rows
ANALYZE t;

You can see how bloated a table is by looking at its size and its dead tuple count.

SELECT pg_size_pretty(pg_total_relation_size('t')) AS size,
       n_dead_tup AS dead_tuples
FROM pg_stat_user_tables WHERE relname = 't';

  size   | dead_tuples
---------+-------------
 1884 MB |     4200347
(1 row)

The table sits at 1884 MB, and about 4.2 million of its rows are dead.

In Postgres 19, there are three different ways to utilize REPACK. All three will reclaim the space but they differ in whether they order the rows and whether they lock the table while the rewrite occurs.

REPACK t;                          -- unordered, like VACUUM FULL
REPACK t USING INDEX t_pkey;       -- ordered by an index, like CLUSTER
REPACK (CONCURRENTLY) t;           -- online: reads and writes continue

Run each one against a freshly bloated table and it drops to ~1085 MB, with zero dead tuples remaining.

REPACKING tables online

pg_repack, a popular open-source extension used for compacting tables in Postgres, copies the live rows into a new table while the old table continues to accept writes. Triggers are used to record every change into a log table. This log table is then replayed on the new copy before the two are swapped.

The pg_squeeze extension uses the same approach but without the triggers. The changes are already in the WAL, so it decodes them from there.

REPACK (CONCURRENTLY) is essentially pg_squeeze brought into Postgres core. The design and most of the code are by Antonin Houska, the creator of pg_squeeze. It works by keeping the most expensive part of the operation non-blocking:

1. Takes a SHARE UPDATE EXCLUSIVE lock on the table, which allows reads and writes to continue
2. Takes an MVCC snapshot, a frozen view of the table at that moment
3. Opens a replication slot at that snapshot
4. Builds a new copy of the table and its indexes from the snapshot
5. While the copy builds, the incoming writes to the old table are decoded from the WAL into an ordered backlog in temporary files on disk
6. Once the copy is built, the backlog of writes is replayed onto it. The old table is still accepting writes, however, so the backlog is refilling
7. Upgrades to an ACCESS EXCLUSIVE lock, replays the refilled backlog, swaps the files, and finally releases the lock

Steps 1-6 run under the weak lock, so writes can continue. Only step 7 blocks. The swap is a file switch. This ACCESS EXCLUSIVE lock is held for the final batch of changes, including the writes that landed during the first replay and while waiting for the lock. That scales with write traffic, not the size of the table.

We will dig into the numbers in a companion post on Postgres 19 I/O Benchmarking.

Requirements and caveats

REPACK (CONCURRENTLY) has limits. It uses logical decoding and requires wal_level at replica or higher. It errors on an unlogged table, inside a transaction block, and on a table without a primary key or replica-identity index.

Even on tables it is compatible with, there are implications to consider. The old and new copies live side by side until the swap, so you need adequate disk space to support the copy. It also holds a replication slot the whole time, which keeps its WAL on disk until it's decoded.

REPACK (CONCURRENTLY) is also not MVCC safe. Once a swap commits, the table looks empty to any transaction on a snapshot from before it.

-- Session 1
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM pg_class;      -- takes the snapshot; t untouched

Run REPACK (CONCURRENTLY) t; in a different session to completion.

-- Session 1, same transaction
SELECT count(*) FROM t;

 count
-------
     0
(1 row)

The query returns zero rows with no warnings or errors. Commit the transaction, take a new snapshot, and the rows are back.

The commit message adds one more warning for REPACK (CONCURRENTLY). The final step still holds the SHARE UPDATE EXCLUSIVE lock and asks for an ACCESS EXCLUSIVE lock on top of it. This lock, however, can't be granted until every other transaction releases its lock on the table. For example, say an open transaction ran a SELECT on the table and then issues an UPDATE. REPACK waits for that transaction's lock to release before it can upgrade. But the UPDATE needs a new lock queued behind REPACK's request, making each wait on the other. Postgres breaks this deadlock by aborting one of them, and if it picks REPACK, the whole rewrite is rolled back.

JIT disabled by default

JIT, just-in-time compilation, compiles the repetitive parts of a query into native machine code. It does this at the start of every execution. It has defaulted to on since Postgres 12. The Postgres 19 commit subject is "jit: Change the default to off."

At first this sounds like a bad thing. Why turn off a performance optimization?

JIT genuinely helps many longer-running analytical queries. The overhead of compiling a query is worth it when the query needs to process and aggregate millions of rows. The problem is Postgres can't always confidently tell in advance which queries those are. It switches on whenever a query's estimated cost crosses a threshold, so something as small as a few extra rows can tip a fast query over the line.

This impacts OLTP queries whose estimates sit near the threshold. Take a small index-based aggregation:

SELECT sum(total) FROM orders WHERE customer_id = 100;

The index lookup finishes in well under a millisecond, but as orders grows, the planner's estimated cost drifts up with it. Once it crosses jit_above_cost, every run pays the LLVM compile cost, which easily outweighs the query's actual work. EXPLAIN ANALYZE reports the JIT time separately, so you can see when compilation dominates the run.

Rather than keep guessing, Postgres 19 turns it off and lets you opt in.

Your query planner is smarter, sometimes

Postgres 19 ships several planner improvements. The one most likely to change a query plan is eager aggregation. Say you have a customers table with 1,000 rows and an orders table with 200,000 rows, where each order has a foreign key to its customer. What happens when you sum order totals by region?

SELECT c.region, sum(o.total)
FROM orders o
JOIN customers c ON c.id = o.customer_id
GROUP BY c.region;

Postgres 18 does the obvious thing by joining every order to its customer and then grouping.

Postgres 19, however, splits this into stages. Each order matches exactly one customer row through its customer_id, so orders with the same customer_id are always in the same region. This allows Postgres to sum them before the join. It collapses the 200,000 orders into 1,000 per-customer sums, so the join sees 1,000 rows instead of 200,000. A final pass then groups those sums into region totals.

It's on by default, but it won't fire on everything. min_eager_agg_group_size (default 8) holds it back unless the planner expects the pre-aggregation to shrink the row count by at least 8x.

NOT IN gets a related fix too. If the planner can prove no NULLs are in play, it now runs as an anti-join, which is much faster on large tables.

Postgres 19 also adds pg_plan_advice, a way to capture a plan's decisions and pin them by query ID. That way if a query regresses, it can be held to a previous trusted plan. For now it covers joins and scans, not aggregation.

The rest, in brief

A handful of smaller changes round out Postgres 19.

• lz4 is the new TOAST default.
• Lock waits get logged by default.
• Foreign-key inserts get faster.
• MultiXact offsets widen to 64 bits.
• Parallel autovacuum arrives, off by default.
• New SQL: ON CONFLICT DO SELECT, GROUP BY ALL, COPY TO ... (FORMAT json), and temporal UPDATE/DELETE via FOR PORTION OF.
• max_locks_per_transaction doubles to 128, and some old auth is retired (RADIUS gone, MD5 warns).
• Property graphs land, but compile to ordinary joins.

Postgres 19 is expected to reach GA in September or October. Thank you to all the contributors behind this release.

You can expect Postgres 19 on PlanetScale later this year.