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