On Fri, Jul 6, 2012 at 4:38 PM, Craig Ringer <ringerc@ringerc.id.au> wrote:
> On 07/06/2012 07:38 PM, Daniel Farina wrote:
>>
>> On Fri, Jul 6, 2012 at 4:29 AM, Craig Ringer <ringerc@ringerc.id.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.
>
> 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.
Question:
Is there a possibility in PostgreSQL to do DELETE on many tables
massively, like TRUNCATE allows. Like DELETE table1, table2, ...?