What is Materialized View?
A database object that stores the precomputed result of a query as a table, refreshed periodically or on demand to improve read performance and reduce computational overhead.
A materialized view is a database object that contains the results of a query, stored physically on disk like a table. Unlike a standard (virtual) view, which executes its defining query every time it is accessed, a materialized view holds a snapshot of the data at the time of the last refresh. This precomputation trades storage space and refresh cost for significantly faster read queries.
Materialized views are common in data warehouses, reporting systems, and any environment where aggregate queries over large datasets must return quickly. The underlying query can involve joins, aggregations, and complex filtering. The database system stores the result set, and the materialized view can be indexed like a regular table. The data in a materialized view becomes stale over time because it does not reflect every subsequent change to the base tables. Administrators must refresh it either on a schedule (for example, nightly), on demand via a manual command, or incrementally using a mechanism such as a materialized view log (Oracle) or a refresh materialized view concurrently in PostgreSQL.
Database platforms differ in how they implement materialized views. Oracle, PostgreSQL, Snowflake, BigQuery, and Amazon Redshift all support some form of materialized view, but the syntax for refresh strategies, query rewrite, and concurrency varies. In systems that support automatic query rewrite, the optimizer can transparently substitute a materialized view for part of a query even if the query does not explicitly reference the view, as long as the view covers the needed data. This makes materialized views a powerful tool for performance tuning without rewriting application queries.
Key facts
- Stores query results as a physical table, unlike a virtual view which executes each time.
- Requires explicit or scheduled refresh to stay synchronized with base tables.
- Can be indexed to improve access speed beyond the base query benefit.
- Supports query rewrite in some databases for transparent optimization.
- Common in data warehousing for pre-aggregated report tables.
How it works in practice
Related terms
References
More in Databases
ACID
ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties guaranteeing reliable database transaction processing, ensuring data integrity despite concurrent access or failures.
B-Tree Index
A B-Tree index is a self-balancing tree data structure that maintains sorted data for efficient insertion, deletion, and range queries in database systems.
BASE
BASE is a design philosophy for distributed databases that prioritizes availability and partition tolerance over immediate consistency, making it a looser alternative to ACID in NoSQL systems.
CAP Theorem
CAP theorem states that a distributed data system cannot simultaneously provide consistency, availability, and partition tolerance; it can only guarantee two of the three.
Connection Pool
A managed cache of database connections that applications reuse rather than opening and closing a connection for each query, reducing latency and server load.
Failover
Failover is the process of automatically or manually promoting a replica database to primary when the active node fails, ensuring continued availability.
Foreign Key
A column or set of columns in a database table whose values must match a primary key in another table, ensuring referential integrity between the two tables.
Hash Index
A data structure that maps keys to storage locations using a hash function, providing constant-time equality lookups but no ordered or range scans.
NoSQL
NoSQL is a family of non-relational database systems designed for flexible schemas, horizontal scaling, and high-throughput data access that traditional SQL databases cannot easily provide.
Read Replica
A read replica is an asynchronously updated copy of a primary database instance used to offload and scale read-only query traffic without affecting the source database's write performance.