Alerting on Feature-Level Replication Lag

Connection-level replication lag is the number most platforms alert on, and it is precisely the number that hides a slow layer: a PostGIS follower can report a sub-second WAL apply delay for the instance as a whole while one high-churn parcel layer is minutes behind because its GiST index rebuild is serialising writes. This page shows how to instrument and alert on replication lag per region and per layer instead of per connection, so a single stale feature class surfaces before it corrupts a spatial join downstream. It sits under Monitoring Topology for Multi-Region GIS and within Geospatial Observability Architecture & Fundamentals, and is written for the data engineers, GIS platform admins, and SREs who own cross-region freshness during a partition.

Connection-level lag masks a single slow layer A primary region replicates three layers to a follower region. The instance-level WAL apply lag reads under one second and looks healthy. Broken out per layer, the roads layer is fresh at two seconds, the points-of-interest layer is fresh at four seconds, but the high-churn parcels layer is one hundred and forty seconds behind because its GiST index rebuild serialises applies. A per-layer alert fires on parcels while the connection-level gauge stays green. Primary region WAL sender Connection-level lag 0.8s · looks healthy Follower region WAL apply stream roads · 2s poi · 4s parcels 140s Per-layer alert fires parcels > budget instance gauge stays green → the slow layer is invisible

Why connection-level lag lies

A streaming follower reports lag as a single scalar — the byte distance or time distance between the last WAL record written on the primary and the last one applied on the follower. pg_last_xact_replay_timestamp() gives you an instance-wide figure, and for a workload where every table writes at roughly the same rate it is a fair proxy. Spatial workloads are not that workload. A parcels layer taking a nightly bulk load rewrites millions of rows and forces a GiST index maintenance burst; a slowly-changing administrative-boundary layer writes a handful of features a week. When the follower serialises WAL apply behind the parcels index work, the instance replay timestamp still advances every time any cheap transaction lands, so the scalar reads fresh while the parcels heap is minutes stale.

The consequence is a false-healthy dashboard of exactly the kind Monitoring Topology for Multi-Region GIS warns against: a per-metric threshold that goes green by omission. A spatial join between fresh roads and stale parcels on the follower returns geometrically wrong results with no error, because the query planner has no notion of per-layer freshness. To catch it you have to demote lag from an instance property to a feature-class property, and that means emitting gis.spatial.replication_lag keyed by both region and layer rather than one gauge per replication slot.

The signal to instrument is the age of the newest applied row for a given layer, measured on the follower against the primary’s clock. If every feature carries an updated_at set by the primary in UTC, the follower can compute its own lag per layer without cross-instance clock coordination — it compares the maximum updated_at it has applied against now(), and the difference is the layer’s staleness. This reframes the Tracking Spatial Data Freshness SLAs budget as something replication has to satisfy per layer, not just per pipeline stage.

Emitting per-layer lag from the follower

Run the measurement on the follower, layer by layer, and expose it as a gauge the regional collector scrapes. The query below reads the newest applied updated_at per spatial table and derives an age in seconds; a small exporter loops over the configured layers and records the gauge with the region and layer dimensions attached.

-- Run on the FOLLOWER. Age of the newest applied feature per layer,
-- against the follower's own clock. updated_at is set by the PRIMARY in UTC.
SELECT
    'parcels'                                   AS layer,
    ST_SRID(geom)                               AS srid,
    COUNT(*)                                    AS feature_count,
    MAX(updated_at)                             AS newest_applied,
    EXTRACT(EPOCH FROM now() - MAX(updated_at)) AS lag_seconds
FROM parcels
GROUP BY ST_SRID(geom);
import time
from opentelemetry import metrics
import psycopg

meter = metrics.get_meter("gis.spatial.replication")
_lag_by_layer: dict[tuple[str, str], float] = {}

def _observe_lag(options):
    from opentelemetry.metrics import Observation
    for (region, layer), seconds in _lag_by_layer.items():
        yield Observation(seconds, {"region": region, "layer": layer})

# An observable gauge is the right instrument: lag is a level, not an event.
meter.create_observable_gauge(
    "gis.spatial.replication_lag",
    callbacks=[_observe_lag],
    unit="s",
    description="Age of the newest applied feature per layer on the follower",
)

LAYERS = ["parcels", "roads", "poi", "admin_boundaries"]

def refresh(region: str, dsn: str) -> None:
    with psycopg.connect(dsn) as conn:
        for layer in LAYERS:
            row = conn.execute(
                f"SELECT EXTRACT(EPOCH FROM now() - MAX(updated_at)) "
                f"FROM {layer}"                      # identifier is from a fixed allowlist
            ).fetchone()
            # A layer with no rows returns NULL; treat it as 0, not as stale.
            _lag_by_layer[(region, layer)] = float(row[0] or 0.0)

if __name__ == "__main__":
    while True:
        refresh("region-b", "postgresql://follower.internal/gis")
        time.sleep(15)

Keep the layer list an explicit allowlist rather than reflecting information_schema at runtime — an unbounded layer dimension is exactly the tag-cardinality blowup that bounding spatial metric tag cardinality exists to prevent. Attach region and layer and stop there; do not add per-feature identifiers.

Freshness-window budgets per layer

A single lag threshold across all layers is as blunt as the connection-level scalar it replaces. A parcels layer that feeds tax assessment might tolerate three minutes of staleness on a follower; a real-time incident-overlay layer tolerates ten seconds. Encode a per-layer freshness-window budget and alert on the ratio of observed lag to that budget, so every layer is judged against its own SLA. Let LlayerL_{\text{layer}} be the observed per-layer lag and BlayerB_{\text{layer}} its budget; the normalised burn is

ρlayer=LlayerBlayer,warn at ρ≥0.7, page at ρ≥1.0\rho_{\text{layer}} = \frac{L_{\text{layer}}}{B_{\text{layer}}}, \qquad \text{warn at } \rho \ge 0.7,\ \text{page at } \rho \ge 1.0

Expressing the alert as a dimensionless ratio means one rule covers every layer, and a layer’s severity rises as it eats into its own window rather than against a global constant. Store the budgets next to the layer allowlist so the runtime exporter and the alert rules read the same source, the same discipline the geospatial metric taxonomy for ETL applies to metric names.

PromQL: per-layer thresholds and absence

The Prometheus-flattened series is gis_spatial_replication_lag with region and layer labels. Join it against a recorded budget series so the ratio, not the raw seconds, drives severity, and tier the alerts by who acts.

groups:
  - name: gis_replication_lag
    rules:
      # Publish the per-layer budget as a series so alerts stay declarative.
      - record: gis:replication_budget_seconds
        expr: |
          label_replace(vector(180), "layer", "parcels", "", "")
          or label_replace(vector(10),  "layer", "incident_overlay", "", "")
          or label_replace(vector(60),  "layer", "roads", "", "")

      # WARNING — a layer has burned 70% of its freshness window.
      - alert: LayerReplicationLagWarning
        expr: |
          max by (region, layer) (gis_spatial_replication_lag)
            / on (layer) group_left gis:replication_budget_seconds > 0.7
        for: 2m
        labels: { severity: warning, team: data-engineering }
        annotations:
          summary: "Layer {{ $labels.layer }} lag at 70%+ of budget in {{ $labels.region }}"

      # CRITICAL — a layer is past its freshness window on the follower.
      - alert: LayerReplicationLagBreach
        expr: |
          max by (region, layer) (gis_spatial_replication_lag)
            / on (layer) group_left gis:replication_budget_seconds >= 1.0
        for: 1m
        labels: { severity: critical, team: db-admin }
        annotations:
          summary: "Layer {{ $labels.layer }} past freshness budget in {{ $labels.region }}"
          runbook: "/spatial-incident-response-and-tooling/spatial-pipeline-incident-runbooks/cross-region-replication-lag-runbook/"

      # BLIND — the per-layer gauge stopped reporting; do not read silence as fresh.
      - alert: LayerReplicationLagAbsent
        expr: |
          absent_over_time(gis_spatial_replication_lag{layer="parcels"}[5m])
        for: 5m
        labels: { severity: critical, team: observability-platform }
        annotations:
          summary: "Per-layer replication lag metric absent for parcels — follower may be blind"

The absent_over_time rule matters as much as the threshold rules. If the follower exporter dies, the per-layer gauge simply stops, and a threshold rule evaluates to no data — the same silent blind spot that turns a partition into an undetected outage. Alert on the absence of the layer series explicitly, and page the observability team, not the on-call DBA, because the fault is in the telemetry path, not the database.

When a breach does fire, it links straight into the cross-region replication lag runbook, which walks through isolating whether the delay is WAL-apply serialisation behind a GiST rebuild, a saturated inter-region link, or a long-running VACUUM FULL holding apply. Pair the alert with the trust-boundary rules in best practices for defining trust boundaries in PostGIS so the follower never serves a stale layer across a jurisdiction that expects a bounded freshness guarantee.

Verification

Prove the per-layer signal works by inducing lag on one layer only. Pause apply on the follower for a single table’s worth of churn — the cleanest test is a bulk update to the parcels layer on the primary while a pg_wal_replay_pause() window holds the follower — then confirm the parcels gauge climbs while roads and POI stay flat and the connection-level scalar barely moves.

# On the follower: hold WAL apply, then resume, watching one layer diverge.
psql -h follower.internal -c "SELECT pg_wal_replay_pause();"
# ... bulk-update parcels on the primary here ...
curl -s http://follower-exporter:8889/metrics \
  | grep 'gis_spatial_replication_lag{.*layer="parcels"'
# Expect parcels lag rising while roads/poi stay near zero.
psql -h follower.internal -c "SELECT pg_wal_replay_resume();"

A correct setup shows three things together: the parcels gauge crosses its budget ratio and fires LayerReplicationLagBreach, the roads and POI gauges stay well under their budgets, and the instance-level replay-timestamp lag never trips a naive global alert. If the global scalar fires first, your per-layer budgets are too loose; if nothing fires, the exporter is not keyed on layer.

FAQ

Why not just alert on pg_last_xact_replay_timestamp()?

Because it is an instance-wide figure that advances on any applied transaction, so a single fresh cheap write masks a stale expensive layer. It is a fine coarse guard against total replication stall, but it cannot tell you which feature class is behind, and per-layer staleness is what corrupts a spatial join. Keep it as a backstop and add the per-layer gauge as the primary signal.

Do I need a clock-sync mechanism between primary and follower?

No, if the freshness signal is derived from an updated_at the primary stamps and the follower reads after apply. The follower compares that applied timestamp against its own now(), so both ends of the subtraction use the same clock — the follower’s — and cross-instance skew never enters the calculation. Reserve NTP discipline for the coarser byte-lag view.

How many layers can I key the gauge on before cardinality bites?

Keep it to the layers with genuinely different freshness budgets — typically tens, not thousands. The dimension is layer, an allowlist, not per-feature identity. If you find yourself wanting a series per feature, you have crossed from observability into a change-data-capture log; bound the dimension per bounding spatial metric tag cardinality instead.

What if a layer legitimately has no recent writes?

A slowly-changing layer with no writes in an hour is not lagging — its newest updated_at is genuinely old. Distinguish replication lag from write cadence by comparing the follower’s max updated_at to the primary’s max, not to now(). If both ends agree, the layer is fresh regardless of absolute age; only a gap between primary and follower maxima is replication lag.

Should the freshness budget live in Prometheus or in the exporter?

Publish it as a recorded series so the alert rules stay declarative and one place defines each layer’s window. The exporter should emit raw lag only; mixing budget logic into the exporter splits the definition across two systems and invites the router-versus-alert drift that per-layer alerting is meant to eliminate.