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, onlineJIT

disabled by default- The query planner is smarter, sometimes

Let's take a look!

Note

Postgres 19 Beta 2 was released on 2026-07-16 with GA expected September/October. Defaults and details may change but feature freeze has been announced.

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.

Note

n_dead_tup

is a statistical estimate so the value might be a little different in your local run. It gets updated intermittently or after an ANALYZE

.

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.

| Command | Before | After | Table locked? |

|---|---|---|---|

REPACK t; | 1884 MB | 1085 MB | yes, the whole time |

REPACK t USING INDEX t_pkey; | 1884 MB | 1085 MB | yes, the whole time |

REPACK (CONCURRENTLY) t; | 1884 MB | 1086 MB | only during the final swap |

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:

• Takes a

SHARE UPDATE EXCLUSIVE

lock on the table, which allows reads and writes to continue - Takes an MVCC snapshot, a frozen view of the table at that moment

• Opens a replication slot at that snapshot
• Builds a new copy of the table and its indexes from the snapshot
• 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
• 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
• 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 NULL

s 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 temporalUPDATE

/DELETE

viaFOR 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.