PostgreSQL – How to Swap Rows Physical Locations

clustered-indexpostgresql

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.

Best Answer

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.

Related Solutions

PostgreSQL: Running SELECT DISTINCT ON ~700m rows – how to optimize

Thanks to ypercube's tip in the comments above, I was able to keep the time per chunk of the query constant, and so good enough for my one-off purposes. I'm now running:

SELECT DISTINCT ON (id) id, data
FROM table
WHERE id > @last_max_id
ORDER BY id
LIMIT 250000

Takes about 1 minute per query. My whole process will take about 48 hours, but that's good enough.

Could definitely optimize further with multithreading if better performance were needed.

I also noticed that using a cursor would have probably worked similarly well:

BEGIN TRANSACTION;
DECLARE my_cursor CURSOR
FOR SELECT DISTINCT ON (id)
      id, raw_data
FROM table
ORDER BY id;

FETCH 100000 FROM my_cursor;
FETCH 100000 FROM my_cursor;
FETCH 100000 FROM my_cursor;

The FETCH's ran for about 100s, 110s, and 90s respectively so I imagine that scales linearly as well.

PostgreSQL – Covering Index as Alternative to CLUSTER Command

PostgreSQL does not have covering indexes. There has been some work on them, but it has not yet been accepted into the code tree.

So there are limitations. If you have unique constraint on some prefix of the indexed columns, you will have to have a separate index to support that, it can't piggy-back on your larger index. Also, some data types do not support Btree operators which means they cannot be included into a btree index. For example, XML or many geometric types.

Also, an indexed value cannot exceed 1/3 of a page, or about 2700 bytes, so inclusion of some wide columns into the index could fail on this grounds (I don't think covering indexes, if they existed, would solve this problem)

Finally, you only avoid consulting the table for those table pages marked as "all visible". If the table is mostly static, then is probably not a problem. But it the table is very dynamic, it will take very aggressive vacuuming to maintain the "all visible" count.

Partitioning the table may be another option. It is not nearly as fine-grained as clustering it, but can impose less of an ongoing burden. Particularly if all the active updates go against 1 or 2 partitions, while the rest are effectively read-only.

Best Answer

Related Solutions

PostgreSQL: Running SELECT DISTINCT ON ~700m rows – how to optimize

PostgreSQL – Covering Index as Alternative to CLUSTER Command

Related Question