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.
I hope the locking during REFRESH will be solved since where I have hacked my own materialized views one of the main concerns has been to avoid locking out readers during the refresh.
Maybe “REFRESH MATERIALIZED VIEW test2 CONCURRENTLY;”, similar to how index rebuilds work.
I am also hoping for incremental soon. Regarding determining what is fresh, maybe something similar to how covering indexes work.
“REFRESH MATERIALIZED VIEW ” do full or incremental refresh? IF do full refresh every time , it will cause a lot of I/O.
@Francs: It does a full refresh while keeping the view locked.
I love seeing huge features released in small useful steps, but personally I will have to wait for locking to be solved for this feature to be useful to me. The full refresh on the other hand does not bother me. It all depends on what usecases one has.
“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.”
Not even close.
The idea of materialized views is that you can replace “CREATE VIEW foo AS …” with “CREATE MATERIALIZED VIEW foo AS …” without changing the results of a “SELECT * FROM foo”.
PostgreSQL 9.3 manages to completely ignore this fundamental correctness requirement, the whole thing is a joke. Even a ten line patch that just ignores the MATERIALIZED keyword would have done better.