PostgreSQL Incremental View Maintenance for Real-Time Multi-Tenant Analytics
Materialized views in PostgreSQL are a trap for analytics dashboards. I learned this the hard way with CitizenApp's usage metrics dashboard. We'd refresh every 5 minutes across a 50GB table, burning through compute, and dashboards would still lag by 300ms+. Full table scans don't scale when you're tracking tenant behavior in real-time.
The solution isn't Redis or Snowflake. It's incremental view maintenance—computing only the rows that changed since the last refresh. With triggers and delta tables, you can push analytics queries under 500ms while keeping everything in PostgreSQL.
Why Materialized Views Alone Fail
Standard materialized views (REFRESH MATERIALIZED VIEW) rebuild the entire result set. With 9 analytics features across CitizenApp, we had queries like:
CREATE MATERIALIZED VIEW tenant_daily_usage AS
SELECT
tenant_id,
DATE(created_at) as usage_date,
COUNT(*) as total_events,
COUNT(DISTINCT user_id) as active_users,
SUM(CASE WHEN feature_type = 'ai_analysis' THEN 1 ELSE 0 END) as ai_features_used
FROM events
WHERE created_at >= NOW() - INTERVAL '90 days'
GROUP BY tenant_id, DATE(created_at);
Every 5-minute refresh scanned 50M rows to update maybe 10K rows of new data. That's wasteful. And concurrent refreshes? PostgreSQL locks the view, blocking reads.
I prefer incremental maintenance because:
- Only process yesterday's and today's events (delta approach)
- Existing view stays readable during updates
- Sub-second refresh on delta tables, even with high event volume
Incremental View Maintenance Pattern
The trick is a change log table + delta view + upsert logic. Instead of refreshing the entire view, you:
- Log changes to a delta table
- Compute only new/updated rows in a temporary result set
- Merge into the materialized view using an upsert pattern
Here's the PostgreSQL setup:
-- Track which events have been processed into the materialized view
CREATE TABLE events_changelog (
id BIGSERIAL PRIMARY KEY,
event_id BIGINT UNIQUE NOT NULL,
processed_at TIMESTAMPTZ DEFAULT NOW()
);
-- The actual materialized view (lightweight)
CREATE TABLE tenant_daily_usage_mv (
tenant_id UUID NOT NULL,
usage_date DATE NOT NULL,
total_events INT DEFAULT 0,
active_users INT DEFAULT 0,
ai_features_used INT DEFAULT 0,
last_updated TIMESTAMPTZ DEFAULT NOW(),
PRIMARY KEY (tenant_id, usage_date)
);
-- Index for fast lookups
CREATE INDEX idx_tenant_daily_usage_date
ON tenant_daily_usage_mv(tenant_id, usage_date DESC);
-- Delta view: events since last refresh
CREATE VIEW events_delta AS
SELECT e.*
FROM events e
LEFT JOIN events_changelog ec ON e.id = ec.event_id
WHERE ec.event_id IS NULL;
Why a table instead of a true materialized view? Tables support upserts. Views don't. When you refresh, you need to merge new rows without locking reads.
The Incremental Refresh Function
This function computes only unprocessed events:
CREATE OR REPLACE FUNCTION refresh_tenant_usage_incremental()
RETURNS TABLE(rows_processed INT, duration_ms INT) AS $$
DECLARE
v_start_time TIMESTAMPTZ;
v_processed_count INT := 0;
BEGIN
v_start_time := NOW();
-- Step 1: Compute deltas for the last 2 days (covers clock skew)
WITH delta_aggregates AS (
SELECT
e.tenant_id,
DATE(e.created_at) as usage_date,
COUNT(*) as total_events,
COUNT(DISTINCT e.user_id) as active_users,
COUNT(*) FILTER (WHERE e.feature_type = 'ai_analysis') as ai_features_used
FROM events e
LEFT JOIN events_changelog ec ON e.id = ec.event_id
WHERE ec.event_id IS NULL
AND e.created_at >= NOW() - INTERVAL '2 days'
GROUP BY e.tenant_id, DATE(e.created_at)
)
-- Step 2: Upsert into materialized view
INSERT INTO tenant_daily_usage_mv
(tenant_id, usage_date, total_events, active_users, ai_features_used, last_updated)
SELECT
tenant_id,
usage_date,
total_events,
active_users,
ai_features_used,
NOW()
FROM delta_aggregates
ON CONFLICT (tenant_id, usage_date) DO UPDATE SET
total_events = EXCLUDED.total_events,
active_users = EXCLUDED.active_users,
ai_features_used = EXCLUDED.ai_features_used,
last_updated = NOW();
-- Step 3: Mark processed events
INSERT INTO events_changelog (event_id)
SELECT e.id
FROM events e
LEFT JOIN events_changelog ec ON e.id = ec.event_id
WHERE ec.event_id IS NULL
AND e.created_at >= NOW() - INTERVAL '2 days'
ON CONFLICT DO NOTHING;
GET DIAGNOSTICS v_processed_count = ROW_COUNT;
RETURN QUERY SELECT
v_processed_count as rows_processed,
EXTRACT(EPOCH FROM (NOW() - v_start_time))::INT * 1000 as duration_ms;
END;
$$ LANGUAGE plpgsql;
Key design choices:
- 2-day window catches backfilled events and clock skew
-
ON CONFLICT DO UPDATEmerges without locking - Changelog table prevents double-processing
Trigger-Based Real-Time Updates
For sub-second dashboards, pair incremental batch refreshes with triggers on high-impact tables:
CREATE OR REPLACE FUNCTION trigger_usage_update()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO tenant_daily_usage_mv
(tenant_id, usage_date, total_events, active_users, ai_features_used, last_updated)
VALUES (
NEW.tenant_id,
DATE(NEW.created_at),
1,
1,
CASE WHEN NEW.feature_type = 'ai_analysis' THEN 1 ELSE 0 END,
NOW()
)
ON CONFLICT (tenant_id, usage_date) DO UPDATE SET
total_events = tenant_daily_usage_mv.total_events + 1,
active_users = (
SELECT COUNT(DISTINCT user_id)
FROM events
WHERE tenant_id = NEW.tenant_id
AND DATE(created_at) = DATE(NEW.created_at)
),
last_updated = NOW();
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_events_usage_update
AFTER INSERT ON events
FOR EACH ROW
EXECUTE FUNCTION trigger_usage_update();
Schedule the batch refresh every 5 minutes:
# In a background worker (Python + APScheduler or pg_cron)
def refresh_analytics():
result = db.session.execute(
"SELECT * FROM refresh_tenant_usage_incremental()"
).fetchone()
logger.info(f"Processed {result.rows_processed} rows in {result.duration_ms}ms")
Or use PostgreSQL's pg_cron:
SELECT cron.schedule('refresh-analytics', '*/5 * * * *',
'SELECT refresh_tenant_usage_incremental()');
Performance Results
With CitizenApp's 50M events:
- Before: 45s refresh, 5min stale data, 85% CPU spike
- After: 280ms batch + 4ms trigger overhead, <30s stale, 12% CPU
Your dashboard reads from tenant_daily_usage_mv directly—no aggregation needed:
// React component - instant loads
async function getTenantMetrics(tenantId: string) {
const response = await fetch(`/api/analytics/${tenantId}`);
return response.json(); // Reads pre-computed MV, <50ms response
}
Gotcha: The Active_Users Calculation
I initially computed active_users in the trigger by counting distinct users on every insert. For high-volume tenants, this killed performance (subquery on 100K rows).
The fix: Use an approximate distinct count in triggers, reconcile during batch refresh:
-- Fast trigger version (approximate)
active_users = array_length(
agg_distinct_approx(ARRAY[NEW.user_id]),
1
)::INT,
-- Accurate batch version
active_users = (SELECT COUNT(DISTINCT user_id) FROM events ...)
Also, don't cascade incremental updates to downstream aggregates (weekly, monthly views). That creates a dependency chain. Instead, compute those from the daily MV:
CREATE VIEW tenant_weekly_usage AS
SELECT
tenant_id,
DATE_TRUNC('week', usage_date) as week,
SUM(total_events) as weekly_events,
SUM(active_users) as weekly_active_users
FROM tenant_daily_usage_mv
GROUP BY tenant_id, DATE_TRUNC('week', usage_date);
Why Not Redis or Kafka?
Tempting, but overkill. You're adding operational complexity (flushing, consistency, failover) for a problem PostgreSQL solves natively. Stay in the database until you genuinely need elasticity. For multi-tenant SaaS, incremental views + batch jobs scale to billions of events without external caches.
PostgreSQL wins when your queries are complex (
Top comments (0)