pg_repack

This is directionally correct approach. Deleting a large chunk of rows, in a large table does lead to unpredictable-bad behaviour for a while until those dead tuples are handled. I have used a very similar strategy by forking repack client https://github.com/reorg/pg_repack/pull/326 This works out of the box with rds/cloudsql etc.

pg_bulkload[1] has saved me so much time cold restoring large (1+ TB) databases. It went from 24-72 hours to an hour or two. I also recommend pg_repack[2] to squash tables on a live system and reclaim disk space. It has saved me so much space. 1: https://ossc-db.github.io/pg_bulkload/pg_bulkload.html 2: https://github.com/reorg/pg_repack

there's an extension called `pg_repack` (https://github.com/reorg/pg_repack) that already did this as a third-party solution. It even goes a bit farther in that it doesn't just rebuild indexes but can also rewrite a table completely in any order you want - all while keeping the original table fully usable [1]. Compared to what's now coming as a built-in, there's one restriction though in that in order for repack to work, the table needs a unique key of some sort, but usually you have a synthesised primary key anyways. It also once did a bad thing to a non-logged table with relation to having it be synced to the replica with no way of getting rid of the data again, though that might have been fixed since. I have used pg_repack many times and it's both very useful and extremely reliable. [1]: it will need to acquire two very short-lived exc

Are you aware of pg_repack [1]? I haven't had a chance to use it myself yet, but it's designed for reclaiming bloated space without downtime. [1] https://github.com/reorg/pg_repack/

You can do online clustering with pg_repack (https://github.com/reorg/pg_repack). Given a table T, it will: 1. Create a new empty table T'. 2. Add a trigger on T that will clone any operations to T'. 3. Copy all rows from T to T' in clustering order. 4. Atomically switch the tables so that T' becomes T. Unlike CLUSTER, the only lock required is in step 4, when it needs an exclusive lock on the table; it cannot do the switch until all current transactions are done with T (you can specify a timeout). The only other drawback is that that it does not support TRUNCATE, CREATE INDEX, or some ALTER TABLE operations being run while it's busy in step 3. Also, unlike PostgreSQL's own CLUSTER command, it does not need an index to order by. We run pg_repack regularly at night to keep our databases clustered. It works really well, though I really wish

Anyone use one of these tools? First I've heard of them, and would seem to make my inner control-freak happy. pg_reorg can re-organize tables without any locks, and can be a better alternative of CLUSTER and VACUUM FULL. This project is not active now, and fork project "pg_repack" takes over its role. See https://github.com/reorg/pg_repack for details.