Materialized Views for Spatial Caching

Some spatial queries are slow because they cannot find rows quickly, and some are slow because the work they do after finding them is genuinely expensive. Indexing fixes the first. Nothing fixes the second except doing the work less often — which is what a materialized view is for. This page, part of Spatial Performance Monitoring & Observability, covers how to tell the two cases apart, how to build a spatial cache that refreshes without blocking anyone, and how to monitor a cache so its staleness is a known quantity rather than a surprise.

Where the time actually goes Two query profiles. The first spends most of its time scanning to find rows, so an index removes almost all of it. The second finds its rows quickly and spends its time in ST_Union and ST_Simplify, which no index affects — only caching the result removes that cost. Index-bound or compute-bound? query A — 4,100 ms Seq Scan — 3,900 ms an index removes 95% of this — no cache needed query B — 3,800 ms ST_Union over 11,400 polygons — 2,900 ms ST_Simplify — 800 ms the index is already doing its job — only caching the result helps Read the node timings in EXPLAIN ANALYZE before choosing. Caching an index-bound query hides a missing index.

Prerequisites and Infrastructure Validation

Materialized views are core PostgreSQL, so nothing needs installing, but two facts about your data decide whether the approach fits.

How often does the underlying data change? A view over data that changes every second is a cache with a permanent invalidation problem. A view over data that changes on a nightly import is nearly free. Check the write rate:

sql
SELECT relname, n_tup_ins + n_tup_upd + n_tup_del AS writes_since_reset,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE relname IN ('parcels', 'zones', 'sensor_readings')
ORDER BY 2 DESC;

How long does the query take, and where? Run the candidate with EXPLAIN (ANALYZE, BUFFERS) and add up the time in aggregate and geometry-function nodes versus scan nodes. If the scans dominate, stop here and fix the index instead.

Core Execution Workflow

Step 1 — Materialize the expensive part only

The instinct is to cache the whole endpoint query. The better result usually comes from caching the expensive intermediate and leaving the cheap filtering live:

sql
CREATE MATERIALIZED VIEW district_coverage AS
SELECT
    d.id                                            AS district_id,
    d.name                                          AS district_name,
    count(p.id)                                     AS parcel_count,
    sum(ST_Area(p.geom::geography))                 AS covered_m2,
    ST_Multi(ST_Union(p.geom))                      AS coverage_geom,
    now()                                           AS computed_at
FROM districts d
JOIN parcels p ON p.geom && d.geom AND ST_Intersects(p.geom, d.geom)
GROUP BY d.id, d.name
WITH DATA;

Three hundred and forty rows, each holding a union that took seconds to compute. The endpoint that filters those rows by name, sorts them or paginates them stays a live query against a tiny table — fast, always current with respect to its own filters, and unaffected by the cache’s staleness.

Step 2 — Index the view like a table

A materialized view is a table for indexing purposes, and it needs the same treatment:

sql
-- required for concurrent refresh: a unique index over the whole row identity
CREATE UNIQUE INDEX district_coverage_pkey
    ON district_coverage (district_id);

-- and a spatial index, because the cached geometry gets queried too
CREATE INDEX district_coverage_geom_idx
    ON district_coverage USING gist (coverage_geom);

The unique index is not optional if you ever want REFRESH ... CONCURRENTLY, and retrofitting it later means a blocking refresh in the meantime. Create it at the same time as the view.

Step 3 — Refresh without blocking

sql
REFRESH MATERIALIZED VIEW CONCURRENTLY district_coverage;
Two refresh modes, two very different experiences A plain refresh rebuilds the view in 42 seconds while holding an exclusive lock, so every reader blocks for the whole period. A concurrent refresh takes 96 seconds because it computes a row-level diff, but readers see the old data throughout and never wait. Faster, or invisible — pick one REFRESH MATERIALIZED VIEW 42 s — ACCESS EXCLUSIVE every SELECT on the view waits for the whole 42 seconds acceptable only for a view nothing reads during the window REFRESH … CONCURRENTLY 96 s — readers see the previous contents throughout, no waiting costs a temporary copy plus the diff, and needs the unique index

The concurrent form is more than twice as slow and that is the right trade for anything user-facing. It builds the new contents in a temporary table, diffs them against the current ones, and applies the difference — which is why it needs the unique index and why it uses roughly twice the disk during the operation.

Performance Considerations

Refresh cost scales with the whole view, not the change. A concurrent refresh recomputes every row even if one district changed. For a view of a few hundred rows that is irrelevant; for a view of ten million it is the dominant cost and the signal to move to an ordinary table with an incremental upsert.

The cache competes for cache. A materialized view holding unioned geometry can be large, and it occupies the same shared_buffers as the tables it summarises. A cache that evicts the index it was meant to relieve is a net loss — measure total buffer hit ratio before and after, not just the endpoint latency.

Refresh contends with the write path. A concurrent refresh reads the source tables at full speed and writes a full copy. Scheduling it during the ingest peak turns two healthy workloads into one unhealthy one. Off-peak scheduling is not optional for large views.

Staleness is a feature with a number attached. The point of a cache is to serve data that is deliberately out of date. Making that explicit — a computed_at column, surfaced in the API response — turns “is this current?” from a support question into a visible fact.

Python Integration Patterns

A cache changes the application in two places: where the read comes from, and how the staleness is communicated.

Reads move to the view, and only reads. The pattern that keeps this honest is a repository function that names the cache explicitly, so a reader of the code can see which data is live and which is not:

python
from datetime import timedelta
from sqlalchemy import select, func
from sqlalchemy.orm import Session


MAX_STALENESS = timedelta(minutes=45)


def district_coverage(session: Session, name_prefix: str) -> list[dict]:
    """Read from the cache. The caller gets the data and its age together."""
    stmt = (
        select(DistrictCoverage.district_name,
               DistrictCoverage.parcel_count,
               DistrictCoverage.covered_m2,
               DistrictCoverage.computed_at)
        .where(DistrictCoverage.district_name.ilike(f"{name_prefix}%"))
        .order_by(DistrictCoverage.district_name)
    )
    rows = session.execute(stmt).all()
    return [
        {
            "district": r.district_name,
            "parcels": r.parcel_count,
            "covered_m2": float(r.covered_m2),
            "as_of": r.computed_at.isoformat(),
        }
        for r in rows
    ]

Returning as_of in the payload rather than hiding it is the single most useful thing an API can do with cached data. It costs one field, it answers the support question before it is asked, and it lets a client decide for itself whether the age is acceptable for what it is doing.

Staleness becomes a health signal. A readiness endpoint that reports cache age turns an invisible failure into a visible one:

python
def cache_health(session: Session) -> dict:
    age = session.scalar(
        select(func.now() - func.max(DistrictCoverage.computed_at))
    )
    return {
        "cache": "district_coverage",
        "age_seconds": age.total_seconds(),
        "healthy": age <= MAX_STALENESS,
    }

Writes never touch the view. A materialized view is read-only by definition, and an application that tries to keep it current by writing to it will fail at the first INSERT. Where a caller genuinely needs the freshest possible answer for one district — an admin recomputing a single row after a correction — the right response is to run the original query for that district, not to update the cache.

When Not to Use a Materialized View

Three situations look like caching problems and are not.

The query is slow because of a missing index. Covered above, and worth repeating because it is the most common misdiagnosis: caching an unindexed query moves the cost into the refresh job, where nobody is watching it.

The result differs per user. A view holds one answer. A query whose result depends on the caller’s permissions, their tenant or their chosen filters cannot be materialised as a single relation — what can be materialised is the expensive shared part underneath it, leaving the per-user filtering live.

The data must be current. If the answer to “how stale may this be?” is “not at all”, the conversation is over. That is not a limitation of materialized views; it is what caching means.

There is a fourth case that is more of a caution than a rule: a view that is refreshed more often than it is read is pure overhead. It happens more than you would expect, usually to a cache built for an endpoint that was later removed. Checking pg_stat_user_tables for a materialized view with a high refresh count and a low seq_scan plus idx_scan total is a five-minute audit that occasionally reclaims a lot of disk and CPU.

Ordinary views, materialized views and tables

Three things in PostgreSQL look similar and behave completely differently, and choosing among them is worth two minutes of thought.

An ordinary view is a stored query. It costs nothing to keep, is always current, and runs its full definition on every read — so it renames complexity rather than removing it. It is the right choice when the underlying query is already fast and you want to stop repeating it in application code.

A materialized view is a stored result. It costs disk and a refresh job, is stale between refreshes, and turns an expensive query into a table scan. It is the right choice when the query is genuinely expensive, the result is shared across callers, and staleness is acceptable.

An ordinary table with an upsert job is a stored result you maintain yourself. It costs the same disk plus the code to keep it current, and in exchange it can be updated incrementally, partitioned, and indexed per partition. It is the right choice once the materialized view’s full recompute becomes the bottleneck.

The progression is one-directional in practice: views become materialized views when they get slow, and materialized views become tables when they get large. Knowing that in advance makes the first two choices easier, because neither is a commitment.

Endpoint latency before and after caching The district coverage endpoint: 3,800 milliseconds live, 41 milliseconds from the cache, and 96 seconds for the refresh that runs hourly in the background. Where the work moves when you cache it live query 3,800 ms · paid by every caller from the cache 41 ms · paid by every caller refresh 96 s · paid once an hour, by nobody waiting Caching does not remove the work. It moves it off the path where a person is waiting for it.

Common Failure Modes and Fixes

cannot refresh materialized view concurrently — the view has no unique index. Add one on a column combination that is genuinely unique in the output; if none exists, add a generated surrogate.

The refresh job overlaps itself. A refresh that takes longer than its schedule interval eventually has two running at once, each doubling the load. Take an advisory lock at the start of the job and skip if it is held.

The view is stale and nobody noticed. A failing cron job produces no error anybody sees; the data simply stops changing. Alert on now() - computed_at exceeding the agreed staleness, which catches a failed job, a stuck lock and a silently disabled schedule with one check.

Disk usage doubled during refresh. Expected for the concurrent form. Size the volume for the view plus its largest refresh, not for the view alone.

Operating a Cache Over Time

A materialized view is a small piece of infrastructure, and like any infrastructure it needs an owner and a set of expectations written down. Four of them matter.

Who is allowed to depend on it. A cache built for one endpoint tends to acquire other consumers — a report, an export, a colleague’s notebook — each of which inherits its staleness without knowing. Naming the view for its purpose (district_coverage_hourly rather than district_coverage) puts the contract in the name where a new consumer will see it.

What happens when it is stale. The endpoint should degrade in a defined way rather than an accidental one. Serving stale data with an as_of field is usually right. Serving an error is occasionally right. Silently falling back to the live query is almost never right, because the fallback is the slow path that caused the cache to exist, and it will arrive exactly when the system is already under stress.

When it stops being worth it. Caches outlive their reasons. An endpoint gets rewritten, a filter gets pushed down, an index gets added, and the view carries on refreshing for a query nobody runs. A quarterly review of idx_scan and seq_scan on every materialized view takes minutes and occasionally deletes a job, a schedule and several gigabytes.

How it is rebuilt from nothing. The definition belongs in a migration, not in someone’s psql history. A cache that cannot be recreated on a fresh database is a piece of production state with no source of truth, and it will be discovered during the first disaster-recovery exercise.

None of this is specific to spatial data. What is specific is the size: a materialized view holding unioned geometry can rival the source table, so the review question — is this still earning its disk? — has a bigger answer here than for a cache of scalar aggregates.

Two questions before building one

Two short questions settle most cache proposals before any SQL is written.

Who else runs this query? A cache is worthwhile in proportion to how often the same expensive answer is recomputed. One nightly report does not need one; an endpoint called four thousand times an hour with identical results does. If the answer is “only this one job, once a day”, the honest conclusion is that the query is slow and nobody is waiting, which is not a problem worth solving.

What is the acceptable age of the answer? If nobody will commit to a number, the cache has no contract and its staleness will be discovered as a bug rather than agreed as a design. A number — five minutes, an hour, until the nightly import — turns the refresh schedule, the alert threshold and the API’s as_of field into consequences of one decision rather than three separate guesses.

Verification

sql
-- is the cache current, by its own record?
SELECT max(computed_at) AS last_refresh,
       now() - max(computed_at) AS staleness
FROM district_coverage;

-- what does the cache actually cost to hold?
SELECT pg_size_pretty(pg_total_relation_size('district_coverage')) AS on_disk;

-- and is the endpoint using it, rather than the live query?
EXPLAIN (ANALYZE, BUFFERS)
SELECT district_name, parcel_count
FROM district_coverage
WHERE district_name ILIKE 'north%';

The third check matters more than it looks: an application that still runs the original aggregate somewhere — in an export, a report, a stale code path — is paying for both, and the cache has bought nothing except disk.