Redis and Postgres Caching Patterns for Geocoding Results

Within the Caching, Deduplication & Spatial Indexing section, a geocoding cache is the single highest-leverage cost control you can add to an automated pipeline: it is a durable store keyed on the normalized address that returns a previously resolved coordinate instead of paying a provider to resolve the same input twice. Manifests, CRM exports, and nightly enrichment jobs are dominated by repeats — the same warehouse, the same store, the same customer address arriving in slightly different spellings — and every repeat that hits cache is an API call you do not pay for. This guide covers the full pattern: designing the cache key, splitting a hot Redis tier from a durable Postgres tier, wiring a read-through lookup, and defending the whole thing against stampedes and stale data.

Caching is only correct if the key is correct. Two spellings of the same place must produce the same key, or the cache silently degrades into a near-miss store that pays the provider anyway. That makes normalization a hard prerequisite, not an optimization — see Unicode and character normalization in Python for the canonical form your key builder must apply before hashing.

Prerequisites

• Python 3.8+ with redis (4.2+ for redis.asyncio), psycopg (3.x), and pandas installed
• A running Redis instance reachable from every worker, sized to hold your hot working set in memory
• A Postgres database with a table dedicated to the durable cache and connection pooling configured
• A deterministic normalization function that produces one canonical string per logical address
• Provider client code that returns a structured result (coordinates, precision, provider id) or a clean no-result signal

Production Workflow

1. Normalize the raw address to a canonical string (NFC Unicode form, collapsed whitespace, expanded abbreviations, uppercased where appropriate).
2. Derive the key as sha256(normalized + "|" + country_code), hex-encoded, so distinct countries never collide on an identical local string.
3. Read Redis first. On a hit, return immediately — this is the sub-millisecond path that serves the bulk of a repeat-heavy batch.
4. Read Postgres on a Redis miss. On a hit, backfill Redis with a fresh TTL and return the durable record.
5. Call the provider only on a miss in both tiers, guarded by a per-key lock so concurrent misses collapse into one upstream call.
6. Write through to both tiers: persist to Postgres (the durable record) and set the Redis key with a jittered TTL.
7. Negatively cache a genuine no-result with a short TTL so the same dead address does not re-bill the provider on the next run.

Cache Key Design

The key must be a pure function of the normalized address. Compute it once, reuse it everywhere — Redis key, Postgres primary key, and idempotency token for the provider call are all the same digest.

from __future__ import annotations

import hashlib
import re
import unicodedata

# Compile once at module level.
_MULTI_WS = re.compile(r"\s+")
_PUNCT = re.compile(r"[.,#]+")


def normalize_for_key(raw: str) -> str:
    """Produce the canonical string a cache key is derived from.

    Deterministic: equivalent addresses must collapse to one output.
    This mirrors the fuller normalization pipeline documented in
    Unicode and character normalization; keep the two in sync.
    """
    nfc = unicodedata.normalize("NFC", raw)
    lowered = nfc.strip().lower()
    depunctuated = _PUNCT.sub(" ", lowered)
    return _MULTI_WS.sub(" ", depunctuated).strip()


def cache_key(raw: str, country_code: str = "US") -> str:
    """SHA-256 hex digest of the normalized address plus country code.

    The country code guards against cross-border collisions where an
    identical local street string exists in two countries.
    """
    canonical = f"{normalize_for_key(raw)}|{country_code.upper()}"
    return hashlib.sha256(canonical.encode("utf-8")).hexdigest()

The country code in the pre-image is deliberate: "100 Main St" exists in thousands of towns worldwide, and without a namespace the digest would fuse them. For pipelines spanning multiple countries, extend the pre-image with a postal code when one is reliably present.

Two Cache Tiers

Redis and Postgres are not redundant — they play different roles. Redis is the hot cache: in-memory, sub-millisecond, volatile, sized to the working set. Postgres is the durable cache: disk-backed, survives restarts and evictions, and doubles as the queryable record of every coordinate the pipeline has ever resolved.

The design goal is to serve as much traffic as possible from L1, fall back to L2 on eviction or cold start, and reach L3 as rarely as possible. A cold Redis (after a deploy or eviction) still costs nothing at the provider because L2 backfills it.

Read-Through / Write-Through Client

The core is a two-tier lookup: Redis → Postgres → provider, backfilling on the way back up. The async client below uses redis.asyncio and psycopg and guards the provider call with a per-key lock to prevent stampedes.

from __future__ import annotations

import asyncio
import json
import time
from typing import Awaitable, Callable, Optional

import psycopg
import redis.asyncio as aioredis

# Sentinel stored for negatively cached (no-result) lookups.
_NEGATIVE = "\x00NEG"
_POSITIVE_TTL = 60 * 60 * 24 * 30   # 30 days
_NEGATIVE_TTL = 60 * 60 * 4         # 4 hours

# Provider fetcher signature: async (raw, country) -> dict | None
Fetcher = Callable[[str, str], Awaitable[Optional[dict]]]


class GeocodeCache:
    """Two-tier read-through cache: Redis (hot) over Postgres (durable)."""

    def __init__(self, redis_url: str, pg_dsn: str) -> None:
        self._redis = aioredis.from_url(redis_url, decode_responses=True)
        self._pg_dsn = pg_dsn
        # In-process locks collapse concurrent misses for the same key.
        self._locks: dict[str, asyncio.Lock] = {}

    async def get_or_geocode(
        self,
        raw: str,
        fetcher: Fetcher,
        country_code: str = "US",
        ttl: int = _POSITIVE_TTL,
    ) -> Optional[dict]:
        """Return a cached result or resolve it via the provider once."""
        key = cache_key(raw, country_code)

        hot = await self._redis.get(key)
        if hot is not None:
            return None if hot == _NEGATIVE else json.loads(hot)

        durable = await self._read_pg(key)
        if durable is not None:
            # Backfill the hot tier and return.
            await self._redis.set(key, json.dumps(durable), ex=_jitter(ttl))
            return durable

        # Miss in both tiers: guard the paid call with a per-key lock.
        lock = self._locks.setdefault(key, asyncio.Lock())
        async with lock:
            # Re-check: another coroutine may have filled it while we waited.
            again = await self._redis.get(key)
            if again is not None:
                return None if again == _NEGATIVE else json.loads(again)

            result = await fetcher(raw, country_code)
            if result is None:
                await self._redis.set(key, _NEGATIVE, ex=_jitter(_NEGATIVE_TTL))
                return None

            await self._write_pg(key, raw, country_code, result)
            await self._redis.set(key, json.dumps(result), ex=_jitter(ttl))
            return result

    async def _read_pg(self, key: str) -> Optional[dict]:
        async with await psycopg.AsyncConnection.connect(self._pg_dsn) as conn:
            row = await (await conn.execute(
                "SELECT result FROM geocode_cache "
                "WHERE cache_key = %s AND expires_at > now()",
                (key,),
            )).fetchone()
        return row[0] if row else None

    async def _write_pg(
        self, key: str, raw: str, country: str, result: dict
    ) -> None:
        async with await psycopg.AsyncConnection.connect(self._pg_dsn) as conn:
            await conn.execute(
                """
                INSERT INTO geocode_cache
                    (cache_key, raw_input, country_code, result,
                     expires_at, updated_at)
                VALUES (%s, %s, %s, %s, now() + interval '30 days', now())
                ON CONFLICT (cache_key) DO UPDATE
                    SET result = EXCLUDED.result,
                        expires_at = EXCLUDED.expires_at,
                        updated_at = now()
                """,
                (key, raw, country, json.dumps(result)),
            )
            await conn.commit()


def _jitter(ttl: int, spread: float = 0.15) -> int:
    """Spread expiries so a batch loaded together does not expire at once."""
    import random
    delta = int(ttl * spread)
    return ttl + random.randint(-delta, delta)

Vectorized pandas batch lookup

Row-at-a-time lookups waste round trips. For a DataFrame of addresses, compute all keys once, fetch the hot tier in a single Redis MGET, resolve the durable tier for the remaining misses in one Postgres query, and only then dispatch the true misses to the provider.

from __future__ import annotations

import json
from typing import Optional

import pandas as pd
import psycopg
import redis


def batch_lookup(
    df: pd.DataFrame,
    r: redis.Redis,
    pg_dsn: str,
    address_col: str = "address",
    country_col: str = "country_code",
) -> pd.DataFrame:
    """Resolve cached coordinates for a DataFrame with two bulk round trips.

    Adds 'cache_key', 'result' (dict or None), and 'source' columns.
    'source' is one of {'redis', 'postgres', 'miss'} so callers can route
    only the misses to a paid provider.
    """
    df = df.copy()
    df["cache_key"] = [
        cache_key(a, c)
        for a, c in zip(df[address_col], df[country_col].fillna("US"))
    ]

    # Tier 1: one MGET for the whole batch.
    hot = r.mget(df["cache_key"].tolist())
    df["result"] = [json.loads(v) if v and v != _NEGATIVE else None for v in hot]
    df["source"] = ["redis" if v is not None else "miss" for v in hot]

    # Tier 2: one query for keys that missed the hot tier.
    misses = df.loc[df["source"] == "miss", "cache_key"].tolist()
    if misses:
        with psycopg.connect(pg_dsn) as conn:
            rows = conn.execute(
                "SELECT cache_key, result FROM geocode_cache "
                "WHERE cache_key = ANY(%s) AND expires_at > now()",
                (misses,),
            ).fetchall()
        durable = {k: v for k, v in rows}
        mask = df["cache_key"].isin(durable)
        df.loc[mask, "result"] = df.loc[mask, "cache_key"].map(durable)
        df.loc[mask, "source"] = "postgres"

    return df

Rows where source == "miss" are the only records that need a paid call. Feeding just those into the routing engine and writing results back through GeocodeCache closes the loop. Because the miss set shrinks on every run, cache hit rate climbs and provider spend falls as the durable tier matures.

Edge Cases

Negative caching of failed lookups

A genuine no-result (the provider resolved the request but found nothing) should be cached so the same dead address does not re-bill on every run. Store a distinct sentinel (_NEGATIVE above) rather than an empty result so the read path can tell “known miss” from “not yet seen.” Give it a short TTL (one to six hours): provider coverage improves over time, and you want the pipeline to re-check eventually. Critically, never negatively cache transient failures — an HTTP 429 or 503 is a retry signal, not a no-result, and caching it would suppress a valid address for hours. Classify provider errors before deciding what to cache, the same discipline used when implementing fallback chains for failed lookups.

Key collisions from weak normalization

SHA-256 collisions are astronomically unlikely; the real collision risk is upstream, in normalization. If the key builder is not strictly deterministic — if it depends on locale, dictionary ordering, or an un-pinned abbreviation table — the same address can hash two ways and split its cache entry, or two different addresses can normalize identically and share one entry. Pin the normalization logic, version it, and include the country code (and postal code where available) in the pre-image. When you change normalization rules, bump a version prefix in the key so old and new entries coexist rather than corrupting each other.

TTL for volatile addresses

New-construction subdivisions, recently renamed streets, and rural routes are the addresses most likely to change or to have improved provider data next quarter. Tie TTL to result confidence and precision: commit a long TTL only for high-confidence rooftop results in mature areas, and a short TTL for anything centroid-level or flagged low-confidence. This keeps the durable cache from pinning a stale, imprecise coordinate for months. The handling PO boxes and rural routes guide covers the input classes that most warrant a short TTL.

Redis eviction under memory pressure

Redis with a maxmemory policy will evict keys when full, which is fine — the durable Postgres tier backfills them at a small latency cost. What is not fine is treating Redis as authoritative. Never write a result only to Redis; always write through to Postgres first so an eviction never loses a paid result. Use allkeys-lru or volatile-ttl eviction so the hottest addresses survive.

Performance & Vectorization

The economics are driven almost entirely by hit rate. A pipeline that re-processes overlapping datasets sees hit rates climb toward 90%+ as the durable cache matures, turning a five-figure monthly provider bill into a fraction of it.

For batches above ~50k rows, chunk the DataFrame (5k–10k per chunk) so a single MGET payload and Postgres ANY array stay a reasonable size, and checkpoint results between chunks. Pair the miss-routing step with the throttling controls in API quota tracking and cost management so that even the reduced miss volume stays within provider budget.

Troubleshooting

Hit rate is far lower than expected

Almost always a normalization defect. Two spellings of the same address are hashing to two keys. Log the normalized pre-image alongside the key for a sample of misses and eyeball whether obvious duplicates produced different canonical strings. Common culprits: inconsistent abbreviation expansion (St vs Street), stray punctuation, and skipped Unicode normalization. Tighten the canonical form and hit rate recovers.

Redis and Postgres disagree on a key

If a key returns one result from Redis and a different one from Postgres, a write path wrote to one tier but not the other, or an invalidation touched only one. Make writes strictly write-through (Postgres first, then Redis) and make invalidation delete the Redis key and update the Postgres row in the same operation. On a hard mismatch, treat Postgres as authoritative and overwrite the Redis key.

Stampede still occurs across processes

The in-process asyncio.Lock above only collapses concurrent misses within one process. If many worker processes miss the same key simultaneously, promote the lock to a distributed one: SET lock:<key> <token> NX EX 10 in Redis, and have losers poll briefly for the winner’s result before falling back to their own provider call. This is the same idempotency-token discipline used in the routing layer.

Durable cache grows without bound

The Postgres table accumulates expired rows unless swept. Run a periodic DELETE FROM geocode_cache WHERE expires_at < now() (batched, off-peak) or partition the table by updated_at month and drop old partitions. Index cache_key (primary key) and expires_at so both the lookup and the sweep stay fast.

FAQ

Why hash the address instead of using it as the cache key directly?

Raw address strings vary in length, contain punctuation and non-ASCII characters, and are not safe as Redis keys or Postgres primary keys without escaping. A SHA-256 digest of the normalized address produces a fixed-length, collision-resistant, index-friendly key. Because the digest is deterministic, the same normalized address always maps to the same key across every worker and process.

Should I cache failed geocoding lookups?

Yes, but with a short TTL. Negative caching stops a permanently unresolvable address from being re-sent to a paid provider on every batch run. Store a sentinel value marking the miss and expire it in one to six hours so that provider data updates or coverage improvements are eventually picked up. Never negatively cache transient errors such as HTTP 429 or 503, only genuine no-result responses.

What TTL should I use for geocoding results?

Scale the TTL to how stable the underlying address is. Established rooftop-precision results for mature urban addresses can live for 90 days or more, while new-construction, rural, or low-confidence results should expire in 7 to 30 days so the pipeline re-checks them against fresher provider data. Add random jitter to every TTL so large batches loaded together do not all expire in the same second.

What is cache stampede and how does a two-tier cache prevent it?

A stampede happens when a popular key expires and many concurrent requests all miss the cache simultaneously, each firing an identical paid provider call. A per-key lock lets only the first request call the provider while the others wait for the result to be written, then read it from cache. Combined with jittered TTLs, this keeps a batch run from issuing thousands of duplicate lookups for the same address.

---

Related

• Caching, Deduplication & Spatial Indexing — the parent section covering caching, spatial indexing, and address deduplication as one enrichment layer.
• Caching geocoding results with Redis TTL in Python — a focused walkthrough of TTL selection, SETEX, and jittered expiry for the hot tier.
• Postgres materialized view geocode cache — schema DDL, UPSERT, and a materialized view for the durable tier.
• Unicode and character normalization in Python — the deterministic normalization your key builder must apply before hashing.
• API quota tracking and cost management — track the provider spend that caching is designed to eliminate, and cap the residual miss traffic.