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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
