X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 3ABE89DCB0D for ; Tue, 20 Dec 2005 15:16:20 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 35626-06 for ; Tue, 20 Dec 2005 15:16:19 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (unknown [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 7919E9DCAA5 for ; Tue, 20 Dec 2005 15:16:17 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id 3A3AC3982A; Tue, 20 Dec 2005 13:16:18 -0600 (CST) Date: Tue, 20 Dec 2005 13:16:18 -0600 From: "Jim C. Nasby" To: James Klo Cc: pgsql-performance@postgresql.org Subject: Re: make bulk deletes faster? Message-ID: <20051220191618.GF28771@pervasive.com> References: <1134988771.3208.81.camel@firebolt> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Operating-System: FreeBSD 6.0-RELEASE amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.037 required=5 tests=[AWL=0.037] X-Spam-Score: 0.037 X-Spam-Level: X-Archive-Number: 200512/505 X-Sequence-Number: 16326 On Mon, Dec 19, 2005 at 11:10:50AM -0800, James Klo wrote: > Yes, I've considered partitioning as a long term change. I was thinking > about this for other reasons - mainly performance. If I go the > partitioning route, would I need to even perform archival? No. The idea is that you have your table split up into date ranges (perhaps each week gets it's own table). IE: table_2005w01, table_2005w02, etc. You can do this with either inheritence or individual tables and a UNION ALL view. In your case, inheritence is probably the better way to go. Now, if you have everything broken down by weeks and you typically only need to access 7 days worth of data, then generally you will only be reading from two tables, so those two tables should stay in memory, and indexes on them will be smaller. If desired, you can also play tricks on the older tables surch as vacuum full or cluster to further reduce space usage and improve performance. > The larger problem that I need to solve is really twofold: > > 1. Need to keep reads on timeblocks that are from the current day > through the following seven days very fast, especially current day reads. > > 2. Need to be able to maintain the timeblocks for reporting purposes, > for at least a year (potentially more). This could probably better > handled performing aggregate analysis, but this isn't on my current radar. I've written an RRD-like implementation in SQL that might interest you; it's at http://rrs.decibel.org (though the svn web access appears to be down right now...) -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461