Warming a Geocoding Cache From Historical Orders

TL;DR: Before a new cache goes live, fill it from history. Extract distinct canonical keys from past orders, rank them by how likely they are to recur, resolve the top slice within a fixed budget, and measure the hit rate the warm-up buys. Where results already exist in durable storage, warming costs nothing at all. The surrounding policy is geocoding cache invalidation and freshness.

Two Kinds of Warming

The word covers two very different operations, and conflating them leads teams to believe warming is expensive when the common case is free.

Rebuild warming and resolve warming Two panels. Rebuild warming copies results already held in the durable table into the cache, costs nothing beyond database time and completes in minutes. Resolve warming calls providers for addresses never resolved before, is metered, and should be bounded by an explicit budget and ranked by expected recurrence. rebuild warming source: the durable results table cost: database time only duration: minutes for millions of rows use after: a restart, a migration, a new region always do this first — it is free resolve warming source: addresses never resolved before cost: metered, one call per key duration: bounded by rate limits use before: a launch, a migration to a new provider rank the keys — the budget is finite

Do the free one first and unconditionally. Only the residue — addresses that appear in history and have genuinely never been resolved — needs a provider call, and on an established system that residue is far smaller than teams expect.

Ranking Keys by Expected Recurrence

A finite budget should buy the most future hits, and past behaviour predicts that well. Two signals do most of the work: how many times a key appeared, and how recently. A key seen forty times last month is a much better investment than one seen once two years ago.

from __future__ import annotations

import math
from dataclasses import dataclass


@dataclass(frozen=True)
class KeyStat:
    cache_key: str
    canonical: str
    occurrences: int
    days_since_last: float


def recurrence_score(stat: KeyStat, half_life_days: float = 120.0) -> float:
    """Frequency weighted by recency — higher scores are worth resolving first."""
    decay = 0.5 ** (stat.days_since_last / half_life_days)
    return math.log1p(stat.occurrences) * decay


def rank(stats: list[KeyStat], budget: int) -> list[KeyStat]:
    """Top `budget` keys by expected recurrence."""
    return sorted(stats, key=recurrence_score, reverse=True)[:budget]

The logarithm on occurrences is deliberate. Without it, a handful of extremely frequent addresses — a warehouse, a returns depot — dominate the ranking entirely, and those are precisely the keys that will be resolved on the first request anyway. Damping the frequency term spreads the budget across the long tail that actually benefits.

Extracting the Candidates

-- Distinct canonical keys from a year of orders, with recurrence inputs.
SELECT  o.cache_key,
        o.canonical_address,
        count(*)                                   AS occurrences,
        extract(epoch FROM now() - max(o.created_at)) / 86400.0 AS days_since_last
FROM    orders_normalised o
LEFT    JOIN geocode_best g USING (cache_key)
WHERE   o.created_at > now() - interval '12 months'
  AND   g.cache_key IS NULL                        -- never resolved: needs a call
GROUP   BY o.cache_key, o.canonical_address
HAVING  count(*) >= 2                              -- one-off addresses rarely recur
ORDER   BY occurrences DESC;

The HAVING clause carries an assumption worth checking against your own data: that an address seen exactly once in a year is unlikely to recur. For a subscription business that holds; for a marketplace with mostly one-time buyers it does not, and the threshold should come out.

Sizing the Warm-Up

Warming is an investment with a measurable payback, and the arithmetic is simple enough to do before committing budget.

Input Example Effect
keys never resolved 180,000 the theoretical maximum spend
after recurrence ranking, top slice 40,000 the keys likely to recur soon
expected hits in the first month 62,000 some keys recur more than once
calls avoided vs. cold start 62,000 the return, in provider calls
net position after one month +22,000 calls the warm-up paid for itself

The interesting column is the third: a warmed key is not worth one avoided call but as many as it recurs. That multiplier is what makes warming favourable even when the ranking is imperfect, and it is why the recurrence weighting matters more than the exact budget.

Running It Without Disturbing Production

Warming inside a reserved slice of the rate budget A stacked timeline of provider rate usage across a day. The main pipeline's usage varies through the day. The warm-up job fills only the unused headroom, expanding overnight and contracting to nothing during the daily peak, so it never competes with delivery traffic. Provider rate usage across one day rate limit warm-up fills the headroom main pipeline Read the limiter's current utilisation and pause the warm-up above a threshold — the batch always wins. A warm-up that delays a delivery batch has cost more than the calls it saved.

An adaptive warm-up is not much harder than a fixed one: read the shared rate limiter’s recent utilisation before each chunk and skip the chunk when the main pipeline is busy. The job then finishes in a day or a week depending on load, which is entirely acceptable for work that has no deadline.

When Not to Warm

Warming is not free even in its cheap form — it consumes database time, cache memory and somebody’s attention — and there are situations where the right decision is to skip it.

A cache with a short working set does not need warming. If ninety percent of lookups concern addresses seen in the last week, a cold cache reaches its steady-state hit rate within a day of normal traffic, and a warm-up merely front-loads work that would have happened anyway. Measure the recurrence distribution before assuming warming helps.

A cache whose memory is tight should not be warmed either. Filling it with historical keys evicts the recent ones that traffic is actually asking for, and the result is a lower hit rate than the cold start would have produced. The working-set calculation — distinct keys per period multiplied by entry size — is the check to run first.

Finally, a migration to a genuinely different provider is a poor warming candidate. The cached answers are keyed by provider, so warming from history means paying the new provider for every historical address, which is a large bill for a hit rate you can obtain gradually for free. In that case the durable table’s existing results still serve the old provider’s traffic while the new one warms naturally from live lookups.

Edge Cases and Failure Modes

Warming with the wrong key version. If the normalisation version changed since the history was written, the extracted keys will not match what production will look up. Re-derive keys from the canonical address at warm time rather than reading stored keys, or the entire warm-up misses.

Historical addresses that no longer exist. A year-old order may reference a demolished building. Those resolve to failures and are cached as negatives, which is harmless but wasteful — filtering by recency of last occurrence keeps most of them out.

Warming a cache larger than its memory. Loading two million entries into an instance sized for eight hundred thousand evicts as fast as it writes, and the eviction is least-recently-used, so the warm-up evicts itself. Check the working-set size against the memory limit before starting.

Duplicate work across regions. Two regional caches warmed independently make the same provider calls twice. Warm the durable table once, then rebuild each region’s cache from it — the free path, applied twice, instead of the metered path applied twice.

In short: warm when the working set is broad and slow-moving, skip it when the working set is narrow and fast-moving, and always prefer the free rebuild path over the metered resolve path where the answers already exist somewhere.

Recurrence Decides the Order

Which candidates the budget should buy first A scatter of candidate keys plotted by how often they occurred against how recently. The upper-right region, frequent and recent, is marked as the target for a limited warm-up budget. The lower-left region, rare and old, is marked as not worth resolving. frequent rare long ago recent spend the budget here frequent and recent — most likely to recur rare and old — skip

Integration Note

Warming is most valuable at exactly the moments when a pipeline is least able to absorb a cost spike: a launch into a new market, a migration to a new provider, or a key version migration. In the last case the read-through-to-the-old-version trick removes most of the need, but a targeted warm-up of the highest-recurrence keys still smooths the first day. The candidate extraction depends on canonical keys being stable, which is the property established in generating canonical address keys in Python.

Whichever path you take, record how many keys the warm-up wrote and what the hit rate was in the following week — those two numbers are what justify running it again next time.