What is REPACK? And the other Postgres 19 features worth knowing
Postgres 19 adds REPACK, one command that replaces VACUUM FULL and CLUSTER and can shrink a bloated table without blocking writes. Here is how it works, where it falls short, and what else changed in 19.
PostgreSQL 19 shipped on November 19, 2026, and the feature most people will feel first is a new command: REPACK. It rebuilds a table to give disk space back to the operating system, and it can optionally reorder rows to match an index. That job used to be split between VACUUM FULL and CLUSTER. Neither could run without locking the table for the entire rebuild, and that lock is why so many teams installed the pg_repack extension instead.
This post covers what REPACK does, how the new CONCURRENTLY mode works, the cases where it will refuse to run, and a quick tour of the rest of the 19 release. Every example below was run on PostgreSQL 19.0, and the sizes are from that run.
Why tables bloat in the first place
Postgres never overwrites a row in place. An UPDATE writes a new version of the row, and a DELETE marks the old version dead. Regular VACUUM (and autovacuum) finds those dead versions and marks their space as reusable, but the file on disk stays the same size. For most tables that is fine, because new rows fill the gaps.
Problems show up after a bulk delete, a large backfill, or a long stretch where autovacuum fell behind. Say a table held 50 million rows and now holds 5 million: the file still takes up the space of 50 million. Sequential scans read every page, the shrunken table still fills your storage quota, and backups copy all of it.
You can watch it happen. Make a table, fill it, and delete two thirds of it:
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
kind text NOT NULL,
payload text,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX events_created_at_idx ON events (created_at);
INSERT INTO events (kind, payload, created_at)
SELECT
(ARRAY['click', 'view', 'signup'])[1 + i % 3],
repeat('x', 200),
now() - (i || ' seconds')::interval
FROM generate_series(1, 1000000) AS i;
SELECT pg_size_pretty(pg_total_relation_size('events'));
DELETE FROM events WHERE id % 3 <> 0;
VACUUM events;
SELECT pg_size_pretty(pg_total_relation_size('events'));In our run, the table and its indexes took 312 MB after the insert. After deleting two thirds of the rows and running VACUUM, they still took 312 MB. The dead space can now be reused, but none of it has gone back to the operating system.
What REPACK does
REPACK writes a fresh copy of the table that holds only live rows, rebuilds its indexes, and then swaps the new files in for the old ones:
REPACK (VERBOSE) events;
SELECT pg_size_pretty(pg_total_relation_size('events'));INFO: repacking "public.events" in physical order
INFO: "public.events": found 0 removable, 333333 nonremovable row versions in 32259 pagesThe same table dropped from 312 MB to 98 MB, about a third of its size, which matches the third of the rows that survived. The rebuild took about half a second on a laptop.
To get the old CLUSTER behavior, which writes rows in index order so that range scans on that column touch fewer pages, add USING INDEX:
REPACK events USING INDEX events_created_at_idx;The full syntax is short:
REPACK [ ( option [, ...] ) ] [ table_name [ ( column_name [, ...] ) ] [ USING INDEX [ index_name ] ] ]
option is one of:
VERBOSE [ boolean ]
ANALYZE [ boolean ]
CONCURRENTLY [ boolean ]Run REPACK with no table name and it processes every table and materialized view you have the MAINTAIN privilege on. Add ANALYZE to refresh planner statistics in the same pass, optionally limited to the columns you list.
VACUUM FULL and CLUSTER still work in 19 and are kept for compatibility, so existing scripts will not break. The release notes describe REPACK as their replacement, though, so new runbooks should use it.
REPACK (CONCURRENTLY): the part people actually wanted
Plain REPACK behaves like the old commands, and so does its lock: it takes an ACCESS EXCLUSIVE lock at the start and holds it until the end. Nothing can read or write the table during the rebuild. On a large table that is an outage.
The new option avoids that:
DELETE FROM events WHERE id % 2 = 0;
REPACK (CONCURRENTLY) events;After deleting half of the remaining rows, the concurrent repack took the table from 98 MB to 50 MB.
With CONCURRENTLY, most of the work runs under a SHARE UPDATE EXCLUSIVE lock, so reads and writes keep going. Postgres uses logical decoding to capture changes made while the copy is being built, applies them to the new copy, and takes ACCESS EXCLUSIVE only for the brief final step that swaps the table and index files. If you have used pg_repack, the idea will be familiar, but now it is built into the server and needs no extension or trigger setup.
The documentation lists real limits, and they are worth reading before you put this in a cron job:
- The table needs a primary key or an index-based replica identity. Without one, Postgres cannot match the captured changes to rows.
- It is not MVCC-safe. Once the repack commits, a transaction that is still using a snapshot taken before the repack began will see the table as empty. Long-running reporting queries and
pg_dumpsessions that overlap the swap are the ones to worry about. - Heavy write churn can make it fail. The command gives up if more than about 104 million rows are updated or deleted while it runs.
- Concurrent DDL can stop it. Another session altering the table mid-run may prevent it from finishing.
- Rows inserted during the run are not sorted. With
USING INDEX, only rows that existed when the repack started end up in index order. - Some tables are not supported: unlogged, partitioned, system catalog and TOAST tables, materialized views, non-heap access methods, and tables marked
user_catalog_table. It also cannot run inside a transaction block, and it needs a free replication slot under the newmax_repack_replication_slotssetting (5 by default in our build).
Two points apply to every form of REPACK. First, you need free disk space at least equal to the table plus its indexes, because both copies exist until the swap, and a sort for USING INDEX can need a temporary file as large as the table. Second, REPACK does not replace regular vacuuming. The docs say plainly that it is not a good way to prevent transaction ID wraparound, and a long run counts as one long transaction.
Watching a long repack
The new pg_stat_progress_repack view reports progress for REPACK, CLUSTER and VACUUM FULL. Run this from a second session while a large table is being rebuilt:
SELECT * FROM pg_stat_progress_repack;When to reach for it
REPACK is for one-off cleanup, not routine maintenance. Good times to use it:
- After deleting or archiving a large share of a table's rows
- After a backfill that rewrote most rows in a table
- When a table is read mostly by range, such as time-series data by
created_at, and the physical order has drifted from the index
If a table bloats again every week, the fix is usually autovacuum tuning, not a scheduled REPACK. Postgres 19 helps there too.
The rest of Postgres 19
REPACK gets the headlines, but these are the other changes most application developers and operators will run into.
INSERT ... ON CONFLICT DO SELECT
"Get or create" used to take either two statements or a DO UPDATE that rewrote the row for no reason. Now the conflicting row can be returned directly:
CREATE TABLE tags (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text UNIQUE NOT NULL
);
INSERT INTO tags (name) VALUES ('postgres')
ON CONFLICT (name) DO SELECT
RETURNING id;Run it twice and you get the same id both times, with no dead row created by a no-op update. The conflicting row can also be locked with FOR UPDATE or FOR SHARE.
IGNORE NULLS in window functions
lead(), lag(), first_value(), last_value() and nth_value() now accept IGNORE NULLS. Carrying the last known reading forward over gaps, which used to need a correlated subquery, is now one line:
CREATE TABLE readings (ts timestamptz PRIMARY KEY, value numeric);
INSERT INTO readings VALUES
('2026-11-20 10:00+00', 12.5),
('2026-11-20 10:01+00', NULL),
('2026-11-20 10:02+00', NULL),
('2026-11-20 10:03+00', 13.1);
SET timezone = 'UTC';
SELECT ts, last_value(value) IGNORE NULLS OVER (ORDER BY ts) AS filled
FROM readings; ts | filled
------------------------+--------
2026-11-20 10:00:00+00 | 12.5
2026-11-20 10:01:00+00 | 12.5
2026-11-20 10:02:00+00 | 12.5
2026-11-20 10:03:00+00 | 13.1Smarter autovacuum
Autovacuum can now clean a table's indexes with parallel workers. The limit is set by autovacuum_max_parallel_workers, and individual tables can override it with the autovacuum_parallel_workers storage parameter. Autovacuum also scores tables to decide which to process first, and the new pg_stat_autovacuum_scores view shows why a particular table is waiting. These changes go after the backlog that leads to bloat in the first place.
Logical replication gets easier
- Sequences can now be replicated. Publish them with
FOR ALL SEQUENCESand bring a subscriber up to date withALTER SUBSCRIPTION ... REFRESH SEQUENCES. Stale sequences on the subscriber used to be a common snag during migrations. - With
wal_level = replica, Postgres raises the effective level to logical when it is first needed, without a restart. The neweffective_wal_levelsetting shows the level actually in use. In our test,wal_levelstayedreplicawhileeffective_wal_levelreportedlogicalonce a logical slot existed. - A new
WAITcommand lets a standby wait until a given LSN is written, flushed or replayed, which makes read-your-writes against a replica practical.
Plan advice
The new pg_plan_advice extension lets you steer planner decisions for a query, and pg_stash_advice applies that advice automatically when a matching query runs. It is aimed at the 2 a.m. case where a plan suddenly flips after a statistics change.
Smaller items
- Foreign key checks are faster, which speeds up bulk inserts into tables that reference others.
COPY TOcan write JSON.- The server supports TLS SNI, so one server can present a different certificate depending on the hostname the client asked for. The hostname and key pairs live in the new
pg_hosts.conf.
Check these before you upgrade
The 19 release notes list over twenty incompatibilities. These are the ones most likely to affect an application:
- JIT is off by default. If you run large analytical queries that benefited from it, turn it back on with
SET jit = on;for those sessions and measure before and after. - New TOAST data is compressed with LZ4 instead of pglz. Existing values are not rewritten.
max_locks_per_transactiondefaults to 128, up from 64. If you raised it before, the release notes say your setting effectively needs to be doubled to keep the same capacity.standard_conforming_stringsis always on. Old dumps made with it off will not load correctly.- Successful MD5 password logins now log a warning. This is a good moment to move to SCRAM.
json_array()over zero rows returns an empty array, notNULL. Check any code that tests forNULL.- Full text search and
pg_trgmnow follow the database's locale provider. The release notes advise reindexing affected indexes and recomputing storedtsvectorvalues afterpg_upgrade.
The full list is in the PostgreSQL 19 release notes, and the REPACK reference page covers every option and limitation.
Try REPACK on a copy first
The safest way to learn how REPACK behaves on your own data is to run it somewhere that does not matter. On Layerbase, PostgreSQL 19 is the default for new databases. Create a PostgreSQL database, or create a branch of an existing one, then run the bloat example above or point REPACK (VERBOSE) at a copy of your largest table and see how much space comes back. Database branching guide
If you are still on the 19 beta, upgrading to 19.0 keeps your connection string, and it works on the Free plan.
Keep reading
- EDB buys Altinity: Postgres and ClickHouse keep moving in togetherEDB bought Altinity on November 5. Four days later ClickHouse said it was buying Xata. Two deals from opposite directions, one conclusion: vendors want to own both your transactional and your analytical database.
- Electric Cloud is winding down. Here is where to run your syncElectric joined Neon inside Databricks in August, and Electric Cloud is winding down with no published shutdown date. The sync engine stays open source. Here is what self-hosting it takes, and the Postgres checks to run before you move.
- Postgres 19 is stable on LayerbaseStart a new database on PostgreSQL 19.0 or upgrade your existing 19 beta database, including on the Free plan.
- Railway paused builds two days in a row. Can you move your app without moving its database?What Railway reported on October 20 and 21, what a separate database does and does not protect, and a practical recovery checklist with a manual Postgres migration.
