Materialized views (MVs) are one of the most requested features in PostgreSQL. They let you store query results physically and access them like a table, which is fantastic for speeding up analytics queries.
But there’s a catch: PostgreSQL materialized views don’t support fast refresh out of the box. Unlike Oracle, which has had fast materialized view refresh capabilities for years, PostgreSQL only supports complete refresh — meaning it recomputes the entire view from scratch every time you refresh it.
This blog will unpack why PostgreSQL doesn’t have fast refresh, the technical challenges involved, and whether we can ever get Oracle-style incremental refresh in the open-source world.
1. The Current State of Materialized Views in PostgreSQL
- PostgreSQL introduced materialized views in version 9.3 (2013).
- The only refresh strategy available is:
REFRESH MATERIALIZED VIEW my_view;
- This does a full recomputation.
- Starting from 9.4, PostgreSQL added the option WITH DATA vs WITH NO DATA and CONCURRENTLY to allow non-blocking refresh, but it’s still a full refresh.
This makes MVs useful for relatively small datasets or batch-updated reports, but not for real-time analytics.
2. Why Oracle Can Do It, But PostgreSQL Can’t (Yet)
Oracle supports fast refresh using Materialized View Logs (MV logs). These are special change-tracking tables automatically maintained on base tables. When you refresh an MV, Oracle only applies the incremental changes from the logs instead of rebuilding everything.
Why hasn’t PostgreSQL done this? There are several reasons:
a) PostgreSQL’s MVCC Model
Postgres uses Multi-Version Concurrency Control (MVCC), which means rows aren’t updated in place; they’re replaced with new versions. To track deltas efficiently, PostgreSQL would need an MV log mechanism that can handle version churn, deletes, and updates while respecting visibility rules.
b) Lack of Built-in Change Data Capture (CDC) for Arbitrary Queries
Oracle has a sophisticated query rewrite + logging system. PostgreSQL doesn’t yet have a generalized CDC layer for arbitrary queries. Logical replication and WAL-based CDC exist, but they’re not tightly integrated with materialized views.
c) Query Complexity
A materialized view can be defined on any query: joins, aggregates, subqueries, DISTINCT, window functions, etc. Maintaining logs for all of these is much harder than tracking simple insert/update/delete changes. Oracle only supports fast refresh for specific classes of queries. PostgreSQL would need to introduce similar constraints.
3. Is It Possible in PostgreSQL?
Yes — but with limitations. Several approaches are being explored:
a) Third-Party Extensions
Projects like pg-mv-fast-refresh (an extension available on GitHub) attempt to bring Oracle-style refresh to PostgreSQL. They create delta tables (similar to MV logs) and custom functions to apply changes incrementally.
- Pros: Works today, OSS, extensible.
- Cons: Limited support for complex queries, not part of core PostgreSQL.
b) Using Triggers or CDC Tools
You can mimic fast refresh by:
- Creating a log table with triggers to track changes.
- Periodically applying these logs to update your MV. This works but adds application-level complexity and isn’t as seamless as Oracle.
c) Future PostgreSQL Development
There have been discussions in the PostgreSQL developer community about adding incremental view maintenance. It would likely require:
- A standardized MV log mechanism.
- Constraints on which MVs qualify for fast refresh (simple aggregates, joins, filters).
- Integration with logical decoding or replication slots.
But so far, it hasn’t landed in core because it’s a massive engineering effort.
4. Practical Alternatives Today
Until PostgreSQL natively supports fast refresh, here are options you can use:
- Extensions: Try pg-mv-fast-refresh or similar.
- Manual Incremental Updates: Use triggers or CDC pipelines (e.g., Debezium, Kafka, or even plain trigger tables).
- Regular Views + Cache Layer: Instead of MVs, precompute results into summary tables and refresh incrementally with custom logic.
- OLAP Solutions: If you need near-real-time analytics, consider pairing PostgreSQL with OLAP databases like ClickHouse, DuckDB, or TimescaleDB for specific workloads.
5. Conclusion
PostgreSQL’s materialized views are great, but their lack of fast refresh makes them less powerful compared to Oracle. This isn’t because Postgres can’t do it — but because implementing it within PostgreSQL’s MVCC and extensibility model is a massive challenge.
For now, the community relies on extensions and workarounds, while core developers continue discussing how to introduce a standardized incremental refresh mechanism.
So, to answer the big question: Yes, it’s possible to bring Oracle-style fast refresh to PostgreSQL — but it requires careful engineering, constraints on queries, and either an extension or future core development.