From: Mark Zealey <mark@allaroundtheworld.fr>
To: pgsql-performance@postgresql.org
Subject: Re: Proposal for unlogged tables
Date: Mon, 4 Jan 2016 16:38:40 +0200
Message-ID: <568A83F0.7050904@allaroundtheworld.fr> (raw)
In-Reply-To: <568A8168.1070900@zioup.com>
References: <568A429C.2010507@allaroundtheworld.fr>
<568A8168.1070900@zioup.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark@allaroundtheworld.fr
Subject: Re: Proposal for unlogged tables
In-Reply-To: <568A83F0.7050904@allaroundtheworld.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox