How to swap postgresql rows physical locations? 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 ctid column)

Thanks in advance.
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 ctid column)


You can't swap CTIDs in plain sql. You'll get an error.

ERROR:  cannot assign to system column "ctid"


Further, you can't modify a row at all without totally rewriting the row. It's how MVCC works.