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 Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 07/06/2012 07:38 PM, Daniel Farina wrote:
On Fri, Jul 6, 2012 at 4:29 AM, Craig Ringer <ringerc@xxxxxxxxxxxxx> 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

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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux