Spatial Table Partitioning in PostgreSQL

There is a point where a single GiST index stops being the right shape for a table. A vehicle-tracking table that ingests two hundred million positions a month does not need one index over four years of history; it needs forty-eight smaller ones, only two of which any dashboard query ever touches. Declarative partitioning is how you get there. This page, part of Advanced GiST Indexing & Optimization, covers when partitioning helps a spatial workload, when it actively hurts, and how to keep partition pruning working once the queries come from an ORM rather than from psql.

What a time-filtered query has to search On the left, a single table with one GiST index covering four years — the query descends one very large tree. On the right, the same data in monthly partitions: the planner prunes to two partitions and searches two small trees, leaving the other forty-six untouched. "positions in this district, last 30 days" one table, one index 9.6 billion rows GiST index: 640 GB index far larger than RAM 48 monthly partitions 2 scanned 46 pruned at plan time each index ~13 GB, both cache-resident The win is not a faster index — it is a smaller one, and the ability to drop last year's data with DETACH rather than DELETE.

Prerequisites and Infrastructure Validation

Declarative partitioning needs PostgreSQL 12 or newer for the pruning behaviour described here; PostgreSQL 13 added the ability to create indexes on a partitioned parent with CONCURRENTLY semantics per partition. Confirm the version and the PostGIS build:

sql
SELECT current_setting('server_version_num')::int >= 130000 AS ok, version();
SELECT postgis_full_version();

Check that the intended partition key is genuinely present in the queries you care about. This is the single decision that determines whether partitioning helps:

sql
-- do the hot statements filter on recorded_at?
SELECT calls, round(total_exec_time::numeric) AS ms, left(query, 90) AS query
FROM pg_stat_statements
WHERE query ILIKE '%vehicle_positions%'
ORDER BY total_exec_time DESC
LIMIT 10;

If the top statements filter only on geom, partitioning by time will make them slower, not faster. In that case the honest answer is a partial GiST index on the hot region instead.

Core Execution Workflow

Step 1 — Create the parent and the partitions

sql
CREATE TABLE vehicle_positions (
    id            bigserial,
    vehicle_id    bigint       NOT NULL,
    recorded_at   timestamptz  NOT NULL,
    geom          geometry(Point, 4326) NOT NULL,
    speed_kph     real,
    PRIMARY KEY (id, recorded_at)          -- the key must include the partition column
) PARTITION BY RANGE (recorded_at);

CREATE TABLE vehicle_positions_2026_08
    PARTITION OF vehicle_positions
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

CREATE TABLE vehicle_positions_2026_09
    PARTITION OF vehicle_positions
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

The primary key must include the partition key — PostgreSQL cannot enforce uniqueness across partitions without it. That is a real constraint on the design, not a formality: if your application assumes a globally unique id alone, partitioning changes that assumption.

Step 2 — Index each partition

sql
-- Index each partition individually, without locking the whole table:
CREATE INDEX CONCURRENTLY vehicle_positions_2026_08_geom_idx
    ON vehicle_positions_2026_08 USING gist (geom);

CREATE INDEX CONCURRENTLY vehicle_positions_2026_09_geom_idx
    ON vehicle_positions_2026_09 USING gist (geom);

-- Then declare the template on the parent and attach the existing indexes:
CREATE INDEX vehicle_positions_geom_idx
    ON ONLY vehicle_positions USING gist (geom);

ALTER INDEX vehicle_positions_geom_idx
    ATTACH PARTITION vehicle_positions_2026_08_geom_idx;
ALTER INDEX vehicle_positions_geom_idx
    ATTACH PARTITION vehicle_positions_2026_09_geom_idx;

ON ONLY is the key word. Without it, CREATE INDEX on the parent builds the index on every partition immediately, holding a lock on each — which on a large pyramid is exactly the outage you partitioned to avoid. With ON ONLY, the parent index starts life invalid and becomes valid automatically once every partition has an attached index.

Step 3 — Keep pruning working from Python

Pruning happens when the planner can prove which partitions a query needs. That proof requires the partition key in the predicate, as a value the planner can see:

python
from datetime import datetime, timedelta, timezone
from sqlalchemy import select, func

# GOOD: the time window is an explicit range on the partition key
since = datetime.now(timezone.utc) - timedelta(days=30)
stmt = (
    select(VehiclePosition)
    .where(VehiclePosition.recorded_at >= since)
    .where(VehiclePosition.recorded_at < datetime.now(timezone.utc))
    .where(func.ST_DWithin(VehiclePosition.geom, district_centre, 0.02))
)

# BAD: no partition key predicate — every partition is searched
stmt_bad = select(VehiclePosition).where(
    func.ST_DWithin(VehiclePosition.geom, district_centre, 0.02)
)

Both queries return the same rows. The first touches two partitions; the second touches forty-eight, each with its own index descent. On this table that is the difference between four milliseconds and nine hundred.

Three pruning outcomes Three query shapes. A literal range on the partition key prunes at plan time and the plan shows only the surviving partitions. A bind parameter prunes at execution time, so the plan lists every partition but most report never executed. No key predicate at all means every partition is scanned for real. Pruning at plan time, at run time, or not at all recorded_at >= '2026-08-01' AND recorded_at < '2026-09-01' plan-time pruning — EXPLAIN shows one partition, planning is fast recorded_at >= $1 AND recorded_at < $2 run-time pruning — all partitions listed, most "never executed" ST_DWithin(geom, $1, 0.02) -- no partition key at all no pruning — 48 index scans, one per partition

The middle case surprises people reading EXPLAIN without ANALYZE: the plan lists every partition because the planner does not know the parameter values yet. Run EXPLAIN (ANALYZE) and the pruned partitions report (never executed) — that is pruning working correctly, just later.

Deciding Whether to Partition at All

Partitioning is a structural change with permanent consequences for the schema — the primary key changes shape, some constraints become harder to express, and every future migration has to think about partitions. It is worth applying a short test before committing.

Does every hot query filter on a candidate key? This is the necessary condition and it is not negotiable. Pull the top statements from pg_stat_statements and read their WHERE clauses. If a majority filter on a time window, partition by time. If a majority filter on a tenant, market or region, partition on that. If the hot queries are purely spatial — a viewport, a radius, a nearest-neighbour — no partitioning scheme will help, and the right answer is a partial index or a covering index instead.

Is the table big enough to care? Below a hundred million rows, a well-maintained GiST index on a single table usually outperforms a partitioned equivalent, because the planning overhead is real and the index still fits in cache. The tipping point is not a row count so much as the moment the index stops being cache-resident: once every query pays random disk reads to walk the tree, splitting the tree into pieces that do fit changes the economics completely.

Do you need cheap deletion? This is often the deciding factor and it has nothing to do with query speed. Deleting a month of rows from a nine-billion-row table is hours of DELETE, an enormous WAL volume, replica lag and a bloated index that then needs rebuilding. Detaching a partition is a catalogue update measured in milliseconds. If your retention policy currently runs as a nightly DELETE ... WHERE recorded_at < ..., partitioning pays for itself on that alone.

Can you live with the constraints? Unique constraints must include the partition key. Foreign keys pointing at a partitioned table were only supported from PostgreSQL 12, and some tooling still handles them poorly. CLUSTER operates per partition. Each of these is manageable, but each is a small ongoing tax, and a table that fails the first two tests should not be paying it.

The honest summary: partition for retention and for cache-resident indexes, not for raw query speed. Query speed is a side effect that appears when the partition key matches the access pattern, and does not appear otherwise.

Performance Considerations

Partition count has a cost even when pruning works. Each partition adds to planning time, carries its own statistics, and needs its own autovacuum attention. The practical guidance:

  • Prefer monthly partitions to daily unless retention or ingest volume forces the finer grain.
  • Keep the count in the low hundreds. Four years of monthly partitions is forty-eight — comfortable. Four years of daily is fourteen hundred, and planning time becomes visible.
  • Set enable_partition_pruning = on (the default) and leave it on. If it is off, everything on this page stops working.

Autovacuum treats each partition as its own table, which is usually a benefit: the current month’s partition is hot and gets vacuumed often, while last year’s is untouched and costs nothing. It also means per-table autovacuum settings must be applied per partition, or set on the parent so new partitions inherit them.

Python Integration Patterns

Two things change in application code once a table is partitioned, and both are small if handled deliberately.

Every hot query carries the partition key. In SQLAlchemy that usually means the repository layer, not the call sites, takes responsibility for it. A helper that always applies the window keeps pruning from depending on whether an individual developer remembered:

python
from datetime import datetime, timedelta, timezone
from sqlalchemy import select
from sqlalchemy.orm import Session

DEFAULT_WINDOW = timedelta(days=30)

def recent_positions(session: Session, *, within=DEFAULT_WINDOW, **filters):
    """Every read goes through here, so the partition key is never forgotten."""
    now = datetime.now(timezone.utc)
    stmt = (
        select(VehiclePosition)
        .where(VehiclePosition.recorded_at >= now - within)
        .where(VehiclePosition.recorded_at < now)
    )
    for column, value in filters.items():
        stmt = stmt.where(getattr(VehiclePosition, column) == value)
    return session.scalars(stmt)

Passing an explicit upper bound as well as a lower one is not redundant. An open-ended recorded_at >= x prunes only the partitions before x; adding the upper bound lets the planner discard future partitions too, which matters once you are creating them three months ahead.

Inserts need a valid target partition. In a long-running writer, a row whose timestamp falls outside every defined range raises an error that looks like a schema problem but is really a maintenance problem. Catch it specifically and make the message say so:

python
from psycopg import errors

try:
    session.execute(insert_stmt)
except errors.CheckViolation as exc:
    raise RuntimeError(
        "no partition covers this timestamp — the partition maintenance job "
        "has not run, or the row is backdated beyond the retention horizon"
    ) from exc

Bulk loading gains an option. Writing directly to a partition — COPY vehicle_positions_2026_08 FROM STDIN — skips the routing step the parent performs per row. On a firehose ingest that is a measurable saving, at the cost of the loader needing to know which partition a batch belongs to. It is worth doing only when the batches are already grouped by time, which for a streaming ingest they naturally are.

Alembic needs help. Autogenerate does not understand partitioned tables and will produce migrations that try to recreate them. Write the partition DDL by hand in the migration, and add the parent to Alembic’s ignore list so subsequent autogenerate runs leave it alone. The Alembic integration guidance applies here in full: spatial DDL is one of the areas where hand-written migrations are the norm rather than the exception.

Common Failure Modes and Fixes

Every query got slower after partitioning. The queries do not filter on the partition key. Either add the key to the predicate — often the application already knows the time window and simply was not sending it — or reconsider whether this table should be partitioned at all.

CREATE INDEX on the parent locked everything. Use ON ONLY plus per-partition CONCURRENTLY builds and ATTACH PARTITION, as shown above.

Inserts fail with “no partition of relation found for row”. A row arrived with a timestamp outside every defined range — usually a clock issue or a backdated import. Create partitions ahead of time and add a DEFAULT partition as a safety net, then monitor it: rows landing in the default partition are a signal, not a solution.

Detaching an old partition blocks. ALTER TABLE ... DETACH PARTITION takes a strong lock; DETACH PARTITION ... CONCURRENTLY (PostgreSQL 14+) does not. Use the concurrent form for routine retention work.

Detached partitions still hold their data. A common surprise during retention work is that DETACH PARTITION does not delete anything — the table simply stops being part of the parent and becomes an ordinary standalone table under its own name. That is a feature: it gives you a window to archive the rows before dropping them, and it makes an accidental detachment recoverable by re-attaching. It also means disk usage does not fall until the DROP TABLE runs, which catches out anyone monitoring free space during a cleanup.

Statistics are per partition. The planner uses each partition’s own statistics for its own scan, which is usually an advantage — a partition covering a dense urban month has a very different geometry distribution from a quiet holiday period, and per-partition statistics capture that. It also means ANALYZE on the parent does not automatically analyse the children in older versions; run it explicitly after a bulk load into a specific partition.

Retention is where partitioning pays for itself Deleting a month of rows from an unpartitioned table takes 47 minutes, writes 38 gigabytes of WAL and leaves a bloated index. Detaching the equivalent partition takes under a second, writes almost no WAL and leaves nothing to clean up. Dropping one month of data, two ways DELETE 47 min · 38 GB of WAL · index bloat to clean up afterwards DETACH under a second · a catalogue update · nothing to clean up and the detached table can be archived before it is dropped For many teams this alone justifies partitioning, regardless of what it does to query latency.

Verification

sql
-- how many partitions does this query actually touch?
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM vehicle_positions
WHERE recorded_at >= '2026-08-01' AND recorded_at < '2026-09-01'
  AND geom && ST_MakeEnvelope(-122.5, 47.4, -122.2, 47.8, 4326);
-- expect: Append with a single child, not 48

-- is every partition indexed?
SELECT c.relname AS partition,
       count(i.indexrelid) FILTER (WHERE am.amname = 'gist') AS gist_indexes
FROM pg_class c
JOIN pg_inherits inh ON inh.inhrelid = c.oid
LEFT JOIN pg_index i ON i.indrelid = c.oid
LEFT JOIN pg_class ic ON ic.oid = i.indexrelid
LEFT JOIN pg_am am ON am.oid = ic.relam
WHERE inh.inhparent = 'vehicle_positions'::regclass
GROUP BY c.relname
ORDER BY c.relname;

A partition with zero GiST indexes is the one that will make a dashboard query mysteriously slow once the month rolls over into it.