Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id D30782E07B2 for ; Mon, 19 Nov 2007 05:49:34 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 95256-05 for ; Mon, 19 Nov 2007 05:49:26 -0400 (AST) Received: from nathalia.gransy.com (nathalia.gransy.com [82.208.29.194]) by postgresql.org (Postfix) with ESMTP id 046B92E079B for ; Mon, 19 Nov 2007 05:49:28 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by nathalia.gransy.com (Postfix) with ESMTP id 2DD2F7B413F; Mon, 19 Nov 2007 10:53:09 +0100 (CET) Received: from nathalia.gransy.com ([127.0.0.1]) by localhost (nathalia.gransy.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id QyMpfHh7Gwyx; Mon, 19 Nov 2007 10:53:09 +0100 (CET) Received: from mail.fuzzy.cz (localhost [127.0.0.1]) by nathalia.gransy.com (Postfix) with ESMTP id EEE2E7B40E9; Mon, 19 Nov 2007 10:53:08 +0100 (CET) Received: from 212.24.144.68 (SquirrelMail authenticated user tv@fuzzy.cz) by mail.fuzzy.cz with HTTP; Mon, 19 Nov 2007 10:53:08 +0100 (CET) Message-ID: <20746.212.24.144.68.1195465988.squirrel@mail.fuzzy.cz> In-Reply-To: <7DA6747D-E739-4E25-9D5C-0516A6A97F1C@decibel.org> References: <473D659B.1070008@nekomancer.net> <20071116111355.GB1195@mail.nordicbet.com> <61575.212.24.144.68.1195220303.squirrel@mail.fuzzy.cz> <7DA6747D-E739-4E25-9D5C-0516A6A97F1C@decibel.org> Date: Mon, 19 Nov 2007 10:53:08 +0100 (CET) Subject: Re: autovacuum: recommended? From: tv@fuzzy.cz To: "Decibel!" Cc: tv@fuzzy.cz, "Tobias Brox" , =?iso-8859-2?Q?G=E1bor_Farkas?= , pgsql-performance@postgresql.org User-Agent: SquirrelMail/1.4.10a MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-2 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200711/265 X-Sequence-Number: 27680 > FWIW, 20k rows isn't all that big, so I'm assuming that the > descriptions make the table very wide. Unless those descriptions are > what's being updated frequently, I suggest you put those in a > separate table (vertical partitioning). That will make the main table > much easier to vacuum, as well as reducing the impact of the high > churn rate. Yes, you're right - the table is quite wide, as it's a catalogue of a pharmacy along with all the detailed descriptions and additional info etc. So I guess it's 50 MB of data or something like that. That may not seem bad, but as I already said the table grew to about 12x the size during the day (so about 500MB of data, 450MB being dead rows). This is the 'central' table of the system, and there are other quite heavily used databases as well. Add some really stupid queries on this table (for example LIKE searches on the table) and you easily end up with 100MB of permanent I/O during the day. The vertical partitioning would be overengineering in this case - we considered even that, but proper optimization of the update process (updating only those rows that really changed), along with a little bit of autovacuum tuning solved all the performance issues. Tomas