Caching Tile Queries With Materialized Views

Problem Statement

A vector-tile endpoint serves a national parcel dataset. At zoom 8 each tile covers thousands of parcels averaging four hundred vertices, and the endpoint spends nearly all of its time in ST_SimplifyPreserveTopology — work that produces the same answer for every request that touches those parcels, recomputed from scratch each time. This page applies materialized-view caching to that specific shape: pre-simplified geometry per zoom band, indexed and refreshed on a schedule.

Why the Naive Approach Fails

The straightforward tile query does everything per request:

sql
SELECT ST_AsMVT(t, 'parcels') FROM (
    SELECT id, ST_AsMVTGeom(
               ST_SimplifyPreserveTopology(geom, %(tolerance)s),
               ST_TileEnvelope(%(z)s, %(x)s, %(y)s)) AS geom
    FROM parcels
    WHERE geom && ST_Transform(ST_TileEnvelope(%(z)s, %(x)s, %(y)s), 4326)
) t;

The && filter is index-driven and fast. The simplification is not indexable at all — it is pure computation over every surviving vertex, and at low zoom the surviving set is enormous.

Where a tile request spends its time by zoom Three stacked bars. At zoom 8 the index scan takes 40 milliseconds and simplification takes 1,860. At zoom 12 the split is 22 against 240. At zoom 16 it is 8 against 14, where caching would buy almost nothing. Simplification cost collapses as zoom increases z8 ST_SimplifyPreserveTopology — 1,860 ms z12 240 ms — still worth caching z16 14 ms — cache it and you have added complexity for nothing index scan geometry simplification Cache the two zoom bands where the amber and red bars dominate; leave the rest alone.

Production-Ready Implementation

One view per zoom band, each holding geometry simplified to that band’s resolution:

sql
-- Band A: zooms 6–10. Tolerance in degrees, roughly a 300 m feature threshold.
CREATE MATERIALIZED VIEW parcels_z6_10 AS
SELECT
    p.id,
    p.parcel_ref,
    ST_SimplifyPreserveTopology(p.geom, 0.0027)  AS geom,
    now()                                        AS computed_at
FROM parcels p
WHERE ST_Area(p.geom::geography) > 5000          -- drop features invisible at this zoom
WITH DATA;

CREATE UNIQUE INDEX parcels_z6_10_pkey ON parcels_z6_10 (id);
CREATE INDEX parcels_z6_10_geom_idx ON parcels_z6_10 USING gist (geom);

-- Band B: zooms 11–13, finer tolerance and no area filter.
CREATE MATERIALIZED VIEW parcels_z11_13 AS
SELECT p.id, p.parcel_ref,
       ST_SimplifyPreserveTopology(p.geom, 0.00017) AS geom,
       now() AS computed_at
FROM parcels p
WITH DATA;

CREATE UNIQUE INDEX parcels_z11_13_pkey ON parcels_z11_13 (id);
CREATE INDEX parcels_z11_13_geom_idx ON parcels_z11_13 USING gist (geom);

Dropping small features at low zoom is as valuable as the simplification itself: a parcel under five thousand square metres occupies less than a pixel at zoom 8, so shipping it costs bytes and renders nothing.

The endpoint picks its source by zoom:

python
from dataclasses import dataclass

@dataclass(frozen=True)
class ZoomBand:
    lo: int
    hi: int
    relation: str

BANDS = (
    ZoomBand(6, 10, "parcels_z6_10"),
    ZoomBand(11, 13, "parcels_z11_13"),
    ZoomBand(14, 22, "parcels"),          # raw table: already cheap up here
)

def relation_for(z: int) -> str:
    for band in BANDS:
        if band.lo <= z <= band.hi:
            return band.relation
    raise ValueError(f"zoom {z} outside the served range")


TILE_SQL = """
    SELECT ST_AsMVT(t, 'parcels') FROM (
        SELECT id, parcel_ref,
               ST_AsMVTGeom(geom, ST_Transform(env.geom, 4326)) AS geom
        FROM {relation}, LATERAL (
            SELECT ST_TileEnvelope(%(z)s, %(x)s, %(y)s) AS geom
        ) env
        WHERE geom && ST_Transform(env.geom, 4326)
    ) t
"""

def tile(conn, z: int, x: int, y: int) -> bytes:
    sql = TILE_SQL.format(relation=relation_for(z))   # relation is from a fixed set
    with conn.cursor() as cur:
        cur.execute(sql, {"z": z, "x": x, "y": y})
        return cur.fetchone()[0]

The relation name is interpolated from a closed set defined in code, never from request input — the zoom is validated against the bands before it reaches the format string.

Which relation serves which zoom Zoom levels 6 to 22 across the top, mapped to three sources: the coarse materialized view for zooms 6 to 10, the finer one for 11 to 13, and the raw parcels table from 14 upward. Each source is annotated with its row count and typical response time. One endpoint, three sources z6 – z10 parcels_z6_10 1.2M rows after the area filter tolerance 0.0027° 1,900 ms → 38 ms z11 – z13 parcels_z11_13 4.1M rows, all features tolerance 0.00017° 260 ms → 24 ms z14 + parcels the raw table, no cache few features per tile 14 ms — already fine Two caches, not sixteen. The bands are chosen so the simplification tolerance is imperceptible within each one. Tile payload size by source Bytes per tile at zoom 8 from three sources: 240 kilobytes from the raw table, 38 kilobytes from the coarse cache, and 31 kilobytes from the cache with the area filter applied. Tile payload at zoom 8 raw table 240 KB · every vertex, every feature cache, simplified 38 KB cache + area filter 31 KB · sub-pixel features dropped The mobile client notices the payload before it notices the latency.

Configuration and Tuning Knobs

Tolerance per band should be derived from tile resolution, not guessed. A tile is 4096 units across and spans 360/2^z degrees, so a tolerance of about 360 / (2**z * 4096) * 2 degrees keeps the error under two tile units — invisible, and aggressive enough to matter.

The area filter at low zoom is the bigger win on parcel-like data. Set the threshold so a feature occupies at least one square pixel at the band’s lowest zoom, and expect to remove most of the rows.

Refresh schedule follows the source data. A cadastral dataset updated nightly wants a nightly refresh right after the import, not an hourly one. Tie the refresh to the import job rather than to the clock where you can.

maintenance_work_mem affects the refresh, which builds indexes on the new contents. Raising it for the refresh session shortens the window measurably.

Verification Steps

sql
-- the cache is materially smaller than the source, or it is not doing its job
SELECT 'parcels'        AS relation, pg_size_pretty(pg_total_relation_size('parcels'))
UNION ALL
SELECT 'parcels_z6_10',  pg_size_pretty(pg_total_relation_size('parcels_z6_10'))
UNION ALL
SELECT 'parcels_z11_13', pg_size_pretty(pg_total_relation_size('parcels_z11_13'));

-- vertex reduction achieved at each band
SELECT 'raw'   AS band, round(avg(ST_NPoints(geom))) AS avg_vertices FROM parcels
UNION ALL
SELECT 'z6_10', round(avg(ST_NPoints(geom))) FROM parcels_z6_10
UNION ALL
SELECT 'z11_13', round(avg(ST_NPoints(geom))) FROM parcels_z11_13;

And visually: render the same tile from the cache and from the raw table at the band’s highest zoom, and compare. The tolerance is right when the two are indistinguishable at that zoom and clearly different at the next one up.

Keeping the cached geometry honest

A cache of simplified geometry is a derived dataset, and derived datasets drift. Two checks keep it trustworthy.

The first is a row-count reconciliation after each refresh: the cached view should hold exactly the rows its filter implies, and a divergence means the filter and the source disagree — usually because a feature’s area crossed the threshold. The second is a validity check, because simplification can produce invalid polygons even with the topology-preserving variant when the input was itself invalid.

sql
SELECT count(*) FILTER (WHERE NOT ST_IsValid(geom)) AS invalid_cached
FROM parcels_z6_10;

Any non-zero result points back at the source table, not at the cache. Fixing it there — see geometry validity and repair — is the durable answer; repairing inside the view definition just hides the problem behind a refresh schedule.

Gotchas Checklist

  • ST_Simplify can invalidate polygons. Use ST_SimplifyPreserveTopology in a cache that other queries will read, or you have cached invalid geometry and made it permanent.
  • The unique index must exist before the first concurrent refresh. Adding it later means a blocking refresh in between.
  • A view that filters by area needs the filter documented in the API. Callers who compare counts between zoom levels will otherwise report the difference as a bug.
  • Do not cache the tile bytes in PostgreSQL. There are orders of magnitude more tiles than features, and a CDN does that job better; cache the geometry that produces them.
  • Watch total buffer usage. Two large materialized views plus the source table can push the working set out of shared_buffers, which slows everything including the queries that were fine.

One more measurement worth taking

Before declaring the cache a success, check the response size as well as the latency. Simplification reduces vertices, and fewer vertices means smaller tiles — often by more than the latency improvement suggests. A tile that drops from 240 KB to 38 KB is a better outcome for a mobile client on a slow connection than the fifty-fold server-side speedup, and it is the number a product owner will recognise.