Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of PostgreSQL materialized view, which is very detailed and has certain reference value. Friends who are interested must finish 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. Refresh the view manually

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

The creation mechanism of the materialized view is the same as the view table of mysql. It queries the relevant information directly from the corresponding table, but the query of the materialized view is the same as the ordinary table, which is equivalent to a cache mechanism to store the complex query results in the materialized view, but each time the refresh operation is needed to update the new data, which is suitable for the non-high real-time requirements of the data. The version is available after PostgreSQL 9.3. Query work is not allowed when the view is refreshed in version 9.3. After version 9.4, CONCURRENTLY parameters can be added to support query-on-read, but there must be an index and the refresh speed will be slower (time in exchange for query locks). Incremental queries are not currently supported.

The above is all the content of the article "sample Analysis of PostgreSQL materialized View". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.