Partitioning Spatial Tables by Time Range

Problem Statement

A fleet-telemetry table takes two hundred million geometry(Point, 4326) rows a month and keeps two years of history. Partitioning it by time is the right call, but a partitioned table that needs a human to create next month’s partition is a scheduled outage waiting for a holiday. This page builds the maintenance job that makes the pyramid self-sustaining, including the GiST index each new partition needs before it goes live.

Why the Naive Approach Fails

The usual first version creates partitions in a monthly cron job, one month at a time, the day before it is needed. It works until the job fails once — a locked catalogue, a failed deploy, a rotated credential — and at midnight on the first, every insert starts failing with no partition of relation "vehicle_positions" found for row.

How much runway the partition horizon leaves Two lines showing days of runway before ingest fails. The just-in-time policy hovers near one day, so a single failed job causes an outage. The three-months-ahead policy hovers near ninety days, and the same failure is invisible because the next two runs recover it. Days of runway before an insert fails 0 100 three months ahead — a failed run is invisible one failed run → ingest stops Jan Jun The fix is not a more reliable cron job. It is enough runway that reliability stops mattering. Creating an empty partition costs milliseconds; carrying three spare ones costs nothing measurable.

Production-Ready Implementation

The maintenance routine does three things every run: ensure the horizon, index anything new, and retire anything past retention. It is idempotent, so running it hourly is as safe as running it monthly.

python
from __future__ import annotations

import logging
from dataclasses import dataclass
from datetime import date, datetime, timezone

import psycopg
from dateutil.relativedelta import relativedelta

log = logging.getLogger("partition-maintenance")

PARENT = "vehicle_positions"
MONTHS_AHEAD = 3
RETENTION_MONTHS = 24


@dataclass(frozen=True)
class Month:
    start: date

    @property
    def end(self) -> date:
        return self.start + relativedelta(months=1)

    @property
    def suffix(self) -> str:
        return self.start.strftime("%Y_%m")

    @property
    def table(self) -> str:
        return f"{PARENT}_{self.suffix}"


def months_needed(today: date) -> list[Month]:
    first = today.replace(day=1)
    return [Month(first + relativedelta(months=i)) for i in range(MONTHS_AHEAD + 1)]


def ensure_partition(conn: psycopg.Connection, month: Month) -> bool:
    """Create the partition and its GiST index if missing. Returns True if created."""
    with conn.cursor() as cur:
        cur.execute("SELECT to_regclass(%s) IS NOT NULL", (month.table,))
        if cur.fetchone()[0]:
            return False

        cur.execute("SET LOCAL lock_timeout = '5s'")
        cur.execute(f"""
            CREATE TABLE {month.table}
            PARTITION OF {PARENT}
            FOR VALUES FROM ('{month.start}') TO ('{month.end}')
        """)
    conn.commit()

    # The index build runs outside the transaction that created the table.
    with conn.cursor() as cur:
        cur.execute(f"""
            CREATE INDEX {month.table}_geom_idx
            ON {month.table} USING gist (geom)
        """)
        cur.execute(f"""
            CREATE INDEX {month.table}_vehicle_time_idx
            ON {month.table} (vehicle_id, recorded_at DESC)
        """)
    conn.commit()

    log.info("created partition %s", month.table)
    return True

A newly created partition is empty, so the index build is instantaneous — this is exactly why partitions are created ahead of time rather than at the moment they fill. The expensive index build never happens on a busy table.

Retirement uses the concurrent detach so ingest never pauses:

python
def retire_expired(conn: psycopg.Connection, today: date) -> list[str]:
    """Detach and drop partitions older than the retention horizon."""
    cutoff = (today.replace(day=1) - relativedelta(months=RETENTION_MONTHS))
    retired: list[str] = []

    with conn.cursor() as cur:
        cur.execute("""
            SELECT c.relname,
                   pg_get_expr(c.relpartbound, c.oid) AS bounds
            FROM pg_class c
            JOIN pg_inherits i ON i.inhrelid = c.oid
            WHERE i.inhparent = %s::regclass
            ORDER BY c.relname
        """, (PARENT,))
        partitions = cur.fetchall()

    for name, bounds in partitions:
        if not _upper_bound_before(bounds, cutoff):
            continue
        with conn.cursor() as cur:
            # CONCURRENTLY needs its own transaction and no surrounding block
            conn.commit()
            cur.execute(f"ALTER TABLE {PARENT} DETACH PARTITION {name} CONCURRENTLY")
            conn.commit()
            cur.execute(f"DROP TABLE {name}")
            conn.commit()
        retired.append(name)
        log.info("retired partition %s", name)

    return retired

Detaching before dropping matters: a DROP TABLE on an attached partition takes the parent’s lock, while a detached table is an ordinary table nobody is querying. It also gives you a window to archive — between detach and drop, the data is still there under its own name, ready for a COPY ... TO or a move to cold storage.

The life of one monthly partition A timeline for a single partition across twenty-six months. It is created empty three months early, becomes the active ingest target for one month, is queried heavily for about three months, sits cold for twenty-two, then is detached concurrently, archived and dropped. Twenty-six months in the life of vehicle_positions_2026_08 created ingest queried heavily cold — costs disk and nothing else retired May Aug Sep–Nov Dec 2026 – Jul 2028 Aug 2028 Index built here, while empty — the only moment it is free. Vacuum settings can differ by phase: aggressive on the ingest partition, default everywhere else. Set them when the partition is created and again when it stops being the active one, both from the same maintenance job. Nothing here needs a human unless the alert fires. Granularity, and what it costs Two years of retention split three ways. Daily gives 730 partitions and fine-grained retention but visible planning overhead. Monthly gives 24 partitions and is comfortable. Quarterly gives 8 partitions but forces retention in three-month steps. Two years of retention, three granularities daily — 730 retention to the day planning time is visible 730 indexes to maintain monthly — 24 retention to the month planning cost negligible the usual right answer quarterly — 8 cheapest to maintain retention in 3-month steps each partition is huge Let the retention policy pick the granularity; query performance barely distinguishes them.

Configuration and Tuning Knobs

MONTHS_AHEAD = 3 is the runway. Raise it if your maintenance job runs monthly rather than hourly; the cost is a few empty tables.

RETENTION_MONTHS should be a product decision written down somewhere other than this script. When it changes, the change is one constant, and the next run does the work — which is a good reason to keep the archive step explicit rather than folding it into the drop.

Per-partition autovacuum settings are worth applying at creation:

sql
ALTER TABLE vehicle_positions_2026_08 SET (
    autovacuum_vacuum_scale_factor  = 0.02,
    autovacuum_analyze_scale_factor = 0.01
);

The active partition takes the whole write load, so it needs far more attention than a cold one. Because settings are per-table, the cold partitions are unaffected and cost nothing.

Backfilling history into a partitioned table

Converting an existing unpartitioned table is a separate job from maintaining the pyramid, and the safe route uses ATTACH PARTITION rather than a copy. Rename the old table, create the partitioned parent under the original name, add a matching CHECK constraint to the old table so PostgreSQL can attach it without a full scan, then attach it as the historical partition:

sql
ALTER TABLE vehicle_positions RENAME TO vehicle_positions_history;

ALTER TABLE vehicle_positions_history
    ADD CONSTRAINT vph_range
    CHECK (recorded_at >= '2024-01-01' AND recorded_at < '2026-08-01') NOT VALID;
ALTER TABLE vehicle_positions_history VALIDATE CONSTRAINT vph_range;

CREATE TABLE vehicle_positions (LIKE vehicle_positions_history INCLUDING ALL)
    PARTITION BY RANGE (recorded_at);

ALTER TABLE vehicle_positions
    ATTACH PARTITION vehicle_positions_history
    FOR VALUES FROM ('2024-01-01') TO ('2026-08-01');

The pre-validated constraint is what makes the attach instant: without it, PostgreSQL scans the entire historical table to prove every row belongs in the declared range, holding a lock for the duration. With it, the attach is a catalogue update. This is the single most useful trick in partition migration, and it is easy to miss because the naive version works fine on a test table with a thousand rows.

Verification Steps

sql
-- how much runway is left?
SELECT max(upper(bounds::text)) AS horizon
FROM (
    SELECT pg_get_expr(c.relpartbound, c.oid)::text AS bounds
    FROM pg_class c
    JOIN pg_inherits i ON i.inhrelid = c.oid
    WHERE i.inhparent = 'vehicle_positions'::regclass
) t;

-- is anything landing in the default partition?
SELECT count(*) FROM vehicle_positions_default;

-- does every partition have its GiST index?
SELECT c.relname
FROM pg_class c
JOIN pg_inherits i ON i.inhrelid = c.oid
WHERE i.inhparent = 'vehicle_positions'::regclass
  AND NOT EXISTS (
      SELECT 1 FROM pg_index x
      JOIN pg_class ic ON ic.oid = x.indexrelid
      JOIN pg_am am ON am.oid = ic.relam
      WHERE x.indrelid = c.oid AND am.amname = 'gist'
  );

The last query should return no rows. A partition without a spatial index is invisible until the month rolls over into it and every map query suddenly does a sequential scan.

Gotchas Checklist

  • DETACH PARTITION CONCURRENTLY cannot run inside a transaction block. Commit first, run it alone, then commit again — the pattern in retire_expired above.
  • A non-empty DEFAULT partition blocks new partition creation. PostgreSQL must scan the default partition to prove no row belongs in the new range. Drain it before the next run, or partition creation starts failing for reasons that look unrelated.
  • The primary key must contain the partition column. If application code assumes id alone is unique, partitioning silently breaks that assumption — audit for WHERE id = ... without a time bound.
  • Index names are global, not per-partition. vehicle_positions_geom_idx cannot exist twice, hence the partition suffix in every index name above.
  • Creating a partition takes a lock on the parent. It is brief, but on a table taking thousands of inserts a second it still deserves a lock_timeout and a retry rather than an unbounded wait.