Cache the Query, Unlearn the Traps: Materialized Views and Anti-Patterns

We close the performance series with two ideas that meet in the middle. The first is what to do when a query is unavoidably expensive and you’d rather not pay for it on every request: cache the result. The second is the opposite problem, queries that look cheap but quietly refuse to use the indexes you built for them, the anti-patterns. Both come down to the same skill this whole series has been teaching: reading a plan and knowing why it’s shaped the way it is.

Materialized views: pay once, read many

A regular VIEW is just a stored query; it re-runs every time you select from it. A materialized view runs the query once, stores the result as a physical table, and hands you that stored answer on every read until you refresh it. It’s the right tool for an expensive aggregate that many readers need but that doesn’t have to be up-to-the-second.

Our bookshop has a natural one: revenue per book, which joins the ~150,000-row order_items table to books and groups. Run raw, it costs real time:

SELECT b.book_id, b.title, count(*) AS times_ordered,
       round(sum(oi.quantity * oi.unit_price), 2) AS revenue
FROM order_items oi JOIN books b ON b.book_id = oi.book_id
GROUP BY b.book_id, b.title;
-- Time: 63.011 ms

Sixty-three milliseconds, every time anyone asks. Materialize it once:

CREATE MATERIALIZED VIEW book_revenue AS
SELECT b.book_id, b.title, count(*) AS times_ordered,
       round(sum(oi.quantity * oi.unit_price), 2) AS revenue
FROM order_items oi JOIN books b ON b.book_id = oi.book_id
GROUP BY b.book_id, b.title;

Now reads hit the stored 600-row result, not the 150,000-row join:

SELECT book_id, title, revenue FROM book_revenue ORDER BY revenue DESC LIMIT 5;
-- Time: 1.594 ms

From 63 ms to 1.6 ms, a fortyfold cut, because the join and aggregation already happened. The trade is staleness: the stored answer doesn’t change when order_items does. You bring it up to date with REFRESH MATERIALIZED VIEW book_revenue, which re-runs the query and replaces the contents. Refresh on whatever cadence your data tolerates: hourly, nightly, after a batch load.

Plain REFRESH, though, takes an exclusive lock: readers are blocked for the duration. For a view people query continuously, that’s a problem, and Postgres offers REFRESH ... CONCURRENTLY to refresh without blocking readers. It comes with one firm requirement, which it enforces:

REFRESH MATERIALIZED VIEW CONCURRENTLY book_revenue;

ERROR:  cannot refresh materialized view "public.book_revenue" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns.

Concurrent refresh works by computing the new result and diffing it against the old, which it can only do if every row is uniquely identifiable. Give it a unique index and the refresh succeeds:

CREATE UNIQUE INDEX book_revenue_pk ON book_revenue (book_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY book_revenue;   -- succeeds

That unique index is not optional decoration; it’s the price of a non-blocking refresh. Build it when you build the view.

The anti-patterns: how to disable your own index

The rest of this chapter is a rogues’ gallery. Each is a common way to write a WHERE clause that looks reasonable and quietly forces a full scan of a table you’d carefully indexed. The fix in every case comes from reading the plan.

A function on the indexed column. A B-tree index on email stores email values, so it can find one by equality instantly:

EXPLAIN: SELECT * FROM customers WHERE email = '[email protected]';
 Index Scan using customers_email_key on customers

Wrap the column in a function, though, and the index is useless, because the index holds email, not lower(email):

EXPLAIN ANALYZE: WHERE lower(email) = '[email protected]';
 Seq Scan on customers (actual rows=1.00 loops=1)
   Filter: (lower(email) = '[email protected]')
   Rows Removed by Filter: 4999
   Execution Time: 7.204 ms

A full scan of all 5,000 rows to find one. The database can’t use the index because it has no index on the expression. So build one:

CREATE INDEX customers_lower_email ON customers (lower(email));

 Bitmap Index Scan on customers_lower_email
   Execution Time: 0.089 ms

From 7.2 ms and a full scan to 0.089 ms. An expression index stores the computed value, so a query that computes the same expression can use it. The rule generalizes: any function or arithmetic on an indexed column, date(created_at), col + 1, upper(name), defeats a plain index unless you index that exact expression.

Leading-wildcard LIKE. A prefix search can use an index (with the right operator class); a search that starts with a wildcard cannot. With a text_pattern_ops index on email:

WHERE email LIKE 'customer250%'   ->  Index Only Scan   (prefix is bounded)
WHERE email LIKE '%2500@%'        ->  Seq Scan          (leading % is unbounded)

A B-tree is sorted, so it can seek to a known prefix, but %2500@% could match anywhere in any string, and there’s no way to seek to “somewhere in the middle.” Leading-wildcard search needs a different index type entirely, a trigram GIN index, which the indexing chapters covered.

SELECT * when you need three columns. Selecting only indexed columns can be answered from the index alone, an index-only scan that never touches the table:

SELECT order_date FROM orders WHERE order_date = '2025-08-15';
 Index Only Scan using orders_order_date on orders
   Heap Fetches: 0
   Execution Time: 0.055 ms

Ask for * and the database must visit the heap for every matching row to fetch the other columns:

SELECT * FROM orders WHERE order_date = '2025-08-15';
 Bitmap Heap Scan on orders
   Heap Blocks: exact=57
   Execution Time: 0.269 ms

Heap Fetches: 0 versus 57 heap blocks read. SELECT * isn’t always wrong, but selecting only what you need is what makes covering and index-only scans possible.

OR across two columns. Here’s one where the folklore is wrong, and only running it shows why. The old advice is that OR defeats indexes. Against Postgres 18, with both columns indexed:

WHERE customer_id = 42 OR order_date = '2025-08-15';
 Bitmap Heap Scan on orders
   ->  BitmapOr
         ->  Bitmap Index Scan on orders_customer_id
         ->  Bitmap Index Scan on orders_order_date

Postgres builds a BitmapOr, scans both indexes, and unions the results. OR across two indexed columns is fine here. The trap is different: OR where one side isn’t indexed. Then the unindexed side needs every row, so the whole query falls back to a sequential scan:

WHERE customer_id = 42 OR status = 'shipped';   -- status has no index
 Seq Scan on orders (Rows Removed by Filter: 49954)

The customer lookup, which had a perfectly good index, got dragged into a full scan by the un-indexed partner. Rewriting as UNION of two separate queries lets the indexed arm use its index independently. The real lesson isn’t “avoid OR,” it’s that every branch of an OR needs its own usable index, or none of them get used.

Final thoughts, and where we’ve been

That closes the performance series. We started by learning to read what the planner decided with EXPLAIN. Then we built the indexes it chooses between, watched it pick join algorithms and lean on statistics, and traced how MVCC lets readers and writers coexist. From there the tools got more structural: locks and the deadlocks to avoid, VACUUM and the bloat it fights, partitioning to keep big tables small, and now caching and the anti-patterns. The thread through all of it is a single habit: don’t guess, look at the plan. Every claim in these chapters was run against PostgreSQL 18. The surprises, the random load balancing, the OR that indexes just fine, the SELECT * that costs you an index-only scan, are exactly the things you’d never learn by reasoning alone. Reach for EXPLAIN (ANALYZE, BUFFERS) whenever a query is slow, and the database will tell you what it’s actually doing.

That’s also the end of thinking about SQL as performance. The final series of this track, SQL in the Real World, steps back out to the questions you meet once a database leaves your laptop. How do you design a schema that won’t fight you later? How do you evolve it with migrations without breaking what’s running? How does SQL fit into application code, from connection pools to the ORM that’s writing your queries for you? You’ve learned how the database runs your query. Next is how to live with one.