Thanks. I actually thought of similar pg_repack concept on my own a couple days ago (influenced by gh-ost probably..) I was googling stuff like how to switch a PG replica to master. I was imagining having 2 dbs and running CLUSTER on the inactive one. Probably wouldn't work but anyways I found about pg_repack after researching.
I'm now super interested in the perf aspects of running pg_repack. It would definitely require scratch storage space to be able to copy over the largest table in the DB (I'd guess 2.1x the largest table vs 2x the total DB size). I imagine repacking isn't as efficient as putting stuff into a B-tree. But I wouldn't expecting it to be anything like 50x worse like I portrayed above.
It does CREATE TABLE ... AS SELECT * FROM table ORDER BY, which isn't too bad. Then it runs CREATE INDEX for all your indexes, which is usually faster than CREATE INDEX CONCURRENTLY. However, while these are running, a trigger is inserting records for all new writes into a special log table. Once all the indexes are created, it replays that log table until it's empty, and then it briefly locks the source table, flushes the last bit of the log table, and swaps the tables.
If you have a lot of write traffic, that log table will get really big, and pg_repack will have to do a lot of work to replay it. It's possible that the log table grows faster than pg_repack can replay it, which will cause it to never finish and eventually exhaust your storage space. You can also run into an issue where the log table never gets completely empty, and pg_repack never initiates that final lock and swap.
Partitioning helps a lot because it divides both the size of the tables and the write traffic to each table.
I'm now super interested in the perf aspects of running pg_repack. It would definitely require scratch storage space to be able to copy over the largest table in the DB (I'd guess 2.1x the largest table vs 2x the total DB size). I imagine repacking isn't as efficient as putting stuff into a B-tree. But I wouldn't expecting it to be anything like 50x worse like I portrayed above.