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.

Daniel Farina-4 wrote
>
> On Fri, Jul 6, 2012 at 4:29 AM, Craig Ringer &lt;ringerc(at)(dot)id&gt; 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
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

That's my experience - I have a set of regression tests that clean the
database (deletes everything from a single parent table and lets the
referential integrity checks cascade to delete five other tables) at the end
of each test run, and it can complete 90 tests (including 90 mass deletes)
in a little over five seconds. If I replace that simple delete with a
truncation of all six tables at once, my test run balloons to 42 seconds.

I run my development database with synchronous_commit = off, though, so I
guess TRUNCATE has to hit the disk while the mass delete doesn't.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-db-30-tables-with-number-of-rows-100-not-huge-the-fastest-way-to-clean-each-non-empty-tab-tp5715643p5715734.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.