Re: Proposal for unlogged tables

On 04/01/16 16:27, Yves Dorfsman wrote:
> I haven't tried, but won't converting an unlogged table into a logged
> table write all the inserts at once instead of once per insert? Or are
> you wanting to do more bulk insert into that table later?
> Are you trying to avoid running a CHECKPOINT? Are you afraid the activity on
> the other tables will create too much I/O?

Setting a table to logged still pushes all the inserts into the WAL
which we don't need and causes a lot of extra IO. It also takes quite a
long time as it is basically rewriting the table and all indexes (eg 60
seconds for 2m rows on one of my test tables). We can do this but a) it
generates lots of additional IO which isn't really required for us, and
b) it acquires an exclusive lock on the table which is also not nice for us.

>> If the last data modification
>> statement was run more than eg 30 seconds or 1 minute before an unclean
>> shutdown (or the data was otherwise flushed to disk and there was no IO since
>> then) can we not assume that the data is not corrupted and hence not truncate
>> the unlogged tables?
> I have to admit that I have been surprised by this, it feels like unlogged
> tables are never written properly unless you do an explicit CHECKSUM.

I don't know how the internals work but unlogged tables definitely
flushed to disk and persist through normal server restarts. It is just
according to the docs if the server ever has an unclean shutdown the
tables are truncated even if they have not been updated in a year. I
can't understand why it has to be like this and it seems that it would
be much nicer to not automatically truncate if it doesn't have to. This
would be great in the situation where you can tolerate a low chance of
data-loss but want very quick upserts.

Mark