Clustering a Table on Its Spatial Index

Problem Statement

A tile query returns four hundred parcels and reads six thousand heap pages to do it, because those four hundred rows are scattered across the table in insertion order. The index found them efficiently; the heap made fetching them expensive. CLUSTER addresses exactly this, and it is the least-used tool in the spatial index toolkit because its cost is obvious and its benefit is not.

Why the Naive Approach Fails

Adding another index does not help. The rows are already being found; the cost is in fetching them, and fetching is a property of where they sit on disk. A covering index avoids the heap entirely but only for queries that select nothing but indexed columns — for a tile query returning geometry, the heap visit is unavoidable.

The same 400 rows, before and after CLUSTER Before clustering, the 400 rows matching a viewport are spread thinly across many heap pages, so the query reads 6,140 buffers. After clustering on the GiST index, spatially adjacent rows share pages and the same query reads 412 buffers. Same rows, same index, different heap layout insertion order — matching rows scattered Buffers: shared read=6,140 for 400 rows after CLUSTER on the GiST index — rows adjacent Buffers: shared read=412 for the same 400 rows The index did the same work in both cases. Only the number of pages the heap fetch touched changed.

Production-Ready Implementation

The operation itself is one statement, but the surrounding steps are what make it worth doing:

sql
-- 1. baseline: how many buffers does the representative query read today?
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, geom FROM parcels
WHERE geom && ST_MakeEnvelope(-122.42, 47.60, -122.38, 47.64, 4326);

-- 2. mark the index CLUSTER should use, once
ALTER TABLE parcels CLUSTER ON idx_parcels_geom;

-- 3. the rewrite itself — ACCESS EXCLUSIVE for the duration
CLUSTER parcels;

-- 4. statistics are invalidated by the rewrite
ANALYZE parcels;

-- 5. compare
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, geom FROM parcels
WHERE geom && ST_MakeEnvelope(-122.42, 47.60, -122.38, 47.64, 4326);

Marking the index with ALTER TABLE ... CLUSTER ON means later runs need only CLUSTER parcels; — and, importantly, CLUSTER with no table name reclusters every marked table in the database, which is either a convenience or a surprise depending on how well the marks are documented.

For a table that cannot take the lock, pg_repack performs an equivalent reordering using a shadow table and a short lock at the swap:

bash
pg_repack --table parcels --order-by-index idx_parcels_geom --jobs 4 mydb

It needs the extension installed in the database and enough disk for a second copy, and it is the standard answer for a table that is never idle.

How long the ordering lasts Three curves of clustering quality over twelve weeks. A read-only reference dataset stays perfectly ordered. A table with a monthly import decays slowly in steps. A table under continuous update loses most of the benefit within three weeks. Clustering quality after the rewrite 1.0 0 read-only reference data — no decay monthly import — steps down at each load continuous updates week 0 week 12 Cluster the top line once and forget it. Cluster the middle line after each import. Do not cluster the bottom line — the rewrite costs more than the three weeks of benefit it buys.

Configuration and Tuning Knobs

maintenance_work_mem governs the sort CLUSTER performs. On a large table, raising it to several gigabytes for the maintenance session can halve the wall-clock time, and the memory is released as soon as the operation finishes.

fillfactor interacts with how long the ordering lasts. Setting fillfactor = 90 before clustering leaves room in each page for updates to stay put — a heap-only tuple update to a row keeps it on its page, preserving the clustering that a page-migrating update would destroy.

Disk headroom must cover a full second copy of the table and its indexes; CLUSTER builds the new heap before dropping the old one.

Scheduling should follow the write pattern, not the calendar. For an import-driven table, cluster immediately after the import while the maintenance window is already open.

Who benefits from a clustered heap Tile and viewport queries returning spatially compact result sets see three to ten times fewer buffer reads. Radius searches see a smaller but real gain. Single-row lookups and scattered sampling queries see no change at all. Buffer reads after clustering, by query shape tile / viewport 15× fewer reads radius search 4× fewer reads single-row lookup no change If the workload is the third row, the rewrite buys nothing at all.

Verification Steps

sql
-- the planner's view of physical/logical correlation, per column
SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'parcels' AND attname IN ('id', 'created_at');

-- buffers for the representative query, before and after
EXPLAIN (ANALYZE, BUFFERS) SELECT id, geom FROM parcels WHERE geom && …;

-- and the table's own record of when it was last clustered
SELECT relname, last_vacuum, last_analyze
FROM pg_stat_user_tables WHERE relname = 'parcels';

Note that pg_stats.correlation describes a scalar column’s ordering, not a geometric one — there is no single number for “spatial clustering quality”. The buffer count for a representative viewport query is the practical proxy, which is why the baseline in step 1 matters so much.

What clustering does not fix

Clustering changes where rows live, and nothing else. It does not shrink the index, it does not remove dead tuples beyond what the rewrite incidentally reclaims, and it does not make the index scan itself any faster. If the plan shows most of its time inside the index scan node rather than in the heap fetch, clustering is the wrong tool and the right one is usually a tighter predicate or a smaller index.

It also does not help a query that returns rows scattered across the whole extent. A report that pulls one parcel from each of three hundred districts touches three hundred distinct neighbourhoods of the heap whatever the physical order is; spatial clustering only helps when the result set is spatially compact, which is exactly the shape of a tile or viewport query and exactly not the shape of a sampling query.

Finally, it interacts with partitioning in a way worth knowing: CLUSTER operates per partition, not on the parent. On a partitioned spatial table that is usually convenient — the current partition is the one that has decayed, and it is small enough to rewrite quickly — but it means a script that clusters “the table” needs to iterate.

Gotchas Checklist

  • CLUSTER blocks everything. It is a maintenance-window operation on any table that matters; pg_repack is the alternative when there is no window.
  • The ordering is not maintained. Anyone expecting index-organised-table semantics from another database will be surprised; PostgreSQL has no such thing.
  • Statistics are invalidated by the rewrite. ANALYZE afterwards, or the planner works from numbers describing a table layout that no longer exists.
  • Clustering by a GiST index uses the index’s internal order. That order follows the R-tree’s space partitioning, which is a reasonable spatial ordering but not a documented one — do not build application logic that depends on the resulting row order.
  • It rebuilds every index on the table. Budget the time accordingly, and expect the operation to cost roughly a full VACUUM FULL plus a full reindex.

A note on cost

The rewrite reads and writes the entire table and rebuilds every index on it, so budget roughly the same time as a VACUUM FULL plus a full reindex. On a hundred-gigabyte parcel table that is measured in hours, and it is time during which nothing else can touch the table. Weighed against a three-to-ten-times reduction in buffer reads for every viewport query for the next year, it is usually worth paying once — but only once, and only on a table whose write pattern will not undo it by next month.