Stop Refreshing Materialized Views — Start Streaming Them

A ward coordinator opens the patient flow dashboard and sees patients still marked in theatre who were discharged earlier that morning. The materialized view behind that tile last completed a full refresh well before the current shift — minutes or hours of clinical reality that never reached the screen. In large acute trusts running millions of encounter rows across multiple source databases, batch refresh is not a scheduling inconvenience. It is a consistency class you chose without documenting it.

The problem space: pre-computed read models at hospital scale

Operational dashboards, FHIR aggregation layers, and population-health tiles all depend on the same primitive: a query result stored as a table so reads stay cheap. At hospital scale, four constraints usually coexist:

• Corpus: millions of encounter, observation, and medication rows joined for a single patient-timeline projection
• Ingestion: sustained row-level changes at morning peak from PAS, LIS, and theatre scheduling
• Read load: dozens of concurrent dashboard users expecting sub-second tile response
• Source systems: Oracle PAS (authoritative encounters), Postgres LIS (results), SQL Server theatre module (status transitions)

When those constraints coexist, the difference between a nightly batch job and an incrementally maintained view is not a tooling preference. It is whether clinicians act on stale state. That boundary sits next to the consistency routing decisions we document in read replicas are not clinical truth — another layer where the read path must declare its freshness contract explicitly.

Why the naive approach fails

The default pattern in Postgres, SQL Server, and Oracle is deceptively simple: define a materialized view, schedule REFRESH MATERIALIZED VIEW, point Grafana or Power BI at the result. Teams pick a cron interval that feels safe — every 15 minutes — and move on. The failure mode arrives quietly on a Monday morning surge.

-- Naive: full recompute on a cron schedule (Postgres)
CREATE MATERIALIZED VIEW ward_patient_flow AS
SELECT
    e.encounter_id,
    e.patient_id,
    e.ward_code,
    e.admission_ts,
    e.discharge_ts,
    t.theatre_status,
    t.estimated_completion,
    COUNT(o.observation_id) AS pending_results
FROM pas.encounters e
LEFT JOIN theatre.sessions t ON t.encounter_id = e.encounter_id
LEFT JOIN lis.observations o
    ON o.encounter_id = e.encounter_id AND o.result_status = 'preliminary'
WHERE e.discharge_ts IS NULL
GROUP BY 1,2,3,4,5,6,7;

-- pg_cron job — looks reasonable until you measure it
SELECT cron.schedule(
    'refresh-ward-flow',
    '*/15 * * * *',
    $$REFRESH MATERIALIZED VIEW CONCURRENTLY ward_patient_flow$$
);

On a production-sized extract, that refresh is not a background blip. Concurrent refresh duration scales with corpus size and concurrent writes to source tables. Non-concurrent refresh locks the materialized view for reads entirely — unacceptable for a ward board on a short cron interval. During refresh, even CONCURRENTLY mode holds elevated I/O and can degrade tile query latency. Worse, the dashboard shows a staleness window equal to the refresh interval plus refresh duration — if a long refresh overlaps the next cron tick, clinicians see state that lags reality by the sum of both.

Batch materialized views also inherit a structural blind spot: they re-aggregate everything when only a fraction of rows changed. CPU cost scales with corpus size, not change rate. At multi-million-row scale, you pay for a full recompute on every tick to reflect a tiny delta.

Engineered approach: incremental materialized views and CDC

Streaming databases treat a materialized view as a continuously maintained pipeline, not a snapshot you periodically rebuild. RisingWave’s materialized view model describes the contract precisely: on write, only affected rows update; queries read persisted state from serving nodes without recomputing the join graph.

The engineered shape has three layers: CDC from source systems, a streaming SQL projection, and a serving interface that downstream dashboards query.

-- Layer 1: CDC sources (Debezium → RisingWave/Kafka)
CREATE SOURCE pas_encounters WITH (
    connector = 'kafka',
    topic = 'cdc.pas.encounters',
    properties.bootstrap.server = 'kafka.internal:9092'
) FORMAT DEBEZIUM ENCODE JSON;

CREATE SOURCE theatre_sessions WITH (
    connector = 'kafka',
    topic = 'cdc.theatre.sessions'
) FORMAT DEBEZIUM ENCODE JSON;

CREATE SOURCE lis_observations WITH (
    connector = 'kafka',
    topic = 'cdc.lis.observations'
) FORMAT DEBEZIUM ENCODE JSON;

-- Layer 2: incrementally maintained projection
CREATE MATERIALIZED VIEW ward_patient_flow_live AS
SELECT
    e.encounter_id,
    e.patient_id,
    e.ward_code,
    e.admission_ts,
    e.discharge_ts,
    t.theatre_status,
    t.estimated_completion,
    COUNT(o.observation_id) FILTER (WHERE o.result_status = 'preliminary') AS pending_results
FROM pas_encounters e
LEFT JOIN theatre_sessions t ON t.encounter_id = e.encounter_id
LEFT JOIN lis_observations o ON o.encounter_id = e.encounter_id
WHERE e.discharge_ts IS NULL
GROUP BY e.encounter_id, e.patient_id, e.ward_code, e.admission_ts,
         e.discharge_ts, t.theatre_status, t.estimated_completion;

-- Layer 3: downstream sink to existing BI stack (optional)
CREATE SINK ward_flow_postgres FROM ward_patient_flow_live
WITH (connector = 'jdbc', jdbc.url = 'jdbc:postgresql://bi-reader:5432/ops');

For teams not yet on a streaming warehouse, the same pattern ports to Postgres with incremental refresh extensions or to a Flink job — but the invariant holds: propagate deltas, do not re-scan the corpus. Nathan Cole’s walkthrough of building a real-time FHIR read model without rewriting hospital systems arrives at the same conclusion from the API side: maintain resources incrementally from out-of-order clinical events rather than assembling them on every request.

What to measure: batch versus incremental

Comparative proof belongs on anonymised production volumes in staging — replay CDC traffic, run parallel readers against both paths, and record end-to-end lag from source commit to dashboard visibility. Do not trust vendor slide decks; measure on your corpus.

Metric	Batch REFRESH (cron)	Incremental MV + CDC
Staleness clinicians observe	Cron interval + refresh duration	Propagation lag from source commit
Tile query latency during refresh	Often elevated — I/O contention	Steady — reads hit pre-computed state
CPU per change event	Full scan of corpus each tick	Partition-local delta only
Ops incident class	Wrong patient location displayed	Lag alert if CDC falls behind
What to instrument	Refresh duration, lock waits, cron overlap	Consumer lag, MV freshness, query p95

Index choice on the batch path still matters if you must keep it temporarily — partial indexes on active encounters can shorten refresh duration, but the fundamental cost remains O(corpus), not O(changes). Load-test approach for comparative benchmarking:

-- Synthetic read load during refresh (pgbench custom script)
\set encounter_id random(1, 14200000)
SELECT ward_code, theatre_status, pending_results
FROM ward_patient_flow
WHERE encounter_id = :encounter_id;

-- Capture p50/p95/p99 every 30s via pg_stat_statements
-- Run A: batch MV during REFRESH CONCURRENTLY
-- Run B: incremental MV during CDC replay at production change rate

Recommended migration steps:

1. Provision Kafka topics and Debezium connectors with initial snapshot
2. Create incremental MV; validate row counts against batch MV on a schedule you define
3. Alert on consumer lag and MV freshness thresholds agreed with clinical informatics
4. Route dashboard queries to serving layer; document consistency class as near-real-time in runbooks
5. Sign-off: clinical informatics lead plus platform SRE after shadow comparison report

Ops gates and who signs off

Treat materialized view freshness as an SLO, not a hope. Proposed gates for a large acute deployment:

• Freshness SLO: propagation lag for ward-flow tiles — define p95 target with clinical informatics; P2 incident if breached for sustained period
• Query SLO: p95 read latency on serving layer — page if degraded beyond agreed threshold
• CDC lag SLO: consumer lag at p99 — freeze deployments if breached during clinical hours
• Sign-off: clinical informatics lead (stale data risk), platform SRE (lag dashboards), data protection officer if pseudonymised extracts feed the MV

Hospital data rarely arrives in tidy order — incremental pipelines must handle late and out-of-order events with watermarks or retractions. Debezium slot lag on Oracle PAS is the operational metric that matters during dual-run; capacity planning belongs in the same CAB paper as the dashboard go-live. Batch refresh is a defensible choice for monthly finance aggregates; it is a poor fit for ward flow or any read model where a clinician changes course based on the number on screen.