From: Craig Ringer <ringerc@ringerc.id.au>
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 19:29:21 +0800
Message-ID: <4FF6CC11.2000904@ringerc.id.au> (raw)
In-Reply-To: <CAFXpGYbgmZYij4TgCbOF24-usoiDD0ASQeaVAkYtB7E2TYm8Wg@mail.gmail.com>
References: <CAFXpGYbgmZYij4TgCbOF24-usoiDD0ASQeaVAkYtB7E2TYm8Wg@mail.gmail.com>
On 07/03/2012 11:22 PM, Stanislaw Pankevich wrote:
> I cannot! use transactions.
Everything in PostgreSQL uses transactions, they are not optional.
I'm assuming you mean you can't use explicit transaction demarcation, ie
BEGIN and COMMIT.
>
> need the fastest cleaning strategy for such case working on
> PostgreSQL both 8 and 9.
Just so you know, there isn't really any "PostgreSQL 8" or "PostgreSQL
9". Major versions are x.y, eg 8.4, 9.0, 9.1 and 9.2 are all distinct
major versions. This is different to most software and IMO pretty damn
annoying, but that's how it is.
>
> 1) Truncate each table. It is too slow, I think, especially for empty
> tables.
Really?!? TRUNCATE should be extremely fast, especially on empty tables.
You're aware that you can TRUNCATE many tables in one run, right?
TRUNCATE TABLE a, b, c, d, e, f, g;
>
> 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.
You can examine the value of SELECT last_value FROM the_sequence ;
that's the equivalent of the MySQL hack you're using. To set it, use
'setval(...)'.
http://www.postgresql.org/docs/9.1/static/functions-sequence.html> I use Ruby code to iterate through all tables
If you want to be fast, get rid of iteration. Do it all in one query or
a couple of simple queries. Minimize the number of round-trips and queries.
I'll be truly stunned if the fastest way isn't to just TRUNCATE all the
target tables in a single statement (not iteratively one by one with
separate TRUNCATEs).
--
Craig Ringer
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: ringerc@ringerc.id.au, 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: <4FF6CC11.2000904@ringerc.id.au>
* 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