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.

On 07/03/2012 08:22 AM, 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.
>
It would help if we really understood your use-case. If you want to 
fully reset your database to a known starting state for test runs, why 
not just have a base database initialized exactly as you wish, say 
"test_base", then just drop your test database and create the new 
database from your template:
drop database test;
create database test template test_base;

This should be very fast but it won't allow you to exclude individual 
tables.

Are you interested in absolute fastest as a mind-game or is there a 
specific use requirement, i.e. how fast is fast enough? This is the 
basic starting point for tuning, hardware selection, etc.

Truncate should be extremely fast but on tables that are as tiny as 
yours the difference may not be visible to an end-user. I just tried a 
"delete from" to empty a 10,000 record table and it took 14 milliseconds 
so you could do your maximum of 100 tables each containing 10-times your 
max number of records in less than two seconds.

Regardless of the method you choose, you need to be sure that nobody is 
accessing the database when you reset it. The drop/create database 
method will, of course, require and enforce that. Truncate requires an 
exclusive lock so it may appear to be very slow if it is waiting to get 
that lock. And even if you don't have locking issues, your reluctance to 
wrap your reset code in transactions means that a client could be 
updating some table or tables whenever the reset script isn't actively 
working on that same table leading to unexplained weird test results.

Cheers,
Steve