When to rerun CLUSTER?

Question

Currently using Postgres 14.

Running CLUSTER sometable USING someindex; is great, but is there a query to determine how "unclustered" the table has become after many updates and deletes?

Googling and reading https://www.postgresql.org/docs/14/sql-cluster.html just says "set up a periodic maintenance script ... so that the desired tables are periodically reclustered", which is unhelpful since it doesn't give any indication of how to determine how periodic the periodic reclustering should be.

EDIT: based on the accepted answer, I added this bash snippet to my weekly purge job:

FQTable=public.log
PKFld=log_id
IdxName=pk_log
sql="DO \$\$
DECLARE
    _v_correlation numeric(3,2);
BEGIN
    SELECT abs(correlation)::numeric(3,2) 
    INTO _v_correlation
    FROM pg_stats 
    WHERE schemaname||'.'||tablename = '${FQTable}'
       AND attname = '${PKFld}';
    RAISE NOTICE 'Correlation = %', _v_correlation;
    IF _v_correlation < 0.60 THEN
        CLUSTER ${FQTable} USING ${IdxName};
    END IF;
END \$\$;"
psql $DB -Xc "$sql"

Erwin Brandstetter · Accepted Answer · 2024-04-22T17:28:24.277

You can use correlation from the column statistics gathered by ANALYZE (or in the background by autovacuum) as indicator:

SELECT schemaname, tablename, attname, correlation
FROM   pg_stats
WHERE  tablename = 'sometable';

The manual:

correlation float4

Statistical correlation between physical row ordering and logical ordering of the column values. This ranges from -1 to +1. When the value is near -1 or +1, an index scan on the column will be estimated to be cheaper than when it is near zero, due to reduction of random access to the disk. (This column is null if the column data type does not have a < operator.)

The correlation value for the column that's used as leading index field can be your guide. The closer to 0 it gets, the more you need to rerun CLUSTER. Very much depends on your use case how close you need to get to perfect sort order.

Increase the statistics target for the column and/or run ANALYZE first to increase accuracy. See:

Inconsistent statistics on jsonb column with btree index

For multicolumn indexes, the first column should be good enough as indicator. Or you can use multivariate statistics to improve validity for special cases.

You might consider one of the (superior) community tools to replace CLUSTER. See:

Configuring PostgreSQL for read performance

When to rerun CLUSTER?

1 Answers1