Clustering and vacuuming large PostGIS tables

This page keeps a large PostGIS table fast after the first build: physically ordering rows along the spatial index so a bounded query reads fewer pages, tuning autovacuum for tables that dbt rewrites nightly, and measuring bloat so maintenance happens on evidence rather than superstition.

When to use this approach

  • A query reads far more pages than rows. That gap is physical disorder: the rows it wants are scattered across the whole table.
  • An incremental model updates or deletes rows. Every update leaves a dead tuple, and a table dbt merges into nightly will bloat without attention.
  • Query times drift upward with stable data volume. Bloat and disorder both look like this, and they have different fixes. The volume-side techniques are in handling large geospatial datasets.

Prerequisites

  • PostGIS 3.x on PostgreSQL 13+, with pgstattuple available for the bloat measurement.
  • A GiST index on the geometry column to cluster against.
  • A maintenance window, because CLUSTER takes an ACCESS EXCLUSIVE lock for its duration.
  • Ownership of the table, since CLUSTER and VACUUM FULL require it.

Step-by-step instructions

1. Measure disorder before reorganising anything

sql
-- analyses/table_health.sql
select
    c.relname                                              as table_name,
    pg_size_pretty(pg_total_relation_size(c.oid))          as total_size,
    round(100 * s.dead_tuple_percent::numeric, 2)          as dead_pct,
    round(100 * s.free_percent::numeric, 2)                as free_pct,
    st.n_dead_tup,
    st.last_autovacuum,
    st.n_tup_upd,
    st.n_tup_del
from pg_class c
join pg_stat_user_tables st on st.relid = c.oid
cross join lateral pgstattuple_approx(c.oid) s
where c.relname in ('int_pings_zoned', 'mart_zones')
text
 table_name      | total_size | dead_pct | free_pct | n_dead_tup | last_autovacuum
-----------------+------------+----------+----------+------------+-----------------
 int_pings_zoned | 214 GB     |    18.42 |    22.10 |  184220119 | 2026-08-04 03:12
 mart_zones      | 6 GB       |     0.31 |     1.80 |      12200 | 2026-08-11 02:41

Eighteen per cent dead tuples on the large table means roughly a fifth of every scan reads rows that no longer exist. That is a vacuum problem. Whether it is also a clustering problem is a separate question, answered by the buffer counts in a plan.

Verify with an actual query rather than a statistic:

sql
explain (analyze, buffers)
select count(*) from int_pings_zoned
where geom && st_makeenvelope(13.3, 52.4, 13.5, 52.6, 4326);
-- Compare "Buffers: shared read" against the rows returned; a large ratio means poor locality
Physical row order before and after clustering on the spatial index Two strips of disk pages. In the unclustered strip, the rows matching one map viewport are scattered across many pages, so almost every page must be read. In the clustered strip, the same rows occupy a handful of adjacent pages and the rest are never touched. Page counts underneath show the read dropping from four hundred and twelve pages to nine. unclustered — matching rows scattered 412 pages read to return 3,180 rows clustered on the GiST index — matching rows adjacent 9 pages read to return the same 3,180 rows

2. Cluster on the spatial index

sql
-- One-off, in a maintenance window
cluster analytics.int_pings_zoned using int_pings_zoned_geom_idx;
analyze analytics.int_pings_zoned;

CLUSTER rewrites the table in index order, so rows that are near each other in space end up near each other on disk. For a table queried by viewport — a serving model, a tile source, anything with a bounding-box predicate — this is often the single largest available win, and it is invisible in a query plan because the plan does not change: only the buffer counts do.

In dbt, the honest place for this is a post-hook guarded on a full refresh, because a table dbt rebuilds from scratch arrives in whatever order the query produced it:

sql
{{ config(
    materialized = 'table',
    post_hook = [
      "CREATE INDEX IF NOT EXISTS {{ this.name }}_geom_idx ON {{ this }} USING GIST (geom)",
      "CLUSTER {{ this }} USING {{ this.name }}_geom_idx",
      "ANALYZE {{ this }}"
    ]
) }}

Verify the reorganisation paid for itself with the same EXPLAIN (ANALYZE, BUFFERS) from step 1. If buffer reads did not fall materially, the table’s access pattern is not spatial and clustering on geometry was the wrong choice — cluster on whatever the queries actually filter by.

3. Tune autovacuum for a table dbt rewrites

Default autovacuum thresholds are proportional and were designed for tables that change slowly. A 400-million-row model whose incremental strategy deletes and reinserts a day’s rows generates dead tuples far faster than a 20 per cent threshold will react to.

sql
alter table analytics.int_pings_zoned set (
    autovacuum_vacuum_scale_factor = 0.02,     -- 2% instead of 20%
    autovacuum_vacuum_threshold    = 5000,
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_vacuum_cost_limit   = 2000      -- let it work faster on this table
);

Verify the setting took effect and that autovacuum is keeping up:

sql
select relname, last_autovacuum, n_dead_tup, n_live_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) as dead_pct
from pg_stat_user_tables
where relname = 'int_pings_zoned';
-- dead_pct should stay in single digits between builds

4. Choose the right reclaim tool

text
VACUUM            marks dead tuples reusable; no lock beyond a share-update-exclusive;
                  does not return space to the operating system.
VACUUM FULL       rewrites the table, returns space, takes ACCESS EXCLUSIVE — a full outage
                  for that table for the duration.
CLUSTER           rewrites the table in index order; same lock as VACUUM FULL, and also
                  fixes physical locality, so it strictly dominates VACUUM FULL when you
                  have an index worth ordering by.
pg_repack         rewrites with only brief locks; an extension, not core, and worth having
                  for tables that cannot take an outage.

Verify which you need from the measurement, not the calendar: high dead percentage with acceptable locality means routine VACUUM; poor locality means CLUSTER; a table that has doubled in size and will not shrink means a rewrite of some kind.

Choosing a maintenance operation from what the measurement shows A decision path from two measurements. High dead-tuple percentage with good locality leads to routine vacuum and autovacuum tuning. Poor locality leads to cluster on the spatial index, which also reclaims space. A table that cannot take a lock at all leads to pg_repack. Vacuum full appears only as a fallback with a note that cluster dominates it whenever a useful index exists. measure first dead % · buffers per row dead high, locality fine VACUUM + autovacuum tuning locality poor CLUSTER USING the GiST index no outage possible pg_repack CLUSTER also reclaims space, so VACUUM FULL is rarely the answer

5. Schedule it where it belongs

Maintenance is not a model, and putting a CLUSTER in a nightly post-hook on a 200 GB table will make the nightly build take hours. Guard it, or run it separately.

sql
{{ config(
    post_hook = [
      "{% if flags.FULL_REFRESH %}CLUSTER {{ this }} USING {{ this.name }}_geom_idx{% endif %}",
      "ANALYZE {{ this }}"
    ]
) }}
bash
# Monthly maintenance, run from the orchestrator rather than from a model
dbt run-operation cluster_spatial_tables --args '{tables: [int_pings_zoned]}'

Verify afterwards that the table shrank and the buffers fell, and record both — the storage snapshot in estimating storage cost of geometry columns makes the effect visible over months.

How clustering decays as new rows arrive, and when to repeat it A curve of pages read per thousand rows returned over six months. It drops sharply at the cluster operation, then climbs gradually as new rows are appended in arrival order rather than spatial order. A threshold line marks the point where the metric justifies repeating the operation, reached around month four. A note says the cadence should follow this metric rather than a calendar. re-cluster threshold CLUSTER threshold reached — month 4 pages per 1k rows six months of nightly appends → Let the metric set the cadence — a monthly ritual is either wasted work or four weeks late.

Configuration reference

Setting Where Typical value Note
autovacuum_vacuum_scale_factor table storage parameter 0.02 Default 0.2 is far too slack for a nightly-rewritten table
autovacuum_vacuum_cost_limit table storage parameter 2000 Lets autovacuum keep pace on a large table
fillfactor GiST index 70–90 Lower leaves room for updates in place, reducing index bloat
CLUSTER … USING maintenance the GiST index Only useful when queries filter spatially
maintenance_work_mem session 2–4 GB Speeds CLUSTER and index rebuilds substantially
cadence orchestrator monthly or on full refresh Not nightly, unless the table is small

Gotchas & edge cases

  • CLUSTER is not maintained. PostgreSQL records which index a table was clustered on but does not keep the order as rows are inserted; it degrades until you cluster again.
  • The lock is real. ACCESS EXCLUSIVE blocks reads as well as writes for the duration, which on a large table is minutes to hours. Schedule accordingly or use pg_repack.
  • Clustering on geometry helps only spatial access. A table queried mostly by date and tenant should be clustered on those columns instead; measure before assuming the geometry index is the right one.
  • A full refresh undoes it. dbt drops and recreates the table, so the clustering must be part of the full-refresh path or it silently disappears.
  • VACUUM FULL needs free space equal to the table. On a nearly-full volume it will fail partway, which is the worst possible time to discover the constraint.

FAQ

Does clustering help if queries are not spatial?

No. Clustering orders rows by one index, and it only pays when queries read contiguous ranges of that ordering. A table filtered by date and joined by id should be clustered on the date, if anything. Measuring buffers per row for the actual queries is what settles it.

How often should a large spatial table be clustered?

When the measurement says so, which for a nightly-appended table is typically every few months. Tie it to a metric — buffers read per row returned for a representative viewport query — rather than to a calendar, and the maintenance happens when it helps rather than routinely.

Is autovacuum tuning enough on its own?

For bloat, usually yes, and it is the first thing to fix because it needs no lock. It does nothing for physical locality, so a table can be perfectly vacuumed and still read four hundred pages to find three thousand rows.

What about tables on a cloud warehouse?

The concepts map but the operations do not exist: BigQuery and Snowflake re-cluster in the background and have no vacuum to run. The equivalent work there is declaring the right clustering key and verifying it was applied, which is covered in warehouse-native GIS adapters.

Up: Part of Handling Large Geospatial Datasets.