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.

Interesting catch, I will try to test the behavior of 'DELETE vs
multiple TRUNCATE'.

I'll post it here, If I discover any amazing results.

On Fri, Jul 6, 2012 at 2:38 PM, Daniel Farina <daniel(at)heroku(dot)com> 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.
>
> I haven't tried a multi-truncate though. Still, I don't know a
> mechanism besides slow file system truncation time that would explain
> why DELETE would be significantly faster.
>
> --
> fdr