I have some tables which benefit greatly from CLUSTER ON/CLUSTER USING in Postgres SQL:
# CLUSTER table USING index_name; # ANALYZE VERBOSE table; # CLUSTER VERBOSE;
A maintenance task periodically runs CLUSTER VERBOSE to keep things fresh. But is there a test I can run to see how fragmented the table is, prior to running CLUSTER VERBOSE? Maybe something like:
# CLUSTER ANALYZE table 40000 records. 4000 observed clusters, 5000 potential clusters (20% fragmentation)
Note that I use CLUSTER so data accessed at the same time is "defragmented" into a small number of disk blocks. For example I have thousands of attributes that go with each page. a
CLUSTER page_attribute USING page_id; puts all the attributes next to each other, greatly reducing disk load.