Read replicas, caching and summary tables

conceptThe concept

Replicas receive WAL asynchronously by default, so they lag the primary by milliseconds to minutes. Route only reads that tolerate that lag. For read-your-writes, either send a user's reads to the primary for a short time after they write, or record the primary's WAL position after the write (pg_current_wal_lsn()) and only read from a replica whose pg_last_wal_replay_lsn() has passed it.

Materialized views store the result of a query. REFRESH MATERIALIZED VIEW recomputes it and blocks readers; REFRESH ... CONCURRENTLY needs a unique index and lets readers continue during the refresh.

Summary tables are maintained incrementally: a trigger or the application updates a counter row on each write. Reads become a primary-key lookup, at the cost of extra work and contention on every write.

whyWhy it matters

Dashboards and listing pages often run the same aggregate thousands of times a minute over data that changes slowly. Computing it once per minute, or once per write, instead of once per request removes most of the load.

Use caseReal-world scenario

A product page shows review count and average rating. Computing them with count(*) and avg() on every page view scans thousands of reviews. A summary table updated by a trigger on review insert turns it into one row lookup.

Try itExamples

Run and edit these in the interactive lesson.

-- on the primary: one row per connected standby (empty here)
SELECT application_name, state, replay_lag,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_behind
FROM pg_stat_replication;
-- on a standby: NULL here because this server is a primary
SELECT pg_last_wal_replay_lsn() AS replayed_up_to,
       now() - pg_last_xact_replay_timestamp() AS replay_delay;

BEGIN;
CREATE MATERIALIZED VIEW daily_sales AS
SELECT created_at::date AS day, count(*) AS orders, sum(total) AS revenue
FROM orders
WHERE status <> 'cancelled'
GROUP BY 1;
CREATE UNIQUE INDEX ON daily_sales (day);
SELECT * FROM daily_sales ORDER BY day DESC LIMIT 3;
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;
SELECT count(*) AS days FROM daily_sales;
ROLLBACK;

BEGIN;
CREATE TABLE product_review_stats (
  product_id integer PRIMARY KEY,
  reviews    integer NOT NULL,
  rating_sum integer NOT NULL
);
INSERT INTO product_review_stats
SELECT product_id, count(*), sum(rating) FROM reviews GROUP BY product_id;

CREATE FUNCTION bump_review_stats() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO product_review_stats AS s VALUES (NEW.product_id, 1, NEW.rating)
  ON CONFLICT (product_id) DO UPDATE
    SET reviews = s.reviews + 1, rating_sum = s.rating_sum + EXCLUDED.rating_sum;
  RETURN NULL;
END $$;
CREATE TRIGGER reviews_stats AFTER INSERT ON reviews
  FOR EACH ROW EXECUTE FUNCTION bump_review_stats();

INSERT INTO reviews (product_id, customer_id, rating, title, body, created_at)
VALUES (7, 1, 5, 'Great', 'Loved it', now());
SELECT product_id, reviews, round(rating_sum::numeric / reviews, 2) AS avg_rating
FROM product_review_stats WHERE product_id = 7;
ROLLBACK;

-- after the write, on the primary
SELECT pg_current_wal_lsn();            -- e.g. 0/3A2B1C8, store it in the user session

-- before reading, on a replica
SELECT pg_last_wal_replay_lsn() >= '0/3A2B1C8'::pg_lsn AS caught_up;
-- caught_up = false: read from the primary or wait briefly and retry

internalsUnder the hood

A materialized view is a heap table with a stored query. A plain refresh builds a new heap and swaps it in under an ACCESS EXCLUSIVE lock. A concurrent refresh builds the new result in a temporary table, diffs it against the old contents using the unique index and applies inserts, updates and deletes under an EXCLUSIVE lock, which still allows SELECT.

Avoid / preferDo this, not that

REFRESH MATERIALIZED VIEW daily_sales;  -- every minute, on a busy page

CREATE UNIQUE INDEX ON daily_sales (day);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;

UPDATE site_stats SET total_orders = total_orders + 1;  -- on every order

INSERT INTO order_counter_shards (shard, n)
VALUES (floor(random() * 16), 1)
ON CONFLICT (shard) DO UPDATE SET n = order_counter_shards.n + 1;
-- read: SELECT sum(n) FROM order_counter_shards;

PitfallsCommon mistakes

• Sending a user to a replica right after they wrote, so their change seems to vanish.
• Refreshing a materialized view that nobody indexes, so every read of it is a sequential scan.
• Maintaining summary tables in application code in some paths but not others, so they drift.

LimitsWhen not to use it

Skip caching when the base query is already fast with the right index; a cache adds staleness and invalidation bugs. Skip replicas for workloads that need strictly current data, such as balance checks before a payment.

SecuritySecurity notes

A materialized view runs with the privileges of its owner and does not apply the row-level security of the base tables to readers. Grant access to it as carefully as to a table, or keep per-user filtering in the query that reads it.

ChallengeTry it yourself

BEGIN;
CREATE MATERIALIZED VIEW category_month AS
SELECT date_trunc('month', o.created_at)::date AS month, p.category,
       sum(i.qty * i.unit_price) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN products p ON p.id = i.product_id
WHERE o.status = 'delivered'
GROUP BY 1, 2;
SELECT DISTINCT ON (month) month, category, revenue
FROM category_month
ORDER BY month, revenue DESC;
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Hot standby
• Table partitioning
• Resource consumption