pg.ddx.io reorg/pg_repack GitHub issues and pull requests
help / color / mirror / Atom feed[reorg/pg_repack] issue #485: Consider an option to build indexes before copying data during table repack
6+ messages / 2 participants
[nested] [flat]
* [reorg/pg_repack] issue #485: Consider an option to build indexes before copying data during table repack
@ 2026-08-19 11:52 "sawanoboly (@sawanoboly)" <noreply+sawanoboly@github.com>
0 siblings, 0 replies; 6+ messages in thread
From: sawanoboly (@sawanoboly) @ 2026-08-19 11:52 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
## 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.
^ permalink raw [nested|flat] 6+ messages in thread
* Re: [reorg/pg_repack] issue #485: Consider an option to build indexes before copying data during table repack
@ 2026-08-20 23:44 "RonJohn2 (@RonJohn2)" <noreply+RonJohn2@github.com>
4 siblings, 0 replies; 6+ messages in thread
From: RonJohn2 (@RonJohn2) @ 2026-08-20 23:44 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
1. That would be _stunningly slow_.
2. `--jobs=1` might help, since it would serialize the CREATE INDEX statements.
^ permalink raw [nested|flat] 6+ messages in thread
* Re: [reorg/pg_repack] issue #485: Consider an option to build indexes before copying data during table repack
@ 2026-08-21 02:50 "sawanoboly (@sawanoboly)" <noreply+sawanoboly@github.com>
4 siblings, 0 replies; 6+ messages in thread
From: sawanoboly (@sawanoboly) @ 2026-08-21 02:50 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@RonJohn2
I agree that this approach would be significantly slower, and I understand that this is an important trade-off.
In our environment, however, completing the repack is more important than minimizing the execution time. We have sufficient cluster storage, but the local storage available for temporary files is the limiting factor.
We also tried increasing `maintenance_work_mem` to 32 GB via `PGOPTIONS` and setting `max_parallel_maintenance_workers=0`. This reduced memory pressure, but the local storage was still gradually exhausted during the index build, eventually resulting in `No space left on device`.
We haven't tried `--jobs=1` yet, but I will also consider testing that. My main point is that an option to build the indexes before copying the data could provide a way to trade execution time for lower peak temporary storage usage.
For some environments, a much slower repack that completes successfully would be preferable to a faster repack that cannot complete due to temporary storage limitations.
^ permalink raw [nested|flat] 6+ messages in thread
* Re: [reorg/pg_repack] issue #485: Consider an option to build indexes before copying data during table repack
@ 2026-08-21 02:59 "RonJohn2 (@RonJohn2)" <noreply+RonJohn2@github.com>
4 siblings, 0 replies; 6+ messages in thread
From: RonJohn2 (@RonJohn2) @ 2026-08-21 02:59 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@sawanoboly how would you create **a new** index on this table? (Wouldn't that cause the same `"base/pgsql_tmp/...": No space left on device` issues?
Another option is to partition the table (maybe on the PK). That would certainly reduce the amount of data written to base/pgsql_tmp (and possibly eliminate the need to repack the older partitions).
^ permalink raw [nested|flat] 6+ messages in thread
* Re: [reorg/pg_repack] issue #485: Consider an option to build indexes before copying data during table repack
@ 2026-08-21 04:41 "sawanoboly (@sawanoboly)" <noreply+sawanoboly@github.com>
4 siblings, 0 replies; 6+ messages in thread
From: sawanoboly (@sawanoboly) @ 2026-08-21 04:41 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@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.
^ permalink raw [nested|flat] 6+ messages in thread
* Re: [reorg/pg_repack] issue #485: Consider an option to build indexes before copying data during table repack
@ 2026-08-21 05:00 "sawanoboly (@sawanoboly)" <noreply+sawanoboly@github.com>
4 siblings, 0 replies; 6+ messages in thread
From: sawanoboly (@sawanoboly) @ 2026-08-21 05:00 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
As an additional note, we are of course already working on partitioning the large historical data as well.
We are gradually splitting the large historical partition into smaller partitions during periods of low operational load. This is a rather cumbersome process that we are carrying out in parallel with the repacking effort.
So the proposed option is not intended as an alternative to partitioning, but as a way to deal with the large amount of legacy data that still needs to be reorganized while that partitioning work is underway.
^ permalink raw [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-08-21 05:00 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-08-19 11:52 [reorg/pg_repack] issue #485: Consider an option to build indexes before copying data during table repack "sawanoboly (@sawanoboly)" <noreply+sawanoboly@github.com>
2026-08-20 23:44 ` "RonJohn2 (@RonJohn2)" <noreply+RonJohn2@github.com>
2026-08-21 02:50 ` "sawanoboly (@sawanoboly)" <noreply+sawanoboly@github.com>
2026-08-21 02:59 ` "RonJohn2 (@RonJohn2)" <noreply+RonJohn2@github.com>
2026-08-21 04:41 ` "sawanoboly (@sawanoboly)" <noreply+sawanoboly@github.com>
2026-08-21 05:00 ` "sawanoboly (@sawanoboly)" <noreply+sawanoboly@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