Re: read-only UNLOGGED tables

On Thu, Mar 31, 2011 at 12:24 PM, Leonardo Francalanci <m_lists(at)yahoo(dot)it> wrote:
>> I suppose that a similar effect could be achieved by something  like
>>
>>   CREATE TABLE mytable_logged AS
>>   SELECT * FROM  mytable_unlogged;
>>
>> which would not produce WAL:
>
>
> yes, but it would mean re-writing the whole data + re-creating the indexes.
>
> I didn't know that some statements don't write WAL at all if wal_level is
> minimal (they just fsync at the end). Couldn't that be done in the
> UNLOGGED to "regular" case? That is: if wal_level is minimal, you can
> transform an unlogged table into a logged one, without having to rewrite
> data + indexes: "just" fsync it and be done with it.
>
> Wouldn't that be useful?

Yes, but its more than fsync.

You'd need to copy the whole table, all indexes and forks to WAL if
wal_level is set high.

That is probably faster than rebuilding the indexes, but OTOH the
ALTER TABLE would lock the table for some time, whereas Gianni's
solution can be done in the background and then swapped so it is more
useful in practive.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services