Modelling Valid Time and Transaction Time in PostGIS

Two clocks, two range columns, one constraint that makes the whole thing safe: the bitemporal spatial schema is small, and almost all of its value comes from the constraint people leave out. This page is a focused companion to temporal versioning and time-travel queries.

Concept & Context

A bitemporal table answers two questions that a single timestamp cannot separate: when was this true on the ground, and when did we believe it. Every spatial dataset driven by surveys, ordinances or field observations has a lag between those two, and every retroactive correction makes the gap explicit.

PostgreSQL is unusually well suited to modelling this because range types carry both bounds in one value, with operators (@>, &&) that read naturally and an index type that supports them. Most importantly, an exclusion constraint over a range column enforces non-overlap declaratively. Without it, a temporal table is a convention that holds until the first concurrent write, and the resulting duplicate versions are discovered by whoever runs the first as-of query on a boundary date.

The schema below is deliberately minimal: one entity column, the payload, two ranges, and the commit that asserted the row. Everything else — soft deletes, status flags, effective-date columns — turns out to be expressible in the two ranges, and adding it alongside them creates two sources of truth about the same thing.

One parcel's versions across both clocks A grid with valid-time periods across the columns and belief periods down the rows, showing which version row answers a query at each combination. A correction closes one belief row and opens another over the same validity, so the two rows differ only in what was believed. 2023 2024 2025 2026 now belief 1 v1 v1 belief 2 v2 v2 belief 3 v2′ v2′ v3 original version, still believed for 2023–24 superseded belief — what we thought in 2025 current belief, including the correction An as-of query reads the bottom row; an as-known-then query reads the row for the date the question is about.

Core Algorithmic Pipeline

  1. Declare both intervals as tstzrange with [) bounds. Adjacent versions then meet at exactly one instant, owned by exactly one row.
  2. Add a GiST exclusion constraint over (entity =, valid &&, tx &&), which makes two overlapping assertions about one entity impossible rather than merely unlikely.
  3. Route every write through one function that closes the predecessor and inserts the successor inside one transaction.
  4. Correct retroactively by superseding: close the transaction interval of the incorrect row, insert a replacement carrying the same valid interval and the corrected values.
  5. Index for the two access patterns — current state, and as-of by extent — because they want different indexes and the second one is the expensive one.
Why the interval bounds decide whether the model works Two panels comparing closed-closed intervals with closed-open ones. With closed-closed bounds two adjacent versions share a boundary instant and an as-of query on that day returns two rows; with closed-open bounds the instant belongs to exactly one version. CLOSED-CLOSED [a, b] Adjacent versions share the boundary instant An as-of query on that day returns two rows The bug appears only on boundary dates The exclusion constraint fires on legitimate writes CLOSED-OPEN [a, b) The boundary instant belongs to exactly one version Adjacent versions meet without overlapping Open-ended written as infinity, never as null Range operators behave the way the model reads A check constraint on the bounds makes the wrong choice impossible to write. Everything else in the schema is negotiable; this is not.

Working Implementation

-- ─────────────────────────────────────────────────────────────────────────────
-- Bitemporal parcel table: one row per (entity, valid interval, tx interval)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE EXTENSION IF NOT EXISTS btree_gist;   -- needed to mix `=` with `&&`

CREATE TABLE parcel_versions (
    version_id   bigserial PRIMARY KEY,
    parcel_id    uuid        NOT NULL,
    geom         geometry(MultiPolygon, 3035) NOT NULL,
    owner_ref    text,
    land_use     text,

    valid_period tstzrange   NOT NULL,
    tx_period    tstzrange   NOT NULL DEFAULT tstzrange(now(), 'infinity', '[)'),
    commit_sha   char(40)    NOT NULL,

    CONSTRAINT valid_bounds CHECK (lower_inc(valid_period) AND NOT upper_inc(valid_period)),
    CONSTRAINT tx_bounds    CHECK (lower_inc(tx_period)    AND NOT upper_inc(tx_period)),
    CONSTRAINT valid_nonempty CHECK (NOT isempty(valid_period))
);

-- The constraint that turns a convention into a guarantee: one entity may not
-- hold two assertions whose validity AND belief windows both overlap.
ALTER TABLE parcel_versions
  ADD CONSTRAINT parcel_versions_no_overlap
  EXCLUDE USING gist (
      parcel_id    WITH =,
      valid_period WITH &&,
      tx_period    WITH &&
  );

CREATE INDEX parcel_versions_geom_gix  ON parcel_versions USING gist (geom);
CREATE INDEX parcel_versions_valid_gix ON parcel_versions USING gist (valid_period);
-- Current state is the overwhelming majority of reads: serve it from its own
-- small partial index rather than from the full range index.
CREATE INDEX parcel_versions_current_idx
    ON parcel_versions (parcel_id)
 WHERE upper_inf(valid_period) AND upper_inf(tx_period);

-- ─────────────────────────────────────────────────────────────────────────────
-- The single write path
-- ─────────────────────────────────────────────────────────────────────────────
CREATE OR REPLACE FUNCTION record_parcel_version(
    p_parcel_id  uuid,
    p_geom       geometry,
    p_owner_ref  text,
    p_land_use   text,
    p_valid_from timestamptz,     -- when the change took effect on the ground
    p_commit_sha char(40)
) RETURNS bigint AS $$
DECLARE
    closed  int;
    new_id  bigint;
BEGIN
    -- Close the open validity of the current belief about this parcel.
    UPDATE parcel_versions
       SET valid_period = tstzrange(lower(valid_period), p_valid_from, '[)')
     WHERE parcel_id = p_parcel_id
       AND upper_inf(valid_period)
       AND upper_inf(tx_period)
       AND lower(valid_period) < p_valid_from;   -- guard against an inverted range
    GET DIAGNOSTICS closed = ROW_COUNT;

    IF closed > 1 THEN
        RAISE EXCEPTION
            'parcel % had % open versions — the exclusion constraint is missing',
            p_parcel_id, closed;
    END IF;

    INSERT INTO parcel_versions
        (parcel_id, geom, owner_ref, land_use, valid_period, commit_sha)
    VALUES
        (p_parcel_id, p_geom, p_owner_ref, p_land_use,
         tstzrange(p_valid_from, 'infinity', '[)'), p_commit_sha)
    RETURNING version_id INTO new_id;

    RETURN new_id;
END;
$$ LANGUAGE plpgsql;

-- ─────────────────────────────────────────────────────────────────────────────
-- Retroactive correction: supersede the belief, keep the validity
-- ─────────────────────────────────────────────────────────────────────────────
CREATE OR REPLACE FUNCTION correct_parcel_version(
    p_version_id bigint,
    p_geom       geometry,
    p_commit_sha char(40)
) RETURNS bigint AS $$
DECLARE
    old parcel_versions%ROWTYPE;
    new_id bigint;
BEGIN
    SELECT * INTO old FROM parcel_versions WHERE version_id = p_version_id
    FOR UPDATE;

    IF NOT FOUND OR NOT upper_inf(old.tx_period) THEN
        RAISE EXCEPTION 'version % is absent or already superseded', p_version_id;
    END IF;

    UPDATE parcel_versions
       SET tx_period = tstzrange(lower(tx_period), now(), '[)')
     WHERE version_id = p_version_id;

    INSERT INTO parcel_versions
        (parcel_id, geom, owner_ref, land_use, valid_period, commit_sha)
    VALUES
        (old.parcel_id, p_geom, old.owner_ref, old.land_use,
         old.valid_period, p_commit_sha)
    RETURNING version_id INTO new_id;

    RETURN new_id;
END;
$$ LANGUAGE plpgsql;

The lower(valid_period) < p_valid_from guard in the write function is what stops a mis-dated edit from creating an inverted range. Without it, an edit dated before the current version’s start silently produces an empty range, and an empty range overlaps nothing — so the exclusion constraint does not catch it and the parcel quietly acquires two open versions.

What one edit does inside the write function Four steps of the single write path: close the open validity of the current belief, guard against an inverted range, insert the successor with an open validity, and let the exclusion constraint reject anything that would overlap. 1 Close the open validity one row, or the constraint is missing 2 Guard the lower bound an inverted range overlaps nothing and passes 3 Insert the successor validity open to infinity 4 Let the constraint decide overlap rejected at write time Step two exists because an empty range is the one way to defeat step four.

Validation & Output Verification

The constraint does the heavy lifting, so the first verification is that it exists and works:

-- It must reject an overlapping assertion
BEGIN;
INSERT INTO parcel_versions (parcel_id, geom, valid_period, commit_sha)
SELECT parcel_id, geom, valid_period, commit_sha
  FROM parcel_versions LIMIT 1;
-- expected: ERROR ... conflicting key value violates exclusion constraint
ROLLBACK;

-- No entity may have more than one currently-believed current version
SELECT parcel_id, count(*) AS open_versions
  FROM parcel_versions
 WHERE upper_inf(valid_period) AND upper_inf(tx_period)
 GROUP BY parcel_id
HAVING count(*) > 1;
-- expected: zero rows

-- No empty or inverted validity ranges
SELECT version_id FROM parcel_versions WHERE isempty(valid_period);
-- expected: zero rows

Then confirm the four temporal questions return what they should:

-- As-of and as-known-then must differ after a correction, and agree before one
SELECT
  (SELECT count(*) FROM parcel_versions
    WHERE valid_period @> TIMESTAMPTZ '2025-04-01' AND upper_inf(tx_period))
    AS believed_now,
  (SELECT count(*) FROM parcel_versions
    WHERE valid_period @> TIMESTAMPTZ '2025-04-01'
      AND tx_period    @> TIMESTAMPTZ '2025-04-01')
    AS believed_then;

If those two counts are always equal on a table that has had corrections applied, the correction path is updating rows rather than superseding them, and the audit trail is not being kept.

Finally, check the plan for the query that actually costs money:

EXPLAIN (ANALYZE, BUFFERS)
SELECT parcel_id FROM parcel_versions
 WHERE valid_period @> TIMESTAMPTZ '2025-04-01'
   AND upper_inf(tx_period)
   AND ST_Intersects(geom, ST_MakeEnvelope(4321000, 3210000, 4326000, 3215000, 3035));
-- Both the geometry GiST index and the valid_period index should appear.

Failure Modes

  • The exclusion constraint refuses to be created — symptom: ALTER TABLE fails on an existing table. Root cause: the loaded history already contains overlapping versions. Fix: treat the failure report as a work list; reconcile the named entities, then create the constraint. Do not proceed without it.

  • btree_gist missing — symptom: data type uuid has no default operator class for access method "gist". Root cause: mixing = with && in one exclusion constraint needs the extension. Fix: CREATE EXTENSION btree_gist before the constraint.

  • Two open versions appear despite the constraint — symptom: an as-of query returns duplicates. Root cause: an empty validity range, which overlaps nothing and therefore never conflicts. Fix: add the NOT isempty(valid_period) check constraint and the lower-bound guard in the write function.

  • Corrections erase the previous belief — symptom: an as-known-then query returns today’s values for every past date. Root cause: corrections applied as UPDATE on the row. Fix: revoke direct UPDATE on the table and route corrections through the supersede function.

Back to Temporal Versioning and Time-Travel Queries