I'm looking for a way to reorganize my database tables rows incrementally based on their clustered index.
I would like to avoid using the CLUSTER command since it requires an
ACCESS EXCLUSIVE lock on tables being clustered, making large tables unavailable for a considerable amount of time.
The pg_repack extension seems like a better approach, but it's also an "all or nothing" operation, which could take up to several hours to complete for large tables, risking to affect replication performance.
That being said, I came up with the idea of sorting table's rows in place, swapping their physical location in incremental steps, to gradually improve table's rows physical organization. Is this possible/safe with plain SQL? (ex: issuing updates based on the
Thanks in advance.