Capturing PostGIS Changes with Logical Replication

Set REPLICA IDENTITY FULL before creating the slot, decode the WAL in a process that does nothing else, and alert on slot lag on day one — an unconsumed replication slot pins the write-ahead log, so a crashed consumer fills the data volume and takes down every writer, not just the feature stream.

This guide sits under Feature Change Triggers, within Core Event Fundamentals & Architecture. It is the alternative to the in-transaction trigger that topic describes, and it trades a write-path cost for an operational one.

When to use this pattern

  • Feature edits are frequent enough that trigger cost shows up in commit latency, or geometries are large enough that serialising one inside the transaction is measurable.
  • Something other than your application writes to the tables — a bulk import, a GIS desktop client, a migration — and a trigger is the only thing that would catch it. This is also the argument for triggers over application-level publishing, and logical replication keeps it.
  • You can operate a slot, which means you can alert on its lag and have somewhere for the consumer to run.

If feature edits are rare and small, a trigger is simpler and the operational hazard is not worth taking on.

Where the cost lands

Inside the transaction, or after it Two change-capture designs are traced against one feature edit. With a trigger, the transaction begins, the row is updated, the trigger then serialises the geometry to GeoJSON and enqueues an event, and only then does the commit complete — so the serialisation time, which scales with vertex count, is added directly to the write latency the application sees, and a queue call inside the trigger holds the transaction open across a network round trip. With logical replication the transaction begins, the row is updated, and the commit completes immediately; the change is durable in the write-ahead log, and a separate decoder process reads it afterwards, serialises the geometry and publishes the event on its own time. The application's write latency is unchanged by the size of the geometry. What has been bought is not less work but work moved off the critical path, and what has been sold is a new failure mode: the decoder is now something that can fall behind, and while it is behind the database cannot recycle the log. trigger — the writer pays for the geometry BEGIN UPDATE row trigger: ST_AsGeoJSON + enqueue COMMIT write latency the application sees — grows with vertex count logical replication — the writer commits and leaves BEGIN UPDATE row COMMIT write latency — independent of geometry size decoder process: read WAL · serialise geometry · publish — on its own time What was bought, and what was sold Not less work — work moved off the critical path. The decoder is now something that can fall behind, and while it is behind the database cannot recycle its log.
Figure 1. The trade is real in both directions. Commit latency stops depending on geometry size; a new process becomes load-bearing for the database's disk usage.

Complete runnable implementation

Set up the database side first. REPLICA IDENTITY FULL is what makes an update carry the old row, which is the only way to tell a geometry change from an attribute change.

sql 
-- Old geometry available on UPDATE. Without this, an update record carries
-- only the primary key of the old row and "did it move?" is unanswerable.
ALTER TABLE features REPLICA IDENTITY FULL;

CREATE PUBLICATION feature_changes FOR TABLE features;

-- pgoutput is built in; wal2json is easier to read but must be installed.
SELECT pg_create_logical_replication_slot('feature_capture', 'wal2json');

Then the decoder. It does one thing: read, publish, confirm.

python 
import asyncio
import json
from datetime import datetime, UTC

import psycopg
from psycopg.rows import dict_row
from prometheus_client import Counter, Gauge
from shapely import wkb
from shapely.geometry import mapping

DECODED = Counter("wal_changes_decoded_total", "WAL changes decoded", ("action",))
SLOT_LAG = Gauge("wal_slot_lag_bytes", "Unconsumed WAL held by the slot")

SLOT = "feature_capture"


def _geometry(hex_ewkb: str | None) -> dict | None:
    """PostGIS emits EWKB hex; shapely reads it and drops the SRID prefix."""
    if hex_ewkb is None:
        return None
    return mapping(wkb.loads(bytes.fromhex(hex_ewkb)))


def to_event(change: dict) -> dict:
    """One WAL change -> one feature-change event.

    Both geometries are carried. A consumer needs the old one to tell a
    move from an attribute edit, and to invalidate the tiles the feature
    used to cover as well as the ones it covers now.
    """
    cols = dict(zip(change["columnnames"], change["columnvalues"]))
    old = dict(zip(change.get("oldkeys", {}).get("keynames", []),
                   change.get("oldkeys", {}).get("keyvalues", [])))

    return {
        "schema_version": 2,
        "action": change["kind"],                     # insert | update | delete
        "feature_id": str(cols.get("id") or old.get("id")),
        "occurred_at": datetime.now(UTC).isoformat(),
        "crs": "EPSG:4326",
        "geometry": _geometry(cols.get("geom")),
        "previous_geometry": _geometry(old.get("geom")),
    }


async def run(dsn: str, publish) -> None:
    """Stream the slot, publish each change, confirm only when durable."""
    async with await psycopg.AsyncConnection.connect(
        dsn, autocommit=True, row_factory=dict_row
    ) as conn:
        cur = conn.cursor()
        await cur.execute(
            "SELECT lsn, data FROM pg_logical_slot_get_changes(%s, NULL, NULL)",
            (SLOT,),
        )
        async for row in cur:
            for change in json.loads(row["data"])["change"]:
                event = to_event(change)
                # Publish BEFORE confirming. Confirming first means a crash
                # here loses the change permanently — the WAL is already gone.
                await publish(event)
                DECODED.labels(action=event["action"]).inc()


async def watch_slot_lag(dsn: str) -> None:
    """The alert that has to exist before the slot does."""
    async with await psycopg.AsyncConnection.connect(dsn) as conn:
        while True:
            row = await (await conn.execute(
                """
                SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
                FROM pg_replication_slots WHERE slot_name = %s
                """,
                (SLOT,),
            )).fetchone()
            SLOT_LAG.set(row[0] or 0)
            await asyncio.sleep(15)

Publishing before confirming gives at-least-once delivery: a crash between the two replays the change. The reverse order gives at-most-once and silent loss, because once the slot has confirmed a position the WAL behind it is recyclable and the change is unrecoverable.

The failure that takes the database with it Write-ahead log usage is plotted across a weekend. Under normal operation the decoder confirms its position continuously and PostgreSQL recycles segments behind it, so usage stays flat at a few gigabytes regardless of write volume. The decoder crashes on Friday evening. From that moment the slot stops confirming, and because PostgreSQL may not recycle any segment a slot has not confirmed, usage climbs at the rate the database generates log — steadily, without any error being raised, because nothing is wrong from the database's point of view. By Sunday the data volume is full, and a full data volume stops every writer, including applications that have nothing to do with the feature stream. The alert that prevents this is a threshold on the byte distance between the current log position and the slot's confirmed position, and it needs to exist before the slot does, because the window between the crash and the outage is measured in hours and nobody is looking at a dashboard for a service that has already stopped emitting metrics. WAL volume on disk · decoder crashes Friday 18:00 data volume full decoder crashes · nothing raises flat — segments recycled behind the confirmed position climbing at the rate the database writes log Fri Fri 18:00 Sat Sun A full data volume stops every writer, including applications with nothing to do with the feature stream. Alert on the byte distance between the current log position and the slot's confirmed one — before the slot is created, not after the first incident.
Figure 2. Nothing errors while this is happening. The database is behaving exactly as documented, which is why it needs an external alert rather than a log line.

Parameter reference

Name Type Spatial constraint Default
REPLICA IDENTITY table setting FULL to carry the old geometry; without it, moves are undetectable DEFAULT
Output plugin str wal2json for readability, pgoutput for lower overhead on large geometries wal2json
max_slot_wal_keep_size size Caps the damage: the slot is invalidated rather than the disk filling -1 (unlimited)
wal_level str Must be logical; changing it needs a restart replica
Slot lag alert bytes Well below free disk; page, do not ticket —
Publication scope table list Feature tables only — a whole-database publication decodes everything —

Gotchas and spatial edge cases

  1. REPLICA IDENTITY FULL multiplies WAL volume for geometry tables. Every update now writes the entire old row into the log, and for a table of large multipolygons that can be tens of kilobytes per edit. Consider an index-backed replica identity covering only the primary key and geometry column, which gets the old geometry without the other columns.

  2. Set max_slot_wal_keep_size even with an alert. It converts the worst case from “the database stops” to “the slot is invalidated and change capture needs a resync”. The second is a bad afternoon; the first is an outage for every application on that instance.

  3. A bulk import produces one change record per row. A shapefile load of two hundred thousand features arrives as two hundred thousand events, all at once, all large. This is where the debouncing in Debouncing Rapid Feature Edits and the backpressure in Backpressure & Flow Control for Spatial Consumers stop being optional.

  4. PostGIS emits EWKB with an embedded SRID, and shapely drops it. The mapping() output has no CRS at all, so the event must state one explicitly. If the table holds mixed SRIDs, read the SRID from the EWKB prefix and normalise as CRS Normalization Strategies describes rather than assuming the column’s declared type.

  5. A TRUNCATE is not a stream of deletes. It arrives as a single truncate message, so a consumer maintaining a derived spatial index will keep every feature unless it handles that message type explicitly. The same applies to DROP — the publication simply stops producing.

  6. Slots do not survive a failover to a physical replica in older PostgreSQL versions. After promotion the slot does not exist and the decoder reconnects to nothing, silently capturing zero changes. Verify slot existence as part of the health check, not just connectivity.

A bulk import is two hundred thousand events, all at once A shapefile load of two hundred thousand parcels is applied to the features table in a single transaction. Logical replication is faithful and records one change per row, so the decoder receives two hundred thousand change records the moment the transaction commits, each carrying a full geometry and — under REPLICA IDENTITY FULL — the old row as well. The publish rate the decoder needs to sustain for the next several minutes is orders of magnitude above its steady state, the broker sees a spike it was not provisioned for, and every downstream consumer meets a backlog of large payloads at once. Nothing is wrong: this is what faithful change capture does with a bulk write. What it means is that the decoder cannot be sized for the steady state alone, and that the debouncing and backpressure stages downstream stop being optional the day someone loads a dataset. one shapefile load · 200 000 parcels · one transaction COMMIT one write, from the app's view 200 000 WAL change records each with a geometry, and its old row decoder publish rate, for several minutes orders of magnitude above steady state steady state — a few edits a second the same channel, during the load Nothing is wrong — this is what faithful change capture does with a bulk write. It means the decoder cannot be sized for the steady state, and that debouncing and backpressure downstream stop being optional the day somebody loads a dataset. The slot absorbs it safely; the consumers are what has to be provisioned for it.
Figure 3. The transaction is one write to the application and two hundred thousand events to everything downstream. That asymmetry is the main operational consequence of capturing at the log rather than in the application.

Verification

python 
import json
import pytest


UPDATE_CHANGE = {
    "kind": "update",
    "columnnames": ["id", "name", "geom"],
    "columnvalues": [4471, "Depot B",
                     "0101000020E610000068B3EA73B5CD2A400E4FAF9465464A40"],
    "oldkeys": {"keynames": ["id", "geom"],
                "keyvalues": [4471,
                              "0101000020E6100000E8D9ACFA5CCD2A40FA7E6ABC74464A40"]},
}


def test_update_carries_both_geometries():
    """Without REPLICA IDENTITY FULL this test fails, which is the point."""
    event = to_event(UPDATE_CHANGE)
    assert event["geometry"] is not None
    assert event["previous_geometry"] is not None
    assert event["geometry"] != event["previous_geometry"]


def test_delete_has_a_feature_id_from_oldkeys():
    """On a delete, every value the consumer needs is in oldkeys."""
    change = {"kind": "delete", "columnnames": [], "columnvalues": [],
              "oldkeys": {"keynames": ["id"], "keyvalues": [4471]}}
    assert to_event(change)["feature_id"] == "4471"
    assert to_event(change)["geometry"] is None


@pytest.mark.integration
async def test_slot_lag_returns_to_zero(dsn, publish):
    """The property the alert is written against."""
    await write_one_feature(dsn)
    await run(dsn, publish)
    assert await slot_lag_bytes(dsn) < 1024

The first test is the one that fails loudly if someone resets REPLICA IDENTITY while tuning WAL volume — a change that looks like pure storage optimisation and silently removes the pipeline’s ability to detect that a feature moved.