Subject: Re: Proposal for unlogged tables
To: Andres Freund <andres@anarazel.de>
References: <568A429C.2010507@allaroundtheworld.fr>
 <568A8168.1070900@zioup.com> <568A83F0.7050904@allaroundtheworld.fr>
 <20160104161234.GE28025@awork2.anarazel.de>
Cc: pgsql-performance@postgresql.org
From: Mark Zealey <mark@allaroundtheworld.fr>
Message-ID: <568AA7F6.5050107@allaroundtheworld.fr>
Date: Mon, 4 Jan 2016 19:12:22 +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: <20160104161234.GE28025@awork2.anarazel.de>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


On 04/01/16 18:12, Andres Freund wrote:
> Pages containing data of unlogged tables aren't ever flushed to disk
> unless
> a) a shutdown checkpoint is performed
> b) a buffer containing data from an unlogged table is used for something
>     else
> c) the database being copied is the the source of a CREATE DATABASE .. TEMPLATE
>
> Hence, if there's an unclean shutdown, there's absolutely no guarantee
> about the on-disk state of unlogged tables. Even if they haven't been
> modified in ages - there could have been many many dirty pages in shared
> buffers when crashing.
>
>
> Always flushing dirty pages of unlogged tables at checkpoint would
> greatly increase the overhead for memory resident, write heavy workloads
> that use unlogged tables.

If there was a command to flush a specific unlogged table to disk it 
would work around all these issues no? Perhaps if you marked the table 
as read only at the same time it would flush it to disk and ensure no 
more data could be written to it eg (ALTER TABLE ... SET READ ONLY on an 
unlogged table would flush + not truncate after crash). In our case this 
would be great as we want to use these as permanent tables for speed; 
but after an initial data dump we don't change the data again so we 
could just do this at the end of the import process.

Mark


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