X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id CA092CB557 for ; Thu, 10 Jul 2003 06:29:41 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 49947-03 for ; Thu, 10 Jul 2003 03:29:09 -0300 (ADT) Received: from smtp.pspl.co.in (unknown [202.54.11.65]) by svr1.postgresql.org (Postfix) with ESMTP id DDF0ACB405 for ; Thu, 10 Jul 2003 03:29:07 -0300 (ADT) Received: (from root@localhost) by smtp.pspl.co.in (8.12.9/8.12.9) id h6A6VEdI003579 for ; Thu, 10 Jul 2003 12:01:14 +0530 Received: from daithan (daithan.intranet.pspl.co.in [192.168.7.161]) (authenticated bits=0) by smtp.pspl.co.in (8.12.9/8.12.9) with ESMTP id h6A6VCKr003533 for ; Thu, 10 Jul 2003 12:01:13 +0530 From: "Shridhar Daithankar" To: Postgresql Performance Date: Thu, 10 Jul 2003 12:00:16 +0530 MIME-Version: 1.0 Subject: Re: [NOVICE] Extreme high load averages Reply-To: shridhar_daithankar@persistent.co.in Message-ID: <3F0D5550.21678.4E122CF@localhost> In-reply-to: <3F0CFBAE.6080002@ethereal-realms.org> References: X-mailer: Pegasus Mail for Windows (v4.12a) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body X-Spam-Checker-Version: SpamAssassin 2.54 (1.174.2.17-2003-05-11-exp) X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/163 X-Sequence-Number: 2456 On 9 Jul 2003 at 23:37, Martin Foster wrote: > By fixing the script and doing the appropriate full vacuum and re-index, > the system is behaving much more like it should. Even if the process > may seem a bit odd to some. > > The reason for removing rows on a daily basis is due to the perishable > nature of the information. Since this is a chat site, posts over a day > old are rarely needed for any reason. Which is why they are archived > into dumps in case we really need to retrieve the information itself and > this gives us the added bonus of smaller backup sizes and smaller > database sizes. I have an idea. How about creating a table for each day. Use it for a while and rename it. Since you can rename a table in transaction, it should not be a problem. You can use inheritance if you want to query all of them. Using indexes and foregin keys on inherited tables is a problem though. That way deletion would be avoided and so would vacuum. It should be mich lighter on the system overall as well. Tell us if it works. Bye Shridhar -- Kaufman's Law: A policy is a restrictive document to prevent a recurrence of a single incident, in which that incident is never mentioned.