Versioning Lineage Rows with Temporal Tables

Part of: PostGIS Lineage Schema Design

Auditors ask two different questions about a dataset’s history: “what did the record say at 3pm on the day of the incident” (system time) and “which processing rule was legally in force when the dataset was published” (valid time). Answering both requires bitemporal versioning — two independent time axes on every lineage row — rather than a single updated_at column that conflates them. This how-to models lineage rows with tstzrange valid-time and system-time columns, enforces non-overlap with an exclusion constraint, and runs a point-in-time as-of query. It builds on the PostGIS Lineage Schema Design and pairs naturally with the append-only lineage audit table.

Prerequisites

  • PostgreSQL 15+ with PostGIS 3.4+ and the btree_gist extension (required to combine a scalar key with a range in one exclusion constraint).
  • Comfort with the tstzrange type and its operators, especially && (overlaps) and @> (contains).
  • All timestamps generated in UTC to avoid the timezone pitfalls described below.
The two time axes and what each quadrant means Valid time against transaction time, showing that a record backfilled today about last year sits in a different quadrant from one recorded as it happened. VALID TIME — when it was true in the world → TRANSACTION TIME ↑ BACKFILLED happened long ago, recorded today CURRENT happened recently, recorded as it happened SUPERSEDED what we used to believe about the past SUPERSEDED what we used to believe about recent events

The upper-left quadrant is why bitemporal modelling is worth the complexity for lineage specifically. Reconstructing provenance for historical datasets — the near-universal starting condition when a lineage programme begins — produces records whose valid time is years ago and whose transaction time is today. A single-timestamp schema has to pick one, and either choice loses something: recording the valid time makes the reconstruction indistinguishable from contemporaneous capture, while recording today makes the history appear to have started this week.

The lower half is what makes “what did we believe on the audit date” answerable. An assessor reviewing a decision taken last March needs the lineage as it stood in March, not as corrected since — and a schema that overwrites on correction cannot produce it. Keeping superseded rows with a closed transaction-time interval is what turns that question from an apology into a query.

Implementation

Each logical lineage fact — say, the projection parameters for a given dataset_id — becomes a series of rows, each valid over a half-open tstzrange. system_time records when the row was physically known to the database; valid_time records when the fact was true in the real world. An exclusion constraint using btree_gist guarantees that no two currently-known rows for the same dataset have overlapping valid periods, which is what keeps an as-of query unambiguous.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE lineage_version (
    version_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    dataset_id   uuid        NOT NULL,
    operation    text        NOT NULL,
    parameters   jsonb       NOT NULL DEFAULT '{}'::jsonb,
    extent       geometry(Polygon, 4326),
    -- Real-world validity of the fact.
    valid_time   tstzrange   NOT NULL,
    -- Database knowledge time; upper bound 'infinity' means "current".
    system_time  tstzrange   NOT NULL DEFAULT tstzrange(now(), 'infinity'),

    -- No two live versions of the same dataset may cover the same valid instant.
    CONSTRAINT no_valid_overlap EXCLUDE USING gist (
        dataset_id WITH =,
        valid_time WITH &&
    ) WHERE (upper(system_time) = 'infinity'),

    CONSTRAINT valid_time_nonempty CHECK (NOT isempty(valid_time))
);

CREATE INDEX lineage_version_valid_gix  ON lineage_version USING gist (valid_time);
CREATE INDEX lineage_version_extent_gix ON lineage_version USING gist (extent);

Superseding a fact is a two-step, append-friendly operation: close the old row on the system-time axis (set its upper bound to now()) and insert the replacement. The old row is never physically deleted, so system-time history stays intact. A helper function keeps the two steps in one transaction.

CREATE OR REPLACE FUNCTION supersede_lineage(
    p_dataset_id uuid,
    p_operation  text,
    p_parameters jsonb,
    p_extent_wkt text,
    p_valid_from timestamptz
) RETURNS bigint AS $$
DECLARE
    new_id bigint;
BEGIN
    -- Close the currently-live row for this dataset on the system axis.
    UPDATE lineage_version
    SET system_time = tstzrange(lower(system_time), now())
    WHERE dataset_id = p_dataset_id
      AND upper(system_time) = 'infinity';

    INSERT INTO lineage_version (dataset_id, operation, parameters, extent, valid_time)
    VALUES (
        p_dataset_id, p_operation, p_parameters,
        ST_GeomFromText(p_extent_wkt, 4326),
        tstzrange(p_valid_from, 'infinity')
    )
    RETURNING version_id INTO new_id;

    RETURN new_id;
END;
$$ LANGUAGE plpgsql;

Indexing Two Time Axes

Two indexes for two query populations A small partial index covering only current rows handles the common case; a larger range index handles the rarer as-of queries. Partial index WHERE tx_end IS NULL covers only current rows small, hot, serves ~99% of reads stays small as history grows Range index over both intervals GiST over tstzrange columns serves as-of queries grows with the full history Storing the intervals as range types rather than four bare timestamps is what makes the second index possible. It also gets you exclusion constraints, which prevent two rows claiming the same key over overlapping time.

Range types repay their slight awkwardness immediately. Four separate timestamp columns force every as-of query into a conjunction of four comparisons that no single index serves well, whereas tstzrange columns support containment operators directly and index under GiST. The exclusion constraint the caption mentions is the bonus: it makes it impossible to insert two current rows for the same logical key, which is otherwise a bug you find months later when a query starts returning duplicates.

Keep the partial index narrow and let the range index carry the rest. Because superseded rows accumulate indefinitely while current rows do not, an index over the whole table grows without bound and slows every write, while the partial index stays proportional to the number of live records. That asymmetry is the main reason bitemporal tables stay fast at scale, and it is worth verifying rather than assuming: check that the planner actually chooses the partial index for the current-view query, since a predicate written even slightly differently from the index’s WHERE clause will not match it and the whole benefit silently disappears. Comparing the view’s definition to the index definition character by character is a two-minute check that saves a confusing afternoon later, and it belongs in the migration that creates the index and the view together, rather than in anyone’s memory of how the two were originally meant to line up with each other.

Verification

Run an as-of query that answers “what was known to be true about this dataset at a specific past instant”. It filters both axes: valid_time @> asof selects the row that was real-world-valid then, and system_time @> asof selects the row the database actually knew at that moment — together they reconstruct the exact bitemporal state.

-- Bitemporal point-in-time read: state as known and as valid at one instant.
SELECT version_id, operation, parameters, lower(valid_time) AS effective_from
FROM lineage_version
WHERE dataset_id = '7c9e...'::uuid
  AND valid_time  @> TIMESTAMPTZ '2026-05-01 12:00:00+00'
  AND system_time @> TIMESTAMPTZ '2026-05-01 12:00:00+00';

To confirm the exclusion constraint works, insert two overlapping valid periods for the same dataset — the second insert must fail:

INSERT INTO lineage_version (dataset_id, operation, valid_time)
VALUES ('7c9e...'::uuid, 'reproject', tstzrange('2026-01-01', '2026-06-01'));

-- Overlaps the first row's valid_time; raises: conflicting key value violates
-- exclusion constraint "no_valid_overlap"
INSERT INTO lineage_version (dataset_id, operation, valid_time)
VALUES ('7c9e...'::uuid, 'reproject', tstzrange('2026-03-01', '2026-09-01'));

A successful rejection proves that current valid-time versions cannot overlap, which is precisely what makes the as-of query return exactly one row.

Query Patterns Over Two Axes

Three questions, three predicates Each common bitemporal question maps to a specific pair of predicates over valid time and transaction time. QUESTION PREDICATE "What is true now?" the default view — 99% of queries valid contains now AND tx_end IS NULL "What did we believe in March?" the audit question valid contains D AND tx interval contains D "How has this record changed?" the forensic question all rows for the key, ordered by tx_start

Provide the first as a view and make it the default everything queries. The overwhelming majority of reads want current knowledge of the present, and requiring every caller to write both predicates guarantees that some of them will omit one and silently include superseded rows. A view named plainly — lineage_current — costs nothing and removes an entire class of quiet error.

The second is the one to test deliberately, because it is rarely exercised until an assessment. Write a fixture that records a fact, supersedes it a month later, and then asserts that an as-of query for a date between the two returns the original. That test is the only proof the bitemporal machinery does what it was built for, and it is easy to have a schema with both timestamp pairs and a query layer that never actually uses the transaction-time interval.

Gotchas & edge cases

  • tstzrange bound inclusivity. Ranges default to [lower, upper) — inclusive lower, exclusive upper. Two ranges that meet at an endpoint, such as [..., '2026-06-01') and ['2026-06-01', ...), do not overlap and both satisfy the constraint, which is the behavior you want. If you accidentally build inclusive-upper ranges, adjacent versions will collide at the shared instant and the exclusion constraint will reject legitimate inserts.
  • Timezone drift. tstzrange stores instants in UTC but renders them in the session TimeZone. If ingestion workers run in local time and construct ranges from naive timestamps, two workers in different zones can produce ranges that look adjacent but actually overlap by the offset. Always build ranges from timestamptz values normalized to UTC (now() at UTC, or explicit AT TIME ZONE 'UTC') so the exclusion constraint reasons over a single clock.
  • Empty and infinite ranges. An accidentally empty range (tstzrange('2026-06-01','2026-06-01') is empty) slips past the overlap check because empty ranges overlap nothing, silently creating an invalid version — the valid_time_nonempty CHECK guards against it. Likewise, leaving valid_time unbounded with 'infinity' on more than one live row for the same dataset is impossible only because the exclusion constraint catches it; never disable that constraint during bulk loads without re-validating afterward.