Skip to content
In this postPostgreSQL

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.

By Bob Bass9 min read
PostgreSQLDatabases

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:

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

sql
REPACK (VERBOSE) events;

SELECT pg_size_pretty(pg_total_relation_size('events'));
text
INFO:  repacking "public.events" in physical order
INFO:  "public.events": found 0 removable, 333333 nonremovable row versions in 32259 pages

The 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:

sql
REPACK events USING INDEX events_created_at_idx;

The full syntax is short:

text
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:

sql
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_dump sessions 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 new max_repack_replication_slots setting (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:

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

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

sql
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;
text
           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.1

Smarter 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 SEQUENCES and bring a subscriber up to date with ALTER 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 new effective_wal_level setting shows the level actually in use. In our test, wal_level stayed replica while effective_wal_level reported logical once a logical slot existed.
  • A new WAIT command 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 TO can 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_transaction defaults 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_strings is 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, not NULL. Check any code that tests for NULL.
  • Full text search and pg_trgm now follow the database's locale provider. The release notes advise reindexing affected indexes and recomputing stored tsvector values after pg_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.

Support chat