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:35:07 +0800
Message-ID: <4FF6CD6B.4080706@ringerc.id.au> (raw)
In-Reply-To: <4FF6CC11.2000904@ringerc.id.au>
References: <CAFXpGYbgmZYij4TgCbOF24-usoiDD0ASQeaVAkYtB7E2TYm8Wg@mail.gmail.com>
<4FF6CC11.2000904@ringerc.id.au>
On 07/06/2012 07:29 PM, Craig Ringer wrote:
> 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).
Oh, also, you can setval(...) a bunch of sequences at once:
SELECT
setval('first_seq', 0),
setval('second_seq', 0),
setval('third_seq', 0),
setval('fouth_seq', 0);
... etc. You should only need two statements, fast ones, to reset your
DB to the default state.
--
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: <4FF6CD6B.4080706@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