Temporal Versioning and Time-Travel Queries for Spatial Data

Version control tells you what the repository held at a commit; temporal modelling tells you what was true on the ground on a date — and in a spatial repository those are two different questions that need two different clocks. This guide is part of Geospatial Data Versioning Fundamentals & Architecture.

Prerequisites & Environment Setup

Before adding temporal columns to a production layer, confirm each of the following:

Core Algorithmic Patterns

Three patterns carry almost all of the design. Each one is small; the discipline is in applying them consistently.

1. Two clocks, never conflated

Valid time is the interval during which a fact was true in the world. Transaction time is the interval during which the repository asserted that fact. They move independently: a boundary that changed in March may be recorded in June and corrected in September.

Conflating them is the single most common temporal modelling error, and it is invisible until someone asks a question that separates them. A layer that stores only one timestamp column can answer what does the database say now about this parcel and nothing else. It cannot reproduce the map that was published in April, and it cannot show that a correction was applied after a decision was made on the old value — which is precisely what an audit asks for.

The cost of carrying both is four columns instead of one. The cost of not carrying both is discovered later, usually during a dispute.

2. Closed-open intervals, so boundaries meet exactly once

Store every interval as [from, to) — inclusive at the start, exclusive at the end. Two adjacent versions of the same feature then share a boundary instant that belongs to exactly one of them. With closed-closed intervals, an as-of query at the boundary returns two rows for one feature, and the bug appears only on the days that happen to be boundaries.

An open-ended interval — the current version — is written with to set to 'infinity' rather than NULL. Range operators handle infinity correctly; they treat NULL as unknown, which silently removes the current version from half the queries you write.

3. Constrain overlaps rather than testing for them

A temporal table without a constraint accumulates overlapping versions of the same feature, and every one of them is a wrong answer waiting to be returned. PostgreSQL’s exclusion constraints reject the overlap at write time:

ALTER TABLE parcel_versions
  ADD CONSTRAINT parcel_no_overlap
  EXCLUDE USING gist (
    parcel_id WITH =,
    valid_period WITH &&,
    tx_period WITH &&
  );

This is one line of schema that removes an entire class of defect. A nightly job that detects overlaps finds them after the wrong answer has already been served.

One boundary change, three different dates A timeline of a single parcel boundary change showing the three dates it carries: when the boundary changed on the ground, when the survey recorded it, and when the repository committed it. A later correction adds a fourth moment that changes the belief without changing the validity. Changed on the ground valid time starts here 12 Mar Surveyed observation recorded 04 May Committed transaction time starts here 19 Jun Corrected same validity, new belief 08 Sep A single timestamp column has to choose one of these, and every question about the others becomes unanswerable.

Production Workflow Implementation

Step 1 — Model the table

The schema below carries both clocks, a stable identity, and the commit that produced each assertion.

CREATE TABLE parcel_versions (
    version_id    bigserial PRIMARY KEY,
    parcel_id     uuid        NOT NULL,          -- the entity, stable across versions
    geom          geometry(MultiPolygon, 3035) NOT NULL,
    owner_ref     text,
    area_m2       numeric(14,2),

    -- when the fact was true on the ground
    valid_period  tstzrange   NOT NULL,
    -- when this repository asserted it
    tx_period     tstzrange   NOT NULL DEFAULT tstzrange(now(), 'infinity'),
    -- which commit wrote this assertion
    commit_sha    char(40)    NOT NULL,

    CONSTRAINT valid_period_bounds CHECK (lower_inc(valid_period) AND NOT upper_inc(valid_period)),
    CONSTRAINT tx_period_bounds    CHECK (lower_inc(tx_period)    AND NOT upper_inc(tx_period))
);

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);
CREATE INDEX parcel_versions_entity_idx ON parcel_versions (parcel_id, lower(valid_period));

The commit_sha column is what ties the temporal table back to the versioned repository. Without it, a database row and a repository artifact can disagree and nobody can tell which one moved.

Step 2 — Write a new version rather than an update

Editing a parcel never updates a row. It closes the current version’s validity and inserts a successor:

from datetime import datetime, timezone
import psycopg


def record_new_version(conn, parcel_id, geom_wkb, owner_ref, area_m2,
                       effective_from, commit_sha):
    """Close the open valid-time interval and open a successor.

    effective_from is VALID time — the date the change took effect on the
    ground — which is usually not the moment this function runs.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            UPDATE parcel_versions
               SET valid_period = tstzrange(lower(valid_period), %s, '[)')
             WHERE parcel_id = %s
               AND upper_inf(valid_period)
               AND upper_inf(tx_period)
            """,
            (effective_from, parcel_id),
        )
        if cur.rowcount > 1:
            raise RuntimeError(
                f"{cur.rowcount} open versions for {parcel_id} — the exclusion "
                "constraint is missing or was dropped"
            )

        cur.execute(
            """
            INSERT INTO parcel_versions
                (parcel_id, geom, owner_ref, area_m2, valid_period, commit_sha)
            VALUES
                (%s, ST_GeomFromWKB(%s, 3035), %s, %s,
                 tstzrange(%s, 'infinity', '[)'), %s)
            """,
            (parcel_id, geom_wkb, owner_ref, area_m2, effective_from, commit_sha),
        )
    conn.commit()

The rowcount > 1 check is deliberately an exception rather than a warning. If more than one open version exists, every as-of query on that parcel has been returning duplicates, and the correct response is to stop rather than to add a third.

Step 3 — Answer the four temporal questions

Bitemporal data supports four distinct queries, and it is worth writing all four down because teams routinely implement one and assume it answers the others.

-- 1. Current belief about the current state — the everyday query
SELECT parcel_id, geom FROM parcel_versions
 WHERE upper_inf(valid_period) AND upper_inf(tx_period);

-- 2. As-of: what was true on the ground on 2025-04-01, as we understand it today
SELECT parcel_id, geom FROM parcel_versions
 WHERE valid_period @> TIMESTAMPTZ '2025-04-01'
   AND upper_inf(tx_period);

-- 3. As-known-then: what we BELIEVED on 2025-04-01 about that same date
SELECT parcel_id, geom FROM parcel_versions
 WHERE valid_period @> TIMESTAMPTZ '2025-04-01'
   AND tx_period    @> TIMESTAMPTZ '2025-04-01';

-- 4. What changed between two dates, and why
SELECT parcel_id, lower(valid_period) AS effective, commit_sha
  FROM parcel_versions
 WHERE lower(valid_period) >= TIMESTAMPTZ '2025-01-01'
   AND lower(valid_period) <  TIMESTAMPTZ '2025-07-01'
 ORDER BY effective;

Query 3 is the one that matters in a dispute: it reproduces the map a decision was made from, including any error that was later corrected. Query 2 reproduces the truth as currently understood. A system that can only answer query 2 will confidently tell an auditor that the correct value was always there.

Step 4 — Combine time with space

The point of a spatial temporal table is asking both questions at once — what stood inside this extent on that date:

SELECT p.parcel_id, p.owner_ref, ST_Area(p.geom) AS area_m2
  FROM parcel_versions p
 WHERE p.valid_period @> TIMESTAMPTZ '2025-04-01'
   AND upper_inf(p.tx_period)
   AND ST_Intersects(
         p.geom,
         ST_MakeEnvelope(4321000, 3210000, 4326000, 3215000, 3035)
       );

The planner needs both indexes for this to stay fast. The GiST index on geom narrows by extent; the index on valid_period narrows by date. With only the spatial index, the temporal predicate is applied to every feature in the envelope across its whole history — which on a mature table is several times more rows than the answer.

Step 5 — Reconcile with the commit graph

A temporal table and a versioned repository can drift. The reconciliation is a single query plus a checkout, run on a schedule:

# Every commit_sha asserted by the table must exist in the repository
psql -Atc "SELECT DISTINCT commit_sha FROM parcel_versions" \
  | while read sha; do
      git cat-file -e "${sha}^{commit}" 2>/dev/null \
        || echo "ORPHAN ASSERTION: $sha"
    done

An orphan assertion means the table records a fact produced by a commit the repository no longer has — usually the aftermath of a history rewrite. Catching it on a schedule turns a silent inconsistency into a dated alert.

The four questions two clocks let you ask A grid of the four temporal queries against the predicate each one uses and the situation it answers: current state, as-of, as-known-then, and the change log between two dates. Predicate Answers Current state both intervals open what is true now, as we understand it now As-of valid contains t, tx open what was true on that date, corrections included As-known-then valid contains t, tx contains t the map a decision was actually made from Change log valid lower bound in range what changed between two dates, and which commit did it Systems that implement only the second row will confidently tell an auditor the correct value was always there.

Code Reliability Patterns

Never derive valid time from now(). The moment a row is written is transaction time, and the database supplies it. Valid time comes from the source: a survey date, an ordinance date, an observation timestamp. Defaulting valid time to now() produces a table that looks bitemporal and answers every historical question with today’s data.

Reject retroactive writes silently at your peril. A correction whose valid-time interval starts before an existing version is legitimate and common. What it must not do is overwrite. Close the transaction-time interval of the superseded assertion instead, so the old belief remains queryable:

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

Snap valid-time boundaries to the granularity of the source. If surveys are dated to the day, storing a boundary at 14:32:07 implies a precision the data does not have, and two systems rounding differently will disagree about which side of midnight an edit falls on.

Make the constraint failure readable. An exclusion constraint violation reports index names, not intent. Catch it and re-raise with the parcel identifier and both intervals, or the first person to hit it will spend an afternoon on it.

Correcting a row, versus superseding it Two panels contrasting an update in place with the supersede pattern. Updating the row makes the historical query return today's answer for every past date; closing the transaction interval and inserting a replacement keeps both the old belief and the new one queryable. UPDATE IN PLACE The previous belief is gone, with no trace As-known-then returns today's value for every date An audit cannot see that a correction happened The exclusion constraint never fires — nothing overlapped SUPERSEDE Transaction interval closed, replacement inserted Validity interval carried over unchanged Both beliefs remain queryable by date The correction itself is visible in the history Revoke direct UPDATE on the table, and the wrong option stops being available. The distinction only matters after the first retroactive correction — which is also when it is too late to add.

Performance & Scale Considerations

The dominant cost in a temporal spatial table is geometry storage, because a version row copies the geometry even when only an attribute changed. Two mitigations are worth the complexity at scale:

Store geometry once per distinct shape. Move geometry to a side table keyed by a content hash, and let version rows reference it. On a parcel layer where most versions are ownership changes rather than boundary changes, this removes 60–80% of the geometry bytes. The cost is a join on every query, which the planner handles well when the hash column is indexed.

Partition by valid-time year. Range partitioning on lower(valid_period) lets an as-of query for 2019 touch one partition. Combined with a per-partition GiST index, the working set for a historical query stays roughly constant as history grows. Current-state queries — the overwhelming majority — touch only the newest partition.

Watch the index bloat on the open interval. Every current version has valid_period ending at infinity, so the range index has a large cluster of identical upper bounds. A partial index on WHERE upper_inf(valid_period) serves current-state queries directly and keeps them off the main index entirely.

At around 50 million version rows, plan on the geometry side table rather than treating it as an optimisation to consider later; retrofitting it means rewriting every row.

Troubleshooting & Failure Modes

Symptom Root Cause Fix
An as-of query returns two rows for one feature Closed-closed intervals, so adjacent versions share a boundary instant Convert to [) bounds and add the exclusion constraint; re-check historical boundaries with valid_period && valid_period
Every historical query returns today’s geometry Valid time defaulted to now() on insert Backfill valid time from the source dates; make the column NOT NULL with no default so the omission fails loudly
The current version disappears from half the queries Open intervals stored as NULL rather than 'infinity' Replace NULL upper bounds with 'infinity' and use upper_inf() in predicates
An as-of window query scans the whole table Only the geometry index exists, so the temporal predicate is a filter Add a GiST index on valid_period; verify with EXPLAIN that both appear in the plan
A correction overwrote the previous belief The update path modified the row instead of closing its transaction interval Restore from backup, then route all writes through the version-insert function; revoke direct UPDATE on the table
commit_sha values that no longer exist History was rewritten in the repository after rows were written Run the reconciliation check on a schedule; treat a rewrite as a migration that must update the table

FAQ

Why not just check out an old commit instead of storing temporal columns?

A checkout answers what the repository held at a moment in its own history. It cannot answer what was true on the ground at a moment in the world, because a survey recorded in June may describe a boundary that changed in March. Only valid time answers that, and it is not derivable from the commit graph. The two mechanisms answer different questions, and most production repositories need both — which is why the schema above records the commit that produced each assertion.

How much storage does a bitemporal spatial table cost?

One row per version of each feature, not one per feature. A parcel layer of 200,000 features averaging 3.4 recorded versions holds roughly 680,000 rows. Geometry dominates, so the practical control is storing each distinct shape once and referencing it from version rows — on ownership-heavy layers that removes most of the growth. See the performance section above for when to adopt it.

What happens when a survey is corrected retroactively?

That is the case bitemporal modelling exists for. The correction closes the transaction-time interval of the incorrect row and inserts a new row carrying the same valid-time interval and the corrected geometry. An as-of query now returns the corrected answer; an as-known-then query still returns what the repository believed at the time. Nothing is deleted, so the audit trail survives, which is also what security boundaries in spatial repositories assumes when it requires immutable records.

Do I need a database, or can temporal columns live in files?

Files work for append-mostly datasets: a GeoParquet dataset partitioned by valid-time year answers as-of queries by reading a subset of files, and it versions cleanly through pointer synchronization for raster datasets or an equivalent artifact tracker. What files cannot give you is a constraint that prevents two overlapping versions of one feature from being written concurrently. If that correctness matters, a database is the cheaper answer.

How does this interact with branching?

A branch and a transaction-time interval both represent “an alternative assertion”, so the temptation is to use one for the other. Do not: branches are for work in progress and merge into a single history, while transaction time records what was asserted and never merges. Keep branch-scoped edits in the repository, and write to the temporal table only when a branch merges. The branching and merge strategies section covers the branch side of that boundary.

Back to Geospatial Data Versioning Fundamentals & Architecture