Table Partitioning for Large Spatial Datasets

← Back to Geospatial Caching and Query Optimization

A vehicle-tracking table gains 40 million rows a week. By month nine the GiST index no longer fits in shared buffers, VACUUM takes six hours, and a query that reads yesterday’s positions has to descend an index built over three quarters of a billion rows it will never look at. Nothing is wrong with the query — the table has simply outgrown the shape it was created in. Declarative partitioning fixes that by splitting one enormous heap into a set of physically separate tables that the planner can eliminate wholesale.

Partitioning is not a general performance trick, and it is frequently applied where an index would have done. It earns its keep on three specific problems: bounded maintenance (each partition vacuums and reindexes independently), cheap retention (dropping a month is a catalogue operation, not a 200 GB delete), and pruning (a request scoped to a time window never opens the other partitions). This page shows how to get all three on a PostGIS table without breaking the spatial index behaviour described in Query Plan Analysis & Index Tuning.

# Prerequisites & Environment

PostgreSQL 14 or later — declarative partitioning works from 10, but runtime pruning, partition-wise joins and ATTACH without a full validation scan only became dependable in 12–14. PostGIS 3.3+, and enough disk headroom to hold the largest partition twice during a migration.

Confirm the planner settings that partitioning depends on before measuring anything:

SHOW enable_partition_pruning;      -- must be on (default)
SHOW enable_partitionwise_join;     -- off by default; on helps joined partitioned tables
SHOW enable_partitionwise_aggregate;
SHOW constraint_exclusion;          -- 'partition' is the correct value

# Decision Matrix: is partitioning the right tool?

The row count alone never decides it. A 2 billion row table queried exclusively by bounding box gains almost nothing; a 200 million row table with a 90-day retention policy gains a great deal.

# Step-by-Step Implementation

# 1. Create the partitioned parent

The partition key must be part of every unique constraint, which means the primary key becomes composite. This is the single change that breaks the most application code, so make it first.

CREATE TABLE positions (
    id          bigserial,
    vehicle_id  bigint      NOT NULL,
    observed_at timestamptz NOT NULL,
    geom        geometry(Point, 4326) NOT NULL,
    speed_kph   real,
    PRIMARY KEY (id, observed_at)          -- partition key must be in the PK
) PARTITION BY RANGE (observed_at);

-- Index on the PARENT: cloned onto every partition, now and in the future
CREATE INDEX positions_geom_gix     ON positions USING GIST (geom);
CREATE INDEX positions_vehicle_time ON positions (vehicle_id, observed_at DESC);

# 2. Create partitions, plus a default

CREATE TABLE positions_2026_09 PARTITION OF positions
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE positions_2026_10 PARTITION OF positions
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

-- Catch-all so an out-of-range insert fails softly rather than erroring
CREATE TABLE positions_default PARTITION OF positions DEFAULT;

A default partition is a safety net, not a strategy. Rows landing there are invisible to pruning, and attaching a new partition whose range overlaps existing default rows requires a full scan of the default. Alert on positions_default being non-empty.

# 3. Size the partitions around the retention window

Partition width is a trade between planning overhead and retention granularity. Monthly is the default answer for a 12–36 month retention policy: it keeps the count in the dozens, and dropping a month is a fine enough granularity that nobody minds carrying at most 30 extra days. Weekly makes sense when retention is measured in weeks, or when a single month’s partition would exceed roughly 100 GB and index maintenance on it stops fitting the window. Daily is almost always a mistake outside of short-retention telemetry, because the partition count crosses a thousand within three years and planning time starts to dominate short queries.

# 4. Automate the rolling window

Create partitions ahead of the data, never on demand from the write path.

CREATE OR REPLACE FUNCTION ensure_position_partitions(months_ahead int DEFAULT 3)
RETURNS void LANGUAGE plpgsql AS $$
DECLARE
    start_month date;
    i int;
BEGIN
    FOR i IN 0..months_ahead LOOP
        start_month := date_trunc('month', now())::date + (i || ' month')::interval;
        EXECUTE format(
            'CREATE TABLE IF NOT EXISTS %I PARTITION OF positions
                 FOR VALUES FROM (%L) TO (%L)',
            'positions_' || to_char(start_month, 'YYYY_MM'),
            start_month,
            start_month + interval '1 month'
        );
    END LOOP;
END $$;

Retention becomes a detach plus a drop, which takes milliseconds instead of grinding through a DELETE and the vacuum that follows:

ALTER TABLE positions DETACH PARTITION positions_2025_09 CONCURRENTLY;
DROP TABLE positions_2025_09;

DETACH … CONCURRENTLY (PostgreSQL 14+) avoids the ACCESS EXCLUSIVE lock that the plain form takes on the whole hierarchy — the difference between a maintenance blip and a two-minute outage.

# 5. Understand what prunes and what does not

This is the part that surprises people coming from a purely spatial mindset: a bounding box predicate prunes nothing. Pruning works on the partition key only.

The API-level consequence is concrete: give every listing endpoint an optional from/to window, default it to something sane rather than unbounded, and document it. A default of “last 24 hours” turns a twelve-partition append into a single-partition index scan.

# Production Code Example

A FastAPI route that pushes the time bound into the query so the planner can prune, and reports which partitions were touched during development.

from datetime import datetime, timedelta, timezone
from typing import Annotated, Any

import asyncpg
from fastapi import APIRouter, Depends, HTTPException, Query

router = APIRouter(prefix="/v1/positions", tags=["positions"])

MAX_WINDOW = timedelta(days=31)

POSITIONS_SQL = """
SELECT p.vehicle_id,
       p.observed_at,
       ST_AsGeoJSON(p.geom, 6)::json AS geometry,
       p.speed_kph
FROM   positions p
WHERE  p.observed_at >= $1
  AND  p.observed_at <  $2          -- both bounds: an open range prunes nothing above it
  AND  p.geom && ST_MakeEnvelope($3, $4, $5, $6, 4326)
ORDER  BY p.observed_at DESC
LIMIT  $7
"""


async def get_pool() -> asyncpg.Pool:      # wired at app startup
    raise NotImplementedError


@router.get("")
async def list_positions(
    bbox: Annotated[str, Query(description="minx,miny,maxx,maxy in EPSG:4326")],
    since: Annotated[datetime | None, Query()] = None,
    until: Annotated[datetime | None, Query()] = None,
    limit: Annotated[int, Query(ge=1, le=5000)] = 500,
    pool: asyncpg.Pool = Depends(get_pool),
) -> dict[str, Any]:
    now = datetime.now(timezone.utc)
    # A defaulted window is what makes pruning possible for the common request
    until = until or now
    since = since or (until - timedelta(days=1))
    if until <= since:
        raise HTTPException(422, detail={"error": "until_must_follow_since"})
    if until - since > MAX_WINDOW:
        raise HTTPException(
            422,
            detail={"error": "window_too_large", "max_days": MAX_WINDOW.days,
                    "hint": "narrow the range or page through it"},
        )

    try:
        minx, miny, maxx, maxy = (float(v) for v in bbox.split(","))
    except ValueError:
        raise HTTPException(422, detail={"error": "bbox_must_be_four_numbers"})

    async with pool.acquire() as conn:
        rows = await conn.fetch(
            POSITIONS_SQL, since, until, minx, miny, maxx, maxy, limit
        )

    return {
        "window": {"since": since.isoformat(), "until": until.isoformat()},
        "count": len(rows),
        "positions": [dict(r) for r in rows],
    }

The MAX_WINDOW guard is doing real work. Without it a client can request three years, the planner opens every partition, and one request consumes the connection pool’s worth of I/O — the failure mode covered in Cost-Based Throttling for Expensive PostGIS Queries.

# Verification & Testing

Prove that pruning happens rather than assuming it. EXPLAIN lists the partitions the executor will open:

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM   positions
WHERE  observed_at >= now() - interval '2 days'
  AND  geom && ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326);

A healthy plan names only the partitions in range:

Aggregate  (cost=... rows=1 width=8) (actual time=41.2..41.2 rows=1 loops=1)
  ->  Append  (cost=... rows=18422 width=0) (actual time=0.6..38.9 rows=17904 loops=1)
        ->  Index Scan using positions_2026_10_geom_gix on positions_2026_10 p_1
              Index Cond: (geom && '...'::geometry)
              Filter: (observed_at >= (now() - '2 days'::interval))
Planning Time: 1.9 ms

Two things to check every time: the Append node lists a small subset of partitions, and planning time has not ballooned. If EXPLAIN shows all twelve, the predicate is not prunable — usually because the key is wrapped in a function or the bound is open-ended.

A regression test keeps it honest:

import pytest


@pytest.mark.asyncio
async def test_recent_window_prunes_partitions(db_conn):
    plan = await db_conn.fetchval(
        """
        EXPLAIN (FORMAT JSON)
        SELECT count(*) FROM positions
        WHERE observed_at >= now() - interval '2 days'
        """
    )
    text = str(plan)
    # Only the current and previous month may appear in the plan
    assert text.count("positions_20") <= 2, text

# Failure Modes & Edge Cases

1. ERROR: no partition of relation "positions" found for row — an insert fell outside every range and there is no default partition. Run the partition-creation function on a schedule and alert when the newest partition is less than 30 days ahead of now().
2. ERROR: unique constraint on partitioned table must include all partitioning columns — the composite primary key requirement. Application code that assumes id alone is unique needs review; id remains unique in practice because of the shared sequence, but the database no longer guarantees it globally.
3. Planning time creeping up. Every partition is considered before pruning. Above roughly 500 partitions, short queries start paying several milliseconds of planning. Use plan_cache_mode = force_custom_plan for prepared statements against wide partition sets, or reduce the partition count.
4. ATTACH PARTITION blocking. Attaching a populated table validates the constraint unless a matching CHECK already exists. Add CHECK (observed_at >= … AND observed_at < …) to the standalone table first; PostgreSQL then skips validation and the attach is instant.
5. Indexes silently missing on an attached table. CREATE TABLE … PARTITION OF clones parent indexes; ATTACH does not build them for you and errors if they are absent. Build every parent index on the standalone table before attaching.
6. Autovacuum tuning does not inherit. Storage parameters set on the parent do not propagate to partitions created earlier. Set them per partition, or in the creation function.
7. Cross-partition ORDER BY with LIMIT. An Append over partitions must merge results; without a matching sort order per partition PostgreSQL sorts the union. Keep the partition key first in the ORDER BY so a Merge Append can short-circuit.
8. Cached plans against a moving window. A generic plan built when the newest partition was September keeps pruning to September after October exists. plan_cache_mode = auto usually recovers; verify after a partition rollover.

# Performance Notes

On the 780 million row tracking table used for the figures above, monthly partitioning changed the numbers as follows. A one-day bounding box query dropped from 1 240 ms to 88 ms, almost entirely because the working index shrank from 44 GB to 3.4 GB and stayed resident. A spatial-only query with no time bound got slower — 1 310 ms versus 1 240 ms — because twelve index scans and an append cost more than one large scan. Retention went from a 4-hour DELETE plus vacuum to a 40 ms DETACH.

Planning time rose from 0.4 ms to 1.9 ms with twelve partitions, and to 11 ms in a test with 400 daily partitions. That is the real ceiling on partition count for an interactive API.

Partitioning composes well with the rest of the performance stack: each partition can carry its own materialized view for low-zoom aggregates, and cache keys that already include a time window map naturally onto partition boundaries, so a Redis entry and a partition expire together.

# Related

• Query Plan Analysis & Index Tuning — reading the Append and Index Scan nodes a partitioned plan produces
• Materialized Views for Spatial Aggregations — pre-aggregating per partition
• Connection Pooling & PgBouncer Setup — why prepared statements and partition pruning interact
• Async PostGIS Transaction Patterns — writing into a partitioned table at volume
• Row-Level Security for Multi-Tenant PostGIS — the alternative when isolation, not size, is the problem

← Back to Geospatial Caching and Query Optimization