Tracking Dirty Tile Extents with PostGIS Triggers

Part of the Incremental Data Processing guide.

Operative rule: on a geometry update the trigger must record both the old and the new extent — recording only the new one leaves the feature’s ghost in every tile covering where it used to be.

Letting the Database Do the Bookkeeping

Computing what changed by comparing exports is expensive and approximate. The database already knows: it saw the write. A small trigger that appends the affected bounding box to a queue table turns change detection into a by-product of the transaction, at a cost of a few microseconds per row.

The queue is deliberately dumb. It records rectangles, not tile addresses, so the database never needs to know your zoom range or tiling scheme, and a change to either does not require a migration.

From a row write to a purge list A row write fires a trigger that appends one or two bounding boxes to a dirty-extent table inside the same transaction. A periodic worker claims the pending rows, unions overlapping extents, expands them into tile addresses across the published zoom range, regenerates those tiles and purges exactly them. The database records what changed; the worker decides what that means row write insert · update · delete trigger → dirty_extent 1 box, or 2 if geometry moved worker unions claims a batch, merges tiles + purge exact list Why the trigger records boxes and not tile addresses tile maths in a trigger runs inside every write transaction and pins the database to one zoom range The purge list falls out of the same union — no separate bookkeeping, and no guessing at the edge. This is the list the CDN purge step has always wanted and usually approximates.

Production-Ready Implementation

CREATE TABLE IF NOT EXISTS dirty_extent (
    id          bigserial PRIMARY KEY,
    layer       text        NOT NULL,
    extent      geometry(Polygon, 4326) NOT NULL,
    reason      text        NOT NULL,       -- insert | update_geom | update_attr | delete
    created_at  timestamptz NOT NULL DEFAULT now(),
    claimed_at  timestamptz
);

CREATE INDEX IF NOT EXISTS dirty_extent_unclaimed
    ON dirty_extent (layer, created_at) WHERE claimed_at IS NULL;

CREATE OR REPLACE FUNCTION mark_dirty() RETURNS trigger AS $$
BEGIN
    IF (TG_OP = 'INSERT') THEN
        INSERT INTO dirty_extent (layer, extent, reason)
        VALUES (TG_ARGV[0], ST_Envelope(NEW.geom::geometry), 'insert');

    ELSIF (TG_OP = 'DELETE') THEN
        INSERT INTO dirty_extent (layer, extent, reason)
        VALUES (TG_ARGV[0], ST_Envelope(OLD.geom::geometry), 'delete');

    ELSIF (TG_OP = 'UPDATE') THEN
        IF NOT ST_Equals(OLD.geom, NEW.geom) THEN
            -- TWO rows: the feature left one place and arrived at another.
            INSERT INTO dirty_extent (layer, extent, reason)
            VALUES (TG_ARGV[0], ST_Envelope(OLD.geom::geometry), 'update_geom'),
                   (TG_ARGV[0], ST_Envelope(NEW.geom::geometry), 'update_geom');
        ELSIF (OLD.* IS DISTINCT FROM NEW.*) THEN
            INSERT INTO dirty_extent (layer, extent, reason)
            VALUES (TG_ARGV[0], ST_Envelope(NEW.geom::geometry), 'update_attr');
        END IF;
    END IF;
    RETURN NULL;                              -- AFTER trigger: return value unused
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER incidents_mark_dirty
    AFTER INSERT OR UPDATE OR DELETE ON incidents
    FOR EACH ROW EXECUTE FUNCTION mark_dirty('incidents');
def claim_dirty_extents(conn, layer: str, limit: int = 5000) -> list[tuple]:
    """Claim a batch atomically, union overlapping boxes, return merged extents."""
    with conn.cursor() as cur:
        cur.execute(
            """
            WITH claimed AS (
                UPDATE dirty_extent SET claimed_at = now()
                WHERE id IN (
                    SELECT id FROM dirty_extent
                    WHERE layer = %s AND claimed_at IS NULL
                    ORDER BY created_at
                    LIMIT %s
                    FOR UPDATE SKIP LOCKED
                )
                RETURNING extent
            )
            SELECT ST_XMin(g), ST_YMin(g), ST_XMax(g), ST_YMax(g)
            FROM (
                SELECT (ST_Dump(ST_UnaryUnion(ST_Collect(extent)))).geom AS g
                FROM claimed
            ) parts;
            """,
            (layer, limit),
        )
        return cur.fetchall()

FOR UPDATE SKIP LOCKED is what lets two workers run without processing the same extent twice, and claiming rather than deleting means a worker that crashes leaves the claim visible for a sweeper to reset rather than losing the work silently.

Expanding Extents into Tile Addresses

One extent, expanded across the zoom range A single small bounding box covers one tile at zoom eight, about four at zoom ten, about sixteen at zoom twelve and about sixty-four at zoom fourteen. The deepest two levels dominate the regeneration and purge cost, exactly as they dominate the full pyramid. One changed feature, expanded across the published zoom range z8 1 tile z10 ≈ 4 tiles z12 ≈ 16 tiles z14 ≈ 64 tiles the deepest two levels account for most of the work — cap the depth you regenerate per pass if it grows

Keeping the Queue Healthy

Three rules that keep the queue from becoming the problem Cap the batch size so one bulk import does not produce an unbounded regeneration. Sweep claims older than a timeout back to unclaimed so a crashed worker loses nothing. When the pending count is very large, abandon incremental processing for that pass and rebuild fully. Three rules, all learned the same way cap the batch a bulk import must not turn into one unbounded regeneration sweep stale claims reset claimed_at older than a timeout — a crashed worker loses nothing fall back to a full rebuild past a threshold, rebuilding everything is faster than tracking it

The design goal throughout is that the database records facts and the worker makes decisions. Everything that could change — the zoom range, the tiling tool, the purge mechanism, the batch size — lives on the worker side, so none of it requires a schema migration to adjust.

Verification Steps

  • Move one feature and confirm two rows appear, with the old and new extents.
  • Change only an attribute and confirm exactly one row appears.
  • Run two workers concurrently and confirm no extent is processed twice.
  • Kill a worker mid-batch and confirm the sweeper returns its claims to the queue.
  • Compare the generated purge list against the tiles that actually changed on disk.

Common Errors & Fixes

Ghost features remain at old locations

The update branch records only NEW.geom. Record both, as the trigger above does.

The queue grows faster than the worker drains it

The batch cap is too small or the interval too long. Raise both, and add the full-rebuild fallback so a bulk import cannot create an unbounded backlog.

Writes to the table have become slower

The trigger is doing too much — usually tile arithmetic or a spatial join. Keep it to an envelope insert; everything else belongs in the worker.

Two workers regenerate the same tiles

Claims are not atomic. Use FOR UPDATE SKIP LOCKED inside the same statement that sets claimed_at.

What the Trigger Should Never Do

A trigger runs inside the writing transaction, which makes it the most expensive place in the system to put anything slow. Every millisecond it adds is paid by whatever wrote the row — an application request, a bulk import, a replication apply — and the cost is invisible until someone profiles a write path and finds it.

Three things in particular belong in the worker rather than the trigger. Network calls of any kind: an HTTP request to a purge API from inside a trigger ties a database write to the availability of an external service, and a slow response holds a transaction open. Tile arithmetic: expanding an envelope into addresses is cheap in isolation but couples the schema to a zoom range that will change. And any query against another table: a join to enrich the extent turns a constant-time insert into something that depends on the size of the other table.

There is also a correctness argument for keeping the trigger minimal. A trigger that raises inside a transaction fails the write that caused it. A reader updating one row should not have their request rejected because a tile-tracking table had a constraint violation — the map’s bookkeeping must never be able to block the system of record. Keeping the trigger to a single insert into a table with no foreign keys and no unusual constraints makes that failure mode essentially impossible.

For bulk operations, consider bypassing the trigger deliberately. A one-off import of two million rows will produce two million queue entries describing an area you already know is the whole dataset. Disabling the trigger for the duration, then enqueuing one extent covering the import’s bounding box, is both faster and more accurate — and it avoids a backlog that the worker then spends hours draining.

Finally, keep the queue table’s own maintenance in mind. It is a high-churn table: rows are inserted constantly and deleted or archived after processing. Without periodic cleanup and appropriate autovacuum settings it bloats, and a bloated queue slows the very worker that is meant to be draining it.

Gotchas & Edge Cases

  • ST_Equals is a geometric comparison, not a byte comparison: two geometries that differ only in vertex order are equal, which is usually what you want and occasionally not.
  • A geometry column that allows nulls needs an explicit branch, because ST_Envelope(NULL) yields null and inserts a row the worker cannot use.
  • Statement-level triggers are cheaper than row-level ones for bulk updates but lose the per-row extents; if bulk operations dominate, prefer disabling the trigger and enqueuing one extent by hand.
  • Extents recorded in the table’s own CRS must be reprojected before they are turned into tile addresses — a queue in a projected system and a tiler expecting degrees produce silently wrong purge lists.
  • A transaction that rolls back also rolls back its queue rows, which is correct: work that never happened should not be regenerated.
  • Deleting processed rows immediately keeps the table small but loses the audit trail; archiving them for a few days costs little and answers “why was this tile rebuilt?” later.