Subject: Re: Proposal for unlogged tables
To: pgsql-performance@postgresql.org
References: <568A429C.2010507@allaroundtheworld.fr>
 <568A8168.1070900@zioup.com>
From: Mark Zealey <mark@allaroundtheworld.fr>
Message-ID: <568A83F0.7050904@allaroundtheworld.fr>
Date: Mon, 4 Jan 2016 16:38:40 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.4.0
MIME-Version: 1.0
In-Reply-To: <568A8168.1070900@zioup.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance