Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Tim Kane
Date:
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-83@gemulon.postgresql.org)

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

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

From
Vik Fearing
Date:
On 05/18/2014 05:47 PM, Tim Kane wrote:
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
-- 
Vik

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

From
Michael Paquier
Date:
On Mon, May 19, 2014 at 6:47 AM, Tim Kane <tim.kane@gmail.com> wrote:
> 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.
--
Michael

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

From
Tim Kane
Date:


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