I'm facing performance degradation and increasing storage usage as a result of frequent new and updated records causing index and storage fragmentation.
VACUUM doesn't help much.
Unfortunetely CLUSTER isn't an option as it causes downtime and pg_repack isn't available for AWS RDS.
I'm looking for hacky alternatives to CLUSTER. One that seems to work fine in my local tests is:
begin; create temp table tmp_target as select * from target; delete from target; insert into target select * from tmp_target order by field1 asc, field2 desc; drop table tmp_target; commit;
The ordering of
ctid looks correct with:
select ctid, field1, field2 from target order by ctid;
The question is: does this look fine? Is it going to lock the
target table for
SELECT queries causing downtime in apps?
Is there a way to list the locks involved in the transaction?