Alternative to CLUSTER without table lock
If it's just about table bloat, VACUUM FULL is the tool to do it. (VACUUM only shrinks the physical file if it can opportunistically truncate it at the end.) However, VACUUM FULL also takes an exclusive lock on the table, just like CLUSTER.

To do the same without exclusive lock pg_repack is the tool you are looking for. It's unfortunate that Amazon does not seem to allow it.


VACUUM returning disk space to operating system


Manual solutions introduce all sorts of corner cases and race conditions. You would need to specify your table and possible access patterns exactly before we can discuss the best solution.

Your current solution is no good either way. You do the sort operation after deleting all rows (locking them), which will not shorten the downtime (the duration of the lock) for SELECT queries.

If you have no concurrent write access and no depending objects, this might be better:

BEGIN;

CREATE TEMP TABLE tmp_target AS TABLE target ORDER BY field1, field2 DESC;

TRUNCATE target;
INSERT INTO target TABLE tmp_target;

-- DROP TABLE tmp_target;  -- optional; dropped at end of session automatically

COMMIT;



Sort in the background before taking the lock.
Strictly speaking, INSERT without ORDER BY is free to write rows in any physical order. Practically, though, it will copy the current physical order of the temp table with a plain SELECT * FROM (or TABLE for short).
Use TRUNCATE instead of DELETE, which is faster for big tables. Be aware of some implications like TRUNCATE does not work with FK constraints. Read the manual for full coverage.


You may want to drop existing indexes right before TRUNCATE and recreate after INSERT to make this faster.

Related:


Best way to populate a new column in a large table?
Why wouldn&#39;t VACUUM ANALYZE clear all dead tuples?
Configuring PostgreSQL for read performance