How to Use the REPACK Command in PostgreSQL 19

Database tables constantly undergo INSERT, UPDATE, and DELETE operations. Over time, these modifications can create table bloat, causing tables and indexes to consume more disk space than necessary. While PostgreSQL's VACUUM process helps reclaim dead tuples for future reuse, it does not always reduce the physical size of a table on disk.

PostgreSQL 19 introduces the new REPACK command, which provides a built-in method for rewriting tables and reclaiming wasted storage space. It allows administrators to compact tables, rebuild indexes, and improve storage efficiency without relying on external tools.

In this article, we will explore the PostgreSQL 19 REPACK command using a practical example and examine its various options.

Understanding the REPACK Command

Inside the psql terminal on PostgreSQL 19, check the help command to get more detailed information about the repack

\h repack

Result :

Command:     REPACK
Description: rewrite a table to reclaim disk space
Syntax:
REPACK [ ( option [, ...] ) ] [ table_and_columns [ USING INDEX [ index_name ] ] ]
REPACK [ ( option [, ...] ) ] USING INDEX
where option can be one of:
    VERBOSE [ boolean ]
    ANALYZE [ boolean ]
    CONCURRENTLY [ boolean ]
and table_and_columns is:
    table_name [ ( column_name [, ...] ) ]
URL: https://www.postgresql.org/docs/19/sql-repack.html

The primary purpose of the command is to rewrite a table and reclaim unused storage generated by updates and deletes.

Creating a Test Environment

First, create a sample table.

CREATE TABLE repack_test (
    id BIGSERIAL PRIMARY KEY,
    name TEXT,
    description TEXT,
    amount NUMERIC,
    created_at TIMESTAMP
);

Next, populate the table with one million rows.

INSERT INTO repack_test (
    name,
    description,
    amount,
    created_at
)
SELECT
    md5(random()::text),
    repeat(md5(random()::text), 5),
    random() * 10000,
    now() - (random() * interval '365 days')
FROM generate_series(1, 1000000);

Output:

INSERT 0 1000000

Creating Indexes

Create two indexes on the table.

CREATE INDEX idx_repack_name
ON repack_test(name);
CREATE INDEX idx_repack_amount
ON repack_test(amount);

Measuring Initial Table Size

Let's inspect the table size before generating any bloat.

SELECT pg_size_pretty(
    pg_total_relation_size('repack_test')
);

Output:

360 MB

Checking only the table data:

SELECT pg_size_pretty(
    pg_relation_size('repack_test')
);

Output:

252 MB

At this stage, the table is freshly loaded and contains very little fragmentation.

Viewing Table Statistics

The pg_stat_user_tables view shows statistics collected for user tables.

SELECT *
FROM pg_stat_user_tables
WHERE relname = 'repack_test';

Result :

-[ RECORD 1 ]----------+---------------------------------
relid                  | 49704
schemaname             | public
relname                | repack_test
seq_scan               | 5
last_seq_scan          | 2026-06-10 19:19:11.623184+05:30
seq_tup_read           | 2000000
idx_scan               | 0
last_idx_scan          | 
idx_tup_fetch          | 0
n_tup_ins              | 1000000
n_tup_upd              | 0
n_tup_del              | 0
n_tup_hot_upd          | 0
n_tup_newpage_upd      | 0
n_live_tup             | 999998
n_dead_tup             | 0
n_mod_since_analyze    | 0
n_ins_since_vacuum     | 0
last_vacuum            | 
last_autovacuum        | 2026-06-10 19:19:49.954187+05:30
last_analyze           | 
last_autoanalyze       | 2026-06-10 19:19:50.65832+05:30
vacuum_count           | 0
autovacuum_count       | 1
analyze_count          | 0
autoanalyze_count      | 1
total_vacuum_time      | 0
total_autovacuum_time  | 5013
total_analyze_time     | 0
total_autoanalyze_time | 704
stats_reset            | 

Here we can see that this table has 0 dead tuples.

Generating Table Bloat

To demonstrate the usefulness of REPACK, let's create table bloat through large-scale updates.

First Update

UPDATE repack_test
SET
    description = repeat(md5(random()::text), 10),
    amount = random() * 10000;

Output:

UPDATE 1000000

Second Update

UPDATE repack_test
SET
    description = repeat(md5(random()::text), 10),
    amount = random() * 10000;

Output:

UPDATE 1000000

Additional Updates

UPDATE repack_test
SET description = repeat(md5(random()::text), 10)
WHERE id % 2 = 0;

Output:

UPDATE 500000

UPDATE repack_test
SET description = repeat(md5(random()::text), 10)
WHERE id % 3 = 0;

Output:

UPDATE 333333

UPDATE repack_testSET description = repeat(md5(random()::text), 10)WHERE id % 5 = 0;

Output:

UPDATE 200000

These repeated updates create multiple row versions, increasing table size significantly.

Creating Dead Tuples

Now delete some rows.

DELETE FROM repack_test
WHERE id % 10 = 0;

Output:

DELETE 100000

Run ANALYZE to refresh planner statistics.

ANALYZE repack_test;

Checking Table Size After Updates and Deletes

SELECT
    pg_size_pretty(pg_relation_size('repack_test')) AS table_size,
    pg_size_pretty(pg_indexes_size('repack_test')) AS index_size,
    pg_size_pretty(pg_total_relation_size('repack_test')) AS total_size;

Output:

-[ RECORD 1 ]-------
table_size | 951 MB
index_size | 229 MB
total_size | 1180 MB

Comparison:

Initial size - 360 MB

After Updates and Deletes - 1180 MB

The table grew by more than three times its original size due to row versioning and dead tuples.

Running the REPACK Command

Execute:

REPACK repack_test;

The command rewrites the table and removes wasted space.

Monitoring REPACK Progress

While REPACK is running, progress can be monitored using the PostgreSQL progress view.

Open the second psql terminal at the same time where the repack command execution happens in the first psql terminal and check the result from the live progress view named pg_stat_progress_repack.

select * from pg_stat_progress_repack;

Example output:

   pid   | datid | datname  | relid | command |      phase       | repack_index_relid | heap_tuples_scanned | heap_tuples_inserted | heap_tuples_updated | heap_tuples_deleted | heap_blks_total | heap_blks_scanned | index_rebuild_count 
---------+-------+----------+-------+---------+------------------+--------------------+---------------------+----------------------+---------------------+---------------------+-----------------+-------------------+---------------------
 1239323 |     5 | postgres | 49704 | REPACK  | rebuilding index |                  0 |              900000 |               900000 |                   0 |                   0 |          119978 |            119978 |                   1
(1 row)

This allows administrators to monitor long-running repack operations.

Using the VERBOSE Option

The VERBOSE option displays detailed execution information.

REPACK (
    VERBOSE true
) repack_test;

Output:

INFO:  repacking "public.repack_test" in physical order
INFO:  "public.repack_test": found 0 removable, 900000 nonremovable row versions in 47369 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.26 s, system: 0.59 s, elapsed: 0.98 s.
REPACK

This output provides insights into:

• Number of row versions
• Page count
• CPU usage
• Elapsed execution time

The short form is also supported:

REPACK (VERBOSE) repack_test;

Using the ANALYZE Option

The ANALYZE option automatically updates planner statistics after repacking.

REPACK (
    ANALYZE true
) repack_test;

You can also combine ANALYZE with VERBOSE.

REPACK (
    VERBOSE,
    ANALYZE
) repack_test;

Result :

INFO:  repacking "public.repack_test" in physical order
INFO:  "public.repack_test": found 0 removable, 900000 nonremovable row versions in 47369 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.32 s, system: 0.47 s, elapsed: 1.10 s.
INFO:  analyzing "public.repack_test"
INFO:  "repack_test": scanned 30000 of 47369 pages, containing 570000 live rows and 0 dead rows; 30000 rows in sample, 900011 estimated total rows
INFO:  finished analyzing table "postgres.public.repack_test"
avg read rate: 1183.870 MB/s, avg write rate: 0.276 MB/s
buffer usage: 107 hits, 30004 reads, 7 dirtied
WAL usage: 32 records, 7 full page images, 53571 bytes, 41524 full page image bytes, 0 buffers full
system usage: CPU: user: 0.18 s, system: 0.01 s, elapsed: 0.19 s
REPACK

This removes the need to run a separate ANALYZE command afterward.

Using the CONCURRENTLY Option

To reduce locking impact on active systems, use the CONCURRENTLY option.

REPACK (
    CONCURRENTLY
) repack_test;

This allows the repack process to proceed with reduced disruption to concurrent activity.

The option can also be combined with other parameters.

REPACK (
    VERBOSE,
    ANALYZE,
    CONCURRENTLY
) repack_test;

Result :

INFO:  repacking "public.repack_test" in physical order
INFO:  "public.repack_test": found 0 removable, 900000 nonremovable row versions in 47421 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.82 s, system: 0.85 s, elapsed: 3.09 s.
INFO:  analyzing "public.repack_test"
INFO:  "repack_test": scanned 30000 of 47398 pages, containing 569723 live rows and 0 dead rows; 30000 rows in sample, 900124 estimated total rows
INFO:  finished analyzing table "postgres.public.repack_test"
avg read rate: 436.642 MB/s, avg write rate: 436.336 MB/s
buffer usage: 101 hits, 30013 reads, 29992 dirtied
WAL usage: 22007 records, 21958 full page images, 176834611 bytes, 175745472 full page image bytes, 21006 buffers full
system usage: CPU: user: 0.22 s, system: 0.17 s, elapsed: 0.53 s
REPACK

Repacking Using a Specific Index

REPACK can reorganize a table according to a particular index.

REPACK repack_test
USING INDEX idx_repack_name;

Output:

REPACK

This can improve the physical ordering of table data based on the selected index.

Repacking Using an Index Scan

You can instruct PostgreSQL to repack using available indexes.

REPACK USING INDEX;

With VERBOSE enabled:

REPACK (VERBOSE) USING INDEX;

Output:

INFO:  repacking "public.repack_test" using index scan on "idx_repack_name"
INFO:  "public.repack_test": found 0 removable, 900000 nonremovable row versions in 47369 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.38 s, system: 0.65 s, elapsed: 1.41 s.
REPACK

This confirms that PostgreSQL selected the specified index scan strategy during the repack operation.

Benefits of REPACK

The new PostgreSQL 19 REPACK command provides several advantages:

• Reclaims unused storage space.
• Rebuilds indexes efficiently.
• Supports detailed progress monitoring.
• Offers automatic ANALYZE execution.
• Supports concurrent operation.
• Can reorganize data using a chosen index.
• Eliminates dependency on external repack utilities for many use cases.

The REPACK command introduced in PostgreSQL 19 provides a simple and powerful mechanism for reclaiming wasted storage and reorganizing bloated tablesREPACK allowed PostgreSQL to rewrite the table, rebuild indexes, and restore storage efficiency.

For database administrators managing write-intensive workloads such as ERP systems, e-commerce platforms, analytics applications, and large PostgreSQL deployments, REPACK offers a valuable built-in maintenance tool for controlling table growth and maintaining optimal performance.