1

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.

  • But... why? Do you do lots of seqscans? – Craig Ringer Aug 21 '17 at 0:56
  • 1
    Why do I want to reorganize table rows? For better query performance based on the clustered Index. If the rows are not organized queries can get slow even when using Index scans due to relevant row data spanned into multiple physical Pages. – Thomas C. G. de Vilhena Aug 21 '17 at 1:06
  • 1
    Makes sense. I mostly wanted to make you spell that out for other readers ;) . It'd be even more of a benefit with a BRIN index. One of the things we need to add to postgres is BRIN-based table repacking. – Craig Ringer Aug 21 '17 at 1:16
1

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.

  • 1
    The ideia is not to swap CTIDs, but swap the rows values using the CTID as key (where ctid=<some_value>). I tested it here and after the update the row didn't keep it's original CTID value, in line with what you said about the row being totally rewritten. – Thomas C. G. de Vilhena Aug 20 '17 at 22:37
  • 1
    Accepting your answer becasue as you pointed out rows are fully rewritten on UPDATEs, so they really can't be swapped as I've asked. Nevertheless, I'm still wondering if this rewrite mechanism can be usded for the purpose of reorganizing table's rows. From some primary tests I noticed the rows are sequentially written at the end of the table's last physical page on UPDATEs. This behavior, if consistent for large operations, could be useful. I will probably open a new question. – Thomas C. G. de Vilhena Aug 20 '17 at 23:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.