Message-ID: <4FF6EA64.9010308@ringerc.id.au>
Date: Fri, 06 Jul 2012 21:38:44 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:13.0) Gecko/20120605 Thunderbird/13.0
MIME-Version: 1.0
To: Daniel Farina <daniel@heroku.com>
CC: Stanislaw Pankevich <s.pankevich@gmail.com>,
	pgsql-performance@postgresql.org
Subject: Re: PostgreSQL db, 30 tables with number of rows < 100
	(not huge) - the fastest way to clean each non-empty table and reset
	unique identifier column of empty ones.
References: 
 <CAFXpGYbgmZYij4TgCbOF24-usoiDD0ASQeaVAkYtB7E2TYm8Wg@mail.gmail.com>
	<4FF6CC11.2000904@ringerc.id.au>
	<CAAZKuFbpYyfRA=+DKD4yCr_c_Tag2JNO32B8NVU15gg4xX3u_Q@mail.gmail.com>
In-Reply-To: 
 <CAAZKuFbpYyfRA=+DKD4yCr_c_Tag2JNO32B8NVU15gg4xX3u_Q@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 07/06/2012 07:38 PM, Daniel Farina wrote:
> On Fri, Jul 6, 2012 at 4:29 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:
>> 1) Truncate each table. It is too slow, I think, especially for empty
>> tables.
>>
>> Really?!? TRUNCATE should be extremely fast, especially on empty tables.
>>
>> You're aware that you can TRUNCATE many tables in one run, right?
>>
>> TRUNCATE TABLE a, b, c, d, e, f, g;
> I have seen in "trivial" cases -- in terms of data size -- where
> TRUNCATE is much slower than a full-table DELETE.  The most common use
> case for that is rapid setup/teardown of tests, where it can add up
> quite quickly and in a very big way. This is probably an artifact the
> speed of one's file system to truncate and/or unlink everything.
That makes some sense, actually. DELETEing from a table that has no 
foreign keys, triggers, etc while nothing else is accessing the table is 
fairly cheap and doesn't take much (any?) cleanup work afterwards. For 
tiny deletes I can easily see it being better than forcing the OS to 
journal a metadata change or two and a couple of fsync()s for a truncate.

I suspect truncating many tables at once will prove a win over 
iteratively DELETEing from many tables at once. I'd benchmark it except 
that it's optimizing something I don't care about at all, and the 
results would be massively dependent on the file system (ext3, ext4, 
xfs) and its journal configuration.

--
Craig Ringer