Database-Native Versioning in PostGIS and GeoPackage

Some spatial data is edited continuously by many people at once, and for that pattern the version history belongs where the transactions are — inside the database — rather than in a repository that would need a commit per edit. This guide is part of Choosing Formats & Tools for Spatial Versioning.

Prerequisites & Environment Setup

Before adding a database-native history to a production layer, confirm each of the following:

• A stable primary key on the live table that is never reused; history rows reference it and a reused key merges two entities’ histories
• PostgreSQL 13+ with PostGIS 3.1+ for the PostGIS route, or SQLite 3.35+ for the GeoPackage route with triggers enabled
• An application user identity reaching the database — either real database roles per user, or a session variable set by the application on every connection
• A retention and archival policy decided before the first trigger is installed, because history tables grow without bound and the decision is much harder once there are 400 million rows
• A tested restore path: history is only an audit trail if you can restore a table to a past state from it, and that path needs to have been exercised
• A reconciliation plan with the file repository — see versioning GeoPackage-PostGIS round trips for the export half

Core Algorithmic Patterns

1. History as an append-only mirror

The history table mirrors the live table’s columns and adds four of its own: what operation occurred, when, by whom, and the transaction that did it. Every write to the live table appends the previous state to history, so the live table always holds the current row and history holds every row it replaced.

Appending the previous state rather than the new one is a deliberate choice. It means the live table and the history together contain each state exactly once, with no duplication of the current row — and a restore to time t reads history for rows superseded after t and the live table for everything else.

2. Attribution flows through the session, not the row

A history row that records postgres as the author is worthless. Where every editor has a database role, current_user is sufficient. Where the application pools connections under one role — which is most web editing stacks — the application must set the acting user on the session:

SET LOCAL app.user_id  = 'a.okonkwo';
SET LOCAL app.reason   = 'boundary correction, ticket GIS-4471';

SET LOCAL scopes the setting to the transaction, so a pooled connection cannot leak one editor’s identity into another’s writes. A trigger that reads a session variable set with plain SET will eventually attribute an edit to whoever used the connection previously.

3. Triggers capture rows; they do not capture intent

A trigger sees UPDATE parcels SET geom = … and records it faithfully. What it cannot see is that this update and the next four were one operation — a parcel split — performed by one person for one reason.

Recording a transaction identifier on every history row recovers that grouping: rows sharing a transaction id were one logical operation. Combined with the session reason, a reviewer sees “five rows changed in one transaction: parcel split, ticket GIS-4471” rather than five unrelated edits.

Production Workflow Implementation

Step 1 — Create the history table

CREATE TABLE parcels_history (
    history_id   bigserial PRIMARY KEY,
    -- mirror of the live table
    parcel_id    uuid        NOT NULL,
    geom         geometry(MultiPolygon, 3035),
    owner_ref    text,
    land_use     text,
    -- audit columns
    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
);

CREATE INDEX parcels_history_entity_idx ON parcels_history (parcel_id, changed_at DESC);
CREATE INDEX parcels_history_txid_idx   ON parcels_history (txid);
CREATE INDEX parcels_history_geom_gix   ON parcels_history USING gist (geom);

The (parcel_id, changed_at DESC) index is the one that makes as-of reads viable; without it every historical question scans the whole history table.

Step 2 — Install the trigger

CREATE OR REPLACE FUNCTION parcels_audit() RETURNS trigger AS $$
DECLARE
    acting_user text := coalesce(
        current_setting('app.user_id', true),   -- true = do not error if unset
        current_user
    );
    reason text := current_setting('app.reason', true);
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO parcels_history
            (parcel_id, geom, owner_ref, land_use, operation,
             changed_by, change_reason, txid)
        VALUES (OLD.parcel_id, OLD.geom, OLD.owner_ref, OLD.land_use, 'D',
                acting_user, reason, txid_current());
        RETURN OLD;
    END IF;

    IF TG_OP = 'UPDATE' THEN
        -- Skip no-op updates: an application that rewrites unchanged rows
        -- would otherwise fill history with entries that record nothing.
        IF OLD IS NOT DISTINCT FROM NEW THEN
            RETURN NEW;
        END IF;
        INSERT INTO parcels_history
            (parcel_id, geom, owner_ref, land_use, operation,
             changed_by, change_reason, txid)
        VALUES (OLD.parcel_id, OLD.geom, OLD.owner_ref, OLD.land_use, 'U',
                acting_user, reason, txid_current());
        RETURN NEW;
    END IF;

    INSERT INTO parcels_history
        (parcel_id, geom, owner_ref, land_use, operation,
         changed_by, change_reason, txid)
    VALUES (NEW.parcel_id, NULL, NULL, NULL, 'I',
            acting_user, reason, txid_current());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER parcels_audit_trg
    AFTER INSERT OR UPDATE OR DELETE ON parcels
    FOR EACH ROW EXECUTE FUNCTION parcels_audit();

The no-op check is worth its four lines. Editing clients frequently rewrite every attribute on save whether or not anything changed, and without the check a day of browsing produces a day of history.

Step 3 — Restore a table to a past state

CREATE OR REPLACE FUNCTION parcels_as_of(at timestamptz)
RETURNS TABLE (parcel_id uuid, geom geometry, owner_ref text, land_use text) AS $$
    -- The most recent history row superseded after `at` holds the state as of `at`
    WITH superseded AS (
        SELECT DISTINCT ON (h.parcel_id)
               h.parcel_id, h.geom, h.owner_ref, h.land_use, h.operation
          FROM parcels_history h
         WHERE h.changed_at > at
         ORDER BY h.parcel_id, h.changed_at ASC
    )
    SELECT s.parcel_id, s.geom, s.owner_ref, s.land_use
      FROM superseded s
     WHERE s.operation <> 'I'                    -- created after `at`: did not exist
    UNION ALL
    SELECT p.parcel_id, p.geom, p.owner_ref, p.land_use
      FROM parcels p
     WHERE NOT EXISTS (
             SELECT 1 FROM parcels_history h
              WHERE h.parcel_id = p.parcel_id AND h.changed_at > at
           );
$$ LANGUAGE sql STABLE;

Reading this function is the fastest way to check that a history design is sound. If restoring a past state requires anything beyond history plus the live table, something is not being recorded.

Step 4 — Do the same inside a GeoPackage

SQLite has no session variables, so attribution comes from a column the application sets:

CREATE TABLE parcels_history (
    history_id   INTEGER PRIMARY KEY AUTOINCREMENT,
    parcel_id    TEXT NOT NULL,
    geom         BLOB,
    owner_ref    TEXT,
    operation    TEXT NOT NULL CHECK (operation IN ('I','U','D')),
    changed_at   TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now')),
    changed_by   TEXT NOT NULL
);

CREATE TRIGGER parcels_audit_update
AFTER UPDATE ON parcels
FOR EACH ROW
BEGIN
    INSERT INTO parcels_history
        (parcel_id, geom, owner_ref, operation, changed_by)
    VALUES (OLD.parcel_id, OLD.geom, OLD.owner_ref, 'U',
            coalesce(NEW.last_edited_by, 'unknown'));
END;

Register the history table in gpkg_extensions so other tools understand it is part of the container rather than stray data, and remember that a container copied to a field device carries its history with it — which is either exactly what you want or a data-minimisation problem, depending on the layer.

Step 5 — Reconcile with the file repository

# Deterministic export, recorded against both histories
TXID=$(psql -Atc "SELECT txid_current_snapshot()::text")
ogr2ogr -f GPKG exports/parcels_$(date -u +%Y%m%dT%H%M%SZ).gpkg \
        PG:"$PGCONN" -sql "SELECT * FROM parcels ORDER BY parcel_id" \
        -lco SPATIAL_INDEX=YES

git add exports/ && git commit -m "Snapshot at txid ${TXID}"
psql -c "INSERT INTO export_log (txid, commit_sha) VALUES ('${TXID}', '$(git rev-parse HEAD)')"

The export_log table is small and does the whole job: given a commit you can find the database state it came from, and given a database transaction you can find the commit that published it.

Code Reliability Patterns

Make the history table append-only in practice, not just by convention. Revoke UPDATE and DELETE on it from every role except a dedicated archival role. An audit trail that ordinary application code can modify is not an audit trail.

Handle schema changes on both tables together. A column added to the live table and not to history means history stops recording it silently. Put both ALTER TABLE statements in the same migration, and add a test that compares the two column lists.

Disable the trigger for bulk loads deliberately. Wrap the load in ALTER TABLE parcels DISABLE TRIGGER parcels_audit_trg, then record one history row describing the load. Leaving the trigger on produces millions of rows that record an import nobody will ever query row by row.

Never let a trigger failure be silent. A trigger that swallows an exception to avoid blocking an edit produces gaps in the audit trail exactly where something unusual happened. If history cannot be written, the edit should fail.

Performance & Scale Considerations

History tables grow faster than the layers they track, because they accumulate rather than replace. A parcel layer of 200,000 rows edited at a few hundred changes a day reaches tens of millions of history rows within a few years, dominated by geometry.

Three measures keep that manageable. Partition history by month on changed_at: as-of queries touch the partitions after the target date, and archival becomes a partition detach rather than a mass delete. Move geometry out of unchanged history rows — an attribute-only edit does not need a second copy of the geometry, and a null geometry with a reference to the previous history row halves the table on attribute-heavy layers. Compress and detach old partitions to cheaper storage on the retention schedule you decided before installing the trigger.

The read side is usually fine if the entity index exists. Where it degrades is the whole-layer as-of query on a large history: that is a table scan of every partition after the date, and it is worth materialising as a periodic snapshot instead — which is exactly what the file repository export already is.

Troubleshooting & Failure Modes

FAQ

Related

• Implementing History Tables with PostGIS Triggers — the trigger, the restore function, and the migration that keeps both tables in step
• Snapshotting a PostGIS Schema into a Versioned GeoPackage — the deterministic export that links the two histories
• Versioning GeoPackage-PostGIS Round-Trips — fidelity loss across the export and import boundary
• Temporal Versioning and Time-Travel Queries — adding valid time on top of a transaction-time history
• DVC vs GeoGit vs Git LFS for Vector Datasets — the file-based alternatives this sits beside

Back to Choosing Formats & Tools for Spatial Versioning