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 have following assumptions:
> 
>     I have 30-100 tables. Let them be 30.
> 
>     Half of the tables are empty.
> 
>     Each non-empty table has, say, no more than 100 rows. By this I mean, tables are NOT large.
> 
>     I need an optional possibility to exclude 2 or 5 or N tables from this procedure.
> 
>     I cannot! use transactions.

Why? That would definitely speed up everything.

> 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?

> 2) Check each table for emptiness by more faster method, and then if it is empty reset its unique
> identifier column (analog of AUTO_INCREMENT in MySQL) to initial state (1), i.e to restore its
> last_value from sequence (the same AUTO_INCREMENT analog) back to 1, otherwise run truncate on it.

That seems fragile an won't work everywhere.

What if the table has no primary key with a DEFAULT that uses a sequence?
What if it has such a key, but the DEFAULT was not used for an INSERT?
What if somebody manually reset the sequence?

Besides, how do you find out what the sequence for a table's primary key
is? With a SELECT, I guess. That SELECT is probably not faster than
a simple TRUNCATE.

> Also my guess was that EXISTS(SELECT something FROM TABLE) could somehow be used to work good as one
> of the "check procedure" units, cleaning procedure should consist of, but haven't accomplished it too.

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.

> I would appreciate any hints on how this procedure could be accomplished in PostgreSQL native way.
> 
> Thanks!
> 
> UPDATE:
> 
> I need all this to run unit and integration tests for Ruby or Ruby on Rails projects. Each test should
> have a clean DB before it runs, or to do a cleanup after itself (so called teardown). Transactions are
> very good, but they become unusable when running tests against particular webdrivers, in my case the
> switch to truncation strategy is needed. Once I updated that with reference to RoR, please do not post
> here the answers about "Obviously, you need DatabaseCleaner for PG" and so on and so on.

I completely fail to understand what you talk about here.

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