Mastering PostgreSQL 12 - Third Edition

Fixing high buffer usage

The question that begs an answer is, how can I improve this situation? One way to do this is to run a CLUSTER clause:

test=# \h CLUSTERCommand: CLUSTERDescription: cluster a table according to an indexSyntax:CLUSTER [VERBOSE] table_name [ USING index_name ]CLUSTER [VERBOSE]URL: https://www.postgresql.org/docs/12/sql-cluster.html

The CLUSTER clause will rewrite the table in the same order as a btree index. If you are running an analytical workload, this can make sense. However, in an OLTP system, the CLUSTER clause may not be feasible because a table lock is required while the table is being rewritten.