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.

Stanislaw Pankevich wrote:
>>> ==== 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 ====

Hello,

2 'exotic' ideas:

- use dblink_send_query to do the job in multiple threads (I doubt this really could be faster)
- have prepared empty tables in a separate schema, and a "garbage schema":

ALTER TABLE x set schema garbage;
ALTER TABLE prepared.x set schema "current";

you should be ready for the next test,

but still have to clean garbage nad moved to prepared for the next but one in the background....

best regards,

Marc Mamin

>>>
>>> I wonder, what is the fastest way to accomplish this kind of task in PostgreSQL. I am interested in
>>> the fastest solutions ever possible.

>>> I need the fastest cleaning strategy for such case working on PostgreSQL both 8 and 9.
>>>
>>> I see the following approaches:
>>>
>>> 1) Truncate each table. It is too slow, I think, especially for empty tables.

>> Did you actually try it? That's the king's way to performance questions!
>> Truncating a single table is done in a matter of microseconds, particularly
>> if it is not big.
>> Do you have tens of thousands of tables?

> Actually, 10-100 tables.

>> You could of course run a SELECT 1 FROM table LIMIT 1, but again I don't
>> think that this will be considerably faster than just truncating the table.
>
> Exactly this query is much faster, believe me. You can see my latest
> results on https://github.com/stanislaw/truncate-vs-count.

Ok, I believe you.

My quick tests showed that a sible truncate (including transaction and
client-server roundtrip via UNIX sockets takes some 10 to 30 milliseconds.

Multiply that with 100, and you end up with just a few seconds at most.
Or what did you measure?

I guess you run that deletion very often so that it is painful.

Still I think that the biggest performance gain is to be had by using
PostgreSQL's features (truncate several tables in one statement, ...).

Try to bend your Ruby framework!

Yours,
Laurenz Albe