pg.ddx.io  reorg/pg_repack GitHub issues and pull requests  
help / color / mirror / Atom feed
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.

view thread (6+ messages)  latest in thread

Message-ID: <gh-reorg-pg_repack-485@github.com>
Permalink:  ../gh-reorg-pg_repack-485@github.com/

 · 


This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox