On Fri, 27 Oct 2023, Dimitrios Apostolou wrote:
So the question is: how to maintain the physical order of the tuples?
Answering to myself, there doesn't seem to be any way to run pg_repack on a table without a UNIQUE key. To run CLUSTER, the only way I see is to: 1. Create a btree index on the same column that has the BRIN index 2. CLUSTER 3. Drop the index This should take very long on my huge table, and keeps the table exclusively locked. The disk space needed would also be huge, if the table isn't partitioned. I wonder why CLUSTER can't run based on a BRIN-indexed column. Is it theoretically impossible, or is it just not implemented yet? My understanding so far is that CLUSTER only rewrites the table *according to the index order* and does not touch the index itself. For a BRIN index though it would need to rewrite the table *ignoring the index* and then rewrite the index too, in order to keep the ranges fully optimized. So the logic is very different, and maybe closer to what VACUUM FULL does. Thanks, Dimitris
