"PostgreSQL" materialized view

Source: Internet
Author: User
Tags create index postgresql



The materialized view appears after PostgreSQL 9.3. Materialized views are made up of real data, which is fundamentally different from the general view.



1. The materialized view creation syntax is as follows:


--Create grammar
CREATE MATERIALIZED VIEW table_name
     [(column_name [, ...])]
     [WITH (storage_parameter [= value] [, ...])]
     [TABLESPACE tablespace_name]
     AS query
     [WITH [NO] DATA]

--Refresh syntax
REFRESH MATERIALIZED VIEW [CONCURRENTLY] name
     [WITH [NO] DATA] 



2. Create a materialized view diagram example


--Create test table
test = # CREATE TABLE TEST01 (ID INT);
CREATE TABLE
test = # INSERT INTO TEST01 SELECT GENERATE_SERIES (1,1000000);
INSERT 0 1000000

--Create a materialized view
test = # CREATE MATERIALIZED VIEW V_TEST01 AS SELECT * FROM TEST01;
SELECT 1000000
test = #
test = # INSERT INTO TEST01 VALUES (1000001);
INSERT 0 1
test = # \ timing
Timing is on.

--Refresh without CONCURRENTLY parameter
test = # REFRESH MATERIALIZED VIEW V_TEST01;
REFRESH MATERIALIZED VIEW
Time: 1888.860 ms
test = # INSERT INTO TEST01 VALUES (1000002);
INSERT 0 1
test = # CREATE UNIQUE INDEX INX_ID ON V_TEST01 (ID);
CREATE INDEX

--Add CONCURRENTLY parameter refresh
test = # refresh materialized view concurrently v_test01;
REFRESH MATERIALIZED VIEW
Time: 100476.707 ms

It can be seen that after adding the CONCRRENTLY parameter, the refresh time increases significantly. But the update with the CONCRRENTLY parameter will not block the query operation. 





The end!



2017-08-21



"PostgreSQL" materialized view


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.