materialised views vs unlogged table (also, ize vs ise)

[Tim Kane]

HI all,

I’m curious about materialised views.
I was just setting something up, and noticed there is no support for
UNLOGGED materialised views
(http://www.postgresql.org/message-id/E1UZNrD-0008Eh-***@gemulon.postgresql.o
rg)

I also notice that the creation of an unlogged table appears to be a lot
quicker than that of a materialised view (as you might expect).
Read operations seem indistinguishable, though I’ve not tested with cold
buffers.

Aside from the convenience of the REFRESH functionality, are there any other
factors I should consider?
(the base tables get dropped/recreated each night, so I can’t rely on
REFRESH functionality – I’ll need to recreate the table/view in either
case).



Oh, I also noticed we don’t support alternate spellings of MATERIALIZE, as
we do for ANALYZE.
I’m not sure if we do this anywhere else, maybe it’s just analyze being the
odd one out.


=# create materialized view testview as select 1;
SELECT 1
Time: 21.760 ms
=# create materialised view testview as select 1;
ERROR: syntax error at or near "materialised"
LINE 1: create materialised view testview as select 1;


Not that it isn’t difficult for users to simply spell it one way or the
other, it just seems a minor inconsistency.

Cheers,

Tim

[Vik Fearing]

Post by Tim Kane
Oh, I also noticed we don’t support alternate spellings of
MATERIALIZE, as we do for ANALYZE.
I’m not sure if we do this anywhere else, maybe it’s just analyze
being the odd one out.

For the moment, if not forever, ANALYSE is the odd one out.

http://www.postgresql.org/message-id/CA%2BTgmoYOdgD90FLO_NjPx9gYk9dsXS3iu%2BGx7HfaqYp%3DGmmA0w%40mail.gmail.com

[Michael Paquier]

Post by Tim Kane
Aside from the convenience of the REFRESH functionality, are there any other
factors I should consider?

An exclusive lock is taken on the materialized view during a REFRESH
operation, blocking an read or write queries attempted on them. You
can tackle this limitation in the upcoming 9.4 by using REFRESH
CONCURRENTLY, a unique index being necessary on the materialized view.

[Tim Kane]

Post by Michael Paquier

Post by Tim Kane
Aside from the convenience of the REFRESH functionality, are there any other
factors I should consider?

An exclusive lock is taken on the materialized view during a REFRESH
operation, blocking an read or write queries attempted on them. You
can tackle this limitation in the upcoming 9.4 by using REFRESH
CONCURRENTLY, a unique index being necessary on the materialized view.
--

Yep. Thanks Michael. I was actually trying to say that I have no need for
refresh functionality in this instance. :)

- The table/views I need will be destroyed and recreated each night.
- Refresh functionality isn’t helpful in this instance, as the underlying
tables will also be destroyed
- Crash recovery isn’t necessary

So, in this scenario - will I get any benefit from a materialised view,
that I wouldn't have from an unlogged table?

Cheers