Temporal Tables in Postgres: Keeping Full Row History

Sooner or later a regulator, an auditor or a customer’s lawyer asks the one question your schema cannot answer: what did this record say last March? If your table only holds the current state, the honest answer is “we don’t know”, and that answer costs you.

A temporal table solves this by keeping every version of every row, each stamped with the window of time it was true. You can then ask the database for the state of the world as it stood at any past instant and get a straight answer. Postgres does not ship this natively yet, so this piece builds it the way that actually works in production: the temporal_tables extension for system versioning, a tstzrange period column, a history table, and a GiST index so the as-of queries stay fast. Stack: PostgreSQL 17.

System-versioning is not an audit log

People conflate the two and then build the wrong thing. An audit log records events: who called which endpoint, what they changed, when. It is append-only, it is usually semi-structured, and it is excellent for answering “who did this”. It is poor at answering “what was the full, queryable state of row 4471 on 12 March”, because reconstructing state from a stream of deltas is slow, brittle and easy to get wrong.

System-versioning stores state, not events. Every historical version is a complete row you can query with ordinary SQL, join against and filter by validity time. The two are complementary. The audit log tells you who and why; the temporal table tells you exactly what the data was. Regulated systems generally need both, and confusing one for the other leaves a gap you only discover under examination.

The moving parts

PostgreSQL 17 has no native SYSTEM_TIME period or FOR SYSTEM_TIME AS OF syntax; SQL:2011 system-versioning is still not in core. The mature answer is Vlad Arkhipov’s temporal_tables extension, which gives you a versioning trigger and a settable transaction clock. You supply three things: a period column of type tstzrange on the live table, a history table with an identical shape, and a trigger that moves the old version into history on every write.

CREATE EXTENSION IF NOT EXISTS temporal_tables;

-- Live table: current state only
CREATE TABLE client_mandate (
    id           bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    client_ref   text NOT NULL,
    risk_rating  text NOT NULL,
    status       text NOT NULL,
    sys_period   tstzrange NOT NULL
);

-- History table: an exact structural copy
CREATE TABLE client_mandate_history (LIKE client_mandate);

-- The versioning trigger. Arguments:
--   'sys_period'             the tstzrange period column
--   'client_mandate_history' where superseded rows are written
--   true                     adjust overlapping periods on concurrent writes
CREATE TRIGGER client_mandate_versioning
    BEFORE INSERT OR UPDATE OR DELETE ON client_mandate
    FOR EACH ROW EXECUTE PROCEDURE
    versioning('sys_period', 'client_mandate_history', true);

From here on, writes behave normally. On INSERT the trigger sets sys_period to [now, infinity). On UPDATE or DELETE it closes the outgoing row’s upper bound at the transaction timestamp, copies that closed row into client_mandate_history, and lets the new version take the open-ended period. The live table stays lean; the past accumulates next door. Because this is trigger-driven, the same discipline you apply to any write path applies here, including making sure retried writes stay correct rather than double-recording; the reasoning in making POST safe to retry transfers directly.

Querying the past cleanly

An as-of query asks for the version whose validity window contains a given instant. The range containment operator @> does exactly that. Current state lives in the live table, superseded state in history, so an honest point-in-time read spans both:

-- What did mandate 4471 look like on 12 March 2026?
SELECT id, client_ref, risk_rating, status, sys_period
FROM   client_mandate
WHERE  id = 4471
  AND  sys_period @> '2026-03-12 00:00:00+00'::timestamptz
UNION ALL
SELECT id, client_ref, risk_rating, status, sys_period
FROM   client_mandate_history
WHERE  id = 4471
  AND  sys_period @> '2026-03-12 00:00:00+00'::timestamptz;

For current state you never touch history: just query client_mandate as you always have. That separation is the whole point. Reads of live data pay nothing for the history you are keeping, and the historical query is opt-in. If you want the full lifecycle of one record, drop the timestamp predicate and order the history table by the lower bound of sys_period.

Index the history or it will bite you

The history table is where volume lands, and range containment against an unindexed tstzrange is a sequential scan. Range types support GiST directly. When you filter by an entity key and a period, as the as-of query does, you want both columns in one GiST index, which needs the btree_gist extension so a scalar like bigint can share the index with a range.

CREATE EXTENSION IF NOT EXISTS btree_gist;

-- Composite: entity key + validity window
CREATE INDEX client_mandate_history_id_period_idx
    ON client_mandate_history
    USING gist (id, sys_period);

That turns the point-in-time lookup into an index scan instead of a table scan. Make the index part of the migration that introduces versioning, not a follow-up you promise to add later, so it is enforced the same way the rest of your schema is — the same argument for keeping standards executable rather than aspirational that sits behind a pre-commit baseline that actually runs.

The storage cost, stated plainly

System-versioning trades disk for answerability. Every update writes a full copy of the old row, so a table churned frequently can grow its history to many times the size of the live data. That is not a reason to avoid it; it is a reason to plan for it. Decide up front which tables genuinely need full history — mandates, positions, pricing, permissions, anything a supervisor can ask you to reconstruct — and leave high-churn, low-value tables out. Set a retention policy where the regulation permits one, and partition the history table by the lower bound of sys_period if it grows large enough that pruning old partitions beats deleting rows.

The failure mode to avoid is turning this on everywhere by reflex and discovering the cost at capacity-planning time. Being able to show what a record said, and when it changed, is exactly the kind of reconstructable evidence that also underpins credible operational metrics; if you are already instrumenting change and recovery, temporal history is the data layer that lets those numbers survive scrutiny.

Build it on the tables where “we don’t know what it said” is an unacceptable answer, index the history, price the storage honestly, and the question that used to end in a shrug ends in a single query instead.