PostgreSQL Materialized Views: From Manual Refresh to Real-Time

Introduction

Your PostgreSQL materialized view summarizes order revenue by region. It runs against 50 million rows, and refreshing it takes four minutes. You schedule it with pg_cron every 15 minutes, but your operations team still complains about stale numbers. Meanwhile, a developer adds REFRESH MATERIALIZED VIEW CONCURRENTLY to avoid locking reads during the refresh, only to discover it requires a unique index and still recomputes the entire result set.

PostgreSQL materialized views are a powerful feature for caching expensive query results. They work well when your data changes infrequently and staleness of minutes or hours is acceptable. But as real-time expectations grow, the manual refresh model becomes a bottleneck. Teams cobble together cron jobs, worry about overlapping refreshes, and accept that their dashboards are always slightly behind.

This guide covers how PostgreSQL materialized views work, where they fall short, and how to move from scheduled full refreshes to automatic incremental updates using RisingWave, a PostgreSQL-compatible streaming database. You will see complete SQL examples for both PostgreSQL and RisingWave, side-by-side comparisons, and a concrete migration path.

How do materialized views work in PostgreSQL?

A materialized view in PostgreSQL is a database object that stores the result of a query as a physical table. Unlike a regular view, which re-executes its defining query on every read, a materialized view persists the computed data on disk. Reads are fast because they scan the precomputed result, not the underlying tables.

Creating a materialized view

The syntax follows standard SQL. Here is a realistic example that computes order analytics for an e-commerce platform:

-- Create the underlying table
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    product_name VARCHAR(100),
    amount DECIMAL(10, 2),
    status VARCHAR(20),
    region VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Insert sample data
INSERT INTO orders (customer_id, product_name, amount, status, region, created_at) VALUES
    (101, 'Wireless Headphones', 79.99, 'completed', 'us-east', '2026-03-01 10:00:00'),
    (102, 'USB-C Hub', 45.00, 'completed', 'us-west', '2026-03-01 11:30:00'),
    (103, 'Mechanical Keyboard', 129.99, 'pending', 'eu-west', '2026-03-02 09:15:00'),
    (104, 'Monitor Stand', 34.50, 'completed', 'us-east', '2026-03-02 14:20:00'),
    (105, 'Webcam HD', 89.00, 'completed', 'us-west', '2026-03-03 08:45:00'),
    (106, 'Laptop Sleeve', 25.00, 'refunded', 'eu-west', '2026-03-03 16:00:00'),
    (107, 'Wireless Mouse', 39.99, 'completed', 'us-east', '2026-03-04 10:30:00'),
    (108, 'HDMI Cable', 12.99, 'completed', 'us-west', '2026-03-04 13:00:00');

-- Create the materialized view
CREATE MATERIALIZED VIEW order_summary AS
SELECT
    region,
    status,
    COUNT(*) AS order_count,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY region, status;

Query the materialized view:

SELECT * FROM order_summary ORDER BY region, status;

Expected output:

 region  |  status   | order_count | total_revenue | avg_order_value
---------+-----------+-------------+---------------+-----------------
 eu-west | pending   |           1 |        129.99 |          129.99
 eu-west | refunded  |           1 |         25.00 |           25.00
 us-east | completed |           3 |        154.48 |           51.49
 us-west | completed |           3 |        146.99 |           49.00
(4 rows)

The result is physically stored. Queries against order_summary read from this stored result, not from the orders table.

Refreshing materialized views

PostgreSQL materialized views are snapshots. They do not update when the underlying data changes. You must explicitly refresh them.

Standard refresh (blocking):

-- This blocks all reads on the materialized view until the refresh completes
REFRESH MATERIALIZED VIEW order_summary;

During a standard refresh, any SELECT query against the materialized view blocks until the refresh finishes. For a view that takes minutes to refresh, this means your application sees query timeouts or hangs.

Concurrent refresh (non-blocking reads):

-- First, create a unique index (required for CONCURRENTLY)
CREATE UNIQUE INDEX idx_order_summary_region_status
    ON order_summary (region, status);

-- Now refresh without blocking reads
REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;

The CONCURRENTLY option lets existing queries continue reading the old data while the refresh runs. PostgreSQL builds the new result set in the background, compares it with the old one, and applies the differences. This avoids blocking reads but has requirements:

• The materialized view must have at least one unique index that covers all rows (no partial indexes).
• The view must already be populated (you cannot use CONCURRENTLY on a view created with WITH NO DATA).
• Only one REFRESH can run at a time per materialized view.

Indexing materialized views

Because materialized views store data physically, you can index them just like regular tables:

-- Speed up queries that filter by region
CREATE INDEX idx_order_summary_region ON order_summary (region);

-- Speed up queries that sort by total_revenue
CREATE INDEX idx_order_summary_revenue ON order_summary (total_revenue DESC);

Indexes on materialized views improve read performance significantly, especially for dashboards that filter or sort the precomputed results.

Complete workflow: insert, query, refresh, query again

Here is the full lifecycle showing how data changes require manual intervention:

-- 1. Insert new orders after the materialized view was created
INSERT INTO orders (customer_id, product_name, amount, status, region, created_at) VALUES
    (109, 'Standing Desk', 499.00, 'completed', 'us-east', '2026-03-05 09:00:00'),
    (110, 'Desk Lamp', 55.00, 'completed', 'eu-west', '2026-03-05 10:30:00');

-- 2. Query the materialized view: still shows OLD data
SELECT region, order_count, total_revenue
FROM order_summary
WHERE region = 'us-east' AND status = 'completed';

 region  | order_count | total_revenue
---------+-------------+--------------
 us-east |           3 |       154.48
(1 row)

The new $499.00 standing desk order is not reflected. The materialized view still shows 3 orders and $154.48.

-- 3. Manually refresh
REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;

-- 4. Query again: now shows updated data
SELECT region, order_count, total_revenue
FROM order_summary
WHERE region = 'us-east' AND status = 'completed';

 region  | order_count | total_revenue
---------+-------------+--------------
 us-east |           4 |       653.48
(1 row)

After the refresh, the view reflects the new order. But between step 1 and step 3, anyone querying the view saw stale data.

What are the limitations of PostgreSQL materialized views?

PostgreSQL materialized views work well for static reporting. For anything requiring fresh data, they have structural limitations that no amount of configuration can fully resolve.

Manual refresh is required

PostgreSQL has no built-in mechanism to refresh materialized views automatically when underlying data changes. You need external tooling:

-- Using pg_cron to schedule a refresh every 5 minutes
SELECT cron.schedule(
    'refresh_order_summary',
    '*/5 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary'
);

This creates a 0-to-5-minute staleness window. If the refresh takes 2 minutes to run, you have a 7-minute gap where data could be stale. Overlapping refreshes can cause failures, and there is no built-in coordination to prevent them.

Full recomputation on every refresh

Every refresh recomputes the entire query from scratch. If your materialized view aggregates 100 million rows and only 500 rows changed since the last refresh, PostgreSQL still scans all 100 million rows. The cost scales with total data size, not change size.

This means refresh time grows linearly as your data grows, regardless of your actual change rate. A view that refreshed in 30 seconds with 10 million rows might take 5 minutes with 100 million rows.

Blocking or slow concurrent refresh

Standard REFRESH blocks all reads. REFRESH CONCURRENTLY avoids the read lock but:

• Still performs a full recomputation
• Requires a unique index
• Must diff the old and new result sets row by row, which can be slower than a standard refresh when many rows change
• Only one refresh can run at a time per view

For large views with frequent changes, neither option provides acceptable performance.

No streaming data support

PostgreSQL materialized views can only query data that already exists in PostgreSQL tables. They cannot consume data directly from event streams like Apache Kafka, message queues, or CDC (Change Data Capture) feeds. If your data flows through Kafka, you need a separate ingestion pipeline to land data in PostgreSQL before your materialized views can see it.

Stale data between refreshes

This is the fundamental limitation. Between refreshes, the materialized view shows outdated results. For use cases like real-time dashboards, fraud detection, or operational monitoring, any staleness window is a problem. Reducing the refresh interval helps but increases compute costs and the risk of overlapping refreshes.

How does RisingWave extend PostgreSQL materialized views to real-time?

RisingWave is a PostgreSQL-compatible streaming database that uses incremental view maintenance instead of full refresh. When you create a materialized view in RisingWave, it continuously processes incoming data changes and updates the view result incrementally, with sub-second latency.

Same SQL, different engine

RisingWave uses PostgreSQL-compatible SQL. The materialized view syntax is nearly identical:

-- RisingWave: creating a materialized view looks the same
CREATE MATERIALIZED VIEW order_summary AS
SELECT
    region,
    status,
    COUNT(*) AS order_count,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY region, status;

The key difference: you never run REFRESH MATERIALIZED VIEW. RisingWave detects every insert, update, and delete on the source table and propagates only the change (the delta) through the query plan. If one new order arrives, RisingWave increments the relevant counter and adds to the revenue total. It does not rescan the entire table.

Automatic incremental refresh

RisingWave implements incremental view maintenance based on a dataflow architecture. When source data changes, the change event flows through a computation graph that mirrors your SQL query. Each operator (filter, join, aggregation) processes only the delta, not the full dataset.

The result: materialized view updates complete in milliseconds, not minutes. There is no REFRESH command, no cron job, and no staleness window.

Native streaming sources

Unlike PostgreSQL, RisingWave can consume data directly from streaming platforms. You can create materialized views over Kafka topics, CDC feeds from PostgreSQL or MySQL, and other event sources:

-- Create a CDC source from your existing PostgreSQL database
CREATE SOURCE pg_source WITH (
    connector = 'postgres-cdc',
    hostname = 'your-pg-host.example.com',
    port = '5432',
    username = 'replication_user',
    password = 'your_password',
    database.name = 'ecommerce'
);

-- Create a table that ingests from the CDC source
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_name VARCHAR,
    amount DECIMAL,
    status VARCHAR,
    region VARCHAR,
    created_at TIMESTAMP
) FROM pg_source TABLE 'public.orders';

-- Create a materialized view over the CDC table
-- This view updates automatically as orders change in PostgreSQL
CREATE MATERIALIZED VIEW order_summary AS
SELECT
    region,
    status,
    COUNT(*) AS order_count,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY region, status;

With this setup, every insert, update, or delete in your PostgreSQL orders table is captured via CDC, streamed into RisingWave, and the order_summary materialized view updates within seconds. No cron jobs, no manual refresh, no stale data.

Side-by-side comparison

Here is how the same analytics workflow differs between PostgreSQL and RisingWave:

PostgreSQL workflow:

-- 1. Data lives in PostgreSQL tables
-- 2. Create materialized view
CREATE MATERIALIZED VIEW order_summary AS SELECT ... FROM orders GROUP BY ...;
-- 3. Schedule refresh
SELECT cron.schedule('*/5 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary');
-- 4. Query (data may be up to 5+ minutes stale)
SELECT * FROM order_summary WHERE region = 'us-east';
-- 5. When data grows: refresh takes longer, increase interval, accept more staleness

RisingWave workflow:

-- 1. Connect to source data (PostgreSQL CDC, Kafka, or direct inserts)
CREATE SOURCE pg_source WITH (connector = 'postgres-cdc', ...);
CREATE TABLE orders (...) FROM pg_source TABLE 'public.orders';
-- 2. Create materialized view (same SQL)
CREATE MATERIALIZED VIEW order_summary AS SELECT ... FROM orders GROUP BY ...;
-- 3. Query (data is always fresh, sub-second latency)
SELECT * FROM order_summary WHERE region = 'us-east';
-- 4. When data grows: incremental cost stays proportional to change rate, not data size

How do you migrate from PostgreSQL materialized views to RisingWave?

If you already use PostgreSQL materialized views and want real-time results, you can migrate to RisingWave without rewriting your application. The process uses CDC to replicate your PostgreSQL data into RisingWave, where your materialized views update automatically.

Step 1: Set up CDC from PostgreSQL to RisingWave

First, configure your PostgreSQL instance for logical replication. This requires PostgreSQL 10 or later:

-- On your PostgreSQL server: check that wal_level is set to 'logical'
SHOW wal_level;
-- If not 'logical', update postgresql.conf:
-- wal_level = logical
-- Then restart PostgreSQL

-- Create a replication user (if you don't already have one)
CREATE ROLE replication_user WITH REPLICATION LOGIN PASSWORD 'secure_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replication_user;

Then, in RisingWave, create the CDC source connection:

-- Connect RisingWave to your PostgreSQL database
CREATE SOURCE pg_cdc_source WITH (
    connector = 'postgres-cdc',
    hostname = 'your-pg-host.example.com',
    port = '5432',
    username = 'replication_user',
    password = 'secure_password',
    database.name = 'ecommerce'
);

For a detailed walkthrough, see the RisingWave PostgreSQL CDC guide.

Step 2: Recreate your materialized views in RisingWave

Create tables in RisingWave that mirror your PostgreSQL tables via CDC:

-- Mirror the orders table from PostgreSQL
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_name VARCHAR,
    amount DECIMAL,
    status VARCHAR,
    region VARCHAR,
    created_at TIMESTAMP
) FROM pg_cdc_source TABLE 'public.orders';

Then recreate your materialized views using the same SQL:

-- Same query as your PostgreSQL materialized view
CREATE MATERIALIZED VIEW order_summary AS
SELECT
    region,
    status,
    COUNT(*) AS order_count,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY region, status;

RisingWave backfills the existing data from PostgreSQL and then continuously processes new changes. The CREATE MATERIALIZED VIEW syntax supports the same SQL constructs you use in PostgreSQL: joins, aggregations, window functions, and subqueries.

Step 3: Point applications to RisingWave for real-time queries

RisingWave speaks the PostgreSQL wire protocol. Your applications can connect using the same tools:

• psql: psql -h risingwave-host -p 4566 -d dev -U root
• JDBC: Change the connection URL from jdbc:postgresql://pg-host:5432/ecommerce to jdbc:postgresql://rw-host:4566/dev
• Any PostgreSQL driver: Python (psycopg2), Node.js (pg), Go (pgx), Ruby (pg gem)

No ORM changes, no query rewrites. The application issues the same SELECT * FROM order_summary and gets fresh results.

What changes vs. what stays the same

What you remove:

• pg_cron schedules for REFRESH MATERIALIZED VIEW
• Application-level refresh triggers
• Unique indexes that only existed for CONCURRENTLY
• Monitoring and alerting for stale materialized views

What stays the same:

• SQL query syntax for creating views
• SQL query syntax for reading views
• Client libraries and connection drivers
• JDBC, psql, and ORM compatibility
• Indexes on materialized views for query performance (RisingWave also supports indexes)

FAQ

Can I use the same SQL for PostgreSQL and RisingWave materialized views?

Yes. RisingWave supports PostgreSQL-compatible SQL for CREATE MATERIALIZED VIEW. Most queries that work in PostgreSQL work in RisingWave without modification. The main differences are in source definitions (RisingWave adds streaming connectors) and some PostgreSQL-specific functions. Check the RisingWave SQL reference for any syntax variations.

How often should I refresh PostgreSQL materialized views?

It depends on your freshness requirements and refresh cost. Common intervals range from every 1 minute to every hour. Shorter intervals mean fresher data but higher compute usage and greater risk of overlapping refreshes. If you need sub-minute freshness, PostgreSQL's refresh model becomes impractical, and an incremental approach like RisingWave's incremental materialized views is a better fit.

Does RisingWave replace PostgreSQL?

No. RisingWave complements PostgreSQL. PostgreSQL remains your primary transactional database (OLTP). RisingWave sits alongside it, consuming changes via CDC, and serves real-time materialized views for analytics, dashboards, and operational queries. Your applications can read from PostgreSQL for transactional data and from RisingWave for real-time aggregated results.

What is the performance difference between PostgreSQL and RisingWave materialized views?

PostgreSQL refreshes materialized views by recomputing the full query, so refresh time scales with total data size. A view over 100 million rows takes the same time to refresh whether 10 rows or 10 million rows changed. RisingWave uses incremental maintenance, so update cost scales with the size of the change. For a 100-million-row dataset with 1,000 new rows per second, RisingWave updates the materialized view continuously in milliseconds, while PostgreSQL would need minutes for each full refresh cycle.

Conclusion

PostgreSQL materialized views solve a real problem: caching expensive query results for fast reads. But the manual refresh model introduces staleness, blocking, and scaling challenges that compound as data volumes and freshness requirements grow.

Key takeaways:

• PostgreSQL materialized views require manual REFRESH and recompute the entire query every time
• REFRESH CONCURRENTLY avoids blocking reads but still performs full recomputation and requires a unique index
• Incremental view maintenance (as implemented by RisingWave) processes only data changes, reducing update cost from O(total data) to O(change size)
• RisingWave uses PostgreSQL-compatible SQL, so migration requires minimal query changes
• CDC from PostgreSQL to RisingWave lets you keep PostgreSQL as your transactional database while getting real-time materialized views

Ready to try this yourself? Try RisingWave Cloud free, with no credit card required. Sign up here.

Join our Slack community to ask questions and connect with other stream processing developers.