Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 36FDA633615 for ; Fri, 8 Jan 2010 02:14:31 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 53880-09 for ; Fri, 8 Jan 2010 06:14:20 +0000 (UTC) Received: from news.hub.org (news.hub.org [200.46.204.72]) by mail.postgresql.org (Postfix) with ESMTP id C5EDE632F08 for ; Fri, 8 Jan 2010 02:14:20 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.3/8.14.3) with ESMTP id o086EJ5g044631 for ; Fri, 8 Jan 2010 02:14:19 -0400 (AST) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.3/8.14.3/Submit) id o086EFbw044623 for pgsql-performance@postgresql.org; Fri, 8 Jan 2010 02:14:15 -0400 (AST) (envelope-from news) From: "Carlo Stonebanks" X-Newsgroups: pgsql.performance Subject: Re: Massive table (500M rows) update nightmare Date: Fri, 8 Jan 2010 01:14:58 -0500 Organization: Hub.Org Networking Services Lines: 15 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org In-Reply-To: X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Windows Mail 6.0.6002.18005 X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005 To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.19 tagged_above=-10 required=5 tests=BAYES_50=0.001, FH_DATE_PAST_20XX=3.188, STOX_REPLY_TYPE=0.001 X-Spam-Level: *** X-Archive-Number: 201001/137 X-Sequence-Number: 37017 > It might well be checkpoints. Have you tried cranking up checkpoint > segments to something like 100 or more and seeing how it behaves then? No I haven't, althugh it certainly make sense - watching the process run, you get this sense that the system occaisionally pauses to take a deep, long breath before returning to work frantically ;D Checkpoint_segments are currently set to 64. The DB is large and is on a constant state of receiving single-row updates as multiple ETL and refinement processes run continuously. Would you expect going to 100 or more to make an appreciable difference, or should I be more aggressive? >