BRIN index maintenance on table without primary key

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

Responses

Browse pgsql-general by date

  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