Query-Rewrite Rules for Materialized Views in PostgreSQL #

Databases with automatic materialized-view rewrite let the optimizer detect that a submitted query matches a stored matview and silently redirect it. PostgreSQL has none of that. There is no automatic query rewrite to a matview — a query uses one only when it names it. This page is about closing that gap by hand: routing queries deliberately, using a plain view as a swappable indirection layer, and understanding why the RULE system, despite its name, does not give you automatic rewrite either.

No Automatic Rewrite — and RULEs Do Not Add It #

Two facts anchor everything below. First, the planner never substitutes a matview for a base-table query; adopting a matview is always an explicit act of pointing a query at it, as covered in when the planner chooses a matview scan. Second — and this is the common source of confusion — PostgreSQL’s RULE system does not fill the gap.

The RULE system and INSTEAD rules exist to make regular views updatable: they rewrite INSERT, UPDATE, and DELETE aimed at a view into statements against the underlying tables. That is a rewrite of write statements onto base tables, not a rewrite of read queries onto a precomputed matview. No rule you can write makes the planner notice that SELECT sum(amount) FROM orders GROUP BY region could be answered from mv_region_revenue. The redirection has to be something you build into how queries are addressed.

Approach 1 — Route Queries Directly at the Matview #

The simplest approximation is to have every report and application query name the matview instead of the base tables. This is the materialized view strategy in its most direct form:

-- Report code edited to read the precomputed relation by name
SELECT region, revenue
FROM   mv_region_revenue
WHERE  region = 'EU';

It works and it is transparent to the planner, but it hard-codes the matview name across your codebase. The day you want to change the precomputation — split the matview, rename it, or temporarily fall back to live base tables — you must edit every call site. That coupling is what the next approach removes.

Approach 2 — A Regular View as an Indirection Layer #

Put a plain (non-materialized) view in front of the matview and have the application read the view. Because a regular view is just a stored query that is inlined at plan time, reading through it costs nothing extra, but it gives you a single place to repoint.

-- Indirection layer: application reads region_revenue, never the matview name directly
CREATE VIEW region_revenue AS
SELECT region, sale_date, revenue, line_count
FROM   mv_region_revenue;

Application queries reference the view:

SELECT region, revenue FROM region_revenue WHERE region = 'EU';

Now the swap is a one-line redefinition. If you ever need to serve live data during a matview rebuild, or migrate to a differently named matview, you redefine the view alone and no application query changes:

-- Fall back to live base tables without touching a single application query
CREATE OR REPLACE VIEW region_revenue AS
SELECT o.region, o.sale_date, sum(li.amount) AS revenue, count(*) AS line_count
FROM   orders o JOIN line_items li ON li.order_id = o.order_id
GROUP  BY o.region, o.sale_date;

This is the closest thing to “rewrite” you get in PostgreSQL: not the optimizer choosing the matview, but you choosing it behind a stable name.

Indirection View Over a Materialized View Application queries read a stable regular view; the view SELECTs from the materialized view, which is scanned via its index. The view can be redefined to point at base tables instead, without changing application queries. Application reads region_revenue VIEW region_revenue inlined at plan time expands MATVIEW mv_region_revenue Index Scan result Redefine the view to read base tables instead — application queries never change

EXPLAIN: The View Expands to a Matview Scan #

The value of the indirection layer holds only if the planner inlines the view and pushes your filter down to the matview’s index. It does, for a straightforward view. Watch a filtered query on the view expand:

EXPLAIN (ANALYZE, BUFFERS)
SELECT region, revenue
FROM   region_revenue          -- the VIEW
WHERE  region = 'EU' AND sale_date >= '2026-06-01';
Index Scan using ux_mv_region_revenue on mv_region_revenue
    (cost=0.29..21.80 rows=36 width=18)
    (actual time=0.017..0.069 rows=35 loops=1)
  Index Cond: ((sale_date >= '2026-06-01'::date) AND (region = 'EU'::text))
  Buffers: shared hit=5
  -- ① cost= is a unitless planner metric; actual time= is wall-clock ms
  -- ② no "VIEW" node: the view was inlined and vanished from the plan
  -- ③ filter from the view query pushed into Index Cond on the matview
  -- ④ this is filter pushdown through the view — same mechanic as any inlined subquery

The view has disappeared entirely; what remains is an Index Scan directly on the matview with your predicate folded into the Index Cond. That folding is ordinary filter pushdown — the view is treated like any inlinable subquery, and its predicate is pushed as far down as the planner can take it.

Before/After: Base-Table Aggregate vs Matview Through a View #

-- BEFORE: view defined over base tables → live aggregate on every read
HashAggregate  (cost=48210.00..48960.00 rows=900 width=18)
               (actual time=612.4..631.8 rows=880 loops=1)
  Group Key: o.region, o.sale_date
  ->  Hash Join  (cost=1820..41200 rows=1900000 ...)   -- scans/joins millions of base rows
        ...

-- AFTER: same view name redefined over the matview → cheap indexed read
Index Scan using ux_mv_region_revenue on mv_region_revenue
    (cost=0.29..21.80 rows=36 ...) (actual time=0.017..0.069 rows=35 loops=1)
  Index Cond: ((sale_date >= '2026-06-01') AND (region = 'EU'))

The application query — SELECT ... FROM region_revenue WHERE ... — is byte-for-byte identical across both plans. Only the view definition changed. That is the whole benefit of the indirection layer: the swap from a live base-table aggregate to a matview scan is a schema change, not a code change.

Common Pitfalls #

The view hides staleness. A stable view name that quietly points at a matview means consumers cannot tell they are reading a snapshot. Document the freshness contract and, if it matters, expose a last_refreshed column so callers know how old the data is.

RULE complexity. Reaching for CREATE RULE to simulate rewrite leads to hard-to-debug, order-sensitive behavior — and it still will not give you automatic matview substitution. Prefer a plain view for read indirection; reserve rules for genuine updatable-view needs.

Permission layering. Queries against the view run with the view owner’s privileges on the matview, not the caller’s. If you swap the view to read base tables, the owner needs SELECT on those tables too, or the redefinition breaks access for every consumer at once.

Filter not folded through the view. Most simple views inline cleanly, but a view wrapping a non-inlinable construct — certain aggregates, DISTINCT ON, or a volatile function — can block predicate pushdown, forcing a full matview scan before the filter applies. Keep the indirection view a plain projection over the matview so the planner can push predicates into an Index Cond.

Frequently Asked Questions #

Does PostgreSQL’s RULE system rewrite queries onto materialized views? No. The RULE system and INSTEAD rules apply to updatable regular views and let you redirect INSERT/UPDATE/DELETE. They do not give the planner automatic rewrite of a base-table SELECT onto a matview. There is no built-in mechanism that substitutes a matview for a matching base query.

How do I swap base tables for a matview without changing application code? Put a plain regular view in front of the matview and have the application read the view. The view SELECTs from the matview, so you can repoint it between base tables and the matview by redefining the view alone, leaving query text in the application unchanged.

Will the planner push a WHERE filter through a view into the matview scan? Usually yes. A simple view over a matview is inlined during planning, so a filter on the view is folded into an Index Cond or Filter on the matview scan. It can fail to push down through non-inlinable constructs like some aggregates or volatile functions, which forces a full matview scan first.

Related