Spatial Partitioning for Lineage Tables

Part of: PostGIS Lineage Schema Design

A lineage table that captures every reprojection, clip, and QA pass across a national imagery program grows by millions of rows a month, and once it crosses a few hundred million rows even well-indexed queries start scanning more than they should. Declarative range partitioning by ingestion month keeps each child table small enough for fast GiST scans while letting the planner prune away months the query never asked for. This how-to partitions a high-volume lineage table by month and proves that partition pruning works, extending the base PostGIS Lineage Schema Design.

Prerequisites

  • PostgreSQL 15+ (for the improved runtime-pruning and MERGE/default-partition handling) with PostGIS 3.4+.
  • A partition key column that is set at insert and never updated — ingested_at timestamptz is ideal.
  • Rights to create partitioned tables and indexes.
  • A scheduler (cron, pg_cron, or an Airflow/Prefect job) to create next month’s partition ahead of time.
Partition by time, not by region Time partitions are evenly sized and detach cleanly for archival; regional partitions are wildly unbalanced because one metropolitan area dominates. BY INGESTION MONTH — balanced by construction 2025-01 2025-02 2025-03 2025-04 2025-05 Writes hit one partition · date predicates prune the rest · an old partition detaches as a unit BY REGION — unbalanced in every real estate metro area — most of the rows county B C rural — nearly empty Most queries touch the big partition anyway, so pruning saves little — and the big one still needs its own strategy. Spatial partitioning earns its place only when regions are balanced AND queries are reliably region-scoped.

The imbalance is not a tuning problem to be solved by choosing better boundaries; it reflects where work actually happens. An agency reprocesses its populated areas far more often than its empty ones, so any geographic partitioning scheme concentrates most rows in a minority of partitions, and the query that most needs pruning is the one that lands in the biggest partition.

Time partitioning avoids all of this because lineage volume is a function of pipeline activity, which is roughly steady, and because the archival story falls out for free: a partition older than the hot window detaches, moves to cheaper storage, and stops being scanned without any query changing. Where region-scoped queries genuinely dominate, sub-partition by region within a time partition rather than replacing time as the top level.

Implementation

Declare the parent as PARTITION BY RANGE (ingested_at), then attach one child per month. Each child gets its own GiST index on the geometry column and its own BRIN index on the timestamp, so indexes stay small and can be reindexed one partition at a time. Crucially, the partition key must be part of the primary key in a partitioned table, so the key becomes (lineage_id, ingested_at).

CREATE TABLE lineage_event (
    lineage_id  uuid        NOT NULL DEFAULT gen_random_uuid(),
    dataset_id  uuid        NOT NULL,
    operation   text        NOT NULL,
    extent      geometry(Polygon, 4326),
    payload     jsonb       NOT NULL DEFAULT '{}'::jsonb,
    ingested_at timestamptz NOT NULL,
    PRIMARY KEY (lineage_id, ingested_at)
) PARTITION BY RANGE (ingested_at);

-- One partition per ingestion month. Bounds are [lower, upper).
CREATE TABLE lineage_event_2026_06 PARTITION OF lineage_event
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
CREATE TABLE lineage_event_2026_07 PARTITION OF lineage_event
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

-- A default partition catches out-of-range rows instead of failing the insert.
CREATE TABLE lineage_event_default PARTITION OF lineage_event DEFAULT;

-- Per-partition spatial and temporal indexes.
CREATE INDEX lineage_2026_06_gix  ON lineage_event_2026_06 USING gist (extent);
CREATE INDEX lineage_2026_06_brin ON lineage_event_2026_06 USING brin (ingested_at);
CREATE INDEX lineage_2026_07_gix  ON lineage_event_2026_07 USING gist (extent);
CREATE INDEX lineage_2026_07_brin ON lineage_event_2026_07 USING brin (ingested_at);

Provisioning next month’s partition should be automated. A small monthly job keeps the runway ahead of ingestion so writes never land in the default partition:

-- Run on the 25th of each month to create the following month.
DO $$
DECLARE
    start_date date := date_trunc('month', now() + interval '1 month');
    end_date   date := start_date + interval '1 month';
    part_name  text := 'lineage_event_' || to_char(start_date, 'YYYY_MM');
BEGIN
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF lineage_event
             FOR VALUES FROM (%L) TO (%L)',
        part_name, start_date, end_date);
    EXECUTE format('CREATE INDEX IF NOT EXISTS %I ON %I USING gist (extent)',
        part_name || '_gix', part_name);
    EXECUTE format('CREATE INDEX IF NOT EXISTS %I ON %I USING brin (ingested_at)',
        part_name || '_brin', part_name);
END $$;

What Partitioning Costs

Three costs to weigh against the pruning benefit Per-partition indexes, the partition key requirement on unique constraints, and full scans for queries lacking a date predicate. Indexes are created per partition A new index means N index builds, and each new partition inherits them — automate or drift. Unique constraints must include the partition key A globally unique step_id is no longer enforceable by the database alone — use UUIDs. Queries with no date predicate scan every partition "find this step_id" becomes N index lookups instead of one — add the date where you know it.

The middle row is the one that surprises people mid-implementation. PostgreSQL cannot enforce a unique constraint across partitions unless the constraint includes the partition key, so a lineage table partitioned by month cannot guarantee that a step identifier is unique across the whole table. In practice this is a non-issue if identifiers are UUIDs or content-derived digests, where collision is not a realistic concern — and a genuine problem if they are sequence-generated per partition.

The third row shapes how application queries should be written. Looking up a single record by identifier with no date predicate is a common pattern and becomes proportionally slower with each partition added. Where the caller knows the approximate date — which they usually do, because they got the identifier from a record that had one — passing it narrows the scan to one partition and restores the single-lookup cost.

Verification

Confirm that a time-bounded query touches only the relevant partitions. With a WHERE clause on ingested_at, EXPLAIN should list only the matching child tables and mark the rest as pruned:

EXPLAIN (COSTS OFF)
SELECT lineage_id, operation
FROM lineage_event
WHERE ingested_at >= '2026-07-01' AND ingested_at < '2026-07-15'
  AND ST_Intersects(extent, ST_MakeEnvelope(-124, 32, -114, 42, 4326));

A pruned plan reads roughly as follows — note that only the July partition appears and the June partition is absent entirely:

 Append
   ->  Bitmap Heap Scan on lineage_event_2026_07
         Recheck Cond: ...
         ->  Bitmap Index Scan on lineage_2026_07_gix
               Index Cond: (extent && '...'::geometry)

To see the contrast, drop the ingested_at predicate and rerun EXPLAIN: every partition, including lineage_event_default, now appears in the Append node, because without a bound on the partition key the planner cannot exclude any child. That difference is the whole payoff of partitioning.

Automating Partition Creation

Create ahead, detach behind A maintenance job keeps several future partitions ready and detaches partitions past the hot window; a missing future partition causes insert failures. detached, cold 2024 and earlier hot window — attached indexed, queried current receiving writes created ahead If the maintenance job stops, the first insert past the last partition boundary FAILS. Not silently — the pipeline stops. Keep several months of headroom and alert on the count. A DEFAULT partition prevents the outage and hides the problem — prefer alerting on headroom instead.

The failure mode is abrupt and total: PostgreSQL rejects a row that matches no partition, so a maintenance job that quietly stopped three months ago manifests as every lineage insert failing at midnight on the first of the month. Keep at least three months of future partitions created, and monitor the count rather than the job — a job that runs successfully and creates nothing because of a logic error looks healthy in every scheduler.

A DEFAULT partition is the tempting insurance and carries a real cost: rows landing there escape pruning entirely, and moving them out later requires detaching and redistributing. Worse, it converts a loud failure into a silent degradation, which is the wrong trade for a table whose completeness is the whole point. If you do add one, alert on it being non-empty rather than treating it as normal.

When Not to Partition

Partitioning is frequently applied earlier than it earns its keep, and the reversal is more work than the original change. Two conditions should both hold before starting.

The first is size. A lineage table of a few tens of millions of rows on modern hardware, with the right indexes, does not need partitioning — the pruning benefit is small relative to what a BRIN index already achieves on an append-only table, and the operational overhead is immediate. The threshold worth watching is not row count in the abstract but whether vacuum, index maintenance or archival have become awkward on the single table.

The second is a genuine archival requirement. Much of partitioning’s value is that an old partition detaches and moves to cold storage as a unit; if retention policy keeps everything hot forever, that benefit does not apply and what remains is pruning alone. Where the retention schedule does have a boundary, partitioning aligned to it turns archival from a delete-and-vacuum operation — slow, bloat-producing, and painful on a large table — into a metadata change.

If neither condition holds yet, the useful preparation is cheap: make sure every query carries a date predicate where it can, keep identifiers UUID-based rather than sequence-based, and ensure the ingestion timestamp is never null. Those three choices cost nothing now and mean that partitioning later is a migration rather than a redesign.

Gotchas & edge cases

  • Constraint exclusion needs the key in the predicate. Pruning only happens when the query filters on ingested_at directly. A predicate on a derived expression such as date_trunc('month', ingested_at) = ... defeats pruning because the planner cannot map it to partition bounds. Always filter on the raw column with plain range comparisons, and keep enable_partition_pruning at its default on.
  • The default partition is a trap, not a safety net. Rows with a NULL or out-of-range ingested_at silently collect in lineage_event_default, which has no useful bounds and cannot be pruned — every partition-key query then scans it. Monitor its row count and treat any growth as an ingestion bug; you also cannot add a new partition whose range overlaps rows already sitting in the default without first moving them out.
  • Cross-partition uniqueness. A unique constraint on a partitioned table must include the partition key, so you cannot enforce global uniqueness of lineage_id alone. If a truly global unique identifier matters for foreign keys from other tables, generate UUIDs (collision probability is negligible) rather than relying on a database-enforced unique index across partitions.