Dead-Letter Queues for Failed Geotasks

In short: a dead-letter queue is durable storage for work that exhausted its retries, holding enough context to reproduce the failure and re-run it later. For geospatial work “enough context” is unusually large — the geometry, its CRS, the nodata value, the tile index and the exact parameters — because a spatial failure is almost never reproducible from a stack trace alone.

Most pipelines fail the same way: a handful of records out of millions cannot be processed, the task raises, the orchestrator marks the run failed, and the successful 99.97% is discarded along with the failure. A dead-letter queue changes the shape of that outcome. The batch completes, the failures are captured with their full context, and someone triages them on Monday against a table rather than by re-reading yesterday’s logs. In spatial pipelines the payoff is larger than usual, because the failures cluster on exactly the interesting data — the antimeridian-crossing polygon, the tile at the edge of the projection’s valid area, the one municipality whose extract uses a deprecated EPSG code.

Prerequisites & Architecture Baseline#

• PostgreSQL 14+ with PostGIS 3.3+, or an object store plus a metadata table — the queue must outlive the flow run that wrote it
• A retry policy that terminates: a task that retries forever never dead-letters, it just occupies a worker
• An idempotency key per unit of work, stored with the dead letter so a re-drive re-enters at the same identity
• geoalchemy2 0.14+ or psycopg 3.1+ for writing geometry with an explicit SRID
• A metric and an alert on queue depth — a dead-letter queue nobody watches is a delete with extra steps
• A written retention rule: how long entries live, and who is expected to look at them

Core Principles#

1. Dead-letter the payload, not the exception. A stored traceback tells you what broke; a stored payload lets you re-run it. The row should carry everything the task consumed — geometry in a known CRS, tile index, source URI, parameters — so that re-driving is a pure function of the row. If reproducing a failure requires finding the original source file, the queue has not done its job.

2. Record the CRS explicitly, next to the geometry. A geometry column with a declared SRID is the minimum; a separate src_crs text column capturing what the source claimed is better, because the most common spatial failure is precisely a disagreement between the two. Storing only the reprojected geometry destroys the evidence of the bug you are trying to triage.

3. Classify at write time. A single error_text column turns triage into grep. Add a coarse failure_class — invalid_geometry, crs_mismatch, upstream_timeout, oom, unknown — assigned by the task that dead-letters, and the queue becomes groupable. Ninety per cent of entries usually collapse into two or three classes, and that shape is the actionable information.

4. Keep the entry small enough to store and large enough to replay. A failed raster tile should not put 400 MB of pixels in a database row: store the source URI and the window, not the array. A failed vector feature is small enough to store whole, and storing it whole is what makes replay trivial. The rule is to store the inputs at their smallest faithful representation.

5. Re-driving is a first-class flow, not a manual script. The queue is only valuable if there is a routine, tested path back into the pipeline. That path must respect idempotency, must mark entries as re-driven rather than deleting them, and must itself be able to fail and dead-letter — see reprocessing dead-letter geotasks safely.

6. The queue is where a pipeline’s unknown unknowns accumulate. Every failure class you have thought about already has a handler; what lands in the queue is, by construction, what you did not anticipate. That makes the unknown bucket the most valuable few rows in the system — each one is either a new classification rule or a bug. Reading it weekly is a small habit that repays itself, because the alternative is discovering the same failure mode six months later as an incident.

7. Depth is the health signal, not the error count. A queue that gains two entries a day and is drained weekly is healthy. A queue whose depth grows monotonically is a pipeline quietly failing at a constant rate, and it will look fine on every dashboard that only measures completed runs. Alerting on dead-letter queue growth covers what to alert on and what to ignore.

There is a second, less obvious benefit to that split. Once failures stop failing the run, the pipeline’s success metric stops being a proxy for data completeness and starts being a proxy for pipeline health — and those are different things that teams routinely conflate. A run that completes with eleven dead letters is a healthy pipeline processing slightly broken data. A run that completes with zero dead letters and 400 fewer tiles than yesterday is a broken pipeline with a clean dashboard. Separating the two measurements is most of the value of building the queue at all, and it is why the depth metric belongs on the same dashboard as the run-duration metric rather than in a separate corner nobody visits.

Production Implementation#

The helper below wraps a task body, captures whatever failed with its spatial context, and classifies the cause. It is deliberately a decorator rather than a base class, because the thing you want to reuse is the capture, not an inheritance hierarchy.

from __future__ import annotations

import functools
import json
import traceback
from typing import Any, Callable, Optional

import psycopg
import shapely
from pyproj import CRS
from shapely.errors import GEOSException


class CrsMismatchError(ValueError):
    """The declared CRS and the observed coordinate range disagree."""


def classify(exc: BaseException) -> str:
    """Coarse, groupable failure class. Deliberately few values."""
    if isinstance(exc, GEOSException):
        return "invalid_geometry"
    if isinstance(exc, (CrsMismatchError,)):
        return "crs_mismatch"
    if isinstance(exc, MemoryError):
        return "oom"
    if isinstance(exc, TimeoutError):
        return "upstream_timeout"
    return "unknown"


INSERT_DEAD_LETTER = """
INSERT INTO geotask_dlq
       (work_key, task_name, geom, src_crs, tile_z, tile_x, tile_y,
        params, failure_class, error_text, traceback, failed_at)
VALUES (%(work_key)s, %(task_name)s,
        ST_SetSRID(ST_GeomFromWKB(%(wkb)s), %(srid)s),
        %(src_crs)s, %(z)s, %(x)s, %(y)s,
        %(params)s::jsonb, %(failure_class)s, %(error_text)s, %(traceback)s, now())
ON CONFLICT (work_key) DO UPDATE
   SET attempts   = geotask_dlq.attempts + 1,
       error_text = EXCLUDED.error_text,
       failed_at  = now()
"""


def dead_letter_on_failure(conn_factory: Callable[[], psycopg.Connection]):
    """Capture the payload of a task that exhausted its retries."""

    def decorate(fn: Callable[..., Any]) -> Callable[..., Any]:
        @functools.wraps(fn)
        def wrapper(work, *args, **kwargs):
            try:
                return fn(work, *args, **kwargs)
            except Exception as exc:
                geom = getattr(work, "geometry", None)
                srid = CRS.from_user_input(work.src_crs).to_epsg() or 0
                with conn_factory() as conn, conn.cursor() as cur:
                    cur.execute(
                        INSERT_DEAD_LETTER,
                        {
                            "work_key": work.key,
                            "task_name": fn.__name__,
                            # Store WKB, not WKT: it round-trips exactly, including
                            # the vertex values that caused the failure.
                            "wkb": shapely.to_wkb(geom) if geom is not None else None,
                            "srid": srid,
                            "src_crs": work.src_crs,
                            "z": work.tile_z, "x": work.tile_x, "y": work.tile_y,
                            "params": json.dumps(work.params, sort_keys=True),
                            "failure_class": classify(exc),
                            "error_text": str(exc)[:2000],
                            "traceback": "".join(traceback.format_exception(exc))[:8000],
                        },
                    )
                raise      # the orchestrator still sees the failure

        return wrapper

    return decorate

The table is the durable half of the design, and its shape decides what triage is possible:

CREATE TABLE geotask_dlq (
    work_key      text PRIMARY KEY,           -- same identity the pipeline uses
    task_name     text        NOT NULL,
    geom          geometry,                   -- SRID varies deliberately: keep the source's
    src_crs       text,                       -- what the source CLAIMED, as a string
    tile_z        smallint, tile_x integer, tile_y integer,
    params        jsonb       NOT NULL,
    failure_class text        NOT NULL,
    error_text    text,
    traceback     text,
    attempts      integer     NOT NULL DEFAULT 1,
    failed_at     timestamptz NOT NULL,
    redriven_at   timestamptz,                -- NULL until it goes back through
    resolved_at   timestamptz
);

CREATE INDEX geotask_dlq_open ON geotask_dlq (failure_class, failed_at)
    WHERE resolved_at IS NULL;

Step-by-Step Walkthrough#

1. Let the retry policy finish first. The decorator sits outside the retry wrapper, so it only fires when the budget is spent. Capturing on the first transient failure fills the queue with entries that would have succeeded on their own.
2. Serialise the geometry as WKB, with its original SRID. WKB round-trips exactly; WKT loses precision at the digits where floating-point geometry bugs live. Keeping the source SRID rather than the pipeline’s target SRID preserves the evidence for a CRS mismatch.
3. Store parameters as sorted JSONB. Sorted so two identical payloads compare equal; JSONB so triage can query into them — params->>'resampling' = 'cubic' is the question you will want to ask on the day a resampling change breaks 40 tiles.
4. Classify the exception into a small vocabulary. Five classes is plenty. The purpose is to make GROUP BY failure_class informative, and a vocabulary with thirty values is a free-text column wearing a schema.
5. Upsert on the work key. The same tile failing on three consecutive nights should be one row with attempts = 3, not three rows. This is what makes “how many distinct things are broken?” answerable.
6. Re-raise. The queue captures the failure; it does not swallow it. The orchestrator still needs to see the task fail so its own metrics, alerting and run state stay honest.
7. Mark, never delete, on re-drive. redriven_at and resolved_at preserve the history that tells you whether a class of failure is genuinely fixed or merely re-driven repeatedly.

Edge Cases & Failure Recovery#

The failure is the geometry, and the geometry will not store. A self-intersecting polygon may violate a CHECK (ST_IsValid(geom)) on the queue table itself, so the capture fails and the payload is lost — the worst possible outcome. Never validate the dead-letter table’s geometry column. It exists to hold exactly the geometries the rest of the schema rejects.

The payload is too large to store. A failed mosaic window covering 4 GB of source pixels cannot live in a row. Store the source URIs and the window bounds, and accept that the re-drive re-reads from source. The test for “small enough” is whether the row plus its payload stays under a megabyte or so; past that, store a reference.

The database is the thing that failed. If the queue lives in the same PostgreSQL instance that just went down, the capture fails too. For pipelines whose main dependency is PostGIS, write dead letters to object storage as newline-delimited JSON and load them into the table asynchronously. The queue’s durability must not share a fate with the pipeline’s.

A poison payload that fails the re-drive every time. Some entries will never succeed — a geometry with NaN coordinates, a source that has been withdrawn. Cap attempts, and move entries past the cap into a resolved_at state with a reason of abandoned. An uncapped re-drive loop is a slow denial of service against your own pipeline.

A schema change between capture and re-drive. Entries can sit in the queue for weeks, during which the task they came from may gain a parameter or change a default. A re-drive that constructs the work object from params will then either fail on a missing key or, worse, silently pick up a new default that was not in force when the entry was written. Store the code version alongside the payload and have the re-drive refuse entries it cannot faithfully reconstruct, rather than guessing.

Sensitive coordinates in a widely readable table. A dead-letter queue accumulates exactly the records that got stuck, which in some domains are the sensitive ones — a protected site, a private address. Apply the same controls as the source table, and see masking sensitive coordinates in task logs for the logging half of the same exposure.

Configuration Reference#

Retention deserves a decision rather than a default because the queue’s rows are small but its geometries are not. A queue that captures failed vector features at a steady 200 a day is a few megabytes a year; one that captures failed mosaic windows with their metadata can be far larger. Archive resolved entries to the warehouse on a schedule, keep open entries indefinitely, and make the archive job part of the same flow that reports depth — a retention job nobody can see is how a “durable” queue quietly becomes lossy.

Frequently Asked Questions#

Related#

• Storing failed geometries in a PostGIS dead-letter queue — the schema and write path in detail
• Reprocessing dead-letter geotasks safely — the re-drive flow
• Alerting on dead-letter queue growth — what to page on
• Idempotency keys in spatial ETL — the identity a re-drive re-enters at
• Exponential backoff for API rate limits — the retry budget that must run out first

← Resilience & Failure Handling for GIS Pipelines