The dashboard outage was perfectly periodic: every hour, at six minutes past, every panel querying the daily rollup froze for about eleven minutes, then recovered. The cause took embarrassingly long to find because nobody thought of the materialized view as a lock holder. The refresh job ran REFRESH MATERIALIZED VIEW without CONCURRENTLY, which takes an ACCESS EXCLUSIVE lock on the view, and ACCESS EXCLUSIVE blocks even plain SELECTs. Eleven minutes was how long the refresh took over a 200-million-row events table, so eleven minutes was how long the dashboard stared at nothing, every hour, on the hour.
The kicker: the platform had migrated from MySQL two years earlier, where the same rollup was a hand-built summary table maintained by a cron job. That system never locked anyone out, because it was just a table. What it did instead was drift: a failed cron run in March went unnoticed until an auditor found the summary six weeks behind in May. MySQL and PostgreSQL both fail at precomputed aggregates, just in opposite directions. PostgreSQL gives you a real object with a dangerous default; MySQL gives you nothing and lets you build the danger yourself.
Does MySQL have materialized views?
No. MySQL has no materialized view object at any version, and a MySQL VIEW is always a stored query that executes against live data every time you select from it. There is no storage, no refresh, no staleness concept, because there is nothing materialized. What MySQL shops build instead is the summary-table pattern: a real table holding precomputed aggregates, plus some mechanism to keep it current. That mechanism is one of three things: a scheduled job, typically the event scheduler or an external cron; triggers on the base tables that update the summary on every write; or application code that maintains the rollup as part of the write path. Each has a distinct failure mode, and I have met all three in production.
The event scheduler route looks like this, and note the first line, because event_scheduler defaults to OFF and a surprising number of "my event never runs" mysteries end there:
-- MySQL 8.0: the scheduler must be enabled first
SET GLOBAL event_scheduler = ON;
CREATE EVENT refresh_daily_rollup
ON SCHEDULE EVERY 1 HOUR
STARTS '2025-08-01 00:06:00'
DO
REPLACE INTO daily_rollup (day, event_type, total)
SELECT DATE(created_at), event_type, COUNT(*)
FROM events
WHERE created_at >= CURDATE() - INTERVAL 2 DAY
GROUP BY 1, 2;REPLACE INTO gives you idempotent refresh of the recent window, which is the whole trick: recompute a sliding window you know can change, leave history alone, and make rerunning safe. The failure mode that bit us was not the SQL, it was the silence. When an event's statement errors, the failure lands in the error log and nowhere the on-call dashboard looks, which is how six weeks of staleness accumulated. If you run this pattern, the event's last-execution status in information_schema.events belongs on a monitor, and the summary table should carry a computed-at timestamp so staleness is a SELECT, not an audit finding.
How does PostgreSQL's materialized view work, and why does the default refresh block readers?
A PostgreSQL materialized view stores the query's result like a table, stays stale until you explicitly refresh it, and a plain REFRESH MATERIALIZED VIEW locks the view with ACCESS EXCLUSIVE for the entire rebuild, blocking every reader. That lock is the trap in my opening story, and it is the default behavior, not an edge case. The fix is one keyword:
-- required once: a unique index with no WHERE clause,
-- or CONCURRENTLY is refused
CREATE UNIQUE INDEX daily_rollup_uq
ON daily_rollup (day, event_type);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_rollup;CONCURRENTLY computes the new contents alongside the old and swaps them in, so readers keep seeing the previous snapshot throughout. The requirements are strict and the error is immediate if you miss them: the view must already be populated, and it must have at least one unique index built only on column names with no WHERE clause, because the diff needs a way to match old rows to new. The costs are equally real. The concurrent refresh is slower than the locking one, on our table roughly three times slower, because it computes both versions and merges them, and it runs the diff with plain SQL under the hood, which means the refresh of a big view is itself a workload worth scheduling off-peak. One more operational rule: materialized views never refresh themselves. If nothing calls REFRESH, the data is stale forever, and PostgreSQL will not warn you. The materialized views guide goes deeper on refresh scheduling and the query-rewrite decisions, and the incremental aggregation piece covers the pattern for cases where even CONCURRENTLY is too blunt.
How do you build the MySQL equivalent without the drift or the locks?
The robust MySQL pattern is an atomic swap: build the new rollup in a shadow table, then swap names in a single RENAME TABLE statement, which is atomic and takes the metadata lock only briefly. Readers either see the old table or the new one, never a half-written one, and there is no long lock because the build happened elsewhere:
-- build in the shadow, then swap atomically
CREATE TABLE daily_rollup_new LIKE daily_rollup;
INSERT INTO daily_rollup_new (day, event_type, total)
SELECT DATE(created_at), event_type, COUNT(*)
FROM events
WHERE created_at >= CURDATE() - INTERVAL 30 DAY
GROUP BY 1, 2;
RENAME TABLE daily_rollup TO daily_rollup_old,
daily_rollup_new TO daily_rollup;
DROP TABLE daily_rollup_old;RENAME TABLE is atomic across all the tables named in one statement, which is what makes this safe; two separate renames would leave a gap where the table does not exist and every dashboard query errors. The trade against triggers deserves a sentence, because triggers are the other common way to keep a MySQL summary current, and they move the cost into every write transaction, extend lock hold time, and fire per row with no WHEN clause, all of which we detail in the triggers comparison. For a rollup that tolerates an hour of lag, the swap pattern beats trigger maintenance on any table with real write volume.
How do you monitor freshness on both engines?
On PostgreSQL, track refresh times yourself, because the catalog does not: pg_matviews tells you a view exists and whether it is populated, but not when it last refreshed, so the standard practice is a small audit table that the refresh job updates, or a computed-at column inside the view's own query. A refresh job that silently stops is the PostgreSQL twin of the MySQL silent event failure, and the defense is identical: expose a freshness timestamp and alert on its age. On MySQL, alert on the event scheduler being ON, on the event's last-execution status, and on the rollup's own computed-at column. On both engines, the expensive version of this monitoring is reconciliation, comparing the rollup against a ground-truth count periodically; do it weekly for anything money touches, because every precomputed aggregate is a cache, and caches lie.
Where does MonPG fit around precomputed aggregates?
The failure modes in this article are a lock story and a staleness story, and both are visible if you are watching the right layer. MonPG's PostgreSQL monitoring surfaces lock waits with the blocker identified, which is how an eleven-minute ACCESS EXCLUSIVE on a materialized view stops being a mystery freeze and becomes a named culprit with a query attached, and it keeps the statement history that shows a refresh getting slower month over month as the base table grows.
MonPG monitors PostgreSQL today; MySQL support is on the roadmap. When it lands, the MySQL side of this piece is what it will surface: event scheduler state, the metadata-lock evidence around swap renames, and the digest timing that tells you your hourly rollup is quietly becoming a two-hour rollup. Until then, the MySQL monitoring page tracks that work. And the engine-neutral lesson stands: a precomputed aggregate is a promise you made about freshness, so build the staleness alarm at the same time as the aggregate, or production will schedule the reminder for you, hourly, at six minutes past.