Skip to content

PostgreSQL 19: The “repack” command

Before PostgreSQL 19 you had two commands to completely rewrite a table: Either you can use the “vacuum full” or the “cluster” command to achieve this. Both operations are blocking and the table cannot be used until those operations complete. This can easily be verified with the following simple test cases:

-- session 1
postgres=# create table t ( a int primary key, b text );
CREATE TABLE
postgres=# insert into t select i, md5(i::text) from generate_series(1,10000000) i;
INSERT 0 1000000
postgres=# vacuum full t;

-- session 2
postgres=# select count(*) from t;  -- this blocks until vacuum full completes

The same is true for the “cluster” command:

-- session 1
postgres=# \d t
                 Table "public.t"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           | not null | 
 b      | text    |           |          | 
Indexes:
    "t_pkey" PRIMARY KEY, btree (a)

postgres=# cluster t using t_pkey;

-- session 2
postgres=# select count(*) from t;  -- this blocks until clustering completes

Starting with PostgreSQL 19 (scheduled to be released later this year) these two functionalities are combined into the “repack” command. The commit message makes the reason behind this pretty clear:

Introduce the REPACK command

REPACK absorbs the functionality of VACUUM FULL and CLUSTER in a single
command.  Because this functionality is completely different from
regular VACUUM, having it separate from VACUUM makes it easier for users
to understand; as for CLUSTER, the term is heavily overloaded in the
IT world and even in Postgres itself, so it's good that we can avoid it.

We retain those older commands, but de-emphasize them in the
documentation, in favor of REPACK; the difference between VACUUM FULL
and CLUSTER (namely, the fact that tuples are written in a specific
ordering) is neatly absorbed as two different modes of REPACK.

This allows us to introduce further functionality in the future that
works regardless of whether an ordering is being applied, such as (and
especially) a concurrent mode.

So, instead of spreading the functionality over two commands, there is a new command which combines both:

postgres=# \h repack
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/devel/sql-repack.html

The really cool stuff about this is, that this can be run concurrently which means the table is not locked for others while the command is doing its work:

Questions about your own setup? A dbi services consultant can look at your case.

Talk to an expert (opens in a new tab)
-- session 1
postgres=# repack (concurrently) t;
-- or
postgres=# repack (concurrently) t using index t_pkey;

-- session 2
postgres=# select count(*) from t;  -- not blocking

Nice, thanks to all involved.

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Open source Team

Open source Team

Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.View Author posts

721 posts since September 2011

Most read in Database

The Database posts our readers open most: the all-time favourites, and what is trending right now.

Leave a Comment

Your email address will not be published. Required fields are marked *