Write-Aware Refresh Benchmarks

Methodology

Materialized views trade data freshness for read performance. The question is how to manage that tradeoff: refresh too often and you waste database resources; refresh too rarely and your reads serve stale data. These benchmarks compare four refresh strategies under a sustained mixed workload (50 writes/second, 200 reads/second).

Database load is measured as a percentage of baseline — where 100% is the CPU consumed by the raw query workload without materialized views. Values above 100% mean the refresh overhead exceeds the query savings.

The refresh dilemma

REFRESH MATERIALIZED VIEW CONCURRENTLY is not free. For the test matview (a three-table join aggregation), each refresh takes approximately 180ms of database CPU. At a fixed 1-second interval, that means 18% of every second is spent refreshing — even during periods with zero writes.

-- Fixed-interval refresh: runs every N seconds regardless of writes
-- Problem: wastes CPU when no data changed, stale when writes are bursty
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_revenue_by_region;
-- Cost: ~180ms per refresh on this dataset

-- Gold Lapel write-aware refresh:
-- 1. Monitors INSERT/UPDATE/DELETE on source tables
-- 2. Marks affected views as "dirty"
-- 3. Refreshes only dirty views, after a configurable debounce
-- 4. Coalesces rapid writes into a single refresh
--
-- Result: refreshes happen ONLY when data changes,
-- and bursty writes trigger ONE refresh, not many

Results

Analysis

The fixed-interval strategies expose a fundamental tension:

• 60-second interval: Low overhead (85% load) but 30 seconds of average staleness. Reads may serve data that is a full minute old during periods of active writes.
• 10-second interval: Moderate staleness (5s) but the refresh overhead (112%) actually exceeds the savings from the matview. The database is doing more work than it would without the view.
• 1-second interval: Near-real-time freshness (0.5s) but catastrophic overhead (340%). The database spends more time refreshing views than executing queries. This approach is not viable for production.

Gold Lapel's write-aware strategy breaks the tension by making refresh decisions based on actual writes rather than wall-clock time:

• During active writes (50/s): The view is refreshed approximately once every 1.2 seconds — but only because data is changing, not because a timer fired. The debounce window coalesces rapid writes.
• During write pauses: No refreshes occur. Zero wasted CPU. The view remains valid because no underlying data has changed.
• During write bursts: The debounce coalesces hundreds of writes into a single refresh, avoiding the refresh storm that fixed intervals would trigger.

Burst behavior

Real workloads are bursty. Batch imports, end-of-day processing, and migration scripts produce write patterns that fixed intervals handle poorly.

-- Scenario: 500 orders inserted in a 2-second burst
-- (e.g., batch import, end-of-day processing)

-- Fixed 1s interval: triggers 2 refreshes during the burst
--   (360ms of refresh work, view stale between refreshes)

-- Fixed 10s interval: view is stale for up to 10s after burst
--   (180ms of refresh work, but 10s of serving stale data)

-- Write-aware: debounce coalesces the burst, triggers 1 refresh
--   after the burst settles (180ms of refresh work, 2.4s max stale)
--   During steady-state writes: refreshes every ~1.2s on average

Write-aware refresh adapts to the write pattern. During steady writes, it maintains ~1.2s freshness. During bursts, it waits for the burst to settle before refreshing once. During quiet periods, it does nothing.

The tradeoff chart

How to reproduce

The benchmark suite includes a sustained workload generator that produces configurable write rates, read rates, and burst patterns.

# Clone the benchmark suite
git clone https://github.com/goldlapel/benchmarks
cd benchmarks/write-aware-refresh

# Start test environment
docker compose up -d

# Run all strategies (30 min each)
./run.sh --strategies fixed-60s,fixed-10s,fixed-1s,write-aware \
  --write-rate 50 --read-rate 200 --duration 1800

# Generate comparison report
./compare.sh results/