Views And Materialized Views in PostgreSQL With Example

Views And Materialized Views

Although both views and materialised views in PostgreSQL offer a means of storing named queries, their approaches to data storage and refresh are essentially different. A view is a virtual table that wraps a SELECT statement or acts as a named query. Its content is dynamically calculated each time it is accessed, rather than being physically stored. Views are mostly used to make complex searches simpler, make code more modular, and offer an abstraction layer that can help with security by enabling row-level authorisation or limiting data exposure. Views are implemented internally by PostgreSQL utilising its rule system, which transforms a CREATE VIEW command into a RETURN rule.

This enables the query planner to fully optimise queries using views. Simple views are automatically updatable as of PostgreSQL. Which means that DML operations can be carried out directly on them as long as specific requirements are met. These requirements include being constructed on a single table or updatable view and not having complex clauses or aggregate functions. In order to make more complicated views updatable, triggers or rules may be required. To stop data changes that would render rows invisible through the display, utilise the WITH CHECK OPTION.

Views

PostgreSQL views are basically named queries or wrappers for SELECT statements. As a pseudo-table, it does not actually store data, but it seems like a regular table to a SELECT query. As an alternative, PostgreSQL runs the underlying SELECT query when a view is requested, displaying the results as though they were from a table.

The rule system in PostgreSQL is essential to the implementation of views. A table with a unique RETURN rule is used internally to model a standard view. This recommendation requires the system to rewrite SELECT statements targeting the view to use the view’s underlying SELECT definition. The PostgreSQL parser considers tables and views “relations” and makes no distinction between them. Optimised execution plans are made possible by this design, which gives the query planner thorough knowledge about all the tables and connections involved.

Purposes and Advantages of Views

Views have a number of uses and advantages in database design and application development.

Simplifying Complex Queries and Increasing Code Modularity: Views can encode sophisticated business logic or JOIN operations so that users can query them using more straightforward SELECT expressions.

Data Abstraction and Interface: The interface and data abstraction layer they offer acts as a barrier between the physical data model and high-level programming languages and applications. If the view definition is adjusted to keep the uniform interface, underlying table alterations may not require application modifications.

Security and Authorization: Views can apply row- or column-level security by restricting table access to certain rows or columns. Granting privileges on views restricts data access without providing users direct table access.

Implementing Last-Minute Changes: Last-minute PostgreSQL modifications are made easier by code-level flexibility and configuration adjustments to minimise interruption. For instance, procedural SQL languages allow last-minute bug patches and other changes without service disruption. With views, you can make last-minute modifications without redeploying the software.

View Dependency Tracking: With view dependency tracking, PostgreSQL automatically tracks relationships between database objects, including views. “Dangling views” are avoided since this makes sure that a view cannot be removed if other objects rely on it.

Types of Views

Standard (Non-Materialized) Views: Most views, called standard (non-materialized), display data from the underlying tables in real time when requested.

Updatable Views: PostgreSQL supports automatic-updating single-table views. Such views can directly execute INSERT, UPDATE, and DELETE actions, which immediately convert to table operations. To make more complicated views updatable, such as those with many tables or sophisticated logic, triggers or the rule system more especially, INSTEAD OF triggers are needed. Any rows that are added or modified via the view can be made visible within its specified scope by using the WITH CHECK OPTION.

Temporary Views: Views that are only visible during a user session are known as temporary views, and they are immediately removed at the conclusion of the session.

Recursive Views: Like recursive Common Table Expressions (CTEs), recursive views can be used to query data that is arranged in a hierarchy.

Code Example:

-- Table: employees
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);
-- Sample data
INSERT INTO employees VALUES
(1,'Alice',NULL),
(2,'Bob',1),
(3,'Carol',1),
(4,'David',2),
(5,'Eve',2),
(6,'Frank',3),
(7,'Grace',4);
-- Recursive CTE to get all subordinates of Alice (id = 1)
WITH RECURSIVE subordinates AS (
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees WHERE manager_id = 1
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, s.level+1
    FROM employees e JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates;
-- To count them:
WITH RECURSIVE subordinates AS (
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees WHERE manager_id = 1
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, s.level+1
    FROM employees e JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT COUNT(*) FROM subordinates;

Output:

CREATE TABLE
INSERT 0 7
 employee_id | name  | manager_id | level 
-------------+-------+------------+-------
           2 | Bob   |          1 |     1
           3 | Carol |          1 |     1
           4 | David |          2 |     2
           5 | Eve   |          2 |     2
           6 | Frank |          3 |     2
           7 | Grace |          4 |     3
(6 rows)
 count 
-------
     6
(1 row)

Materialized Views

A materialised view varies from a conventional view in that its contents are physically saved in the database, like a table. It is basically a query result that has been cached. Large, comparatively static datasets, such those found in data warehousing and Online Analytical Processing (OLAP) systems, can benefit greatly from materialised views when it comes to improving query efficiency.

Creation and Refreshment: The sentence generate MATERIALISED VIEW is used to generate a materialised view. DATA or NO DATA can be entered during creation. If it is empty, trying to get data from it will fail. A materialised view’s data is not updated; it represents the underlying tables’ state at the view’s last refresh. REFRESH MATERIALISED VIEW must be executed to refresh its contents. PostgreSQL does not automatically cache materialised views; users need to automate the refresh process with tools like triggers, cron tasks, or pgAgent processes.

A materialised view was blocked for reads while refreshing before PostgreSQL . PostgreSQL and later’s CONCURRENTLY keyword refreshes materialised views with unique indexes without locking reads. However, it usually takes longer to finish a concurrent refresh.

Performance Optimization Features:

Indexing: Materialised views can have indexes established on them, just like conventional tables, because they are tangible objects. By doing this, query access to the data in the materialised view is greatly accelerated.

Clustering: To improve read speed, particularly large materialised views’ physical storage can be grouped according to an index using the CLUSTER command. By specifying an ORDER BY clause during materialised view generation, it can keep a chosen sort order upon refresh without re-clustering, however refresh time may rise.

Reduced Query Execution Time: Pre-calculating and storing query results in materialised views speeds up application response times for commonly viewed reports or analytics by eliminating the need to conduct complex queries.

Limitations of Materialized Views

No CREATE OR REPLACE: Materialised views are more difficult to alter than regular views when using CREATE OR REPLACE. It is necessary to remove and reconstruct even little changes, which also implies that all related indexes are lost.

Manual Refresh: Materialised views in PostgreSQL require manual refreshment because their contents are not updated in real time. Materialised view data represents the condition of its underlying tables at its last refresh. Explicitly running REFRESH MATERIALISED VIEW updates its contents.

Storage Overhead: With Materialised Views and Multi-Version Concurrency Control (MVCC), PostgreSQL storage overhead is important. Different from normal views, materialised views save their contents in the database like tables. While favourable for query performance, physical storage requires more disc space.

Comparison: Views vs. Materialized Views

Data abstraction, query speed, and security are improved by standard views, which always present the latest data without retaining it. Materialised views physically store and index pre-computed query responses, swapping real-time data for huge performance increases, making them excellent for analytical workloads where query speed more important than data timeliness.