Scheduling Concurrent Refresh of Spatial Materialized Views

← Back to Materialized Views for Spatial Aggregations

Refresh a PostGIS materialized view on a schedule without ever blocking readers — using REFRESH MATERIALIZED VIEW CONCURRENTLY, a UNIQUE index, and a lock that stops two refreshes from overlapping.

# Context & when to use

A materialized view is a snapshot: it only reflects new base-table data after you refresh it. A plain REFRESH MATERIALIZED VIEW takes an ACCESS EXCLUSIVE lock, so every read against the view blocks until the rebuild finishes — unacceptable when a heatmap or boundary endpoint is serving live traffic. REFRESH MATERIALIZED VIEW CONCURRENTLY solves that: it builds a fresh copy in the background, diffs it against the current view through a UNIQUE index, and applies only the delta, so readers keep hitting the old snapshot until the swap completes.

Use concurrent refresh when the view backs a read endpoint that cannot tolerate downtime and your freshness SLA is measured in minutes, not seconds. The trade-off is cost: CONCURRENTLY builds a full temp copy and does a row-by-row diff, so it is slower and does substantially more I/O than a plain refresh. If reads can tolerate a brief lock in an off-peak window, plain refresh is cheaper. For sub-second freshness, neither fits — route those reads to a live query instead.

Preconditions: the view has at least one non-partial UNIQUE index (the hard requirement for CONCURRENTLY), the refresh completes comfortably within its schedule interval, and refreshes run on a dedicated maintenance connection, never on the request path.

# Refresh timeline

# Runnable implementation

Wrap the refresh in a function that takes a session-level advisory lock (so overlapping runs are skipped, not queued) and stamps a bookkeeping table the read endpoint can surface as last_refresh. Then schedule that function with pg_cron.

-- 0. Prerequisite: the view MUST have a non-partial UNIQUE index, or
--    REFRESH ... CONCURRENTLY fails with:
--    "cannot refresh materialized view concurrently"
CREATE UNIQUE INDEX IF NOT EXISTS uq_mv_ping_heatmap_cell
    ON mv_ping_heatmap (cell_id);

-- 1. Bookkeeping table the API reads to report staleness
CREATE TABLE IF NOT EXISTS mv_refresh_log (
    view_name     text PRIMARY KEY,
    last_refresh  timestamptz,
    duration_ms   integer
);

-- 2. Refresh function: advisory lock prevents overlap; timing is recorded.
CREATE OR REPLACE FUNCTION refresh_ping_heatmap()
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    started  timestamptz := clock_timestamp();
    got_lock boolean;
BEGIN
    -- try-lock: returns false immediately if another refresh holds it.
    -- A fixed key (871501) identifies THIS view's refresh across sessions.
    got_lock := pg_try_advisory_lock(871501);
    IF NOT got_lock THEN
        RAISE NOTICE 'refresh_ping_heatmap: previous run still active, skipping';
        RETURN;
    END IF;

    BEGIN
        REFRESH MATERIALIZED VIEW CONCURRENTLY mv_ping_heatmap;

        INSERT INTO mv_refresh_log(view_name, last_refresh, duration_ms)
        VALUES (
            'mv_ping_heatmap',
            clock_timestamp(),
            extract(milliseconds FROM clock_timestamp() - started)::int
        )
        ON CONFLICT (view_name) DO UPDATE
            SET last_refresh = excluded.last_refresh,
                duration_ms  = excluded.duration_ms;
    EXCEPTION WHEN OTHERS THEN
        PERFORM pg_advisory_unlock(871501);  -- always release on error
        RAISE;
    END;

    PERFORM pg_advisory_unlock(871501);
END;
$$;

-- 3. Schedule every 5 minutes with pg_cron (runs inside Postgres).
--    Requires pg_cron in shared_preload_libraries + CREATE EXTENSION pg_cron.
SELECT cron.schedule(
    'refresh-ping-heatmap',       -- job name (unique)
    '*/5 * * * *',                -- standard cron: every 5 minutes
    $$SELECT refresh_ping_heatmap()$$
);

If you cannot install pg_cron (managed Postgres without the extension, for example), drive the same function from an external scheduler on a dedicated maintenance connection:

# scheduler.py — APScheduler fallback when pg_cron is unavailable.
# Runs in a small sidecar process, NOT inside a FastAPI request worker.
import asyncio
import asyncpg
from apscheduler.schedulers.asyncio import AsyncIOScheduler

DSN = "postgresql://maint_user@db:5432/geo"  # a maintenance role, not the API role

async def refresh_heatmap() -> None:
    conn = await asyncpg.connect(DSN)
    try:
        # The function itself takes the advisory lock, so even if two
        # scheduler instances fire, only one refresh actually runs.
        await conn.execute("SELECT refresh_ping_heatmap()")
    finally:
        await conn.close()

def main() -> None:
    scheduler = AsyncIOScheduler(timezone="UTC")
    # coalesce + max_instances=1 stop APScheduler queuing missed runs.
    scheduler.add_job(
        refresh_heatmap, "interval", minutes=5,
        coalesce=True, max_instances=1, id="refresh-ping-heatmap",
    )
    scheduler.start()
    asyncio.get_event_loop().run_forever()

if __name__ == "__main__":
    main()

Reducing base-table churn between refreshes — for example by fronting the read endpoint with a short Redis TTL — is discussed alongside the materialized view vs Redis comparison.

# Key parameters & options

# Gotchas & failure modes

• cannot refresh materialized view concurrently with detail Create a unique index with no WHERE clause on one or more columns of the materialized view. The view has no qualifying UNIQUE index, or the only one is partial (has a WHERE). Add a full, non-partial CREATE UNIQUE INDEX on a stable key before scheduling.

• Overlapping refreshes double the load. If a refresh takes longer than its interval and you have no lock, pg_cron (or a second scheduler replica) starts another one, and now two concurrent refreshes each build a temp copy and compete for I/O. The pg_try_advisory_lock guard above rejects the second run cleanly — monitor for the previous run still active, skipping notice; frequent skips mean your interval is too tight.

• Long refresh blocks autovacuum on the base tables. A CONCURRENTLY refresh holds a long transaction that reads the base tables; while it runs, autovacuum cannot remove dead tuples newer than the refresh’s snapshot, so bloat accumulates on high-write tables like gps_pings. Keep refreshes short (narrow the aggregation window, or refresh less often) and watch n_dead_tup in pg_stat_user_tables.

• CONCURRENTLY is slower and heavier than plain refresh. It builds a temp copy and diffs it — often 2–4× the wall-clock and roughly double the temporary disk of a plain refresh. Do not reach for CONCURRENTLY on an off-peak nightly job where a brief lock is fine.

• Advisory lock leaked across a pooled connection. Session-level advisory locks are tied to the backend connection. If the refresh runs through a transaction-mode connection pool, the lock may be released on an unexpected boundary. Run refreshes on a direct maintenance connection, not through PgBouncer transaction pooling.

• pg_cron runs in the wrong database. pg_cron jobs execute in the database where the extension was created (often postgres) unless you set cron.database_name or pass a target. If the job silently does nothing, confirm it targets the database that owns the view.

# Verification

-- 1. Confirm the job is registered and active
SELECT jobid, schedule, command, active FROM cron.job
WHERE jobname = 'refresh-ping-heatmap';

-- 2. Inspect recent runs: status and duration
SELECT status, start_time, end_time,
       end_time - start_time AS duration
FROM cron.job_run_details
WHERE jobid = (SELECT jobid FROM cron.job WHERE jobname = 'refresh-ping-heatmap')
ORDER BY start_time DESC
LIMIT 5;

-- 3. Confirm freshness from the bookkeeping table (what the API reports)
SELECT view_name, last_refresh, duration_ms,
       now() - last_refresh AS staleness
FROM mv_refresh_log
WHERE view_name = 'mv_ping_heatmap';

A duration_ms creeping toward the schedule interval is the early warning to widen the interval or shrink the view. A status of failed in cron.job_run_details usually points back to a missing UNIQUE index or a base-table lock conflict. Manually time one refresh to establish the baseline:

\timing on
SELECT refresh_ping_heatmap();
-- Time: 8631.204 ms  ← the interval (5 min) must dwarf this

# Related

• Materialized Views for Spatial Aggregations — create and index the view this refresh keeps current
• PostGIS Materialized Views vs Redis Query Caching — when a scheduled refresh beats a TTL cache
• Redis Caching for Spatial Queries — front the view with a short TTL to smooth base-table churn

← Back to Materialized Views for Spatial Aggregations