Postgres views and materialized views in a database

Excerpt from: http://www.cnblogs.com/yjf512/p/4402096.html

The view in Postgres scans the sub-table when it is queried, while materialized views actually save the data in a single table. Materialized views are something that is only after 9.3.

 

Create a View

CREATE OR REPLACE VIEW student_view asselect  *from     student    left  JOIN  teacher    on= teacher.id;

Create a materialized view

CREATE VIEW student_view_m asselect  *from     student   leftJOIN  teacher    on= teacher.id;

Student_view go to each table to query, and student_view_m directly to the graph query, and materialized view query efficiency is indeed more than a lot of views. Data fills for materialized views:

Materialized view since it is a real table, it needs to have a data filling process, the command to populate the data is the refresh materialized view

master=> \h REFRESHCommand:     REFRESH MATERIALIZED VIEWDescription: replace the contents of a materialized viewSyntax:REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name    [ WITH [ NO ] DATA ]

Here's a note, if your psql is 9.3, then you'll only see the Help document:

master=> \h REFRESHCommand:     REFRESH MATERIALIZED VIEWDescription: replace the contents of a materialized viewSyntax:REFRESH MATERIALIZED VIEW name    [ WITH [ NO ] DATA ]

There are two ways to introduce an update database that says Postgres, one is a full-volume update, one is an incremental update, and an incremental update is a concurrently parameter when refresh is added. An incremental update is a 9.4 join operation.

So what kind of update is fast? The answer is a full-volume update, and the incremental update does this by making a join operation of the data in the current view chart and the data in query, before the difference is filled.

But a full-volume update blocks the select operation, which means that all of the view's select operations are blocked during your full-volume update, which is not the case with incremental updates. Materialized views should be suitable for scenarios where the real-time nature of the data is not high.

Postgres views and materialized views in a database