X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DB88452D94 for ; Thu, 14 Jul 2005 13:21:17 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 29436-05 for ; Thu, 14 Jul 2005 16:21:12 +0000 (GMT) Received: from mr1.surnet.cl (smtp1.surnet.cl [216.155.73.162]) by svr1.postgresql.org (Postfix) with ESMTP id AC37A52D56 for ; Thu, 14 Jul 2005 13:21:11 -0300 (ADT) Received: from smtp1.surnet.cl (216.155.73.168) by mr1.surnet.cl (7.0.031.3) id 42587EDE015680B1; Thu, 14 Jul 2005 12:20:30 -0400 Received: from smtp2.surnet.cl (smtp2.surnet.cl []) by mr1.surnet.cl ([216.155.73.168]); Thu, 14 Jul 2005 16:20:30 +0000 Received: from cluster.surnet.cl (216.155.73.164) by smtp2.surnet.cl (7.0.031.3) id 4259ADE300D1F941; Thu, 14 Jul 2005 12:20:50 -0400 Received: from alvh.no-ip.org (200.85.218.66) by cluster.surnet.cl (7.0.043) (authenticated as alvherre@surnet.cl) id 42B3EF60003BA895; Thu, 14 Jul 2005 12:21:06 -0400 Received: by alvh.no-ip.org (Postfix, from userid 1000) id B2F58C370EE; Thu, 14 Jul 2005 12:21:14 -0400 (CLT) Date: Thu, 14 Jul 2005 12:21:14 -0400 From: Alvaro Herrera To: Alison Winters Cc: pgsql-performance@postgresql.org Subject: Re: lots of updates on small table Message-ID: <20050714162114.GA19778@alvh.no-ip.org> References: <42D5F34E.nail9QE1195IE@pluto.mirrabooka.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <42D5F34E.nail9QE1195IE@pluto.mirrabooka.com> User-Agent: Mutt/1.5.9i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.378 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, FORGED_RCVD_HELO, RCVD_IN_NJABL_PROXY X-Spam-Level: * X-Archive-Number: 200507/181 X-Sequence-Number: 13422 On Thu, Jul 14, 2005 at 03:08:30PM +1000, Alison Winters wrote: > Hi, > > Our application requires a number of processes to select and update rows > from a very small (<10 rows) Postgres table on a regular and frequent > basis. These processes often run for weeks at a time, but over the > space of a few days we find that updates start getting painfully slow. > We are running a full vacuum/analyze and reindex on the table every day, Full vacuum, eh? I wonder if what you really need is very frequent non-full vacuum. Say, once in 15 minutes (exact rate depending on dead tuple rate.) -- Alvaro Herrera () "World domination is proceeding according to plan" (Andrew Morton)