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 Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 ====
>>>
>>> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux