From: | Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> |
---|---|
To: | Daniel Farina <daniel(at)heroku(dot)com> |
Cc: | Stanislaw Pankevich <s(dot)pankevich(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)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. |
Date: | 2012-07-06 13:38:44 |
Message-ID: | 4FF6EA64.9010308@ringerc.id.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On 07/06/2012 07:38 PM, Daniel Farina wrote:
> On Fri, Jul 6, 2012 at 4:29 AM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Albe Laurenz | 2012-07-06 13:39:03 | 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. |
Previous Message | Greg Spiegelberg | 2012-07-06 13:35:08 | Re: Paged Query |