Postgres Materialized View as a Durable Geocode Cache

Use a Postgres table as the durable geocode cache and a MATERIALIZED VIEW over it to serve the single best-precision result per address, upserting new results with INSERT ... ON CONFLICT DO UPDATE and rebuilding the view with REFRESH MATERIALIZED VIEW CONCURRENTLY so reads never block; this page details that durable tier within Redis and Postgres caching patterns.

Why a Table Plus a Materialized View

Redis is the fast tier but a volatile one. The durable cache — the record of every coordinate the pipeline has ever paid for — belongs in Postgres, where it survives restarts and evictions and stays queryable. Storing every provider response gives you provenance and lets you compare providers, but read paths want exactly one answer per address. A materialized view resolves that tension: the base table keeps every result, and the view precomputes the best-precision winner per cache key so lookups are a single indexed read.

Append Table, Projected View

The pattern separates two jobs that a single table does badly: recording every result ever obtained, and serving the current best answer per address. The base table appends — several rows per key, one per provider and attempt — while the view projects exactly one winning row per key for readers to join against.

The ordering note is the substance of the pattern. Recency alone is the intuitive rule and the wrong one: a fallback lookup that returns a locality centroid is newer than the rooftop match from six months ago, and a recency-ranked view will hand readers the worse coordinate for as long as the address is not re-resolved.

Column Breakdown

Schema DDL

The base table is keyed on (cache_key, provider) so each provider can hold its own answer for the same address; the materialized view then picks the best one. A precision-rank helper keeps the “best” ordering explicit.

CREATE TABLE IF NOT EXISTS geocode_cache (
    cache_key    char(64)         NOT NULL,
    provider     text             NOT NULL,
    raw_input    text             NOT NULL,
    country_code char(2)          NOT NULL DEFAULT 'US',
    lat          double precision,
    lon          double precision,
    precision    text             NOT NULL DEFAULT 'range',
    confidence   real             NOT NULL DEFAULT 0.0,
    result       jsonb            NOT NULL,
    expires_at   timestamptz      NOT NULL DEFAULT now() + interval '30 days',
    updated_at   timestamptz      NOT NULL DEFAULT now(),
    PRIMARY KEY (cache_key, provider)
);

CREATE INDEX IF NOT EXISTS idx_geocode_cache_expires
    ON geocode_cache (expires_at);

-- Ordinal rank so the view can pick the highest precision deterministically.
CREATE OR REPLACE FUNCTION precision_rank(p text) RETURNS int
    LANGUAGE sql IMMUTABLE AS $$
    SELECT CASE p
        WHEN 'rooftop'  THEN 4
        WHEN 'range'    THEN 3
        WHEN 'centroid' THEN 2
        WHEN 'postal'   THEN 1
        ELSE 0
    END
$$;

Best-Precision Materialized View

DISTINCT ON (cache_key) with an ordering by precision rank then confidence yields exactly one winning row per address. Only non-expired rows are considered, so stale results drop out on the next refresh. The unique index on cache_key is what enables REFRESH ... CONCURRENTLY.

CREATE MATERIALIZED VIEW IF NOT EXISTS geocode_best AS
SELECT DISTINCT ON (cache_key)
    cache_key,
    provider,
    country_code,
    lat,
    lon,
    precision,
    confidence,
    result,
    expires_at
FROM geocode_cache
WHERE expires_at > now()
ORDER BY
    cache_key,
    precision_rank(precision) DESC,
    confidence DESC,
    updated_at DESC;

-- Required for REFRESH MATERIALIZED VIEW CONCURRENTLY.
CREATE UNIQUE INDEX IF NOT EXISTS idx_geocode_best_key
    ON geocode_best (cache_key);

Read paths hit geocode_best for a single-row answer:

SELECT lat, lon, precision, confidence
FROM geocode_best
WHERE cache_key = $1;

UPSERT on Write

Every provider result is upserted into the base table. ON CONFLICT (cache_key, provider) DO UPDATE overwrites a provider’s previous answer for the same address, refreshing coordinates, precision, and expiry in one atomic statement.

INSERT INTO geocode_cache
    (cache_key, provider, raw_input, country_code,
     lat, lon, precision, confidence, result, expires_at, updated_at)
VALUES
    ($1, $2, $3, $4, $5, $6, $7, $8, $9,
     now() + interval '30 days', now())
ON CONFLICT (cache_key, provider) DO UPDATE SET
    lat        = EXCLUDED.lat,
    lon        = EXCLUDED.lon,
    precision  = EXCLUDED.precision,
    confidence = EXCLUDED.confidence,
    result     = EXCLUDED.result,
    expires_at = EXCLUDED.expires_at,
    updated_at = now();

Refreshing the View Concurrently

A plain REFRESH MATERIALIZED VIEW takes an ACCESS EXCLUSIVE lock and blocks every reader until the rebuild finishes — unacceptable for a cache serving live lookups. CONCURRENTLY rebuilds in the background and swaps atomically, so reads keep working throughout. It requires the unique index created above and cannot run inside a transaction block.

REFRESH MATERIALIZED VIEW CONCURRENTLY geocode_best;

Refresh on a cadence that matches write volume: after each large batch load, or on a schedule (for example every 15 minutes) for a continuously-fed pipeline. CONCURRENTLY does more work than a plain refresh, so do not run it more often than your read-staleness tolerance requires.

Refresh Without Locking Readers

REFRESH MATERIALIZED VIEW takes an exclusive lock and blocks every reader for its duration, which on a large cache is minutes. The CONCURRENTLY variant does not — at the cost of a unique index requirement and a slower rebuild. For a cache that production traffic joins against, that trade is not close.

The unique index requirement is the reason the view must project exactly one row per key. It is easy to satisfy by construction here, and it doubles as a correctness assertion: if a change to the ranking logic ever produces two rows for a key, the refresh fails loudly instead of quietly duplicating cache entries.

Python Batch Upsert With psycopg

Row-at-a-time inserts waste round trips. executemany with psycopg batches the upsert, and a single REFRESH ... CONCURRENTLY follows the load. The cache key is derived from the same normalization used elsewhere in the pipeline; see Unicode and character normalization in Python.

from __future__ import annotations

import hashlib
import json
from typing import Iterable, Sequence

import psycopg

_UPSERT = """
INSERT INTO geocode_cache
    (cache_key, provider, raw_input, country_code,
     lat, lon, precision, confidence, result, expires_at, updated_at)
VALUES
    (%s, %s, %s, %s, %s, %s, %s, %s, %s,
     now() + interval '30 days', now())
ON CONFLICT (cache_key, provider) DO UPDATE SET
    lat        = EXCLUDED.lat,
    lon        = EXCLUDED.lon,
    precision  = EXCLUDED.precision,
    confidence = EXCLUDED.confidence,
    result     = EXCLUDED.result,
    expires_at = EXCLUDED.expires_at,
    updated_at = now();
"""


def cache_key(normalized: str, country_code: str = "US") -> str:
    """SHA-256 hex digest of the normalized address plus country code."""
    pre = f"{normalized}|{country_code.upper()}"
    return hashlib.sha256(pre.encode("utf-8")).hexdigest()


def batch_upsert(
    dsn: str,
    rows: Iterable[dict],
    refresh: bool = True,
) -> int:
    """Upsert geocode results and optionally refresh the best-precision view.

    Each row dict needs: normalized, provider, raw_input, country_code,
    lat, lon, precision, confidence, result.
    Returns the number of rows submitted.
    """
    params: list[Sequence[object]] = []
    for r in rows:
        params.append((
            cache_key(r["normalized"], r.get("country_code", "US")),
            r["provider"],
            r["raw_input"],
            r.get("country_code", "US"),
            r["lat"],
            r["lon"],
            r.get("precision", "range"),
            float(r.get("confidence", 0.0)),
            json.dumps(r["result"]),
        ))

    with psycopg.connect(dsn) as conn:
        with conn.cursor() as cur:
            cur.executemany(_UPSERT, params)
        conn.commit()
        if refresh:
            # CONCURRENTLY cannot run inside a transaction block.
            conn.autocommit = True
            with conn.cursor() as cur:
                cur.execute(
                    "REFRESH MATERIALIZED VIEW CONCURRENTLY geocode_best"
                )
    return len(params)

Vectorized pandas variant

When results arrive as a DataFrame, project the columns, derive keys vectorized, and hand the records to the same batch upsert.

from __future__ import annotations

import pandas as pd


def upsert_dataframe(dsn: str, df: pd.DataFrame) -> int:
    """Upsert a DataFrame of geocode results into the durable cache.

    Expects columns: normalized, provider, raw_input, country_code,
    lat, lon, precision, confidence, result (dict).
    """
    df = df.copy()
    df["country_code"] = df["country_code"].fillna("US")
    rows = df.to_dict(orient="records")
    return batch_upsert(dsn, rows, refresh=True)

Keeping the Write Path Idempotent

Batch jobs get retried, and a retry that inserts a second copy of every result turns the history table into a source of duplicate-driven confusion. An upsert keyed on the address key and provider makes re-running a partially failed batch a no-op for the rows that already landed.

Batch the writes as well: execute_values with a few thousand tuples per statement is roughly an order of magnitude faster than per-row inserts, and it keeps the transaction short enough that a failure retries cheaply. Very large single transactions are the other common way this write path goes wrong.

Edge Cases

REFRESH CONCURRENTLY fails without a unique index

CONCURRENTLY requires a unique index on the materialized view (here idx_geocode_best_key on cache_key). Without it, Postgres raises cannot refresh materialized view "geocode_best" concurrently. Create the unique index before the first concurrent refresh, and ensure the view’s DISTINCT ON genuinely produces one row per cache_key or the index build itself will fail on duplicates.

The materialized view goes stale between refreshes

The view is a snapshot; a result upserted after the last refresh is invisible to geocode_best until the next REFRESH. For lookups that must be strictly current, read the base table’s best row directly with the same DISTINCT ON ordering, and reserve the view for high-volume read paths that tolerate minutes of staleness. This is the classic freshness-versus-throughput trade the two-tier design manages, alongside the volatile Redis TTL layer.

Expired rows linger in the base table

expires_at filters expired rows out of the view but does not delete them from the base table. Sweep periodically with a batched DELETE FROM geocode_cache WHERE expires_at < now() run off-peak, or partition by updated_at month and drop old partitions — cheaper than row-by-row deletes at scale. The idx_geocode_cache_expires index keeps both the view filter and the sweep fast.

Integration Note

This durable Postgres tier is the system of record in Redis and Postgres caching patterns: it backfills the hot Redis cache after an eviction or restart so a paid result is never lost to volatility. Because the base table retains every provider’s answer, it also feeds provider benchmarking — the best-precision view is exactly the ground truth used when comparing geocoding accuracy across providers.

---

Related

• Redis and Postgres caching patterns — the two-tier design this durable Postgres cache anchors.
• Caching geocoding results with Redis TTL in Python — the volatile hot tier that this durable store backfills.
• Unicode and character normalization in Python — the normalization the cache key is derived from.
• Comparing geocoding accuracy across providers — uses the retained per-provider rows as a benchmarking corpus.