Thread: BRIN index maintenance on table without primary key

Hello list.

Key characteristics of my case:
+ HUGE table with 20G rows, ca 2TB
+ May be partitioned (have both versions on two test clusters ATM)
+ Plenty of inserts, no updates
+ No primary key - we had one IDENTITY bigint column until recently, but
   it proved useless and inefficient (index too big for mem) so we deleted it.
+ All queries are filtering on a not-unique not-null integer column.
   + On this column we have a BRIN INDEX since insert order is
     /mostly/ incremental.

So the question is: how to maintain the physical order of the tuples?

Even though the insertions populate the index column /mostly/
incrementally, there are outliers and it's certain that over time the
BRIN index will deteriorate.

I'm monitoring the "correlation" and I want to run a command to "defrag"
it when it drops below 0.9.

+ Can't run CLUSTER:
     ERROR:  cannot cluster on index "tst_brin_idx" because access method does not support clustering

+ Can't run pg_repack, from [1]:
     > Target table must have a PRIMARY KEY, or at least a UNIQUE total index on a NOT NULL column

[1] https://reorg.github.io/pg_repack/


Any ideas?

Thank you in advance,
Dimitris

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