PostgreSQL Materialized Views

In this tutorial you’ll discover what PostgreSQL materialized views are and in what cases you should consider working with them. Moreover, you will get familiar with a convenient IDE by Devart – dbForge Studio for PostgreSQL that is designed to make your database development routine easier and more pleasant.

Contents

• What is a materialized view in PostgreSQL?
• Difference between Postgres view and materialized view
• Creating materialized views in PostgreSQL
• Example of creating a materialized view in PostgreSQL
• Refreshing materialized views
• PostgreSQL materialized views and indexes
• How to automate a materialized view refresh?
• Deleting materialized views
• Replacing materialized views
• Conclusion

Software developers deal with databases regularly. Also, many of them specialize particularly in databases. These professionals know everything about different systems, how to make the most of them, and how to save themselves from going insane when the work routines offer yet another demand for them.

It often happens that a project involves complicated operations. Joining many tables, using specific custom logic for them, and working with the query results are among the most popular and painful challenges that database professionals face. When data analysis or business analytics are involved, such troubles become regular.

In PostgreSQL, one of the most efficient solutions for this issue is applying a materialized view.

What is a materialized view in PostgreSQL?

A materialized view is a cached result of a complicated query. We can save this data and work with it as with a traditional table. You can even add primary keys and indexes to this object.

When needed, we can refresh it by executing the query on the underlying level. Otherwise, that query is not executed when we refer to this materialized view, and only the cache is available to us. A materialized View is not virtual. In PostgreSQL, this data is stored physically on a disk.

Database specialists turn to materialized views quite frequently. It is a common method of ensuring quick data access, especially for BI applications and data warehouses. The primary reason why a Postgres materialized view is so helpful is the speed of work it ensures.

Difference between Postgres view and materialized view

If we compare Postgres view vs materialized view, we’ll see that the results turn out to be 4 times slower when using views.

Using the data stored by the materialized view, you can retrieve the results drastically faster. When the data is synchronized directly with the database, the time is only spent on the INSERT, UPDATE, and DELETE operations. The network load is reduced, the speed becomes much higher, and the overall productiveness increases.

Both materialized views and traditional Postgres views serve their purposes well. The two most significant factors defining which of the two is the right choice are the speed of performance and the need for fresh, updated data.

If your query is rather fast to execute, or your situation allows you to tolerate the slow performance, it is better to apply the traditional view. It always brings you the most up-to-date results. This is also the primary criterion when you require fresh data – a view guarantees to deliver it.

However, when the query is heavy and slow, and time delays are unacceptable, it is better to refer to a materialized view. Of course, this also suggests that you agree to work with data that can be outdated to a certain degree. Here, everything depends on your particular scenario and requirements.

Creating materialized views in PostgreSQL

PostgreSQL allows creating a materialized view through a dedicated statement:

CREATE MATERIALIZED VIEW view_name
AS
query
WITH [NO] DATA;

As you might have noticed, there are the following mandatory elements in this Postgres create materialized view statement: 

• view_name is the name of your materialized view in Postgres
• query is that complex query that supplies the data for our materialized view 
• WITH DATA/ WITH NO DATA is the parameter that specifies if the query has to load the data (the query results) at once during the object creation. If we need it while creating a Postgres materialized view, we specify the WITH DATA parameter. If not – use the WITH NO DATA one. 

Example of creating a materialized view in PostgreSQL

In this article, we are going to use dbForge Studio for PostgreSQL to demonstrate the various examples of how to work with materialized views. This solution provides functionality for PostgreSQL data reporting, data editing, data import and export, building pivot tables, and master-detail relations. With this convenient and user-friendly IDE, you will be able to enjoy such features as PostgreSQL Code Completion, SQL Code Formatting, SQL Syntax Check, and many others.

Have a look at the example of the Postgres CREATE MATERIALIZED VIEW script:

CREATE MATERIALIZED VIEW tickets_view AS
SELECT ticket_no, passenger_name
FROM tickets
WITH DATA;

In this script, you can see the entire query we execute to retrieve the data. It has several joins and works according to its specific logic. Our case suggests that executing that query will take quite a long time, and we can afford to work with cached results. Thus, the materialized view will include the data populated by that query.

When everything is ready, we can compose and execute other queries against the tickets_view in the same way as with a database table or a traditional view.

Refreshing materialized views

As we already know, our materialized view does not keep the data up-to-date all the time. We can’t insert the data into that “table” either. Thus, to populate a materialized view with data from the query results after creating or updating that data later, we need to refresh the object. It forces the query in the core of the materialized view to re-execute. This way, the object gets updated and provides the latest, fresh results.

The command is simple:

REFRESH MATERIALIZED VIEW view_name;

For instance, we want to update the data in the tickets_view we created earlier. The command will be as follows:

REFRESH MATERIALIZED VIEW tickets_view;

There is one essential issue to note. PostgreSQL will lock the materialized view table while refreshing. You won’t be able to execute any queries until it gets all the data updated, and that takes time. However, there is a way out – the CONCURRENTLY option for the REFRESH command.

The syntax will be as follows:

REFRESH MATERIALIZED VIEW CONCURRENTLY tickets_view;

With the CONCURRENTLY parameter applied, Postgres prepares a temporary updated materialized view version. The system will compare the two versions and identify the differences between them. Then, it will only apply the changes to the original materialized view table using the standard INSERT and UPDATE operations. This Postgres REFRESH MATERIALIZED VIEW method leaves the original table unlocked and available for the tasks to perform on it.

PostgreSQL has implemented the CONCURRENTLY option in the 9.4 version. Therefore, you need this version or higher to use the command without losing access to the table while refreshing it.

PostgreSQL materialized views and indexes

In order to apply the CONCURRENTLY option to the REFRESH MATERIALIZED VIEW command in Postgres, you need to make sure there is at least one unique index in the materialized view in question. As we mentioned earlier, it is possible to add indexes to these objects since they are similar to traditional tables. We add indexes to the columns in the same way, using SQL:

CREATE INDEX my_index_1 ON user_view (necessary_column);
CREATE INDEX my_index_2 ON user_view (another _column);

How to refresh a materialized view automatically

Refreshing materialized views in Postgres can be manual or automated. To make the process automatic, we can create a schedule or set the database triggers to execute the REFRESH command. For instance, you can create a trigger that launches the updating process when any changes take place in the tables that feed the materialized view. It synchronizes the data for all the users working with the tables.

Deleting materialized views

If you don’t need any particular materialized view anymore, you can drop it. To do that, you need to execute the following Postgres command:

DROP MATERIALIZED VIEW view_name;

In our case it would be:

DROP MATERIALIZED VIEW tickets_view;

An alternative way to drop a materialized view in dbForge Studio for PostgreSQL is to delete it using the graphic interface:

1. Right-click the materialized view in question
2. Point to Generate Script As
3. Point to Drop
4. Click To New SQL Window

The IDE will generate the corresponding DROP script and you will be able to execute it just like any other script:

Note: Postgres DROP MATERIALIZED VIEW command won’t allow you to delete this specific object if it is in use by other processes. First, you need to check and make sure that no references involve that materialized view, and only then drop it.

Materialized views are helpful in many cases as tools for caching data. If you are fine working with the cache, feel free to use these tools. However, it is important to keep in mind that materialized views are not the panacea for the slow query performance. Before choosing which method to apply, consider all the circumstances and requirements.

Replacing materialized views

Sometimes you need to move, replace, or add particular elements within a materialized view. To complete this task, the ALTER MATERIALIZED VIEW Postgres command will be helpful. It modifies the auxiliary characteristics of a materialized view that already exists.

The syntax will be as follows:

ALTER MATERIALIZED VIEW [ IF EXISTS ] name
    action [, ... ]
ALTER MATERIALIZED VIEW name
    [ NO ] DEPENDS ON EXTENSION extension_name
ALTER MATERIALIZED VIEW [ IF EXISTS ] name
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER MATERIALIZED VIEW [ IF EXISTS ] name
    RENAME TO new_name
ALTER MATERIALIZED VIEW [ IF EXISTS ] name
    SET SCHEMA new_schema
ALTER MATERIALIZED VIEW ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
    SET TABLESPACE new_tablespace [ NOWAIT ]

Where action is one of the following:

    ALTER [ COLUMN ] column_name SET STATISTICS integer
    ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
    ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
    ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
    ALTER [ COLUMN ] column_name SET COMPRESSION compression_method
    CLUSTER ON index_name
    SET WITHOUT CLUSTER
    SET TABLESPACE new_tablespace
    SET ( storage_parameter [= value] [, ... ] )
    RESET ( storage_parameter [, ... ] )
    OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }

In this script:

• name is a name of a materialized view that already exists
• column_name is a name of a column
• extension_name is the name of the extension that will be used by the materialized view. When an extension is dropped, a materialized view that is tagged as dependent is immediately discarded
• new_column_name is a new name for a column that already exists
• new_owner is the name of the new materialized view owner
• new_name is a placeholder for a new name for the materialized view
• new_schema is a placeholder for a new schema for the materialized view

Note: You must have the CREATE privilege in the new schema to modify the schema of a materialized view. To change the owner, you must be a member of the new owning role, either directly or indirectly, and that role must have the CREATE privilege in the materialized view’s schema.

Conclusion

In this article, we have discussed PostgreSQL materialized views and how to create, refresh, update and delete those once you do not need them anymore. When it comes to working with Postgres databases, dbForge Studio for PostgreSQL is among the best solutions on the market. This IDE allows users to create, develop, and execute queries, edit and adjust the code to their requirements in a convenient and user-friendly interface.