Subject: Re: Proposal for unlogged tables
To: pgsql-performance@postgresql.org
References: <568A429C.2010507@allaroundtheworld.fr>
From: Yves Dorfsman <yves@zioup.com>
Message-ID: <568A8168.1070900@zioup.com>
Date: Mon, 4 Jan 2016 07:27:52 -0700
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: <568A429C.2010507@allaroundtheworld.fr>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 2016-01-04 02:59, Mark Zealey wrote:
> shutdown all unlogged tables will be truncated. Obviously with 9.5 we can now
> alter tables to be logged/unlogged after insert but this will still write all
> the inserts into the WAL.

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?

> I can understand the requirement to truncate tables
> with active IO at the point of unclean shutdown where you may get corrupted
> data; but I'm interested to find out how easy it would be to not perform the
> truncate for historical unlogged tables.

Are you trying to avoid running a CHECKPOINT? Are you afraid the activity on
the other tables will create too much I/O?

> 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.


-- 
http://yves.zioup.com
gpg: 4096R/32B0F416


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