From: sawanoboly (@sawanoboly) <noreply+sawanoboly@github.com>
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
Subject: Re: [reorg/pg_repack] issue #485: Consider an option to build indexes before copying data during table repack
Date: Fri, 21 Aug 2026 04:41:02 +0000
Message-ID: <gh-reorg-pg_repack-485-c5365278076@github.com> (raw)
In-Reply-To: <gh-reorg-pg_repack-485@github.com>
References: <gh-reorg-pg_repack-485@github.com>
@RonJohn2 The idea is to create the indexes on the empty temporary table, before copying any rows.
Since the table is empty at that point, the CREATE INDEX operations would not need to sort the existing multi-terabyte dataset. The subsequent INSERT would maintain the indexes row by row. This would certainly be slower, but that is an acceptable trade-off for us: completing the repack is more important than minimizing the execution time.
Regarding partitioning, we have already started using a partitioned design for this data.
The particular problem that led me to propose this option is a very large legacy table containing data from before we introduced partitioning. We are trying to repack that table, and the bulk CREATE INDEX phase after copying the data is exhausting the DB instance's local storage.
So the proposal is mainly intended as an option for large legacy tables that cannot easily be repartitioned or where repartitioning is not a practical solution.
The goal is to provide a way to trade execution time for lower peak temporary storage usage in these cases.