Materialized view

What a materialized view is

PostgreSQL introduced materialized views in version 9.3, and the manor has been better run for it. The syntax mirrors CREATE VIEW, but the semantics are different: the query runs immediately, and the result set is persisted to disk as a heap relation — the same storage format used by regular tables.

CREATE MATERIALIZED VIEW order_totals AS
SELECT
  customer_id,
  count(*) AS order_count,
  sum(amount) AS total_amount,
  max(created_at) AS last_order
FROM orders
GROUP BY customer_id;

-- Query it like a table
SELECT * FROM order_totals WHERE customer_id = 42;

Once created, order_totals is a physical table. It has its own storage, its own visibility map, and it can have its own indexes. Querying it does not touch the orders table at all. The planner treats it exactly like a table, because it is one.

The query definition is stored in the system catalog (pg_matviews), but PostgreSQL does not re-execute it automatically. The view is a snapshot taken at creation time, and it remains exactly as it was until you tell it to update. A photograph, not a window.

Why materialized views matter

Some queries are expensive and repetitive. A dashboard that shows order totals per customer does not need to re-aggregate millions of rows on every page load. A reporting query that joins five tables does not need to re-join them every time someone opens the report. Asking the database to redo work it has already done is, if you will forgive the observation, rather poor household management.

Materialized views solve this by precomputing the answer. Common use cases:

• Dashboards and analytics — aggregate metrics computed once, queried many times throughout the day
• Expensive joins — denormalized summaries that avoid joining large tables at read time
• Search and filtering — pre-joined, pre-filtered result sets that support fast lookups with indexes
• Data warehousing patterns — summary tables refreshed on a schedule, queried interactively
• API response caching — precomputed payloads for endpoints that serve the same aggregation to many clients

The common thread: the query is expensive, the data does not need to be real-time, and reads far outnumber writes. If that describes your situation, you have come to the right page.

Creating and refreshing

The lifecycle of a materialized view has two operations: create and refresh. Pleasantly straightforward.

-- Create without populating (useful for setting up indexes first)
CREATE MATERIALIZED VIEW order_totals AS
SELECT customer_id, count(*) AS order_count, sum(amount) AS total_amount
FROM orders
GROUP BY customer_id
WITH NO DATA;

-- The view exists but cannot be queried until refreshed
REFRESH MATERIALIZED VIEW order_totals;

Creating with WITH NO DATA is useful when you want to define the view and set up indexes before populating it. The view exists in the catalog but is marked as unscannable until the first refresh.

Refreshing

Refreshing re-executes the original query and replaces the stored data entirely. There are two modes:

-- Full refresh — locks out readers until complete
REFRESH MATERIALIZED VIEW order_totals;

-- Concurrent refresh — readers see old data until the new version is ready
-- Requires a unique index on the materialized view
REFRESH MATERIALIZED VIEW CONCURRENTLY order_totals;

A standard REFRESH takes an exclusive lock on the view — no one can read it while the refresh is running. For views that take seconds or minutes to refresh, this creates a blackout window. Not ideal when guests are waiting.

REFRESH CONCURRENTLY solves this by computing the new result set alongside the old one, then diffing them. Readers continue to see the old data until the swap is complete. It requires a unique index on the view and is somewhat slower due to the diff computation, but it eliminates read downtime entirely.

Scheduling refreshes

PostgreSQL does not refresh materialized views automatically. Left to its own devices, a materialized view will happily serve yesterday's data indefinitely and never mention it. You need an external trigger — a cron job, an application task, or the pg_cron extension.

-- Refresh every 15 minutes using pg_cron
SELECT cron.schedule(
  'refresh-order-totals',
  '*/15 * * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY order_totals'
);

Indexing materialized views

Because a materialized view is stored as a regular heap table, you can index it exactly like any other table. This is one of its most useful properties — and one that I find is overlooked with disappointing regularity.

-- B-tree index for point lookups
CREATE INDEX ON order_totals (customer_id);

-- Unique index — required for REFRESH CONCURRENTLY
CREATE UNIQUE INDEX ON order_totals (customer_id);

-- Composite index for filtered aggregation queries
CREATE INDEX ON order_totals (total_amount DESC)
WHERE order_count >= 10;

Indexes on materialized views persist across refreshes. You create them once. When a refresh runs, PostgreSQL updates the indexes as part of the refresh operation — you do not need to drop and recreate them.

If you plan to use REFRESH CONCURRENTLY, you must have at least one unique index on the view. This gives PostgreSQL a way to match old rows to new rows during the diff computation.

Trade-offs

I would be doing you a disservice if I presented materialized views without their costs. Understanding the trade-offs is essential to using them well.

These trade-offs are manageable for most use cases. The question I always put to the household is: how stale can your data be? If the answer is "not at all," a materialized view is the wrong tool — and I will say so plainly. If the answer is "minutes to hours," it is very often the right one.

How Gold Lapel relates

I should be candid: of every concept in this glossary, this one is closest to what Gold Lapel does. Materialized views are not merely relevant to Gold Lapel — they are the mechanism at its center.

Gold Lapel is a query proxy that sits between your application and PostgreSQL. It observes your query traffic, identifies expensive patterns that would benefit from materialization, and creates materialized views automatically — without any changes to your application code or SQL. The queries your application sends are intercepted at the proxy and served from materialized results when appropriate.

This addresses the two hardest parts of using materialized views by hand: knowing which queries to materialize, and keeping the refresh schedule aligned with your data's rate of change. Gold Lapel handles both. It monitors how frequently the underlying data changes, adjusts refresh intervals accordingly, and retires views that are no longer earning their keep.

If you are managing materialized views manually today — writing CREATE statements, scheduling refreshes with pg_cron, watching for staleness — Gold Lapel takes on that operational work. If you have not adopted materialized views because the setup and upkeep felt like too much overhead, that is precisely the barrier Gold Lapel was built to remove.