Message-ID: <4EC0EAD6.9010302@archonet.com>
Date: Mon, 14 Nov 2011 10:17:58 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.23) Gecko/20110921 Lightning/1.0b2 Thunderbird/3.1.15
MIME-Version: 1.0
To: Sergey Konoplev <gray.ru@gmail.com>
CC: Stephen Frost <sfrost@snowman.net>, Anibal David Acosta <aa@devshock.com>,
	pgsql-performance@postgresql.org
Subject: Re: unlogged tables
References: <005501cca0ad$b9478440$2bd68cc0$@devshock.com>
	<20111111201822.GN24234@tamriel.snowman.net>
	<CAL_0b1t02q77ft2JWdjMv8q4vaGm=x__K0rzVkpNSvSy2-SjzA@mail.gmail.com>
	<4EC0D827.8090601@archonet.com>
	<CAL_0b1tuxi9YVhGKsNUSTZCUBXgOmppan7w+KkRZARbQ=u4P5w@mail.gmail.com>
In-Reply-To: 
 <CAL_0b1tuxi9YVhGKsNUSTZCUBXgOmppan7w+KkRZARbQ=u4P5w@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 14/11/11 10:08, Sergey Konoplev wrote:
> On 14 November 2011 12:58, Richard Huxton<dev@archonet.com>  wrote:
>> Because they bypass the transaction-log (WAL), hence unlogged.
>> There's no way to know whether there were partial updates applied when the
>> system restarts.
>
> I probably did not understand the "truncate" meaning correct. It
> truncates all the records of the table or several recent records only?

All.

Let's say you were doing something like "UPDATE unlogged_table SET x=1 
WHERE y=2". If a crash occurs during this command, there's no guarantee 
that the affected disk pages were all updated. Worse, a single page 
might be partially updated or even have rubbish in it (depending on the 
nature of the crash).

Without the WAL there's no way to check whether the table is good or 
not, or even to know what the last updates were. So - the only safe 
thing to do is truncate the unlogged tables.

In the event of a normal shutdown, we can flush all the writes to disk 
so we know all the data has been written, so there is no need to truncate.

-- 
   Richard Huxton
   Archonet Ltd