Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 5456EEC7B26 for ; Fri, 6 Jul 2012 08:35:30 -0300 (ADT) Received: from mail-pb0-f46.google.com ([209.85.160.46]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Sn6op-00014V-9n for pgsql-performance@postgresql.org; Fri, 06 Jul 2012 11:35:29 +0000 Received: by pbbrp8 with SMTP id rp8so14491373pbb.19 for ; Fri, 06 Jul 2012 04:35:13 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type:x-gm-message-state; bh=NIbU75DoVmSzcdonAd4Q4TYjCF7Fo/RwdU44mnx9heY=; b=P3I+iKJiCD3RtFp1omLQhi6yyttrEpEnCi3XS4tWpE159HnIbtztOh8Htsh6eWu9Vr Q1Ed4I1pNsJtV+Hk4FuHWlYICQxGHNMz9h1037qfChPWb3ArgKMTHVaqUC19jeN8xkh6 qnb4uuqDATjDJj7dhqhER1dxC9GJFDdTcLNunCmr5TqcsLcBjiqXN46oJUsxm2FRIQ5L vVRqyktibh3sMrNLb3h18onrkdDkOeouwU2TpSGj1/hAnlppxGAIBfILyq2AGKEJg+/m tuE5iQZCwb8JxxI5zi7DsP3NXGAadNUJSjDLzcGPrO2iPH4mPXDVwP4+bGuffuoIr1Sr +Cng== Received: by 10.68.223.34 with SMTP id qr2mr36247682pbc.10.1341574513737; Fri, 06 Jul 2012 04:35:13 -0700 (PDT) Received: from ayaki.localdomain (124-169-169-118.dyn.iinet.net.au. [124.169.169.118]) by mx.google.com with ESMTPS id ms1sm21732593pbb.63.2012.07.06.04.35.10 (version=SSLv3 cipher=OTHER); Fri, 06 Jul 2012 04:35:12 -0700 (PDT) Message-ID: <4FF6CD6B.4080706@ringerc.id.au> Date: Fri, 06 Jul 2012 19:35:07 +0800 From: Craig Ringer User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:13.0) Gecko/20120605 Thunderbird/13.0 MIME-Version: 1.0 To: Stanislaw Pankevich 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. References: <4FF6CC11.2000904@ringerc.id.au> In-Reply-To: <4FF6CC11.2000904@ringerc.id.au> Content-Type: multipart/alternative; boundary="------------070603070801070504020300" X-Gm-Message-State: ALoCoQnM4UCyO0QlCsr5YwDuMw5hcXSY2dk2ZvIX6ufeo0DuM3BjDYNApXtrE7hz2dZEFYZ+XwXL X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/54 X-Sequence-Number: 47235 This is a multi-part message in MIME format. --------------070603070801070504020300 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit 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 --------------070603070801070504020300 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
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
--------------070603070801070504020300--