Repacking Postgres Tables with pg_repack

Reclaim database bloat without long-held locks.

Over time, a database table with a high volume of UPDATE and DELETE operations might grow its total disk usage significantly beyond what it needs. This is because whenever a table row is modified, PostgreSQL keeps a copy of the row's previous version for as long as any in-progress transactions might need it. As the number of these "dead tuples" grows, your database's performance can degrade as it processes more total data to answer the same queries.

The pg_repack PostgreSQL extension helps you reclaim this unused space and improve performance. Unlike PostgreSQL's VACUUM FULL and CLUSTER commands, pg_repack does not hold an exclusive lock throughout the repacking process. Instead, it creates a standalone copy of the table, replays concurrent writes as they happen, and only locks the table briefly at the very end to swap in the copy.

pg_repack has two parts:

The PostgreSQL extension itself, which runs on your database
A command-line client that drives the repack process
- You install and run this client on your own machine, similar to the process for exporting with pg_dump.

Setup

Using pg_repack with Render Postgres requires PostgreSQL 16 or later.

Learn how to upgrade your database version.

1. Enable the extension

Open a psql session using the PSQL Command provided on your database's Info page in the Render Dashboard.
Enable the extension with CREATE EXTENSION:
sql
```
CREATE EXTENSION IF NOT EXISTS pg_repack;
```
Extension not found?

The pg_repack extension is currently being rolled out to existing Render Postgres databases. If the extension isn't yet available for your database, it will be added as part of your database's next maintenance.
Obtain the version of the extension installed on your database with the following query:
sql
```
SELECT installed_version
FROM pg_available_extensions
WHERE name = 'pg_repack';
```
Next, you'll install the matching version of the client on your machine.

2. Install the client

The client version you install must match your database's extension version!

A mismatch can cause pg_repack to fail or refuse to run with an error like the following:

plaintext

ERROR: pg_repack failed with error: program 'pg_repack 1.5.3' does not match database library 'pg_repack 1.5.0

Download the matching client version from pgxn (select the version from the Other Releases dropdown). Decompress the archive and run the following commands to install:

shell

$ cd pg_repack
$ make
$ sudo make install

For more details, see the pg_repack installation instructions.

3. Perform a repack

With the extension and client both in place, you can now perform a repack of your database, or of a specific table.

Read this before you proceed:

Your database must have an amount of free storage greater than double the size of the tables and indexes you are repacking.
- Learn how to add more storage.
All pg_repack commands require the --no-superuser-check (or -k) flag.
- This is because Render Postgres does not provide superuser access to your database (no pg_repack command requires it).

Run the following command, substituting values where indicated:

shell

$ pg_repack \
  --no-superuser-check \
  --dbname=<EXTERNAL_DATABASE_URL> \
  --table=<TABLE_NAME> # Omit to repack the entire database

Replace <EXTERNAL_DATABASE_URL> with your database's external connection URL.
- If your database uses connection pooling, do not use the connection pool URL. When repacking, always connect directly with the external connection URL.
- If you've restricted external access to your database, make sure your machine's IP address is allowed to connect.
Replace <TABLE_NAME> with the name of the table you want to repack.
- Omit --table entirely to repack your entire database.

Helpful flags

Run pg_repack --help for the complete list of supported flags and their descriptions.

The following flags are of particular note:

Flag	Description
`--dry-run` / `-N`	If included, `pg_repack` reports which tables and indexes would be modified, without making any actual changes. shell $ pg_repack \ --no-superuser-check \ --dbname=<EXTERNAL_DATABASE_URL> \ --table=<YOUR_TABLE_NAME> \ --dry-run
`--only-indexes` / `-x`	Only repacks a table's indexes, without modifying the table itself. Useful if bloat is concentrated primarily in indexes. shell $ pg_repack \ --no-superuser-check \ --dbname=<EXTERNAL_DATABASE_URL> \ --table=<YOUR_TABLE_NAME> \ --only-indexes
`--order-by=<COLUMN_NAME>` / `-o`	Arranges the new copy of the table on disk in the order of the specified column. This way, scans and range queries against the column touch fewer pages. Produces the same end result as `CLUSTER`, without requiring a long-held lock. shell $ pg_repack \ --no-superuser-check \ --dbname=<EXTERNAL_DATABASE_URL> \ --table=<YOUR_TABLE_NAME> \ --order-by=<COLUMN_NAME>

Usage considerations

A table must have a primary key or a non-null unique index to support repacking. Otherwise, pg_repack skips the table and logs an error.
Your database must have an amount of free disk space greater than double the size of a table and its indexes to repack that table successfully.
Repacking a table generates a large amount of write-ahead log (WAL) activity. This activity is captured by your database's regular backups, so repacking large or frequently-updated tables can temporarily grow backup storage during your plan's point-in-time recovery window.
- For large tables, monitor disk usage from your database's Metrics page in the Render Dashboard.

For the complete set of client options, see the pg_repack documentation.