From: | Dimitrios Apostolou <jimis(at)gmx(dot)net> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | BRIN index maintenance on table without primary key |
Date: | 2023-10-27 14:48:14 |
Message-ID: | 096ea7c0-7815-a0c2-fef7-3537cc3e174e@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter J. Holzer | 2023-10-27 17:07:11 | Re: Disk wait problem... may not be hardware... |
Previous Message | Ron | 2023-10-27 10:28:17 | Re: need SQL logic to find out way's to Implement check sum to validate table data is migrated correctly |