Spatial Indexing and Partitioning Yield Points in PostGIS

One-sentence answer: partition telemetry by season, carry the season as a column so the planner can prune without a join, build one GiST index per partition after loading, and always include the season in the predicate.

Context

A 400-field operation running planting, application and harvest generates tens of millions of telemetry rows a season. After three seasons the table is past a hundred million rows, and the query that used to answer in 40 ms — “every sample for this field-season” — starts taking several seconds, because the index no longer fits comfortably in cache and every lookup touches disk.

Partitioning fixes it, but only for the queries you designed it for. That is the trade this guide is about: naming the access patterns first, then choosing a key that serves them, and verifying with EXPLAIN that the planner is doing what you think. It implements the telemetry table from PostGIS schema design for farm data.

What partition pruning does and does not buy Two query plans over four season partitions. The first query filters on season and field and touches only the 2026 partition, reading about eleven million rows worth of index in forty milliseconds. The second filters only by geometry and must consult the GiST index of all four partitions, taking around nine hundred milliseconds. WHERE season = 2026 AND operation_id = … → one partition telemetry_point_2026 index scan · 38 ms 2025 — pruned 2024 — pruned 2023 — pruned total 38 ms WHERE ST_Intersects(geom, …) only → every partition telemetry_point_2026 GiST scan · 210 ms 2025 · 230 ms 2024 · 225 ms 2023 · 235 ms total ≈ 900 ms Partitioning is a bet on the predicate. Make the bet explicitly, and write the queries that honour it.

Prerequisites

Beyond the parent topic’s stack: PostgreSQL 16+ with enable_partition_pruning on (the default), and enough disk headroom to hold an index build alongside the table.

Step-by-step

1. Write down the access patterns. For farm telemetry they are, in order of frequency: all samples for one field-season; all samples for one operation; samples within a polygon for one season; and a season-wide aggregate per field. Every one of them carries a season — which is what justifies the key.

What the partition key buys, and what it costs A table of four access patterns against a season-partitioned telemetry table. The three that carry a season predicate prune to a single partition and answer in tens to hundreds of milliseconds; the one that filters only by geometry must consult every partition's index and takes roughly nine hundred milliseconds. Access pattern Prunes? Cost on 110 M rows All samples for one field-season the common case Yes One partition, ~38 ms All samples for one operation debugging a pass Yes One partition, ~12 ms Points inside a polygon, one season zone statistics Yes One GiST scan, ~210 ms Points inside a polygon, any season no season predicate No Every partition, ~900 ms

2. Partition by season, and carry season on the row so no join is needed for the planner to prune.

3. Load, then index.

4. Write pruning-friendly predicates.

5. Verify with EXPLAIN (ANALYZE, BUFFERS).

SQL 
-- 1. Parent table, partitioned by list on season.
CREATE TABLE telemetry_point (
    operation_id bigint      NOT NULL REFERENCES operation(operation_id) ON DELETE CASCADE,
    season       smallint    NOT NULL,
    ts           timestamptz NOT NULL,
    geom         geometry(Point, 4326) NOT NULL,
    yield_dry    double precision,
    speed_m_s    real,
    width_m      real,
    PRIMARY KEY (season, operation_id, ts)
) PARTITION BY LIST (season);

-- 2. One partition per season. Create next season's before harvest, not during it.
CREATE TABLE telemetry_point_2026 PARTITION OF telemetry_point FOR VALUES IN (2026);

-- 3. Load first (COPY into the partition directly), then index.
CREATE INDEX telemetry_2026_geom_gix ON telemetry_point_2026 USING gist (geom)
    WITH (fillfactor = 95);
CREATE INDEX telemetry_2026_op_ix ON telemetry_point_2026 (operation_id, ts);
ANALYZE telemetry_point_2026;
SQL 
-- 4. Pruning-friendly: the season is in the predicate, so three partitions are skipped.
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*), avg(yield_dry)
FROM telemetry_point t
JOIN operation o USING (operation_id)
WHERE t.season = 2026
  AND o.field_season_id = 918234;

-- The plan must contain exactly one "Index Scan ... telemetry_point_2026" and no other partition.
PYTHON 
import re
from sqlalchemy import create_engine, text


def assert_pruned(dsn: str, sql: str, expect_partitions: int = 1) -> str:
    """Run EXPLAIN and assert the planner touched only the expected number of partitions."""
    engine = create_engine(dsn, future=True)
    with engine.connect() as conn:
        plan = "\n".join(r[0] for r in conn.execute(text("EXPLAIN (ANALYZE, BUFFERS) " + sql)))

    touched = set(re.findall(r"telemetry_point_(\d{4})", plan))
    assert len(touched) == expect_partitions, (
        f"planner touched {sorted(touched)} — expected {expect_partitions} partition(s). "
        "Is the season in the predicate, or is it only reachable through a join?")
    assert "Seq Scan" not in plan, f"sequential scan in plan:\n{plan}"
    return plan

Inline verification — measure the thing you actually care about, then prove why it is fast:

PYTHON 
import time

sql = """
SELECT count(*) FROM telemetry_point
WHERE season = 2026 AND operation_id = 55123
"""
start = time.perf_counter()
plan = assert_pruned(DSN, sql, expect_partitions=1)
print(f"{time.perf_counter() - start:.3f}s\n{plan.splitlines()[0]}")
assert "Index Scan" in plan or "Bitmap" in plan, "the index is not being used"

Settings worth being deliberate about

Setting Default here Why it matters
Partition key season Chosen because every listed access pattern carries it. A key that only some queries filter on gives those queries a speed-up and makes every other query slower
Partition granularity one per season Four to ten partitions is the comfortable range. Monthly partitioning turns a decade into 120 relations and adds planning time to every query for no gain
GiST fill factor 95 on closed seasons Telemetry partitions are append-only once the season ends, so a tight index packs more per page; leave the default on the live partition
work_mem 64–256 MB per session Large ST_Intersects joins and zone aggregations spill to disk below this. Raise it for the analytical session rather than globally, where hundreds of connections would each claim it
Index build timing after the bulk load Building on a populated partition is several times faster than maintaining it row by row, and produces a better-balanced tree
ANALYZE after every bulk load Statistics from an empty relation produce a plan built for one, which is how a freshly loaded partition turns into a sequential scan

Create next season’s partition before harvest rather than during it. A row whose season has no partition is rejected outright, and discovering that at 21:00 on the first day of harvest is a bad way to learn about declarative partitioning.

Gotchas and edge cases

  • A season reachable only through a join cannot prune. JOIN field_season fs ON … WHERE fs.season = 2026 gives the planner no constant on the partition key at plan time. That is exactly why season is denormalised onto the row: it is the one column duplicated deliberately, so the predicate can be written directly.
The single most common reason a spatial index looks ignored Two panels comparing two ways of writing a proximity predicate. Using ST_Distance forces a per-row function evaluation and the GiST index goes unused; ST_DWithin is index-aware, so bounding boxes are checked first and only candidates get the exact test. ST_Distance in the predicate WHERE ST_Distance(geom, p) < 30 The function must be evaluated for every row. The GiST index cannot be used at all. Plan: sequential scan over the partition. ST_DWithin in the predicate WHERE ST_DWithin(geom, p, 30) Index-aware: bounding boxes are checked first. Only candidates get the exact distance test. Plan: index scan, orders of magnitude faster.

  • Indexes on the parent are propagated, but per-partition tuning is not. CREATE INDEX ON telemetry_point … creates one on every partition, which is convenient and prevents you from using a different fill factor on the frozen partitions than on the live one. For an append-only history, a high fill factor on closed seasons is free space savings.

  • Too many partitions costs planning time. Partitioning by month instead of season turns four partitions into forty-eight and adds milliseconds of planning to every query, for no gain — nothing in the access-pattern list filters by month alone.

  • ST_DWithin beats ST_Distance for proximity. ST_Distance(geom, p) < 30 cannot use the index because the function must be evaluated per row; ST_DWithin(geom, p, 30) is index-aware and does the same job. This is the single most common reason a GiST index appears to be ignored.

  • Mixed SRIDs silently return nothing. A predicate comparing an EPSG:4326 column with a UTM geometry produces zero rows and no error — the same class of failure catalogued in debugging CRS and projection errors. Transform explicitly in the query, and keep one canonical SRID in storage.

  • ANALYZE after a bulk load, always. A freshly loaded partition with stale statistics gets a plan built for an empty table, which usually means a sequential scan over eleven million rows. It costs seconds and saves hours.

This guide is part of PostGIS Schema Design for Farm Data — see there for the entities, constraints and manifest that surround this table.

Parent section PostGIS Schema Design for Farm Data Related Loading GeoDataFrames into PostGIS with GeoAlchemy2