From: Steve Crawford <scrawford@pinpointresearch.com>
To: Stanislaw Pankevich <s.pankevich@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: 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: Fri, 06 Jul 2012 09:06:47 -0700
Message-ID: <4FF70D17.3070309@pinpointresearch.com> (raw)
In-Reply-To: <CAFXpGYbgmZYij4TgCbOF24-usoiDD0ASQeaVAkYtB7E2TYm8Wg@mail.gmail.com>
References: <CAFXpGYbgmZYij4TgCbOF24-usoiDD0ASQeaVAkYtB7E2TYm8Wg@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scrawford@pinpointresearch.com, s.pankevich@gmail.com
Subject: 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.
In-Reply-To: <4FF70D17.3070309@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox