Partitioning PostGIS Sensor Tables by Time

A single PostGIS table holding a few hundred million sensor readings works fine until it does not. The failure is gradual: index maintenance slows ingestion, VACUUM takes longer than the window between batches, deleting old data locks the table for minutes, and a query for last Tuesday walks an index whose depth has grown with the whole archive. Time-range partitioning fixes all four at once, and the conversion is the part worth planning carefully. This guide covers the layout, the online migration, and the query shapes that quietly defeat it — extending PostGIS storage and spatial indexing for sensor networks.

What Partitioning Actually Buys

Bounded index depth. Each partition has its own indexes over its own rows, so a month’s B-tree stays shallow regardless of how many years the archive holds.

Metadata-speed deletion. DROP TABLE reading_2023_01 removes a hundred million rows instantly and reclaims the space immediately. The DELETE equivalent writes a hundred million dead tuples, locks aggressively, and leaves the space to VACUUM.

Partition pruning. A query with a literal time range touches only the partitions that can contain matching rows. On a seven-year archive queried for one day, that is one partition out of eighty-four.

Independent maintenance. VACUUM, ANALYZE, CLUSTER and index rebuilds run per partition, so maintenance on cold data never contends with ingestion into the current one.

What it does not buy is faster individual row lookups — a well-indexed unpartitioned table finds one row about as fast. The wins are all in bulk operations and maintenance, which is exactly the workload shape of a sensor archive.

What partitioning changes, measured on 400 million rows Grouped bar chart comparing an unpartitioned table with monthly partitions on the time taken to delete a month, to vacuum, and to answer a one-day query. What partitioning changes, measured on 400 million rows 0 1000 2000 Delete one month VACUUM One-day query seconds unpartitioned (s) monthly partitions (s)
Deletion becomes a metadata operation and maintenance runs per partition. Single-row lookups are unchanged — the wins are all in bulk work.

Production-Ready Implementation 

-- postgresql 15 · postgis 3.4
CREATE TABLE reading (
    device_id   text             NOT NULL,
    observed_at timestamptz      NOT NULL,
    metric      text             NOT NULL,
    value       double precision NOT NULL,
    qc_flag     smallint         NOT NULL DEFAULT 1,
    uncertainty real,
    geom        geometry(Point, 4326) NOT NULL,
    -- every unique constraint on a partitioned table must include the partition key
    PRIMARY KEY (device_id, observed_at, metric)
) PARTITION BY RANGE (observed_at);

-- A default partition catches rows outside every declared range rather than
-- failing the insert. It must be monitored: rows landing here are a signal that
-- partition creation has fallen behind, or that a clock is badly wrong.
CREATE TABLE reading_default PARTITION OF reading DEFAULT;

Partition creation has to run ahead of ingestion, which means automating it:

# python 3.11 · psycopg[binary]==3.1.18
from __future__ import annotations

from datetime import date, timedelta

import psycopg


def ensure_partitions(conn: psycopg.Connection, months_ahead: int = 3) -> list[str]:
    """Create monthly partitions up to `months_ahead` in the future.

    Run daily. Creating them lazily on first insert fails the insert; creating
    them months ahead means a scheduler outage has to last a very long time
    before it becomes an incident.
    """
    created = []
    start = date.today().replace(day=1)
    for i in range(months_ahead + 1):
        lo = _add_months(start, i)
        hi = _add_months(start, i + 1)
        name = f"reading_{lo:%Y_%m}"
        with conn.cursor() as cur:
            cur.execute("SELECT to_regclass(%s)", (name,))
            if cur.fetchone()[0] is not None:
                continue
            cur.execute(
                f'CREATE TABLE "{name}" PARTITION OF reading '
                f"FOR VALUES FROM (%s) TO (%s)", (lo, hi)
            )
            # BRIN on time (rows arrive in order), GiST on geometry, B-tree for device lookups
            cur.execute(f'CREATE INDEX ON "{name}" USING brin (observed_at) '
                        f"WITH (pages_per_range = 32)")
            cur.execute(f'CREATE INDEX ON "{name}" USING gist (geom)')
            cur.execute(f'CREATE INDEX ON "{name}" (device_id, observed_at DESC)')
            created.append(name)
    conn.commit()
    return created


def _add_months(d: date, n: int) -> date:
    month = d.month - 1 + n
    return date(d.year + month // 12, month % 12 + 1, 1)

Migrating an existing table happens alongside, not in place:

def migrate_in_batches(conn: psycopg.Connection, *, batch: timedelta = timedelta(days=1)) -> int:
    """Copy an existing table into the partitioned one, one day at a time.

    Bounded batches keep each transaction short, so ingestion into the live
    table is never blocked for more than a moment and the migration can be
    paused and resumed at any point.
    """
    moved = 0
    with conn.cursor() as cur:
        cur.execute("SELECT min(observed_at), max(observed_at) FROM reading_old")
        lo, hi = cur.fetchone()
    while lo < hi:
        upper = lo + batch
        with conn.cursor() as cur:
            cur.execute(
                """
                INSERT INTO reading
                SELECT * FROM reading_old
                WHERE observed_at >= %s AND observed_at < %s
                ON CONFLICT (device_id, observed_at, metric) DO NOTHING
                """,
                (lo, upper),
            )
            moved += cur.rowcount
        conn.commit()
        lo = upper
    return moved

ON CONFLICT DO NOTHING makes the migration restartable: an interrupted run can simply be run again from the beginning without duplicating anything.

Query shapes that prune, and shapes that do not Matrix of five query predicates against whether partition pruning applies and what the planner does instead when it does not. Query shapes that prune, and shapes that do not Prunes? Plan observed_at >= 'x' AND < 'y' yes one partition date(observed_at) = 'x' no append over every partition observed_at > now() - interval '1 day' yes (PG 12+) runtime pruning join on observed_at, no literal no append over every partition device_id only no every partition, index scan each
The second row is the common accident: wrapping the partition key in a function looks harmless and turns a one-partition scan into eighty-four.

Parameter Tuning Guide

Rows per day Partition interval Partitions after 5 years Notes
under 100 k quarterly 20 monthly is unnecessary overhead
100 k – 2 M monthly 60 the common case
2 M – 20 M weekly 260 watch planning time
over 20 M daily 1 825 consider a shorter retention instead
Index Where Size (100 M rows) Serves
BRIN on observed_at every partition ~8 MB time-range scans within a partition
GiST on geom every partition ~7 GB spatial predicates
B-tree (device_id, observed_at DESC) every partition ~3 GB per-sensor history queries
Primary key every partition ~4 GB idempotent ingestion

The GiST index is the expensive one, and on cold partitions it is frequently worth dropping — most historical queries are temporal and per-device rather than spatial, and the index can be rebuilt on demand if a reanalysis needs it.

Migrating a live table without a maintenance window Timeline of an online migration: create the partitioned table, copy a day at a time while writes continue, catch up on the tail, then swap names inside one transaction. Migrating a live table without a maintenance window Create partitioned table alongside the old one Copy in daily batches ON CONFLICT DO NOTHING Catch up the tail last few hours Swap names one transaction Drop the old table after a soak period hours
Bounded batches keep each transaction short, so ingestion is never blocked and the migration can be paused and resumed at any point.

Verification and Testing 

-- Pruning must be visible in the plan: one partition, not eighty-four.
EXPLAIN (COSTS OFF)
SELECT avg(value) FROM reading
WHERE observed_at >= '2026-08-01Z' AND observed_at < '2026-08-02Z'
  AND metric = 'pm25';
-- Expect: Append -> Seq Scan on reading_2026_08  (single child)

-- The shape that DEFEATS pruning: the predicate is on a function of the key
EXPLAIN (COSTS OFF)
SELECT avg(value) FROM reading WHERE date(observed_at) = '2026-08-01';
-- Expect: Append over EVERY partition
# python 3.11 · pytest==8.2.0
def test_query_plan_touches_one_partition(conn):
    with conn.cursor() as cur:
        cur.execute("""
            EXPLAIN (FORMAT JSON)
            SELECT avg(value) FROM reading
            WHERE observed_at >= '2026-08-01Z' AND observed_at < '2026-08-02Z'
        """)
        plan = cur.fetchone()[0][0]["Plan"]
    scanned = _relations(plan)
    assert len(scanned) == 1, f"pruning failed, scanned {scanned}"


def test_default_partition_stays_empty(conn):
    """Rows here mean partition creation fell behind or a clock is wrong."""
    with conn.cursor() as cur:
        cur.execute("SELECT count(*) FROM reading_default")
        assert cur.fetchone()[0] == 0


def test_partitions_exist_three_months_ahead(conn):
    assert ensure_partitions(conn, months_ahead=3) == []      # already created by the daily job

The middle test is the operational alarm worth keeping. A non-empty default partition is silent — inserts succeed, queries return rows — and it means those rows sit outside the partitioning scheme entirely, so retention will never drop them and pruning will never skip them.

Gotchas

Wrapping the partition key in a function. date(observed_at) = '2026-08-01' scans every partition. Always compare against a literal range on the raw column.

A timestamp without time zone partition key. Boundaries then depend on the session’s timezone, so the same insert lands in different partitions from different clients. Use timestamptz.

Forgetting indexes on new partitions. Indexes created on the parent propagate to partitions created afterwards only in recent PostgreSQL versions and only for some index types. Create them explicitly in the automation, as above.

Too many partitions. Planning time grows with partition count; several thousand partitions adds milliseconds to every query, including the ones that touch one. Daily partitions over a seven-year retention is the usual way to get there.

FAQ

Can I partition a table that already has a billion rows?

Not in place — PostgreSQL cannot convert an ordinary table into a partitioned one. Create the partitioned table alongside, copy in bounded batches while writes continue to the old table, then swap names inside a transaction. The copy is the slow part; plan it as a background job over days rather than a maintenance window.

Why does my query still scan every partition?

Almost always because the predicate is not directly on the partition key. Wrapping it in a function (date(observed_at) = ...), comparing against a volatile expression, or joining on it without a literal range all defeat pruning. Check with EXPLAIN — the plan names the partitions it touches.

Does the primary key have to include the partition key?

Yes. PostgreSQL requires every unique constraint on a partitioned table to include the partition key, which is why the natural key here is (device_id, observed_at, metric) rather than a surrogate id. That constraint is a feature: it is also the key that makes ingestion idempotent.