Implementing History Tables with PostGIS Triggers

One generated history table and one generic trigger will audit every spatial table in a schema — and the details that make it trustworthy are attribution, transaction grouping, and a restore function that proves nothing is missing. This page is a focused companion to database-native versioning in PostGIS and GeoPackage.

Concept & Context

Hand-written per-table audit triggers are the usual starting point and the usual source of drift: a column is added to the live table, the trigger is not updated, and history silently stops recording that column. Nobody notices until the field is needed in an investigation.

Generating the trigger from the catalogue removes that failure mode. The history table is derived from the live table’s definition, and the trigger stores attributes as a JSONB snapshot so a new column is captured automatically without a history migration. Geometry stays a typed, indexed column, because historical spatial queries — what did this extent look like last March — are common and JSONB cannot serve them.

The design records the previous state on every write. That choice is what keeps the two tables complementary rather than overlapping: the live table holds the present, history holds everything it replaced, and no state is stored twice.

Recording the previous state, not the new one Two panels. Storing the new state duplicates the current row across both tables and forces a restore to deduplicate; storing the previous state means each state exists exactly once, and a restore is a straightforward union. STORE THE NEW STATE The current row exists in both tables A restore has to deduplicate before returning History grows faster for no additional information Reads have to decide which copy is authoritative STORE THE PREVIOUS STATE Each state exists exactly once across both tables A restore is history-after-t union the live table Nothing is duplicated, so nothing can disagree The live table stays the single source for the present The restore function is the test: if it needs a deduplication step, the wrong state is being stored. It reads like a detail and it decides how every historical query has to be written.

Core Algorithmic Pipeline

  1. Generate the history table from the live table’s catalogue entry: entity key, geometry column with its type and SRID, plus the audit columns.
  2. Install one generic trigger function used by every audited table, reading the table name from the trigger context.
  3. Attribute through SET LOCAL session settings so pooled connections cannot leak identity between transactions.
  4. Group by transaction id so a multi-row logical operation is recoverable as one event.
  5. Expose a restore function, which is both a feature and the design’s own test.

Working Implementation

-- ─── One-time setup: the generic trigger function ────────────────────────────
CREATE OR REPLACE FUNCTION audit_spatial_row() RETURNS trigger AS $$
DECLARE
    hist_table text := TG_TABLE_NAME || '_history';
    key_column text := TG_ARGV[0];              -- entity key column name
    geom_column text := TG_ARGV[1];             -- geometry column name
    previous   jsonb;
    entity     text;
    geom       geometry;
    acting     text := coalesce(current_setting('app.user_id', true), current_user);
    why        text := current_setting('app.reason', true);
BEGIN
    IF TG_OP = 'UPDATE' AND OLD IS NOT DISTINCT FROM NEW THEN
        RETURN NEW;                             -- clients that rewrite on save
    END IF;

    IF TG_OP = 'INSERT' THEN
        previous := NULL;                       -- nothing was replaced
        entity   := to_jsonb(NEW) ->> key_column;
        geom     := NULL;
    ELSE
        previous := to_jsonb(OLD) - geom_column;
        entity   := to_jsonb(OLD) ->> key_column;
        geom     := (to_jsonb(OLD) ->> geom_column)::geometry;
    END IF;

    EXECUTE format(
        'INSERT INTO %I (entity_key, previous_attrs, previous_geom, operation,
                         changed_by, change_reason, txid)
         VALUES ($1, $2, $3, $4, $5, $6, txid_current())', hist_table)
    USING entity, previous, geom, left(TG_OP, 1), acting, why;

    RETURN CASE WHEN TG_OP = 'DELETE' THEN OLD ELSE NEW END;
END;
$$ LANGUAGE plpgsql;

-- ─── Generator: create the history table and attach the trigger ──────────────
CREATE OR REPLACE FUNCTION enable_history(
    p_table       regclass,
    p_key_column  text,
    p_geom_column text DEFAULT 'geom'
) RETURNS void AS $$
DECLARE
    tbl  text := p_table::text;
    hist text := tbl || '_history';
    srid int;
    gtype text;
BEGIN
    SELECT srid, type INTO srid, gtype
      FROM geometry_columns
     WHERE f_table_name = tbl AND f_geometry_column = p_geom_column;

    IF srid IS NULL THEN
        RAISE EXCEPTION '% has no registered geometry column %', tbl, p_geom_column;
    END IF;

    EXECUTE format($fmt$
        CREATE TABLE IF NOT EXISTS %I (
            history_id    bigserial PRIMARY KEY,
            entity_key    text        NOT NULL,
            previous_attrs jsonb,
            previous_geom  geometry(%s, %s),
            operation     char(1)     NOT NULL CHECK (operation IN ('I','U','D')),
            changed_at    timestamptz NOT NULL DEFAULT now(),
            changed_by    text        NOT NULL,
            change_reason text,
            txid          bigint      NOT NULL
        )$fmt$, hist, gtype, srid);

    EXECUTE format(
        'CREATE INDEX IF NOT EXISTS %I ON %I (entity_key, changed_at DESC)',
        hist || '_entity_idx', hist);
    EXECUTE format(
        'CREATE INDEX IF NOT EXISTS %I ON %I USING gist (previous_geom)',
        hist || '_geom_gix', hist);
    EXECUTE format(
        'CREATE INDEX IF NOT EXISTS %I ON %I (txid)', hist || '_txid_idx', hist);

    -- History is evidence: nothing but the archival role may alter it.
    EXECUTE format('REVOKE UPDATE, DELETE ON %I FROM PUBLIC', hist);

    EXECUTE format(
        'CREATE TRIGGER %I AFTER INSERT OR UPDATE OR DELETE ON %I
         FOR EACH ROW EXECUTE FUNCTION audit_spatial_row(%L, %L)',
        tbl || '_audit_trg', tbl, p_key_column, p_geom_column);
END;
$$ LANGUAGE plpgsql;

SELECT enable_history('parcels', 'parcel_uid');

-- ─── Restore: the design's own test ──────────────────────────────────────────
CREATE OR REPLACE FUNCTION parcels_as_of(at timestamptz)
RETURNS TABLE (parcel_uid text, geom geometry, attrs jsonb) AS $$
    WITH superseded AS (
        SELECT DISTINCT ON (h.entity_key)
               h.entity_key, h.previous_geom, h.previous_attrs, h.operation
          FROM parcels_history h
         WHERE h.changed_at > at
         ORDER BY h.entity_key, h.changed_at ASC
    )
    SELECT s.entity_key, s.previous_geom, s.previous_attrs
      FROM superseded s
     WHERE s.operation <> 'I'                       -- created after `at`
    UNION ALL
    SELECT p.parcel_uid, p.geom, to_jsonb(p) - 'geom'
      FROM parcels p
     WHERE NOT EXISTS (SELECT 1 FROM parcels_history h
                        WHERE h.entity_key = p.parcel_uid AND h.changed_at > at);
$$ LANGUAGE sql STABLE;

The application sets attribution per transaction, and SET LOCAL is doing real work here — with a connection pool, a plain SET leaks the previous user’s identity into the next transaction on that connection:

with conn.transaction():
    conn.execute("SET LOCAL app.user_id = %s", (request.user.username,))
    conn.execute("SET LOCAL app.reason  = %s", (request.form["reason"],))
    conn.execute("UPDATE parcels SET geom = %s WHERE parcel_uid = %s", (wkb, uid))
How an editor's name reaches the audit row A sequence in which the application sets the acting user on the transaction with SET LOCAL, the update fires the trigger, and the trigger reads the session setting — so a pooled connection cannot carry one editor's identity into another's transaction. App Connection Trigger History SET LOCAL app.user_id transaction-scoped, never session-scoped UPDATE parcels … AFTER UPDATE, per row previous state + acting user commit clears the setting With a plain SET, the next transaction on that pooled connection inherits the previous editor's name.

Validation & Output Verification

-- 1. The trigger fires, and records the PREVIOUS state
BEGIN;
SET LOCAL app.user_id = 'test.runner';
SET LOCAL app.reason  = 'verification';
UPDATE parcels SET land_use = 'industrial' WHERE parcel_uid = 'EAST-7K3M2P9Q4R';
SELECT operation, changed_by, previous_attrs ->> 'land_use' AS was
  FROM parcels_history
 WHERE entity_key = 'EAST-7K3M2P9Q4R' ORDER BY changed_at DESC LIMIT 1;
-- expected: U | test.runner | <the value before this statement>
ROLLBACK;

-- 2. A no-op update must NOT produce a history row
BEGIN;
SELECT count(*) AS before FROM parcels_history \gset
UPDATE parcels SET land_use = land_use WHERE parcel_uid = 'EAST-7K3M2P9Q4R';
SELECT count(*) - :before AS rows_written FROM parcels_history;
-- expected: 0
ROLLBACK;

-- 3. History is not writable by the application role
SET ROLE gis_app;
UPDATE parcels_history SET changed_by = 'someone.else' WHERE history_id = 1;
-- expected: ERROR: permission denied for table parcels_history
RESET ROLE;

-- 4. Restore reconstructs a known past state
SELECT count(*) FROM parcels_as_of(now() - interval '30 days');

The third check is the one people skip. An audit trail the application can rewrite provides the appearance of accountability and none of the substance.

For a schema change, alter both sides in one migration and assert it afterwards:

-- Attributes live in JSONB, so history needs no column change — but assert it
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
         WHERE table_name = 'parcels_history' AND column_name = 'previous_attrs'
    ) THEN
        RAISE EXCEPTION 'history table lost its attribute snapshot column';
    END IF;
END $$;

Failure Modes

  • Every row attributed to the pool user — symptom: changed_by is always the application role. Root cause: the application never sets app.user_id. Fix: set it with SET LOCAL on every transaction, and make the trigger raise in production when it is absent.

  • History exploded overnight — symptom: millions of rows from one import. Root cause: a bulk load ran with the trigger enabled. Fix: ALTER TABLE parcels DISABLE TRIGGER parcels_audit_trg around the load and write one summary row; purge the noise by txid.

  • An editor’s name appears on another’s edit — symptom: implausible attribution under load. Root cause: SET rather than SET LOCAL on a pooled connection. Fix: switch to SET LOCAL and audit the affected window by transaction id.

  • Restore returns rows that did not exist — symptom: an as-of query includes features created later. Root cause: insert rows not excluded from the superseded set. Fix: filter operation <> 'I', as the restore function does.

What a bulk load does to a history table Horizontal bars comparing history rows written by a normal editing day, by a bulk import with the trigger left enabled, and by the same import with the trigger disabled and a single summary row recorded instead. HISTORY ROWS WRITTEN A day of editing 340 Import, trigger on 1.2e+06 a copy of the import, not an audit trail Import, trigger off 1 one summary row describing the load The middle bar also slows every historical query afterwards, which is how it is usually discovered.

Back to Database-Native Versioning in PostGIS and GeoPackage