Revalidating Stale Geocodes in the Background

TL;DR: Select cached entries that are both weak and old, re-resolve them at low priority against a small quota budget, and replace the stored result only when the new one is at least as precise. Track the promotion rate so the sweep can be sized against the value it actually returns. The wider policy is geocoding cache invalidation and freshness.

Which Entries Are Worth Revisiting

Not every old entry deserves a second call. A rooftop match from a year ago is almost certainly still correct, and re-resolving it spends quota to learn nothing. The candidates worth the money are the ones where the original answer was weak and enough time has passed for the underlying data to have improved.

Which quadrant is worth re-resolving A two-by-two grid of precision against age. High-precision recent and high-precision old entries are both left alone because a rooftop match rarely changes. Low-precision recent entries are left alone because the reference data has not had time to improve. Only low-precision old entries are selected for revalidation. rooftop locality recent old leave alone already the best answer leave alone a rooftop match rarely moves leave alone for now data has not had time to improve revalidate weak answer, enough time for a better one Precision against age — only one quadrant repays a call

Sizing the age threshold from the provider’s own refresh cadence is more effective than picking a round number. If a provider ships reference updates monthly, revalidating an entry after fourteen days will usually re-derive the same answer; after forty days it has had a genuine chance to improve.

The Selection Query

-- Candidates: weak, old, and not already queued.
SELECT cache_key, canonical_address, precision_tier, fetched_at
FROM   geocode_best
WHERE  precision_tier IN ('street', 'locality', 'postcode')
  AND  fetched_at < now() - interval '40 days'
  AND  (revalidated_at IS NULL OR revalidated_at < now() - interval '40 days')
ORDER  BY
       CASE precision_tier
           WHEN 'postcode' THEN 0
           WHEN 'locality' THEN 1
           ELSE 2
       END,
       fetched_at
LIMIT  %(budget)s;

The ordering matters as much as the filter. Weakest first means a limited budget is spent where the potential improvement is largest — promoting a postcode-level guess to a rooftop match is worth far more than nudging a street-level result up one tier.

The Tier Guard

Revalidation must be able to improve an entry and must never be able to worsen it. Without an explicit comparison, an hour in which the primary provider is degraded quietly rewrites good rooftop coordinates as street-level ones across the whole sweep.

from __future__ import annotations

from dataclasses import dataclass

TIER_RANK = {"rooftop": 4, "parcel": 3, "street": 2, "locality": 1, "postcode": 0}


@dataclass(frozen=True)
class Result:
    tier: str
    lat: float
    lon: float
    provider: str


def should_replace(stored: Result, fresh: Result) -> bool:
    """Only accept a revalidation that is at least as precise as what we hold."""
    return TIER_RANK.get(fresh.tier, -1) >= TIER_RANK.get(stored.tier, -1)


def revalidate_one(stored: Result, resolve) -> tuple[Result, bool]:
    """Re-resolve and return (result_to_store, promoted)."""
    fresh = resolve()                       # normal provider chain
    if fresh is None:
        return stored, False                # a failure never overwrites a success
    if not should_replace(stored, fresh):
        return stored, False
    promoted = TIER_RANK[fresh.tier] > TIER_RANK[stored.tier]
    return fresh, promoted

Note that equal precision is accepted while lower precision is not. Accepting an equal result refreshes the timestamp and picks up any coordinate correction within the same tier; rejecting a lower one is the guard. Treating equal as a rejection would leave entries looking stale forever even when they were re-confirmed.

Budgeting the Sweep

Revalidation is optimisation, not delivery, and it must lose every contest for quota with the main pipeline. Three controls keep it in its place, and all three are worth setting explicitly rather than relying on the sweep being small.

Control Setting Why
per-run call budget a fixed integer, e.g. 5,000 makes the cost of a sweep predictable and boring
rate limiter share 10–20% of the provider’s rate leaves headroom for the batch that pays the bills
schedule off-peak, after the main batch the sweep should never be the reason a batch is late
pause switch a flag read at each iteration lets an incident stop the sweep without a deploy

The pause switch is the one people wish they had during an incident. When a provider is degraded and the fallback chain is carrying the load, a background sweep adding thousands of low-priority calls is actively harmful, and being able to stop it with a configuration change rather than a deployment is worth the few lines it costs.

Measuring Whether It Is Worth It

The promotion rate — the share of revalidations that actually improved a tier — is the number that justifies the budget. It varies enormously by market and by provider, and it is the only honest basis for deciding how large the sweep should be.

Promotion rate by market over one quarterly sweep Three markets compared by the share of revalidated entries that improved precision. A market with rapidly developing address data shows a high promotion rate. A mature urban market shows a low one. A market with stagnant reference data shows almost none, indicating the sweep is not worth running there. Share of revalidations that improved a precision tier developing market 31% — clearly worth the budget mature urban market 9% — worth a modest sweep stagnant reference data 1.3% — stop sweeping this market Segment the metric by market before deciding a global budget — one number hides exactly the differences that matter.

A promotion rate near zero is a result, not a failure: it says the cached answers are as good as the available data allows, and the budget belongs elsewhere. Continuing to sweep a market like that is a recurring cost with no return, and the metric is what makes that visible rather than assumed.

Scheduling and Ordering

The sweep is a background job with no deadline, which makes its schedule a matter of convenience rather than correctness — with two exceptions worth planning around.

The first is provider reference-data releases. Revalidating the day before a monthly release wastes the whole sweep, because the data that would have improved the answers arrives afterwards. Where a provider publishes a release cadence, align the sweep to run a few days after it; where it does not, a sweep frequency slightly longer than the typical release interval avoids the worst of the mistiming.

The second is your own batch calendar. A sweep running while a large delivery batch is in flight competes for the same quota even when it is rate-limited to a small share, because the limiter is shared. Scheduling it into the quietest window of the day removes the contention entirely and costs nothing, since the work is not time-sensitive.

Order matters within a sweep too. Processing candidates weakest-first means an interrupted run has still done the most valuable work, which matters more than it sounds: sweeps get interrupted routinely by deployments, incidents and pause switches, and a run that is cancelled halfway should have spent its budget on the entries with the most to gain.

Edge Cases and Failure Modes

Revalidating an address that no longer exists. Demolished and renumbered addresses stop resolving, and the fresh result is a failure. The None guard keeps the old coordinate, which is right — the historical record still needs a location even if the address is gone.

A provider change mid-sweep. If routing changes while a sweep is running, half the entries are revalidated against one provider and half against another, and the promotion rate becomes uninterpretable. Stamp the provider on each revalidation so the metric can be split afterwards.

Sweeps that never finish. With a fixed budget per run and a candidate set larger than the budget, the oldest entries are revalidated repeatedly while newer candidates never get a turn. The revalidated_at column in the selection query is what prevents that.

Interaction with negative caching. An entry that failed revalidation should not be written back as a negative result — it already has a positive answer that remains the best available. Keep the two paths distinct, or a temporary provider outage will convert good entries into cached failures.

Budget Contention, Drawn

Revalidation lives in the headroom A stacked area chart of provider rate usage. The main batch occupies a variable share through the day. The revalidation sweep fills only what remains below the rate limit, expanding when the batch is quiet and shrinking to nothing during its peak. rate limit main batch sweep uses the headroom Read the limiter's utilisation before each chunk and skip when the batch is busy — the sweep has no deadline.

Integration Note

The sweep reads from and writes to the same durable table described in Postgres materialized view geocode cache, using its precision-ranked upsert — which already implements the tier guard at the SQL level. Running the sweep through the normal fallback chain rather than a dedicated path keeps its results directly comparable with production results, which is what makes the promotion-rate metric meaningful.