REPACK(7)

💖 REPACK(7) — rewrite a table to reclaim disk space ✨

🎀 NAME 🎀

REPACK — rewrite a table to reclaim disk space. That's the whole assignment, bestie. 💅

🌈 SYNOPSIS 🌈

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 [, ...] ) ]

🌸 DESCRIPTION 🌸

Okay so REPACK reclaims storage occupied by dead tuples. Unlike VACUUM, it does so by rewriting the entire contents of the table specified by table_name into a new disk file with no extra space (except for the space guaranteed by the fillfactor storage parameter), allowing unused space to be returned to the operating system. Iconic, honestly. Obsessed. 🫶

Without a table_name, REPACK processes every table and materialized view in the current database that the current user has the MAINTAIN privilege on. This form of REPACK cannot be executed inside a transaction block. Also, this form is not allowed if the CONCURRENTLY option is used. Noted, bestie. 📝

If a USING INDEX clause is specified, the rows are physically reordered based on information from an index. Please see the notes on clustering below. 👇

When a table is being repacked, an ACCESS EXCLUSIVE lock is acquired on it. This prevents any other database operations (both reads and writes) from operating on the table until the REPACK is finished. If you want to keep the table accessible during the repacking, consider using the CONCURRENTLY option. Lock behavior: highkey important. 🔒

🌀 Notes on Clustering

If the USING INDEX clause is specified, the rows in the table are physically rearranged according to the ordering implied by the specified index; this is known as clustering. This can have performance implications: in cases where you are accessing single rows randomly within a table, the actual order of the data in the table is unimportant. However, if you tend to access some data more than others, and there is an index that groups them together, you will benefit from using clustering. If you are requesting a range of indexed values from a table, or a single indexed value that has multiple matching rows, clustering will help because once the index identifies the table page for the first row that matches, all other rows that match are probably already on the same table page, and so you save disk accesses and speed up the query. It's giving optimized. ⚡

For B-Tree indexes, the ordering used by clustering is the index's linear sort order. Other clusterable index access methods may use a different ordering strategy, one which may not necessarily correspond to any SQL sort order. If an index name is specified in the command, that index is used and is recorded as the table's clustering index. (This also applies to an index given to the CLUSTER command.) If no index name is specified, then the index that has been configured as the clustering one is used; if none has been configured, an error is thrown. An index can be set manually using ALTER TABLE ... CLUSTER ON, and reset with ALTER TABLE ... SET WITHOUT CLUSTER. Slay. 💅

Clustering is a one-time operation: when the table is subsequently updated, the changes are not clustered. That is, no attempt is made to store new or updated rows according to the clustering order. (If one wishes, one can periodically recluster by issuing the command again. Also, setting the table's fillfactor storage parameter to less than 100% can aid in preserving cluster ordering during updates, since updated rows are kept on the same page if enough space is available there.) Periodt. 🎀

When clustering on a B-Tree index, REPACK can rewrite the table using either an index scan on the specified index, or a sequential scan followed by sorting. It will attempt to choose the method that will be faster, based on planner cost parameters and available statistical information. When clustering on an index of an access method other than B-Tree, REPACK always uses an index scan. Choose wisely, algorithmically. 🧠

Because the planner records statistics about the ordering of tables, it is advisable to specify the ANALYZE option, or to run ANALYZE on the newly repacked table. Otherwise, the planner might make poor choices of query plans. Don't let the planner fumble. 😭

If no table name is specified in REPACK USING INDEX, all tables which have a clustering index defined and which the calling user has privileges for are processed. All the eligible ones, bestie. 👑

🧸 Notes on Resources

When an index scan or a sequential scan without sort is used, a temporary copy of the table is created that contains the table data in the index order. Temporary copies of each index on the table are created as well. Therefore, you need free space on disk at least equal to the sum of the table size and the index sizes. Disk space check: mandatory. 💾

When a sequential scan and sort is used, a temporary sort file is also created, so that the peak temporary space requirement is as much as double the table size, plus the index sizes. This method is often faster than the index scan method, but if the disk space requirement is intolerable, you can disable this choice by temporarily setting enable_sort to off. Big brain, but watch your storage. 🍓

It is advisable to set maintenance_work_mem to a reasonably large value (but not more than the amount of RAM you can dedicate to the REPACK operation) before repacking. Treat it right. ✨

🎛️ PARAMETERS

• table_name — The name (possibly schema-qualified) of a table. Yes, schema-qualified is allowed, bestie. 🫶
• column_name — The name of a specific column to analyze. Defaults to all columns. If a column list is specific, ANALYZE must also be specified. Don't forget the analyze part. 📝
• index_name — The name of an index. Simple as that. 💅
• CONCURRENTLY — Allow other transactions to use the table while it is being repacked.

Internally, REPACK copies the contents of the table (ignoring dead tuples) into a new file, sorted by the specified index, and also creates a new file for each index. Then it swaps the old and new files for the table and all the indexes, and deletes the old files. The ACCESS EXCLUSIVE lock is needed to make sure that the old files do not change during the processing because the changes would get lost due to the swap.

With the CONCURRENTLY option, the ACCESS EXCLUSIVE lock is only acquired to swap the table and index files. The data changes that took place during the creation of the new table and index files are captured using logical decoding (Chapter 47) and applied before the ACCESS EXCLUSIVE lock is requested. Thus the lock is typically held only for the time needed to swap the files, which should be pretty short. However, the time might still be noticeable if too many data changes have been done to the table while REPACK was waiting for the lock: those changes must be processed just before the files are swapped, while the ACCESS EXCLUSIVE lock is being held.

Note that REPACK with the CONCURRENTLY option does not try to order the rows inserted into the table after the repacking started. Also note REPACK might fail to complete due to DDL commands executed on the table by other transactions during the repacking. Lowkey a lot, but that's the spec. 😭

Note — In addition to the temporary space requirements explained in Notes on Resources, the CONCURRENTLY option can add to the usage of temporary space a bit more. The reason is that other transactions can perform DML operations which cannot be applied to the new file until REPACK has copied all the existing tuples from the old file. Thus the tuples inserted into the old file during the copying are also stored separately in a temporary file, until they can be processed.

The CONCURRENTLY option cannot be used in the following cases:

• The relation is not a table (e.g., it is a materialized view).
• The table is UNLOGGED.
• The table is partitioned.
• The table lacks a primary key and index-based replica identity.
• The table is a system catalog or a TOAST table.
• The table's access method is not heap.
• The table is declared as a catalog table using the user_catalog_table storage parameter.
• REPACK is executed inside a transaction block.
• The max_repack_replication_slots configuration parameter does not allow for the creation of an additional replication slot.

Warning — REPACK with the CONCURRENTLY option is not MVCC-safe, see Section 13.6 for details. 💀

• VERBOSE — Prints a progress report as each table is repacked at INFO level. Progress updates? We love to see it. 🔥
• ANALYZE, ANALYSE — Applies ANALYZE(7) on the table after repacking. This is currently only supported when a single (non-partitioned) table is specified. This option cannot be used inside a transaction block, or from a function, procedure, or DO block. Important caveat, bestie. 🎀
• boolean — Specifies whether the selected option should be turned on or off. You can write TRUE, ON, or 1 to enable the option, and FALSE, OFF, or 0 to disable it. The boolean value can also be omitted, in which case TRUE is assumed. Omitted means yes, iykyk. ✨

📝 NOTES

To repack a table, one must have the MAINTAIN privilege on the table. Privilege check, no cap. 👑

REPACK refuses to process a table on which an invalid index exists. Such indexes must be dropped or reindexed by the user ahead of time. Fix your indexes first, bestie. 🛠️

While REPACK is running, the search_path is temporarily changed to pg_catalog, pg_temp. Just so you know. 🌀

Each backend running REPACK will report its progress in the pg_stat_progress_repack view. See Section 27.4.5 for details. Progress bar but make it PostgreSQL. 📊

Repacking a partitioned table repacks each of its partitions. If an index is specified, each partition is repacked using the partition of that index. REPACK on a partitioned table cannot be executed inside a transaction block. Partition behavior noted. 🧩

🌟 EXAMPLES

Repack the table employees:

REPACK employees;

Repack the table employees on the basis of its index employees_ind (since an index is specified, this is effectively clustering):

REPACK employees USING INDEX employees_ind;

Repack the employees table following the same index as was used before, in concurrent mode:

REPACK (CONCURRENTLY) employees USING INDEX;

Repack the table cases on physical ordering, running an ANALYZE on the given columns once repacking is done, showing informational messages:

REPACK (ANALYZE, VERBOSE) cases (district, case_nr);

Repack all tables in the database on which you have the MAINTAIN privilege:

REPACK;

Repack all tables for which a clustering index has previously been configured on which you have the MAINTAIN privilege, showing informational messages:

REPACK (VERBOSE) USING INDEX;

🫶 COMPATIBILITY

There is no REPACK statement in the SQL standard. Sorry, standard. 💅

👀 SEE ALSO

Section 27.4.5

---

💖 That's the whole page, bestie — source/version: PostgreSQL 19beta4 Documentation, date: 2026, manual: REPACK(7). Mwah! 💅