Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 8B3C2EC7B26 for ; Fri, 6 Jul 2012 08:29:41 -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 1Sn6jD-0000wQ-QY for pgsql-performance@postgresql.org; Fri, 06 Jul 2012 11:29:40 +0000 Received: by pbbrp8 with SMTP id rp8so14483839pbb.19 for ; Fri, 06 Jul 2012 04:29:26 -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=zwD+Dfg8u4Brx3YxqXBMA2PYQRGJ/SZqyvyKhDpQyAA=; b=Kdt4gt/jSrnVqFnZA/qrGLI09qg5tCE7f6D4bL2cP5A/7ATg+vlMQPWo0WcRMANwOd Gkuh84nErYwtS9X4rPI03NqwPnGLBtUjj6tSWcqFbZ0gEWyAfST+WX+onh/3w5AfQY+e h6M+Ms4wHnS5huVhxGz9m/B51aU7EwOvJsXskBOPap2UlzKY9lTNBRkEcfL18xahybSt tX/ruyOsOI2SNsxbBK6yqb8Q2ogXcMM/9n2WfFHamcu3uQu05+5QPYeevcVE5enMtcdc 8f6PgN/Rii5sKWqgOdkfsgMJ+pNEKZefZT3yuSddKYDMDAfK0ZokflkEHcFsE5fsPraE n3ZA== Received: by 10.68.238.232 with SMTP id vn8mr2613297pbc.78.1341574166207; Fri, 06 Jul 2012 04:29:26 -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 ny4sm21717167pbb.57.2012.07.06.04.29.23 (version=SSLv3 cipher=OTHER); Fri, 06 Jul 2012 04:29:25 -0700 (PDT) Message-ID: <4FF6CC11.2000904@ringerc.id.au> Date: Fri, 06 Jul 2012 19:29:21 +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: In-Reply-To: Content-Type: multipart/alternative; boundary="------------020605070502040806060205" X-Gm-Message-State: ALoCoQk9N2bu/Dyk1J1QWTpfTtml9vMcSQZ8HvseVWwYMS+ZZHI1558Wy/femJXUpIJrICM9XTdj X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/53 X-Sequence-Number: 47234 This is a multi-part message in MIME format. --------------020605070502040806060205 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit 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 --------------020605070502040806060205 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
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
--------------020605070502040806060205--