PostGIS Materialized Views vs Redis Query Caching

← Back to Materialized Views for Spatial Aggregations

When a spatial aggregation is too expensive to run per request, you can precompute it as a PostGIS materialized view or cache its serialized response in Redis — and the right choice depends entirely on how clients query the result.

# Context & when to use

Both techniques exist to avoid re-running an expensive computation. But they cache different things at different layers. A materialized view stores the aggregated rows on disk inside Postgres, still queryable with SQL and spatial indexes. A Redis cache stores the finished response bytes in memory, keyed by a single lookup string. That difference drives every trade-off below.

Reach for a materialized view when one heavy aggregation feeds many query shapes. A dissolved administrative boundary or a per-cell heatmap grid gets filtered by bounding box, joined to attribute tables, sorted by count, and clipped to different zoom levels — all off the same precomputed result, each using the view’s GiST index. Redis cannot do any of that; it can only return the exact response you stored under the exact key you ask for.

Reach for Redis when the same identical response is requested over and over with a short acceptable staleness. A dashboard tile that every user loads on the same map view, a “features in this fixed region” call behind a popular UI element — these are hot key-value reads where a TTL of 30–120 seconds is fine, and a memory GET beats even an indexed Postgres scan. The two are frequently combined: materialize the aggregate to make cold reads cheap, then put a short Redis TTL over the hottest bounding boxes for sub-millisecond hot reads.

# Comparison at a glance

# Runnable implementation

The same use case — a 250 m heatmap grid — implemented both ways so the difference is concrete.

-- ============================================================
-- OPTION A: Materialized view (query the aggregate many ways)
-- ============================================================
CREATE MATERIALIZED VIEW mv_ping_heatmap AS
SELECT
    row_number() OVER ()                          AS cell_id,
    ST_SnapToGrid(ST_Transform(geom, 3857), 250)  AS cell_geom,
    count(*)                                       AS ping_count
FROM gps_pings
WHERE captured_at >= now() - interval '90 days'
GROUP BY ST_SnapToGrid(ST_Transform(geom, 3857), 250)
WITH DATA;

-- GiST for spatial predicates, UNIQUE for concurrent refresh
CREATE INDEX idx_mv_ping_heatmap_geom ON mv_ping_heatmap USING GIST (cell_geom);
CREATE UNIQUE INDEX uq_mv_ping_heatmap_cell ON mv_ping_heatmap (cell_id);
ANALYZE mv_ping_heatmap;

-- A read can now filter the SAME aggregate by ANY bounding box, cheaply:
-- SELECT cell_id, ping_count FROM mv_ping_heatmap
--   WHERE cell_geom && ST_MakeEnvelope(:minx,:miny,:maxx,:maxy, 3857);

# ============================================================
# OPTION B: Redis query cache (hot identical response, TTL)
# ============================================================
# The aggregation still runs in Postgres on a miss; Redis stores the
# finished GeoJSON so repeated identical requests skip the query entirely.
import json
from redis.asyncio import Redis
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import text

HEATMAP_AGG = text("""
    SELECT jsonb_build_object(
        'type', 'FeatureCollection',
        'features', coalesce(jsonb_agg(jsonb_build_object(
            'type', 'Feature',
            'geometry', ST_AsGeoJSON(ST_Transform(cell_geom, 4326), 6)::jsonb,
            'properties', jsonb_build_object('ping_count', ping_count)
        )), '[]'::jsonb)
    )
    FROM ST_SnapToGrid(...) -- same aggregation as Option A, run live on miss
""")

async def heatmap_cached(
    tile_key: str, redis: Redis, db: AsyncSession, ttl: int = 60
) -> dict:
    cache_key = f"heatmap:{tile_key}"          # ONE key → ONE response
    hit = await redis.get(cache_key)
    if hit:
        return json.loads(hit)                 # ~0.3 ms, no Postgres round-trip

    row = await db.execute(HEATMAP_AGG)        # miss: pay the aggregation cost
    payload = row.scalar()
    await redis.setex(cache_key, ttl, json.dumps(payload))  # TTL-bounded freshness
    return payload

The tags-and-keys strategy for spatial Redis keys is covered in configuring Redis cache tags for bounding-box queries. Note that Option B still runs the full aggregation on every cache miss — Redis does nothing to make the underlying query cheaper, which is exactly why stacking it over Option A is common.

# Key parameters & options

# Gotchas & failure modes

• Double-caching drift. Stacking a Redis TTL over a materialized view means a client sees data that is stale by up to refresh_interval + ttl. A 5-minute refresh plus a 60 s TTL yields up to 6 minutes of staleness in the worst case. Keep the TTL small relative to the refresh interval, and never let the TTL exceed it, or you cache a soon-to-be-replaced snapshot.

• Redis does not reduce aggregation cost. On a cache miss, Option B runs the full ST_SnapToGrid + GROUP BY live — the first request after every TTL expiry pays the multi-second cost and can stampede under concurrency. A materialized view eliminates that cost for all reads. If misses are expensive and frequent, materialize.

• cannot refresh materialized view concurrently. Forgetting the UNIQUE index breaks concurrent refresh entirely. See scheduling concurrent refresh for the full requirement.

• Redis eviction under noeviction. If Redis fills and the policy is noeviction, SETEX starts returning OOM command not allowed when used memory > 'maxmemory' and caching silently fails open to the database. Use an LRU/LFU eviction policy for pure cache workloads.

• Cache key explosion. Keying Redis on arbitrary floating-point bounding boxes gives near-zero hit rate — every request is a unique key. Snap requests to a tile grid before keying, so many clients share cache entries. A view has no such problem because it indexes the whole aggregate.

• Materialized view can’t do sub-second freshness. If the aggregate must reflect writes within seconds, neither a periodic refresh nor a TTL fits well; route those reads to an on-the-fly query against the base tables and accept the cost.

# Verification

Confirm each layer is doing its job:

-- View: index scan, not seq scan, over the precomputed aggregate
EXPLAIN (ANALYZE, BUFFERS)
SELECT cell_id, ping_count FROM mv_ping_heatmap
WHERE cell_geom && ST_MakeEnvelope(-8250000, 4900000, -8200000, 4950000, 3857);
-- expect: Index Scan using idx_mv_ping_heatmap_geom

# Redis: second identical request should be a hit (much faster, no DB log entry)
redis-cli TTL "heatmap:z10:x301:y384"     # remaining seconds; -2 means missing/expired
redis-cli DEBUG OBJECT "heatmap:z10:x301:y384" | grep -o 'serializedlength:[0-9]*'

If the view query shows a Seq Scan, run ANALYZE mv_ping_heatmap;. If the Redis TTL is always -2, your key granularity is too fine to ever hit — snap to a tile grid.

# Related

• Materialized Views for Spatial Aggregations — build, index, and serve the precomputed aggregate
• Scheduling Concurrent Refresh of Spatial Materialized Views — keep view staleness bounded with pg_cron
• Redis Caching for Spatial Queries — TTL-based response caching and cache-tag invalidation

← Back to Materialized Views for Spatial Aggregations