Postgres Materialized Views: Refresh Strategies That Do Not Lock Your Dashboards

The dashboard was unusable by 9 a.m. every Monday. A single page loaded six charts, each running a COUNT with GROUP BY over three months of order data. The queries were not badly written. They had indexes. But indexes cannot precompute an aggregation, and scanning 12 million rows to build a bar chart is slow no matter how you index it.

We added Redis caching. That worked until the first support ticket arrived: “I just processed a refund and the dashboard still shows the old total.” The cache TTL was five minutes. Users expected five milliseconds. We lowered the TTL to 30 seconds and now the database was melting under the refresh load.

The real fix was a materialized view: a snapshot of the aggregated result, stored on disk, refreshed on a schedule. The read time dropped from 28 seconds to 12 milliseconds. Then we discovered the catch. REFRESH MATERIALIZED VIEW takes an access-exclusive lock. While it runs, every dashboard query blocks. Our 45-second refresh window became a 45-second outage, six times per hour.

This post covers three refresh strategies that keep the data current without locking out readers: REFRESH CONCURRENTLY, the transactional swap pattern, and trigger-based incremental updates. Each has different trade-offs, and most teams need more than one.

What a materialized view actually buys you

A regular view is just a stored query. Every time you select from it, Postgres runs the underlying SQL. A materialized view is the result of that query, written to a physical table. The first read is expensive (during creation), but every subsequent read is a simple table scan of the precomputed result.

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT
  date_trunc('day', created_at) AS day,
  SUM(amount) AS revenue,
  COUNT(*) AS order_count
FROM orders
WHERE created_at > now() - interval '90 days'
GROUP BY 1;

CREATE INDEX idx_daily_revenue_day ON daily_revenue(day);

Querying daily_revenue is now a single index scan over 90 rows instead of an aggregation over 12 million. The speedup is not 2x or 5x. It is three orders of magnitude. But the data is frozen at the moment of creation. To update it, you must refresh.

The locking problem

The default refresh is brutal:

REFRESH MATERIALIZED VIEW daily_revenue;

This runs the underlying query, writes the result to a new storage chunk, and swaps it in. While the swap happens, Postgres acquires an ACCESS EXCLUSIVE lock on the materialized view. That lock blocks SELECT, INSERT, UPDATE, DELETE, and even REFRESH CONCURRENTLY from any other session. If your refresh takes 40 seconds, your dashboard is down for 40 seconds.

In our case, the refresh was fast in staging (2 million rows) and catastrophic in production (12 million rows plus a JOIN to customers for segmentation). The lock duration scaled with data size, which is exactly the wrong direction for a performance optimization.

Strategy 1: REFRESH CONCURRENTLY

Postgres 9.4 added the right tool for most cases:

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

Instead of swapping the storage in place, CONCURRENTLY builds the new result in a hidden temporary table, then swaps the underlying filenode pointers at the end. The swap is fast (metadata-only), so the ACCESS EXCLUSIVE lock is held for milliseconds instead of seconds. Readers continue to see the old data during the refresh, then atomically switch to the new data when it is ready.

There is one hard requirement: the materialized view must have at least one unique index.

CREATE UNIQUE INDEX idx_daily_revenue_day_unique
  ON daily_revenue(day);

Without this, Postgres cannot identify rows for the internal diff operation that merges the new snapshot. The refresh will fail with a clear error, which is better than silently locking, but you need to plan for it at creation time.

When CONCURRENTLY is not enough

REFRESH CONCURRENTLY still has to run the full underlying query. If that query takes 60 seconds, the refresh takes 60 seconds plus swap time. During those 60 seconds, the old data is still visible, which is correct for dashboards, but the refresh itself consumes I/O and CPU. If you refresh six views every minute, you can saturate a read replica.

It also does not work if the underlying query contains:

• ORDER BY (sorting a materialized view is pointless; add an index instead)
• LIMIT or OFFSET (the result set is not deterministic enough for diffing)
• Mutable functions like random(), now(), or current_timestamp inside the view definition (the unique index cannot stabilize across refreshes)

For most analytics dashboards, these restrictions are fine. For real-time leaderboards or time-series rollups that refresh every 10 seconds, the full-query cost is too high.

Strategy 2: The transactional swap pattern

When CONCURRENTLY is unavailable (Postgres < 9.4, or the view has LIMIT, or you need more control), you can fake it with two tables and a view wrapper.

The idea is simple. Maintain two identical tables: daily_revenue_a and daily_revenue_b. A regular view called daily_revenue points to whichever table is currently “live.” To refresh, you populate the inactive table, then swap the view definition in a transaction.

-- Two backing tables
CREATE TABLE daily_revenue_a (LIKE daily_revenue INCLUDING ALL);
CREATE TABLE daily_revenue_b (LIKE daily_revenue INCLUDING ALL);

-- The swapable view
CREATE OR REPLACE VIEW daily_revenue AS
SELECT * FROM daily_revenue_a;

-- Populate the inactive table
INSERT INTO daily_revenue_b
SELECT date_trunc('day', created_at) AS day,
       SUM(amount) AS revenue,
       COUNT(*) AS order_count
FROM orders
WHERE created_at > now() - interval '90 days'
GROUP BY 1;

-- Atomic swap
BEGIN;
CREATE OR REPLACE VIEW daily_revenue AS
SELECT * FROM daily_revenue_b;
COMMIT;

The CREATE OR REPLACE VIEW inside a transaction acquires a brief lock on the view, but because it is only metadata, the lock duration is negligible. Readers see either the old data or the new data; there is no intermediate state.

Automating the swap

In practice, you do not want to write this by hand every refresh. A PL/pgSQL function handles the A/B toggle:

CREATE OR REPLACE FUNCTION refresh_daily_revenue_swap()
RETURNS void AS $$
DECLARE
  current_live text;
  next_table text;
BEGIN
  -- Discover which table is currently live
  SELECT CASE WHEN definition LIKE '%daily_revenue_a%'
              THEN 'a' ELSE 'b' END
  INTO current_live
  FROM pg_views
  WHERE viewname = 'daily_revenue';

  next_table := CASE WHEN current_live = 'a' THEN 'b' ELSE 'a' END;

  -- Truncate and populate the inactive table
  EXECUTE format('TRUNCATE TABLE daily_revenue_%s', next_table);
  EXECUTE format(
    'INSERT INTO daily_revenue_%s
     SELECT date_trunc(''day'', created_at) AS day,
            SUM(amount) AS revenue,
            COUNT(*) AS order_count
     FROM orders
     WHERE created_at > now() - interval ''90 days''
     GROUP BY 1',
    next_table
  );

  -- Swap the view
  EXECUTE format(
    'CREATE OR REPLACE VIEW daily_revenue AS SELECT * FROM daily_revenue_%s',
    next_table
  );
END;
$$ LANGUAGE plpgsql;

Call it from a cron job, pg_cron, or your application scheduler:

SELECT refresh_daily_revenue_swap();

The downside is double storage. You need enough disk for two copies of the result set. For a 90-day rollup that is 10 MB, this is free. For a 10-year rollup that is 200 GB, it is not. Measure before you deploy.

Strategy 3: Incremental refresh with triggers

If your dashboard needs near-real-time data (sub-5-second lag), full refreshes are too expensive. The solution is to stop recomputing the whole view and start updating only the changed rows.

Postgres does not have built-in incremental materialized views (unless you install pg_ivm, which is promising but not production-ready for most teams). You can build the same behavior with a summary table and triggers.

CREATE TABLE daily_revenue_summary (
  day date PRIMARY KEY,
  revenue numeric(12,2) NOT NULL DEFAULT 0,
  order_count int NOT NULL DEFAULT 0
);

-- Seed it once
INSERT INTO daily_revenue_summary (day, revenue, order_count)
SELECT date_trunc('day', created_at)::date,
       SUM(amount),
       COUNT(*)
FROM orders
GROUP BY 1
ON CONFLICT (day) DO UPDATE SET
  revenue = EXCLUDED.revenue,
  order_count = EXCLUDED.order_count;

Then maintain it with a trigger on the source table:

CREATE OR REPLACE FUNCTION maintain_daily_revenue_summary()
RETURNS trigger AS $$
DECLARE
  target_day date;
  delta_amount numeric(12,2);
BEGIN
  IF TG_OP = 'INSERT' THEN
    target_day := date_trunc('day', NEW.created_at)::date;
    delta_amount := NEW.amount;
  ELSIF TG_OP = 'UPDATE' THEN
    -- Handle both day change and amount change
    IF date_trunc('day', OLD.created_at)::date
       IS DISTINCT FROM date_trunc('day', NEW.created_at)::date THEN
      -- Decrement old day
      INSERT INTO daily_revenue_summary (day, revenue, order_count)
      VALUES (date_trunc('day', OLD.created_at)::date, 0, 0)
      ON CONFLICT (day) DO UPDATE SET
        revenue = daily_revenue_summary.revenue - OLD.amount,
        order_count = daily_revenue_summary.order_count - 1;
      -- Increment new day
      target_day := date_trunc('day', NEW.created_at)::date;
      delta_amount := NEW.amount;
    ELSE
      target_day := date_trunc('day', NEW.created_at)::date;
      delta_amount := NEW.amount - OLD.amount;
    END IF;
  ELSIF TG_OP = 'DELETE' THEN
    target_day := date_trunc('day', OLD.created_at)::date;
    delta_amount := -OLD.amount;
  END IF;

  IF TG_OP = 'DELETE' OR
     (TG_OP = 'UPDATE' AND date_trunc('day', OLD.created_at)::date
      IS DISTINCT FROM date_trunc('day', NEW.created_at)::date) THEN
    -- Already handled above for the delete case
    IF TG_OP = 'DELETE' THEN
      UPDATE daily_revenue_summary
      SET revenue = revenue + delta_amount,
          order_count = order_count - 1
      WHERE day = target_day;
    END IF;
  ELSE
    INSERT INTO daily_revenue_summary (day, revenue, order_count)
    VALUES (target_day, delta_amount,
            CASE WHEN TG_OP = 'INSERT' THEN 1 ELSE 0 END)
    ON CONFLICT (day) DO UPDATE SET
      revenue = daily_revenue_summary.revenue + EXCLUDED.revenue,
      order_count = daily_revenue_summary.order_count +
        CASE WHEN TG_OP = 'INSERT' THEN 1 ELSE 0 END;
  END IF;

  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_maintain_daily_revenue
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION maintain_daily_revenue_summary();

This trigger is verbose because it handles day boundaries correctly. An UPDATE that moves an order from Monday to Tuesday must decrement Monday and increment Tuesday. The ON CONFLICT clause ensures the summary row exists before we try to update it.

The read query is trivial:

SELECT * FROM daily_revenue_summary
WHERE day > now() - interval '90 days'
ORDER BY day;

The cost of triggers

Every INSERT into orders now runs trigger logic. If you bulk-load 100,000 rows, the trigger fires 100,000 times. For bulk loads, disable the trigger first:

ALTER TABLE orders DISABLE TRIGGER trg_maintain_daily_revenue;
-- bulk insert
ALTER TABLE orders ENABLE TRIGGER trg_maintain_daily_revenue;
-- Then reconcile the summary table with a single upsert

Triggers also add latency to the write transaction. If your dashboard refresh interval is 30 seconds, a full REFRESH CONCURRENTLY is probably cheaper than paying the trigger tax on every write. Use incremental refresh only when stale data is actually a business problem, not just a preference.

Choosing a strategy

Scenario	Recommended approach
Dashboard refreshes every 5-15 minutes, query takes < 30 s	REFRESH CONCURRENTLY with a unique index
Dashboard refreshes every minute, CONCURRENTLY not available	Transactional swap pattern
Dashboard must reflect writes within seconds	Trigger-based incremental summary
Query takes > 2 minutes, refresh frequency > 10 minutes	Transactional swap or accept the lock window
Source table has very high write volume, few reads	Do not use a materialized view; index the source

Most production dashboards I have worked with fall into the first bucket. REFRESH CONCURRENTLY is the default for a reason. It is simple, safe, and handles the lock problem without extra tables or trigger complexity.

Monitoring and maintenance

Materialized views are tables. They bloat. They need VACUUM. They need ANALYZE so the query planner knows how big they are.

Add a dedicated autovacuum configuration if the view refreshes frequently:

ALTER MATERIALIZED VIEW daily_revenue SET (
  autovacuum_vacuum_scale_factor = 0.1,
  autovacuum_analyze_scale_factor = 0.05
);

Monitor the refresh duration in your metrics pipeline. A sudden jump from 8 seconds to 45 seconds usually means the underlying query lost a good plan (check for stale statistics) or the source table grew past an index threshold.

Also watch disk space. REFRESH CONCURRENTLY keeps the old snapshot until the transaction ends, so a long-running SELECT on the view can pin the old data and prevent cleanup. If you have analysts running ad-hoc queries against the dashboard schema, set idle_in_transaction_session_timeout to something reasonable (5 minutes) so they do not hold dead tuples forever.

The takeaway

Materialized views are not a magic performance button. They are a trade-off: you accept stale data in exchange for read speed. The key is managing the refresh so the staleness window is predictable and the refresh itself does not become a new outage source.

Start with REFRESH CONCURRENTLY and a unique index. If that is not available, use the transactional swap pattern. Only reach for triggers when real-time is a hard requirement. And always measure the refresh duration in production, because the query planner loves to surprise you at scale.

A note from Yojji

The difference between a dashboard that loads in 12 milliseconds and one that times out after 30 seconds is often not a bigger server. It is understanding which Postgres primitives actually precompute work and which ones just hide the cost. Yojji’s teams have been building read-heavy analytics features and the data pipelines behind them since 2016, using exactly the kind of incremental refresh and swap patterns that keep dashboards fast without locking out users.

Yojji is an international custom software development company founded in 2016, with offices in Europe, the US, and the UK. They specialize in the JavaScript ecosystem (React, Node.js, TypeScript), cloud platforms (AWS, Azure, Google Cloud), and the database architecture decisions that keep production systems responsive when the dataset grows past the point where naive queries still work.