Catalog My learning Blog
SQL Server to PostgreSQL Migration Guide (30 Days) · Phase 2 · Lesson 11 · 4 min read

Materialized Views in PostgreSQL for SQL Server Users

On this page
  1. Migrating regular views
  2. Indexed views vs materialized views
  3. Creating and refreshing a materialized view
  4. Choosing a replacement for an indexed view
  5. Check yourself

Migrating regular views

A regular view is a saved query with no data of its own, in both databases. Migration is mostly syntax:

SQL
-- T-SQL
CREATE VIEW dbo.vw_ActiveCustomers AS
SELECT CustomerId, CustomerName, Email FROM dbo.Customers WHERE IsActive = 1;
SQL
CREATE TABLE customers (customer_id int PRIMARY KEY, customer_name text, email text, is_active boolean NOT NULL);

CREATE VIEW active_customers AS
SELECT customer_id, customer_name, email FROM customers WHERE is_active;

Three small differences:

  • Changing a view. ALTER VIEW becomes CREATE OR REPLACE VIEW. It can add columns at the end but can't remove or reorder them. For that, drop and recreate the view.
  • Writing through views. Simple single-table views are updatable in both databases. Complex ones need an INSTEAD OF trigger in both (Day 10). For filtered views, add WITH CHECK OPTION so rows written through the view must match its WHERE clause.
  • SCHEMABINDING. There's no equivalent, and none is needed. PostgreSQL tracks view dependencies itself and refuses to drop a table a view depends on, unless you use CASCADE.

Indexed views vs materialized views

A SQL Server indexed view stores its result in a clustered index, and the engine keeps it current: every INSERT, UPDATE or DELETE on the base tables updates the view in the same transaction.

PostgreSQL's closest feature is the materialized view. It also stores its result, but it's a snapshot from the last time someone refreshed it. Nothing keeps it current automatically.

SQL Server indexed viewPostgreSQL materialized view
Always currentYes, maintained on every writeNo, current as of the last REFRESH
Who pays the costEvery write to the base tablesWhoever runs REFRESH, all at once
Query restrictionsMany: no outer joins, deterministic only, SCHEMABINDING and moreAlmost any SELECT
Readers during refreshNot applicableBlocked, unless you use CONCURRENTLY

This is the main thing to plan for. Code that read an indexed view expected up-to-the-second numbers. After migration, the same query against a materialized view returns numbers from the last refresh.

Creating and refreshing a materialized view

SQL
CREATE TABLE orders (order_id int PRIMARY KEY, customer_id int, order_date date, order_total numeric);
INSERT INTO orders VALUES (1, 101, '2026-09-01', 600), (2, 101, '2026-09-20', 700), (3, 102, '2026-09-05', 90);

CREATE MATERIALIZED VIEW customer_order_summary AS
SELECT customer_id,
       count(*)         AS total_orders,
       sum(order_total) AS total_spent,
       max(order_date)  AS last_order_date
FROM orders
GROUP BY customer_id;

CREATE UNIQUE INDEX ON customer_order_summary (customer_id);

SELECT * FROM customer_order_summary WHERE total_spent > 1000;   -- customer 101

New orders don't appear until you refresh:

SQL
INSERT INTO orders VALUES (4, 102, '2026-10-01', 1500);
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_order_summary;
SELECT * FROM customer_order_summary WHERE total_spent > 1000;   -- now 101 and 102

A plain REFRESH locks the materialized view, so readers wait until it finishes. CONCURRENTLY lets readers keep using the old data during the refresh. It requires a unique index on the materialized view, which is why the example creates one. It's slower, though, because it compares the old and new results.

PostgreSQL has no built-in scheduler. Run REFRESH from cron, a job runner, the pg_cron extension (available on most managed PostgreSQL services), or your application right after a data load.

Choosing a replacement for an indexed view

For each indexed view you migrate, decide how fresh the data has to be:

RequirementUse
Must be current, and the query is cheap with good indexesA regular view. Check it with EXPLAIN (Day 15) before assuming it's too slow.
Can be minutes or hours oldA materialized view refreshed on a schedule
Must be current, and the query is too expensive to run liveA summary table kept up to date by triggers (Day 10), which is what the indexed view did for you

The third option is the most work, but it's the faithful translation of an indexed view. A statement-level trigger with transition tables can update the summary rows for just the customers touched by each statement.

In DataGrip, views and materialized views appear in separate nodes under the schema. There's no menu action to refresh a materialized view's data, so run REFRESH in a console.

Check yourself

PostgreSQL materialized views only change when refreshed. Schedule a refresh that matches how fresh the dashboard needs to be, or use a regular view or trigger-maintained summary table if it must be live.

Concurrent refresh needs a unique index on the materialized view to match old rows with new ones. Create one on the grouping key, then refresh again.

Nothing extra. PostgreSQL records view dependencies and refuses to drop or change a table a view depends on, unless someone deliberately uses CASCADE.

When the underlying query is already fast with the right indexes on the base tables. Indexed views precompute results, but often a plain view plus a well-chosen index, or a summary table maintained by a trigger, performs well enough without staleness.

Refresh it on a schedule with REFRESH MATERIALIZED VIEW CONCURRENTLY, which needs a unique index and doesn't block readers. Pick the schedule from how stale the data may be, and monitor the refresh job so a failure doesn't leave a dashboard silently out of date.