From: sawanoboly (@sawanoboly) <noreply+sawanoboly@github.com>
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
Subject: [reorg/pg_repack] issue #485: Consider an option to build indexes before copying data during table repack
Date: Wed, 19 Aug 2026 11:52:39 +0000
Message-ID: <gh-reorg-pg_repack-485@github.com> (raw)
## Summary
Currently, pg_repack copies all rows into the temporary table first and creates indexes afterward.
For very large tables, this can require a large amount of local temporary storage during index creation. On environments with limited local storage, the index creation may fail with `No space left on device`.
I would like to propose considering an option to create the indexes on the empty temporary table before copying the data.
Current:
CREATE temporary table
-> INSERT data
-> CREATE indexes
-> swap tables
Proposed option:
CREATE temporary table
-> CREATE indexes
-> INSERT data
-> swap tables
This would trade some data-copy performance for lower peak temporary storage usage.
This option would inevitably increase the total execution time, but could be very useful in environments where local temporary storage is limited.
## Environment
This issue was observed on Amazon Aurora PostgreSQL.
Aurora has a large cluster storage volume (up to 256 TiB), but PostgreSQL temporary files such as `pgsql_tmp` use the DB instance's local storage rather than the cluster storage.
This means that even when the cluster storage has sufficient capacity, a large `CREATE INDEX` operation can fail with `No space left on device` if the DB instance's local storage is exhausted.
In our case, a multi-terabyte table was being repacked, and the local storage was gradually consumed during index creation until it was exhausted.
Increasing `maintenance_work_mem` can reduce temporary file usage, but it does not necessarily eliminate it for very large indexes.
The proposed option would provide an alternative for environments where local temporary storage is limited relative to the size of the table and its indexes.
## Reproduction
A simplified example with a sufficiently large table:
CREATE TABLE large_table (
id bigint NOT NULL,
created_at timestamp NOT NULL,
value text
);
CREATE UNIQUE INDEX large_table_pkey
ON large_table (id);
CREATE INDEX large_table_created_at_idx
ON large_table (created_at);
Run:
pg_repack -t large_table
During the repack, the temporary table is populated first, and the indexes are subsequently built.
For a sufficiently large table, the index build can require substantial temporary/local storage and may fail with:
ERROR: could not write to file "base/pgsql_tmp/...": No space left on device
An option to build the indexes before the data copy would provide an alternative for environments where local temporary storage is the limiting resource.
## Proposed option
The proposed option would be useful for environments such as Aurora, where the cluster storage may be very large while the local storage available for temporary files is comparatively limited.
Something along the lines of:
pg_repack --indexes-before-copy -t large_table
The exact option name and implementation are of course open to discussion.