On 4th of March, Kevin Grittner committed patch:

Add a materialized view relations. A materialized view has a rule just like a view and a heap and other physical properties like a table. The rule is only used to populate the table, references in queries refer to the materialized data. This is a minimal implementation, but should still be useful in many cases. Currently data is only populated "on demand" by the CREATE MATERIALIZED VIEW and REFRESH MATERIALIZED VIEW statements. It is expected that future releases will add incremental updates with various timings, and that a more refined concept of defining what is "fresh" data will be developed. At some point it may even be possible to have queries use a materialized in place of references to underlying tables, but that requires the other above-mentioned features to be working first. Much of the documentation work by Robert Haas. Review by Noah Misch, Thom Brown, Robert Haas, Marko Tiikkaja Security review by KaiGai Kohei, with a decision on how best to implement sepgsql still pending.

This is huge.

Materialized views are important, and lack of them in PostgreSQL has been quite often brought up by fans of other databases.

Now, we're getting them.

Let's see how it works.

First, let's imagine a view query that is rather slow (which is usually the point in materialized view cases). Something along the lines of:

$ CREATE VIEW test1 AS SELECT 1 AS a WHERE pg_sleep ( 5 ) IS NOT NULL ; CREATE VIEW

Every query to it will take ~ 5 seconds:

$ \timing Timing IS ON . $ SELECT * FROM test1; a --- 1 ( 1 ROW ) TIME : 5008.621 ms $ SELECT * FROM test1; a --- 1 ( 1 ROW ) TIME : 5001.657 ms $ SELECT * FROM test1; a --- 1 ( 1 ROW ) TIME : 5001.519 ms

Nice. Now, I can create materialized view, that will be clearly faster:

CREATE materialized VIEW test2 AS SELECT 1 AS a WHERE pg_sleep ( 5 ) IS NOT NULL ; SELECT 1

Interestingly, it's return text is “SELECT 1" and not “CREATE VIEW". Plus – it took 5 seconds to run.

But now:

$ \timing Timing IS ON . $ SELECT * FROM test2; a --- 1 ( 1 ROW ) TIME : 8.060 ms $ SELECT * FROM test2; a --- 1 ( 1 ROW ) TIME : 0.311 ms $ SELECT * FROM test2; a --- 1 ( 1 ROW ) TIME : 0.263 ms

Nice.

of course – materialized views need to be updated every now and then. This is done using “REFRESH MATERIALIZED VIEW" command, like this:

$ \timing Timing IS ON . $ REFRESH MATERIALIZED VIEW test2; REFRESH MATERIALIZED VIEW TIME : 5037.111 ms

Unfortunately, during view recreation, you can't access old view data, as it's fully locked:

$ BEGIN ; BEGIN $ SELECT oid , relfilenode FROM pg_class WHERE relname = 'test2' ; oid | relfilenode -------+------------- 28686 | 28693 ( 1 ROW ) $ refresh materialized VIEW test2; REFRESH MATERIALIZED VIEW $ SELECT * FROM pg_locks WHERE pid = pg_backend_pid ( ) ; locktype | DATABASE | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | GRANTED | fastpath ---------------+----------+----------+--------+--------+------------+---------------+---------+--------+----------+--------------------+-------+---------------------+---------+---------- relation | 16393 | 11090 | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | 2 / 2219 | 26886 | AccessShareLock | t | t relation | 16393 | 2663 | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | 2 / 2219 | 26886 | AccessShareLock | t | t relation | 16393 | 2662 | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | 2 / 2219 | 26886 | AccessShareLock | t | t relation | 16393 | 1259 | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | 2 / 2219 | 26886 | AccessShareLock | t | t virtualxid | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | 2 / 2219 | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | 2 / 2219 | 26886 | ExclusiveLock | t | t transactionid | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | 2716 | [ NULL ] | [ NULL ] | [ NULL ] | 2 / 2219 | 26886 | ExclusiveLock | t | f object | 16393 | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | 1247 | 28700 | 0 | 2 / 2219 | 26886 | AccessExclusiveLock | t | f relation | 16393 | 28699 | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | 2 / 2219 | 26886 | AccessExclusiveLock | t | f relation | 16393 | 28686 | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | 2 / 2219 | 26886 | ShareLock | t | f relation | 16393 | 28686 | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | 2 / 2219 | 26886 | AccessExclusiveLock | t | f object | 16393 | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | 1247 | 28701 | 0 | 2 / 2219 | 26886 | AccessExclusiveLock | t | f object | 0 | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | [ NULL ] | 1260 | 16385 | 0 | 2 / 2219 | 26886 | AccessShareLock | t | f ( 12 ROWS ) $ SELECT locktype , mode FROM pg_locks WHERE pid = pg_backend_pid ( ) AND relation = 'test2' ::regclass; locktype | mode ----------+--------------------- relation | ShareLock relation | AccessExclusiveLock ( 2 ROWS ) $ commit; COMMIT

This means that refreshing will block all concurrent access to the view. Whether this is acceptable for your situation – it's up to you. If it's not, you can do something along the lines of:

$ do $$ BEGIN EXECUTE 'create materialized view test2_new as ' || pg_get_viewdef ( 'test2' ::regclass ) ; END ; $$; DO $ BEGIN ; BEGIN $ DROP materialized VIEW test2; DROP MATERIALIZED VIEW $ ALTER materialized VIEW test2_new RENAME TO test2; ALTER MATERIALIZED VIEW $ commit; COMMIT

Maybe with some statement_timeout/lock/statement_timeout loop, to make it less lock prone.

Since materialized view is internally a table, you can have indexes on it, to further speed up queries on it.

All in all – it's great first step, thanks guys.

