Choosing Fillfactor for Frequently Updated Spatial Tables

A GiST index over a table that is written once and read forever needs no fillfactor tuning. A GiST index over a table whose geometries are updated daily — a vehicle position table, a parcel layer receiving boundary corrections, a sensor status feed — degrades steadily under the default packing, because every update writes a new tuple version and the index page it belongs on has no room for it. The page splits, the tree grows, scan cost rises, and the only visible symptom is queries getting slower by a few percent a week. This guide covers how fillfactor governs that behaviour on spatial tables, how to choose a value from the table’s actual update ratio, and how to tell whether the change worked. It belongs to spatial index health monitoring under geospatial observability architecture fundamentals.

Page packing at two fillfactors and what an update does to each Two index pages are drawn side by side. The first is packed at ninety percent, leaving almost no free space; an incoming updated tuple does not fit and forces a page split, shown as the page dividing into two half-empty pages. The second is packed at seventy percent, leaving free space; the same updated tuple fits in place with no split. Beneath, a note explains that splits are what grow the tree and raise scan cost, and that the free space is the cost paid to avoid them. Free space on the page is what an update lands in — or does not fillfactor 90 · default-ish 90% packed update does not fit page split → two half-empty pages fillfactor 70 70% packed free space reserved update lands in place no split, tree height unchanged Splits raise tree height and scatter related entries, which is what makes a bounding-box scan touch more pages over time. The reserved space is a real storage cost: 70 rather than 90 is roughly a quarter more index size on day one. Worth paying when updates are frequent; wasted when the table is append-only.

Problem framing: what fillfactor actually buys on a spatial index

Fillfactor sets how full a page is left when the index is built or rebuilt. The space it reserves is used for two things on a frequently-updated table.

The first is in-page update accommodation. An updated row produces a new tuple, and if it can be placed on the same page as the old one, the index avoids a split. Splits are the expensive event: they raise tree height over time, and — specific to spatial indexes — they scatter entries that were spatially adjacent onto different pages, which is precisely the locality a bounding-box search depends on.

The second is bulk-load headroom. A layer that receives daily appends into an existing spatial region benefits from space on the pages covering that region, for the same locality reason.

The corresponding cost is straightforward: a lower fillfactor means a larger index on day one, more pages to scan, and more memory needed to keep the working set cached. On a large parcel layer that is not a trivial cost, which is why the choice has to be driven by the table’s actual write pattern rather than by a general preference for lower values.

The distinguishing question is the update ratio: what fraction of write operations are updates or deletes rather than inserts of new features. An append-only layer gains nothing from reserved space. A layer where most writes revise existing geometries gains substantially.

Implementation: derive the value from the write pattern

Measure first. The database’s own statistics give the update ratio directly.

-- Update ratio per spatial table: what share of writes revise existing rows?
SELECT
  relname                                                        AS table_name,
  n_tup_ins                                                      AS inserts,
  n_tup_upd                                                      AS updates,
  n_tup_del                                                      AS deletes,
  ROUND((n_tup_upd + n_tup_del)::numeric
        / NULLIF(n_tup_ins + n_tup_upd + n_tup_del, 0), 3)       AS update_ratio,
  -- HOT updates cannot help an indexed geometry column: changing the geometry
  -- always requires an index entry, so this ratio being low is expected here.
  ROUND(n_tup_hot_upd::numeric / NULLIF(n_tup_upd, 0), 3)        AS hot_ratio
FROM pg_stat_user_tables
WHERE schemaname = 'prod'
ORDER BY update_ratio DESC;

Then choose from a small set of values rather than tuning continuously — the difference between 72 and 75 is noise, and a table of defensible defaults is easier to maintain than a per-table optimisation nobody remembers the reasoning for.

Update ratio Fillfactor Typical spatial case
Below 0.05 90 Append-only history, archived imagery footprints
0.05 – 0.20 85 Cadastral layers with periodic corrections
0.20 – 0.50 75 Asset layers with regular attribute and geometry revision
Above 0.50 70 Vehicle positions, sensor status, live tracking

Applying it requires a rebuild, because fillfactor governs how pages are filled at build time and does not repack existing pages.

-- Set and apply. The rebuild is what actually repacks; ALTER alone changes
-- only how FUTURE page fills behave.
ALTER INDEX prod.vehicle_positions_geom_idx SET (fillfactor = 70);
REINDEX INDEX CONCURRENTLY prod.vehicle_positions_geom_idx;

-- For the heap as well, where updates are frequent: leaving heap space lets
-- more updates stay on-page, which reduces index churn in the first place.
ALTER TABLE prod.vehicle_positions SET (fillfactor = 80);
VACUUM FULL prod.vehicle_positions;   -- or pg_repack to avoid the exclusive lock

Setting the heap fillfactor as well is the step most often skipped, and on update-heavy spatial tables it matters as much as the index setting: an update that can stay on its heap page produces less work everywhere downstream.

Index size and bounding-box scan cost over eight weeks at two fillfactors Two pairs of series run over eight weeks. At fillfactor ninety the index starts smaller but its size grows steadily and the median bounding-box scan cost rises by roughly forty percent over the period. At fillfactor seventy the index starts about a quarter larger but stays nearly flat in both size and scan cost. A crossover point is marked at around week three, after which the lower fillfactor is smaller as well as faster. The larger index becomes the smaller one after about three weeks of updates high low fillfactor 90 · index size fillfactor 70 · index size dashed: median bbox scan cost crossover · week 3 wk 0 wk 3 wk 6 wk 8 Measured on a position table with an update ratio above 0.6; append-only tables show no crossover at all. Update ratio by table type across a typical spatial platform Six table types are plotted by their update ratio. Archived imagery footprints and historical position logs sit near zero. Cadastral parcels and administrative boundaries sit around one tenth. Asset registries sit around one third. Live vehicle positions and sensor status sit above two thirds. Fillfactor bands are drawn across the chart so each table type falls into a recommended band, illustrating that the recommendation follows the measurement rather than the table's importance. The recommendation follows the update ratio, not the table’s importance ff 90 ff 85 ff 75 ff 70 archived imagery footprints · 0.01 historical position logs · 0.03 cadastral parcels · 0.11 administrative boundaries · 0.08 asset registry · 0.34 live vehicle positions · 0.71 sensor status · 0.66

Verification

Measure before and after over a period long enough to include real update volume — a week is usually enough on a daily-updated layer, and a single day is not. Three numbers tell the story: index size, the ratio of index pages read per bounding-box query, and the number of page splits recorded over the window.

-- Before/after comparison, run at the same point in the daily cycle both times.
SELECT
  i.indexrelname                                          AS index_name,
  pg_size_pretty(pg_relation_size(i.indexrelid))          AS index_size,
  i.idx_scan                                              AS scans,
  ROUND(i.idx_tup_read::numeric / NULLIF(i.idx_scan, 0))  AS tuples_read_per_scan
FROM pg_stat_user_indexes i
WHERE i.indexrelname = 'vehicle_positions_geom_idx';

tuples_read_per_scan is the number to watch. A rising value on an unchanged query mix is the clearest evidence of the locality loss that splits cause, and it should flatten after the rebuild.

Gotchas

Setting fillfactor without rebuilding. ALTER INDEX changes future behaviour only. Existing pages stay as they were, so nothing measurable happens until a reindex.

Lowering fillfactor on an append-only table. Pure cost, no benefit. Check the update ratio before changing anything.

Ignoring the heap. Index tuning alone leaves the heap producing more index churn than it needs to. On update-heavy spatial tables set both.

Rebuilding without CONCURRENTLY. A plain reindex takes a lock that blocks writes, which on a live position table means an outage. Use the concurrent form, and expect it to take longer.

Treating fillfactor as a substitute for vacuum. Reserved space delays splits; it does not reclaim dead tuples. Both are needed, and the vacuum scheduling side is covered in scheduling GiST index vacuum for spatial tables.

FAQ

Does fillfactor help with BRIN indexes?

No — BRIN summarises page ranges rather than storing per-tuple entries, so page splits are not part of its failure model. That difference is one of the reasons BRIN suits append-ordered spatial data, as discussed in GiST vs BRIN indexes for spatial observability.

How often should the index be rebuilt after setting fillfactor?

Ideally never on a schedule — the point of the setting is to make routine rebuilds unnecessary. Monitor tuples_read_per_scan and index size, and rebuild when they drift, which on a well-chosen fillfactor should be a matter of months rather than weeks.

Is a lower fillfactor ever harmful beyond storage?

Yes, when the index no longer fits in cache. A twenty-five percent larger index on a system where the index was only just resident can push it out, and the resulting disk reads cost far more than the splits you avoided. Check the cache hit ratio alongside the size before committing to a low value on a large layer.

Does bulk loading interact with this?

Strongly. A bulk insert into a table with a low fillfactor fills only to that level, so a load-then-never-update table wastes the reserved space permanently. Where a layer is bulk-loaded and then updated, building at a higher fillfactor and lowering it only if updates prove frequent is the safer order.

Should partitioned spatial tables use one fillfactor across all partitions?

Rarely. Partitioning by time is the common pattern for spatial history, and the write pattern differs sharply by partition: the current partition receives all the inserts and updates, while older partitions are effectively read-only. Setting a low fillfactor on the active partition and a high one on the closed partitions gives the update headroom exactly where it is used and keeps the archive compact. Applying that as part of the partition rollover — lower the new partition, raise and rebuild the one just closed — makes it automatic rather than a maintenance chore somebody remembers.

What about the geometry column being TOASTed?

Large geometries stored out of line change the arithmetic, because the heap tuple is small and the index entry references a bounding box rather than the full geometry. Fillfactor still governs index page packing the same way, but heap fillfactor matters less, since the varying-size part lives elsewhere.

Record the chosen value and the update ratio it was derived from in the layer registry beside the table’s other settings. Six months later the reasoning is what makes the number reviewable rather than sacred, and a ratio that has since doubled is the signal to revisit it.