The history tables pattern

Capsule overview

The audit log you built in capsule 03 answers "who changed what and when." It's lightweight, fast, and enough for 80% of cases. But there are questions it can't answer well. For example: "how exactly did customer Acme's contract look on March 15, 2025?". Your audit log has the incremental diffs (an amount change on March 10, a plan change on the 12th, a date change on the 14th), but reconstructing the complete state from diffs is slow, fragile, and doesn't work well when there are deletions in between.

History tables solve exactly this problem. Instead of storing the change, they store the complete row every time it changes, marked with valid_from and valid_to — the time range during which that version was "the truth." A single query (WHERE valid_from <= '2025-03-15' AND valid_to > '2025-03-15') gives you the exact snapshot of any moment in the past. It's the pattern the temporal tables of the SQL:2011 standard formalized, and although PostgreSQL doesn't implement them natively, replicating it with explicit columns is straightforward.

In this capsule you're going to understand when history tables are the right answer vs when an audit log is enough, you're going to implement the complete pattern (schema, trigger, point-in-time query) in PostgreSQL 16+, and you're going to see the real cost: the write doubles (every UPDATE writes to tasks AND to tasks_history), storage grows faster than with an audit log, but the "how did it look exactly" query is trivial and fast. In the end you'll have criteria for choosing between the two approaches or combining them.

Mental model: photo vs changes

Think about the difference with a metaphor. Your kid changes outfits every day. To document their growth, you have two options.

Option A (audit log): you write down each change of clothes. "Day 12: went from red pants to blue pants." "Day 13: went from a white shirt to a yellow shirt." To answer "what outfit were they wearing on day 50?", you have to read every change from day 1 and apply them in order. It's lightweight (you only write down what changed), but reconstructing a specific day requires a replay.

Option B (history table): you take a full-body photo every time something changes. Day 12: photo. Day 13: photo. Day 14: photo. To answer "what outfit were they wearing on day 50?", you look up that day's photo. It weighs more (each photo is the full image, not just what changed), but the answer is direct.

An audit log is option A. A history table is option B. Both are valid. The choice depends on which question you ask more often.

A real case where the history table wins: looking at the exact contract on a past date for a legal dispute, showing the user "your plan in March was Pro" without reconstructing from the log, running reports that cross data from several entities as they existed at a specific moment.

A real case where the audit log wins: support asking "who changed this yesterday?", compliance requesting "a list of every change to field X in the last 90 days," debugging when the change is what matters, not the state.

They frequently coexist. Most serious B2B SaaS have both for critical tables (contracts, billing).

The schema of a history table

The structure is simple: a mirror table of the original, with two additional columns (valid_from and valid_to) that bound the time range during which that version was valid.

Base definition

-- The domain table (already existing)
CREATE TABLE tasks (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    description TEXT NULL,
    status TEXT NOT NULL DEFAULT 'open',
    priority INTEGER NOT NULL DEFAULT 0,
    assignee_id BIGINT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- The history table: each complete version, with its validity range
CREATE TABLE tasks_history (
    history_id BIGSERIAL PRIMARY KEY,
    -- The columns of tasks (mirrored)
    id BIGINT NOT NULL,
    title TEXT NOT NULL,
    description TEXT NULL,
    status TEXT NOT NULL,
    priority INTEGER NOT NULL,
    assignee_id BIGINT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL,
    -- The temporal versioning columns
    valid_from TIMESTAMPTZ NOT NULL,
    valid_to TIMESTAMPTZ NOT NULL DEFAULT 'infinity'::TIMESTAMPTZ,
    -- Optional metadata (similar to the audit log)
    operation TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    changed_by BIGINT NULL,

    -- Guarantee: only one "current" version per id at any moment
    EXCLUDE USING gist (id WITH =, tstzrange(valid_from, valid_to) WITH &&)
);

-- Indexes for the typical queries
CREATE INDEX idx_tasks_history_id_validity
    ON tasks_history (id, valid_from DESC, valid_to);

CREATE INDEX idx_tasks_history_changed_by
    ON tasks_history (changed_by, valid_from DESC)
    WHERE changed_by IS NOT NULL;

The schema's decisions explained:

• history_id separate from id: multiple versions have the same id (the original task's id). The primary key has to be unique, hence history_id.
• valid_to DEFAULT 'infinity': PostgreSQL supports the special value infinity for timestamps. A "currently valid" version has valid_to = infinity. When a change arrives, that row gets "closed" (valid_to = NOW()) and a new one is inserted with valid_from = NOW(), valid_to = infinity.
• EXCLUDE USING gist: this constraint guarantees that for any id, the valid_from..valid_to ranges don't overlap. Essential: if two rows with the same id have overlapping ranges, the "state at T" query returns two results — a serious bug. The constraint prevents it at the schema level. It requires the btree_gist extension (CREATE EXTENSION btree_gist;).
• tstzrange(): PostgreSQL has a native tstzrange type (a range of timestamps with timezone). The constraint uses that type to check for overlap.
• operation: records what kind of change generated that version (INSERT/UPDATE/DELETE). On DELETE, the last version is left with valid_to = NOW() and nobody replaces it with valid_from = NOW().

The trigger that maintains the history

CREATE OR REPLACE FUNCTION tasks_history_trigger()
RETURNS TRIGGER AS $$
DECLARE
    v_user_id BIGINT;
    v_now TIMESTAMPTZ := NOW();
BEGIN
    v_user_id := NULLIF(current_setting('audit.user_id', true), '')::BIGINT;

    -- On UPDATE and DELETE: close the previous version
    IF TG_OP IN ('UPDATE', 'DELETE') THEN
        UPDATE tasks_history
        SET valid_to = v_now
        WHERE id = OLD.id AND valid_to = 'infinity'::TIMESTAMPTZ;
    END IF;

    -- On INSERT and UPDATE: insert the new current version
    IF TG_OP IN ('INSERT', 'UPDATE') THEN
        INSERT INTO tasks_history (
            id, title, description, status, priority, assignee_id,
            created_at, updated_at,
            valid_from, valid_to,
            operation, changed_by
        )
        VALUES (
            NEW.id, NEW.title, NEW.description, NEW.status, NEW.priority, NEW.assignee_id,
            NEW.created_at, NEW.updated_at,
            v_now, 'infinity'::TIMESTAMPTZ,
            TG_OP, v_user_id
        );
    END IF;

    -- On DELETE: we only close the previous version (already done above)
    -- We don't insert a new version, because the entity stopped existing.

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tasks_history_trigger
AFTER INSERT OR UPDATE OR DELETE ON tasks
FOR EACH ROW
EXECUTE FUNCTION tasks_history_trigger();

The trigger's logic:

• INSERT on tasks: inserts ONE row in tasks_history with valid_from = NOW(), valid_to = infinity.
• UPDATE on tasks: closes the previous version (valid_to = NOW()), inserts a new version with valid_from = NOW(), valid_to = infinity.
• DELETE on tasks: closes the previous version. Inserts nothing new. The history is complete: the entity existed from X to Y, then stopped existing.

An important note: any UPDATE tasks_history SET valid_to = ... from inside the trigger does NOT fire the trigger recursively, because tasks_history doesn't have its own trigger. That's by design: the history table is append-mostly from the trigger, and must not be modified externally.

The point-in-time query

This is the pattern's star query: given an id and a date, return the exact state at that moment.

-- The state of task #42 on March 15, 2026 at 12:00 UTC
SELECT id, title, description, status, priority, assignee_id
FROM tasks_history
WHERE id = 42
  AND valid_from <= '2026-03-15 12:00:00+00'
  AND valid_to > '2026-03-15 12:00:00+00';

Why this query always returns zero or one row:

• The EXCLUDE USING gist constraint guarantees the ranges don't overlap for the same id.
• For any moment T, there's at most one valid version (the one satisfying valid_from <= T AND valid_to > T).
• If the entity didn't exist at T (created later or deleted earlier), it returns zero rows.

Specialized operators with tstzrange:

-- The equivalent version with the @> operator (range contains)
SELECT *
FROM tasks_history
WHERE id = 42
  AND tstzrange(valid_from, valid_to) @> '2026-03-15 12:00:00+00'::TIMESTAMPTZ;

Both are equivalent. The first is more explicit; the second is more idiomatic in PostgreSQL.

FastAPI endpoint: "see the task as it was on day X"

from datetime import datetime

@app.get("/tasks/{task_id}/at/{at_time}")
async def task_at_time(
    task_id: int,
    at_time: datetime,
    db: AsyncSession = Depends(get_session),
):
    result = await db.execute(text("""
        SELECT id, title, description, status, priority, assignee_id, valid_from, valid_to
        FROM tasks_history
        WHERE id = :tid
          AND valid_from <= :at
          AND valid_to > :at
    """), {"tid": task_id, "at": at_time})

    row = result.first()
    if row is None:
        raise HTTPException(404, f"Task {task_id} did not exist at {at_time}")

    return {
        "id": row.id,
        "title": row.title,
        "description": row.description,
        "status": row.status,
        "priority": row.priority,
        "assignee_id": row.assignee_id,
        "version_valid_from": row.valid_from.isoformat(),
        "version_valid_to": (
            row.valid_to.isoformat() if row.valid_to.year < 9999 else "current"
        ),
    }

A real use case: a TaskFlow customer disputes a task's state ("I said I closed it on Tuesday, not Friday"). The endpoint returns exactly how it looked on Tuesday. Without a history table, that question has no direct answer.

Audit log vs history table: side by side

Decision matrix: when to use which

Use an audit log if:

• The main question is "who changed what and when?".
• Storage is a constraint (the audit log is orders of magnitude lighter).
• The schema changes frequently (the audit log adapts with no ALTERs on the history).
• Compliance is the main driver (the frameworks typically ask for a "change log," not "historical snapshots").

Use a history table if:

• The main question is "how exactly did this look on date X?".
• You need reports that cross entities as they existed at a moment (like "billing as of December 31").
• Legal disputes about past states are recurring.
• The schema is stable (schema changes require an ALTER on the history).

Use both if:

• Both questions are frequent.
• Storage isn't a critical constraint.
• Your product has compliance (audit log) AND features like "see the previous version" (history table).

Use neither if:

• The table is for sessions, cache, or volatile config where history adds no value.
• The volume is so high that even an audit log isn't viable (typically >1M changes/second) — there you get into CDC with Debezium and similar, out of the module's scope.

Why does this matter in real work?

1. Legal disputes about past data are expensive. A customer claims their contract said X, not Y. Without a history table, you have to rely on the audit log to reconstruct, which is slow and vulnerable to interpretation. With a history table, you have the exact snapshot in a one-second query. The difference between "we have the evidence" and "we have the logs" in court is enormous.

2. "As-of" reports become trivial. "Inventory state at the close of March" in an e-commerce app with constant changes is a nightmare without a history table — you have to freeze monthly reports in a separate system. With a history table, a query with WHERE valid_from <= '2026-03-31 23:59:59' AND valid_to > '2026-03-31 23:59:59' solves it.

3. Features like "see the previous version" are cheap. The product team wants "undo change" or "see the visual history." With a history table, each version is a complete snapshot ready to display. Without a history table, you have to implement replay from the audit log — more complex, more fragile.

4. Advanced regulatory auditing asks for it specifically. Some frameworks (notably HIPAA in healthcare, MiFID II in finance) ask for "complete snapshots at specific moments," not just a "change log." For those cases, an audit log isn't enough.

Traps and common mistakes

Mistake 1 (conceptual): thinking a history table replaces the audit log

Symptom: the team implements a history table and removes the audit log because "we already have the history." Later support asks "who changed this?" and they discover that extracting "the change's actor" from the history table requires comparing versions to infer who changed what — more complex than reading the audit log.

Why it's confusing: both seem to "store the history." But they answer different questions. A history table answers "how the data looked"; an audit log answers "what changed and who did it."

How to tell: try to answer with the history table alone: "what changed when user 47 did the UPDATE on Tuesday at 14:33?". You need to extract the two consecutive versions, compare field by field, and infer the change. With the audit log it's direct: a single row already has the diff and the changed_by.

How to fix it: treat the audit log and the history table as complementary. Implement the one that solves the dominant problem; if both cases are frequent, implement both. The cost of maintaining both systems is low when they come from the same shared trigger.

Mistake 2 (operational): not using EXCLUDE USING gist and allowing overlapping ranges

Symptom: the "state at T" query sometimes returns two rows. The application doesn't know which one to use. The bug shows up sporadically in production, almost always during high-concurrency moments.

Why it happens: without the EXCLUDE constraint, two concurrent UPDATEs can generate versions whose [valid_from, valid_to) ranges overlap. The trigger isn't atomic against concurrent updates on other connections.

How to tell: run this audit query:

-- Detect overlaps in a history table
SELECT a.id, a.valid_from, a.valid_to, b.valid_from, b.valid_to
FROM tasks_history a
JOIN tasks_history b ON a.id = b.id
  AND a.history_id < b.history_id
  AND tstzrange(a.valid_from, a.valid_to) && tstzrange(b.valid_from, b.valid_to)
LIMIT 10;

If it returns any rows, you have overlaps.

How to fix it: add the constraint and clean up the inconsistent data (manually, choosing which version stays):

-- 1. Make sure the extension is available
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- 2. Clean up the duplicates (manual; depends on the project's context)

-- 3. Add the constraint
ALTER TABLE tasks_history
ADD CONSTRAINT tasks_history_no_overlap
EXCLUDE USING gist (id WITH =, tstzrange(valid_from, valid_to) WITH &&);

After this, two concurrent transactions that try to generate overlapping versions: one will commit, the other will fail with a constraint violation. That's what we want: a single "truth" per moment.

Mistake 3 (operational): storage explodes because every UPDATE generates a complete version

Symptom: after six months with a history table on a 100k-record table, tasks_history weighs 50GB. The original table weighs 200MB.

Why it happens: if the table has large columns (TEXT with long descriptions, JSONB with heavy metadata), each complete version duplicates everything. 1k changes per entity × 100k entities × row size = a storage explosion.

How to tell: measure:

SELECT
    pg_size_pretty(pg_total_relation_size('tasks')) AS source_size,
    pg_size_pretty(pg_total_relation_size('tasks_history')) AS history_size,
    (pg_total_relation_size('tasks_history')::FLOAT / pg_total_relation_size('tasks'))::INT AS multiplier;

If the multiplier is >100x and the source table is large, the history table is accumulating too much.

How to fix it (options):

1. Exclude large, volatile columns from the history table. If description changes a lot and is enormous, consider not replicating it. The history is incomplete but the storage stays under control.

2. Partition tasks_history by date and archive the old partitions. Covered in capsule 07.

3. Implement retention: delete versions older than N years. The audit log can keep everything (it's lightweight); the history table can have a more aggressive retention.

4. Migrate to an audit log if the snapshots aren't necessary. Sometimes the team implemented a history table without really needing it; reconsidering is valid.

Mistake 4 (conceptual): assuming the "current state" query runs against tasks_history

Symptom: a developer writes SELECT * FROM tasks_history WHERE id = 42 AND valid_to = 'infinity' to get the current state. It works, but it's 10x slower than SELECT * FROM tasks WHERE id = 42.

Why it happens: the history table has N versions; even with an index, it searches among all of them. The original table has a single row per id, a direct lookup by primary key.

How to tell: compare performance. For current-state queries, the original table always has to be the source.

How to fix it: use the original table (tasks) for current-state queries. Use tasks_history only for point-in-time queries or comparisons between versions. This is by design: the history table is for historical queries, the original one is for current queries.

# CORRECT: current state from the original table
async def get_current_task(db, task_id):
    return await db.get(Task, task_id)

# CORRECT: past state from the history
async def get_task_at(db, task_id, at_time):
    result = await db.execute(text("""
        SELECT * FROM tasks_history
        WHERE id = :tid AND valid_from <= :at AND valid_to > :at
    """), {"tid": task_id, "at": at_time})
    return result.first()

Mistake 5 (operational): schema changes on tasks don't propagate to tasks_history

Symptom: the team adds a tasks.due_date column. The column exists in tasks but not in tasks_history. The trigger keeps working (it doesn't mention due_date), but the new versions in tasks_history don't have the field. When you try to reconstruct the state of a task with due_date, the column is empty (the default).

Why it happens: the history table is manual: any ALTER to tasks has to be replicated in tasks_history. The trigger has to be updated to include the new field in its INSERT.

How to tell: after any migration that touches tasks, verify that tasks_history has the same columns (excluding the metadata: history_id, valid_from, valid_to, operation, changed_by).

How to fix it (an operational process):

1. Every migration to tasks includes a parallel migration to tasks_history (in the same Alembic op.execute()).
2. A schema parity test that fails if the migrations diverge:

def test_tasks_history_schema_matches_tasks():
    """tasks_history has to have every column of tasks (plus the versioning ones)."""
    tasks_cols = set(get_columns("tasks")) - {"id"}  # id is BIGINT, not BIGSERIAL in history
    history_cols = set(get_columns("tasks_history")) - {
        "history_id", "valid_from", "valid_to", "operation", "changed_by"
    }
    missing = tasks_cols - history_cols
    extra = history_cols - tasks_cols
    assert not missing, f"tasks_history missing columns: {missing}"
    assert not extra, f"tasks_history has extra columns: {extra}"

3. Generate the trigger from code. Some teams use templates that generate the trigger automatically from the source table's schema. More complex but it eliminates the risk of divergence.

Exercises

Exercise 1: implement a history table for a new table

Your app is going to add a contracts table with sensitive fields that carry potential legal disputes. Schema:

CREATE TABLE contracts (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    plan TEXT NOT NULL CHECK (plan IN ('free', 'pro', 'enterprise')),
    monthly_amount_cents BIGINT NOT NULL,
    starts_at DATE NOT NULL,
    ends_at DATE NULL,
    terms_text TEXT NOT NULL,
    signed_by TEXT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Implement contracts_history and the corresponding trigger.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE contracts_history (
    history_id BIGSERIAL PRIMARY KEY,
    -- A mirror of contracts
    id BIGINT NOT NULL,
    customer_id BIGINT NOT NULL,
    plan TEXT NOT NULL,
    monthly_amount_cents BIGINT NOT NULL,
    starts_at DATE NOT NULL,
    ends_at DATE NULL,
    terms_text TEXT NOT NULL,
    signed_by TEXT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL,
    -- Temporal versioning
    valid_from TIMESTAMPTZ NOT NULL,
    valid_to TIMESTAMPTZ NOT NULL DEFAULT 'infinity'::TIMESTAMPTZ,
    operation TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    changed_by BIGINT NULL,

    EXCLUDE USING gist (id WITH =, tstzrange(valid_from, valid_to) WITH &&)
);

CREATE INDEX idx_contracts_history_id_validity
    ON contracts_history (id, valid_from DESC, valid_to);

CREATE INDEX idx_contracts_history_customer
    ON contracts_history (customer_id, valid_from DESC);

CREATE OR REPLACE FUNCTION contracts_history_trigger()
RETURNS TRIGGER AS $$
DECLARE
    v_user_id BIGINT;
    v_now TIMESTAMPTZ := NOW();
BEGIN
    v_user_id := NULLIF(current_setting('audit.user_id', true), '')::BIGINT;

    IF TG_OP IN ('UPDATE', 'DELETE') THEN
        UPDATE contracts_history
        SET valid_to = v_now
        WHERE id = OLD.id AND valid_to = 'infinity'::TIMESTAMPTZ;
    END IF;

    IF TG_OP IN ('INSERT', 'UPDATE') THEN
        INSERT INTO contracts_history (
            id, customer_id, plan, monthly_amount_cents, starts_at, ends_at,
            terms_text, signed_by, created_at, updated_at,
            valid_from, valid_to, operation, changed_by
        )
        VALUES (
            NEW.id, NEW.customer_id, NEW.plan, NEW.monthly_amount_cents,
            NEW.starts_at, NEW.ends_at, NEW.terms_text, NEW.signed_by,
            NEW.created_at, NEW.updated_at,
            v_now, 'infinity'::TIMESTAMPTZ, TG_OP, v_user_id
        );
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER contracts_history_trigger
AFTER INSERT OR UPDATE OR DELETE ON contracts
FOR EACH ROW
EXECUTE FUNCTION contracts_history_trigger();

Exercise 2: a point-in-time query + a comparison between versions

Write two queries:

a) The state of contract #5 on December 31, 2025 at 23:59:59 UTC.

b) A comparison of consecutive versions of contract #5: each version + which fields changed relative to the previous one.

SELECT
    id,
    customer_id,
    plan,
    monthly_amount_cents,
    starts_at,
    ends_at,
    terms_text,
    signed_by,
    valid_from,
    valid_to
FROM contracts_history
WHERE id = 5
  AND valid_from <= '2025-12-31 23:59:59+00'::TIMESTAMPTZ
  AND valid_to > '2025-12-31 23:59:59+00'::TIMESTAMPTZ;

WITH versions AS (
    SELECT
        history_id,
        id, plan, monthly_amount_cents, starts_at, ends_at, signed_by,
        valid_from, valid_to, operation, changed_by,
        LAG(plan) OVER w AS prev_plan,
        LAG(monthly_amount_cents) OVER w AS prev_amount,
        LAG(starts_at) OVER w AS prev_starts_at,
        LAG(ends_at) OVER w AS prev_ends_at,
        LAG(signed_by) OVER w AS prev_signed_by
    FROM contracts_history
    WHERE id = 5
    WINDOW w AS (PARTITION BY id ORDER BY valid_from)
)
SELECT
    valid_from,
    valid_to,
    operation,
    changed_by,
    CASE WHEN plan IS DISTINCT FROM prev_plan
         THEN jsonb_build_object('plan', jsonb_build_array(prev_plan, plan)) ELSE NULL END
    || CASE WHEN monthly_amount_cents IS DISTINCT FROM prev_amount
         THEN jsonb_build_object('monthly_amount_cents', jsonb_build_array(prev_amount, monthly_amount_cents)) ELSE NULL END
    || CASE WHEN starts_at IS DISTINCT FROM prev_starts_at
         THEN jsonb_build_object('starts_at', jsonb_build_array(prev_starts_at, starts_at)) ELSE NULL END
    || CASE WHEN ends_at IS DISTINCT FROM prev_ends_at
         THEN jsonb_build_object('ends_at', jsonb_build_array(prev_ends_at, ends_at)) ELSE NULL END
    || CASE WHEN signed_by IS DISTINCT FROM prev_signed_by
         THEN jsonb_build_object('signed_by', jsonb_build_array(prev_signed_by, signed_by)) ELSE NULL END
    AS changes_from_previous
FROM versions
ORDER BY valid_from;

Exercise 3: detect range overlaps in a history table

An old history table in your app doesn't have the EXCLUDE USING gist constraint. You suspect there are overlapping ranges from historical trigger bugs. Write the query that detects them and propose a fix.

-- Detection query
SELECT
    a.history_id AS history_id_a,
    b.history_id AS history_id_b,
    a.id AS entity_id,
    a.valid_from AS a_valid_from,
    a.valid_to AS a_valid_to,
    b.valid_from AS b_valid_from,
    b.valid_to AS b_valid_to,
    -- The magnitude of the overlap
    LEAST(a.valid_to, b.valid_to) - GREATEST(a.valid_from, b.valid_from) AS overlap_duration
FROM tasks_history a
JOIN tasks_history b ON a.id = b.id
  AND a.history_id < b.history_id
  AND tstzrange(a.valid_from, a.valid_to) && tstzrange(b.valid_from, b.valid_to)
ORDER BY entity_id, a_valid_from
LIMIT 100;

-- Example: if A and B overlap, and B is "more recent",
-- adjust A.valid_to = B.valid_from
UPDATE tasks_history a
SET valid_to = b.valid_from
FROM tasks_history b
WHERE a.id = b.id
  AND a.history_id < b.history_id
  AND a.valid_to > b.valid_from
  AND a.valid_to <= b.valid_to;

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE tasks_history
ADD CONSTRAINT tasks_history_no_overlap
EXCLUDE USING gist (id WITH =, tstzrange(valid_from, valid_to) WITH &&);

async def test_history_no_solapa(session):
    """Guarantee there are no overlapping ranges in tasks_history."""
    result = await session.execute(text("""
        SELECT COUNT(*) FROM tasks_history a
        JOIN tasks_history b ON a.id = b.id
          AND a.history_id < b.history_id
          AND tstzrange(a.valid_from, a.valid_to) && tstzrange(b.valid_from, b.valid_to)
    """))
    assert result.scalar() == 0, "Overlaps detected in tasks_history"

Exercise 4: measure a history table's storage cost

Your team is going to enable a history table on a table with 500k rows and ~5 changes/month per row. Each row weighs ~2KB. Compute the expected storage of the history table in a year, and propose mitigation strategies if it exceeds 50GB.

DELETE FROM tasks_history
WHERE valid_to < NOW() - INTERVAL '18 months';

Exercise 5: defend the "audit log + history table together" decision in a code review

Your teammate suggests "picking one of the two, not both, because maintaining two systems doubles the complexity." Argue why for a contracts table (high legal criticality) both are justified.

Summary and next step

In this capsule you learned:

• A history table stores the complete row with valid_from/valid_to, not the change. The difference from an audit log is fundamental: a photo vs a note about a change.
• The "state at T" query is trivial: WHERE valid_from <= T AND valid_to > T. That simplicity is the pattern's main value.
• The EXCLUDE USING gist constraint guarantees no overlap and it's essential for the "state at T" query to always return a single version.
• The trigger closes the previous version and opens a new one on every UPDATE. On DELETE, it only closes (the entity stopped existing).
• The audit log and the history table are complementary, not mutually exclusive. The audit answers "what changed and who"; the history answers "how it looked exactly." They frequently coexist.
• A decision matrix: an audit log if the question is "who/what changed," a history table if it's "how it looked at T," both if your product has both needs.
• A history table's storage grows fast because each version is the complete row. Strategies: exclude large columns, retention, partitioning (capsule 07).
• Schema changes on the source table require parallel changes on the history table. Schema parity tests prevent divergence.

Before moving on you should be able to:

• Implement the complete pattern (schema + trigger + EXCLUDE constraint) for a new table in under 30 minutes.
• Decide between an audit log, a history table, or both for a specific table with judgment.
• Detect and fix range overlaps in an existing history table.
• Estimate the annual storage of a history table and propose mitigations if it exceeds the budget.
• Defend the decision to maintain both systems (audit + history) when it applies.

Next capsule — Lightweight vs full event sourcing. You're going to meet the module's third approach: instead of snapshots (a history table) or diffs (an audit log), storing the events that generated the changes. You're going to understand why this approach shines in event-driven systems and why it's over-engineering for a monolithic API. You're going to implement the "lightweight" version (an append-only events table, replay to rebuild an aggregate) in PostgreSQL, with no Kafka or dedicated event store. And you're going to learn the criteria for recognizing when to migrate to the "full" approach (real event sourcing with projections, snapshots, and CQRS), which is material for another guide.

Resources

1. PostgreSQL Documentation — Range Types — the reference for tstzrange, the &&, @> operators, etc. Required reading if you've never used ranges in PostgreSQL.
2. PostgreSQL Documentation — EXCLUDE constraints — the EXCLUDE USING gist constraint with an explanation.
3. PostgreSQL Documentation — btree_gist extension — the extension that lets you use types like BIGINT in GIST indexes alongside ranges.
4. Martin Fowler — "Bitemporal History" — the formal pattern history tables are a simplified version of. Useful for understanding the theory behind it.
5. Vlad Mihalcea — "How to track effective dates with PostgreSQL temporal tables" — an implementation of the pattern in another stack; useful for comparing approaches.
6. SQL:2011 standard — Temporal features — an overview of the standard that formalized this pattern. PostgreSQL doesn't implement it natively, but replicating it with explicit columns is straightforward.
7. Ben Brumm — "When to use temporal tables" — an analysis of when the pattern applies and when it doesn't, with examples from several DBs.

Module 3 — SQL Patterns for Production APIs Guide

Next capsule: Lightweight vs full event sourcing — the third approach, its trade-offs, and when NOT to use it.