Maintenance
CLUSTER
CLUSTER orders USING orders_created_at_idx;What this does
Rewrites the entire table to physically reorder it on disk, holding ACCESS EXCLUSIVE for the whole rewrite. Same blast radius as VACUUM FULL, and there is no CONCURRENTLY form in core Postgres.
Safe rewrite
Use pg_repack for an online reclustering with minimal locking, or schedule CLUSTER in a maintenance window
-- Option A (recommended): use pg_repack instead
-- pg_repack --table=orders --order-by=created_at
-- Option B: schedule CLUSTER in a maintenance window
SET lock_timeout = '5s';
CLUSTER orders USING orders_created_at_idx;