State Management in Geospatial Flows

In short: a spatial pipeline has three kinds of state — what has been built, how far a long job got, and how current the output is — and they need different homes. Conflating them is why a re-run either redoes everything or skips work it should have redone. Keep the ledger, the checkpoint and the watermark separate, derive each from content rather than from time, and every recovery question becomes a query rather than an investigation.

The orchestrator’s own state is about task runs: this one succeeded, that one is retrying, the flow finished at 03:14. That is exactly the wrong granularity for a pipeline whose unit of work is a tile or a municipality, because a task run is ephemeral and a tile is not. When the question is “does tile 12/2147/1398 need rebuilding”, the orchestrator can only answer “a task with that parameter succeeded in a run last Tuesday”, which is not the same thing at all — the source may have changed since, or the recipe, or the tile may have been rebuilt by a manual intervention the orchestrator never saw.

So spatial pipelines end up maintaining state of their own, and the mistake that follows is to maintain one blob of it. A single last_run.json holding a timestamp, a partial file list and a set of completed identifiers looks tidy and fails in three directions at once: it cannot be read concurrently by a fan-out, it cannot answer a coverage question, and it makes a checkpoint and a completeness record share a lifetime when they should not. The three kinds of state have genuinely different shapes, and separating them costs one afternoon and saves the same afternoon every quarter thereafter.

There is a diagnostic worth applying to any state a pipeline keeps: ask what breaks if it is deleted. If the answer is “nothing, the next run is slower”, it is a checkpoint. If it is “the pipeline rebuilds work it did not need to”, it is a ledger. If it is “the pipeline re-reads a source from the beginning of time”, it is a watermark. Anything whose deletion produces two of those answers is holding two kinds of state in one place, and it will eventually be split under pressure at an inconvenient hour.

Prerequisites & Architecture Baseline#

• A canonical unit of work with a stable name — tile index, scene id, municipality code
• A content digest available for every source, so "changed" is a fact rather than a guess
• A recipe version bumped whenever the processing rule changes
• Somewhere transactional to put the ledger: PostGIS is usually already there
• Object storage for checkpoints, with a lifecycle rule so partial work expires
• Agreement on what "current" means for the layer, expressed as a maximum age

Core Principles#

1. The ledger records what exists, not what ran. One row per unit of work, keyed by the unit and the inputs it was built from. It survives cache eviction, it answers coverage questions directly, and it is the only state a re-run consults before deciding to skip.

2. A checkpoint is scratch, not truth. It exists so a four-hour mosaic does not restart from zero, and it should be deletable at any moment without affecting correctness. If deleting a checkpoint loses information, it has quietly become a ledger.

3. The watermark is about the source, not about the run. “We have ingested everything published up to 2026-08-06T22:00Z” is a statement about the publisher’s timeline. Storing the run’s own start time instead is how a pipeline that ran late skips a delivery permanently.

4. State is keyed by content, not by time. A timestamp answers “when” and the question is always “what from”. Two runs an hour apart over an unchanged source should produce identical keys and do no work.

5. Writers must be safe to run twice. A fan-out of four hundred tasks writing the same ledger will collide; an upsert keyed on the unit makes the collision a no-op rather than a duplicate.

6. Every piece of state must be reconstructible. If the ledger is lost, a scan of the output store should rebuild it. State that cannot be rebuilt is a single point of failure wearing a database’s clothing.

Production Implementation#

The ledger is a table, not a file. PostGIS is the natural home because the pipeline is already connected to it and because the unit of work usually has a geometry worth indexing.

CREATE TABLE tile_ledger (
    layer          text        NOT NULL,
    z              smallint    NOT NULL,
    x              integer     NOT NULL,
    y              integer     NOT NULL,
    work_key       text        NOT NULL,   -- sha256(tile | source digest | recipe)
    source_digest  text        NOT NULL,
    recipe_version text        NOT NULL,
    built_at       timestamptz NOT NULL DEFAULT now(),
    valid_px_pct   real,
    geom           geometry(Polygon, 3857) NOT NULL,
    PRIMARY KEY (layer, z, x, y)
);

CREATE INDEX tile_ledger_geom_idx ON tile_ledger USING gist (geom);
CREATE INDEX tile_ledger_key_idx  ON tile_ledger (work_key);

Writing to it is an upsert, so a retried task and a manual re-run land in the same place:

UPSERT = """
INSERT INTO tile_ledger
    (layer, z, x, y, work_key, source_digest, recipe_version, valid_px_pct, geom)
VALUES (%(layer)s, %(z)s, %(x)s, %(y)s, %(key)s, %(digest)s, %(recipe)s,
        %(valid_pct)s, ST_TileEnvelope(%(z)s, %(x)s, %(y)s))
ON CONFLICT (layer, z, x, y) DO UPDATE
SET work_key       = EXCLUDED.work_key,
    source_digest  = EXCLUDED.source_digest,
    recipe_version = EXCLUDED.recipe_version,
    valid_px_pct   = EXCLUDED.valid_px_pct,
    built_at       = now()
-- Only when something actually changed: an unchanged rebuild must not move built_at,
-- or the freshness figure becomes a record of when the pipeline last ran.
WHERE tile_ledger.work_key IS DISTINCT FROM EXCLUDED.work_key;
"""


def needs_build(cur, layer: str, tile: Tile, key: str) -> bool:
    cur.execute(
        "SELECT 1 FROM tile_ledger WHERE layer=%s AND z=%s AND x=%s AND y=%s AND work_key=%s",
        (layer, tile.z, tile.x, tile.y, key),
    )
    return cur.fetchone() is None

The WHERE work_key IS DISTINCT FROM clause on the upsert deserves a note, because it is the difference between a built_at column that means something and one that does not. Without it, a nightly run that rebuilds nothing still touches every row, and built_at becomes a record of when the pipeline last ran rather than when the tile last changed. Every freshness question then answers “everything is fresh”, including for tiles whose source has not been republished in two years — which is exactly the situation a freshness metric exists to reveal.

The watermark is the smallest of the three and the one most often got wrong, so it is worth writing out. It is a statement about the publisher’s timeline, advanced only when everything derived from that position is durable:

ADVANCE = """
INSERT INTO source_watermark (stream, position, advanced_at)
VALUES (%(stream)s, %(position)s, now())
ON CONFLICT (stream) DO UPDATE
SET position = GREATEST(source_watermark.position, EXCLUDED.position),
    advanced_at = now();
"""


def advance_watermark(conn, stream: str, position: datetime, expected_units: int) -> None:
    """Commit point for the run. Never called before the ledger is complete."""
    with conn.cursor() as cur:
        # An advisory lock, so two overlapping runs cannot both decide they are done.
        cur.execute("SELECT pg_advisory_xact_lock(hashtext(%s))", (stream,))
        cur.execute(
            "SELECT count(*) FROM tile_ledger WHERE layer = %s AND source_digest = %s",
            (stream, digest_for(stream, position)),
        )
        built, = cur.fetchone()
        if built < expected_units:
            raise RuntimeError(
                f"{built}/{expected_units} units in the ledger — not advancing {stream}"
            )
        cur.execute(ADVANCE, {"stream": stream, "position": position})

GREATEST in the upsert is not decoration. Watermark advances arrive out of order whenever a retried run finishes after a later one — a re-drive of Tuesday’s delivery completing on Thursday afternoon is entirely normal — and a plain assignment would walk the watermark backwards, causing the pipeline to re-ingest two days of published data it already holds. Making the column monotonic by construction removes a class of bug that is otherwise found only by noticing a suspiciously long run.

The count check before advancing is the other half. A watermark is a promise that everything up to that position has been processed, and a promise made on the basis of “the flow reached its last task” is worth very little — the flow reaches its last task whether four hundred and twelve units succeeded or three hundred did. Verifying the ledger before committing costs one query and converts the watermark from an assertion about control flow into an assertion about data.

Step-by-Step Walkthrough#

1. Compute the work key for each unit from the tile index, the source digest and the recipe version. Nothing else belongs in it.
2. Query the ledger for units whose stored key differs or is absent. That set is the work.
3. Write a checkpoint per unit as it completes, under a prefix scoped to the run, so a resumed run can skip finished units without consulting the ledger.
4. Upsert the ledger as each unit’s output is durably written — after the object store confirms, never before.
5. Advance the watermark only once every unit derived from that source position is in the ledger. This is the commit point for the run.
6. Expire checkpoints on a lifecycle rule. If anything breaks when they vanish, something has been miscategorised.

Edge Cases & Failure Recovery#

The ledger is lost. Scan the output store, recompute the key from each object’s stored metadata, and reinsert. This is why the source digest and recipe version belong in the object’s metadata as well as the ledger row — the ledger becomes an index over the outputs rather than the only record of them.

A run dies between the object write and the ledger upsert. The unit is rebuilt on the next run, which is harmless because the build is idempotent by key. Ordering it the other way round is not harmless: a ledger row for an object that does not exist makes a missing tile invisible.

A source is corrected retroactively. Its digest changes, so every key derived from it changes, so the affected units come back into the work set automatically. No manual invalidation list is needed, which is the main practical argument for content-keyed state.

Two runs overlap. The upsert makes concurrent writers safe, and the checkpoint prefix keeps their scratch apart. What is not safe is two runs advancing the same watermark, so that step belongs behind an advisory lock.

A unit is deleted upstream. A municipality is merged, a map sheet is withdrawn. The ledger row still exists and its output still sits in the store, so the layer keeps serving ground that no longer has a source. Reconciling the ledger against the current manifest — rather than only inserting into it — is what catches this, and it is a nightly query rather than a piece of machinery.

The recipe changes for one layer only. Bump that layer’s recipe version. Because the key includes it, exactly that layer rebuilds and the others do not — the granularity of invalidation follows the granularity of the version string, which is a good reason not to have one global version.

Configuration Reference#

Frequently Asked Questions#

Related#

• Checkpointing large raster mosaics — the disposable middle column, in practice
• Managing state for incremental shapefile updates — watermarks against a vector source
• Storing flow state in PostGIS versus object storage — choosing the home
• Resuming a failed mosaic from its last checkpoint — the recovery path end to end
• Idempotency keys in spatial ETL — making the writes safe to repeat
• Skipping tiles with no new source data — what the ledger is consulted for

← Geospatial Orchestration Architecture Fundamentals