From: | Stanislaw Pankevich <s(dot)pankevich(at)gmail(dot)com> |
---|---|
To: | Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> |
Cc: | Daniel Farina <daniel(at)heroku(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 15:27:03 |
Message-ID: | CAFXpGYb0EK3vkeaU65Wduu0AdX0kLw2uT+ODoZs6kUpAOQ53LQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Fri, Jul 6, 2012 at 5:22 PM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Stanislaw Pankevich | 2012-07-06 15:44:34 | 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 | Marc Mamin | 2012-07-06 15:24:54 | 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. |