PostgreSQL materialized view process analysis

This article mainly introduces the process analysis of PostgreSQL materialized views. The article introduces it in great detail through sample code, which has certain reference learning value for everyone's study or work. Friends in need can refer to it.

1. Create a view

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name
[ (column_name [, ...] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ TABLESPACE tablespace_name ]
AS query
[ WITH [ NO ] DATA ]

2. Delete the view

DROP MATERIALIZED VIEW table_name

3. Create an index

CREATE INDEX idx_index_name ON table_name USING index(column);

4. Manually refresh the view

REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
  [ WITH [ NO ] DATA ]

The creation mechanism of materialized views is the same as that of MySQL view tables. Relevant information is queried directly from the corresponding table. However, the query of materialized views is the same as that of ordinary tables. It is equivalent to a cache mechanism, storing complex query results in materialized views, but a refresh operation is required each time to update new data. It is suitable for requirements that are not particularly high for data real-time performance. The version is only available after PostgreSQL 9.3. When refreshing the view in version 9.3, query work cannot be performed. After version 9.4, adding the CONCURRENTLY parameter can support read-time queries, but indexes must be available and the refresh speed will be slower (time is exchanged for query locks). Incremental queries are not currently supported.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.