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. - Mailing list pgsql-performance

On Fri, Jul 6, 2012 at 5:22 PM, Craig Ringer <ringerc@ringerc.id.au> wrote:
> On 07/06/2012 09:45 PM, Stanislaw Pankevich wrote:
>
>> Question: Is there a possibility in PostgreSQL to do DELETE on many tables
>> massively, like TRUNCATE allows. Like DELETE table1, table2, ...?
>
>
> Yes, you can do it with a writable common table expression, but you wanted
> version portability.
>
> WITH
>   discard1 AS (DELETE FROM test1),
>   discard2 AS (DELETE FROM test2 AS b)
> SELECT 1;
>
> Not only will this not work in older versions (IIRC it only works with 9.1,
> maybe 9.0 too but I don't see it in the documentation for SELECT for 9.0)
> but I find it hard to imagine any performance benefit over simply sending
>
>   DELETE FROM test1; DELETE FROM test2;
>
> This all smells like premature optimisation of cases that don't matter. What
> problem are you solving with this?

I will write tests for both massive TRUNCATE and DELETE (DELETE
each_table) for my case with Ruby testing environment, and let you
know about the results. For now, I think, I should go for massive
TRUNCATE.