Postgres materialized views: query-level caching

A PostgreSQL materialized view stores the result of a query as a table-like object, so reads hit precomputed rows instead of re-running an expensive join or aggregate every time. The data is only as fresh as the last REFRESH MATERIALIZED VIEW, which you run on a schedule or after changes; with the CONCURRENTLY option and a unique index, students can keep reading while it refreshes. It is the closest thing Postgres has to a query cache, and it lives inside the database.

Views vs materialized views

The PostgreSQL documentation puts the difference well: a materialized view is like a table created from a query, except that it remembers the query so it can be refreshed, and it can't be modified in any other way.

Creating and indexing one

Take a mock-test leaderboard, a typical expensive query on a learning platform. Students may attempt a test more than once, the leaderboard shows each student's best score, and thousands of students open it at the same time. Computing it live means grouping and ranking every submitted attempt on every page view.

CREATE MATERIALIZED VIEW test_leaderboard AS
SELECT test_id,
       student_id,
       max(score) AS best_score,
       rank() OVER (PARTITION BY test_id ORDER BY max(score) DESC) AS test_rank
FROM test_attempts
WHERE status = 'submitted'
GROUP BY test_id, student_id;

CREATE UNIQUE INDEX ON test_leaderboard (test_id, student_id);
CREATE INDEX ON test_leaderboard (test_id, test_rank);

Both indexes earn their place. The unique index on (test_id, student_id) answers "what is my rank?" in a single lookup, and it is also what allows concurrent refreshes, as the next section explains. The second index serves the top-100 list:

SELECT student_id, best_score, test_rank
FROM test_leaderboard
WHERE test_id = 42
ORDER BY test_rank
LIMIT 100;

Instead of aggregating every attempt, both queries now read a few index pages. Run ANALYZE after creating the view, so the planner has statistics for it, and read our guide to database indexing for choosing the columns.

Refreshing: full and concurrent

For anything students read, use CONCURRENTLY. A plain refresh of a leaderboard at 10:05 on results day blocks every student who opens it until the refresh completes. The REFRESH documentation adds two details: only one refresh can run on a view at a time, even concurrently, and a view created WITH NO DATA must get a plain refresh first before it can be refreshed concurrently. Because a concurrent refresh applies its changes as ordinary inserts, updates and deletes, it leaves dead row versions behind, so make sure autovacuum keeps up with frequently refreshed views.

Scheduling refreshes

PostgreSQL never refreshes a materialized view by itself. Pick a trigger for the refresh:

• On a timer, inside the database, with the pg_cron extension. It has to be loaded through shared_preload_libraries, so on a managed database check that your provider supports it.
  
  
• On a timer, from your application, with Celery beat or cron calling a management command.
  
  
• After an event, such as the test window closing or results being published.
  
  

SELECT cron.schedule(
    'refresh-test-leaderboard',
    '* * * * *',              -- every minute
    $$REFRESH MATERIALIZED VIEW CONCURRENTLY test_leaderboard$$
);

pg_cron also accepts intervals such as '30 seconds'. Choose the interval from two numbers: how stale the data may be, and how long a refresh takes. If a refresh takes 12 seconds and runs every minute, the database spends a fifth of its time rebuilding this one view. If a refresh ever takes longer than the interval, pg_cron queues the next run until the current one finishes, so refreshes run back to back and the database never gets a break; log refresh durations and alert when they approach the interval. One more detail from pg_cron's documentation: it doesn't run jobs on a hot standby, only on the primary.

Incremental alternatives

REFRESH always recomputes the whole query. When the base table is large and changes are small, that's wasteful, and there are three ways around it.

• A summary table you maintain yourself. Keep a best_scores table and upsert into it as each attempt is submitted, with INSERT ... ON CONFLICT (test_id, student_id) DO UPDATE SET best_score = greatest(best_scores.best_score, excluded.best_score). Every submission costs one extra row write, and the table is always current. Compute ranks at read time from an index on (test_id, best_score DESC).
  
  
• The pg_ivm extension. pg_ivm keeps an "incrementally maintainable materialized view" up to date with triggers, in the same transaction as each change. It supports joins, DISTINCT and count, sum, avg, min and max, but not window functions, ORDER BY, LIMIT or HAVING, so it couldn't maintain the ranked view above directly. Every write to the base tables gets slower.
  
  
• Smaller views. A view per test series, or one covering only recent attempts, refreshes faster than one view over all history.
  
  

A summary table is usually the better choice when data arrives steadily and must be current within seconds. A materialized view is better when a periodic snapshot is good enough and the query is too complex to maintain row by row.

Does Postgres have a query cache?

No, not in the sense of remembering query results. PostgreSQL caches data pages: shared_buffers holds recently used 8 kB pages, and the operating system's file cache holds more. A repeated query therefore avoids disk reads, but it still parses, plans, joins, sorts and aggregates every time. Prepared statements can reuse plans, which saves planning but not execution. MySQL once had a result cache and removed it in version 8.0.

So caching results is your job, and you have two places to do it:

They combine well: refresh the materialized view every minute and cache the top-100 list from it in Redis for 30 seconds (see Redis caching for eviction and sizing). And if the view's reads still crowd the primary, serve them from read replicas; a materialized view replicates like any table. For how this layer fits with browser, CDN and application caches, see types of caching.

Key takeaways

• A materialized view stores a query's result; reads are cheap and can use indexes, but data is only as fresh as the last refresh.
  
  
• Use REFRESH ... CONCURRENTLY for anything users read; it needs a unique index on plain columns and a populated view.
  
  
• Schedule refreshes with pg_cron or your app, and keep the refresh time well below the interval.
  
  
• For always-current aggregates, maintain a summary table with upserts, or consider pg_ivm within its limits.
  
  
• PostgreSQL caches pages, not results, so store expensive results in a materialized view or Redis.
  
  

Frequently asked questions

What is materialized view?

A materialized view is a database object that stores the result of a query, rather than just the query itself as an ordinary view does. Reading it is as cheap as reading a table, and you can add indexes to it, but its contents only change when it is refreshed. It suits expensive queries whose results can be a little out of date, such as leaderboards, dashboards and reports.

What is materialized view in PostgreSQL?

In PostgreSQL, you create one with CREATE MATERIALIZED VIEW name AS query. Postgres runs the query and stores the rows, and you update them with REFRESH MATERIALIZED VIEW, which recomputes the whole result. It is never refreshed automatically, so you schedule refreshes yourself. Adding CONCURRENTLY lets reads continue during a refresh, provided the view has a unique index on plain columns.

What is materialized view in database?

Across databases, a materialized view is a precomputed, stored query result that trades freshness for read speed. Databases differ in how they keep it current: some can refresh incrementally or maintain it automatically as base tables change, while PostgreSQL's built-in version recomputes the full query on each refresh. In every case, the view is a cache that lives inside the database, with the same questions about staleness as any cache.