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 B598A9DC9C4 for ; Mon, 19 Dec 2005 15:10:54 -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 23764-03 for ; Mon, 19 Dec 2005 15:10:53 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 994679DC9CF for ; Mon, 19 Dec 2005 15:10:51 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 1DFB133A61; Mon, 19 Dec 2005 20:10:52 +0100 (MET) From: James Klo X-Newsgroups: pgsql.performance Subject: Re: make bulk deletes faster? Date: Mon, 19 Dec 2005 11:10:50 -0800 Organization: Hub.Org Networking Services Lines: 43 Message-ID: References: <1134988771.3208.81.camel@firebolt> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923) X-Accept-Language: en-us, en In-Reply-To: <1134988771.3208.81.camel@firebolt> To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.06 required=5 tests=[AWL=0.060] X-Spam-Score: 0.06 X-Spam-Level: X-Archive-Number: 200512/458 X-Sequence-Number: 16279 Mitch Skinner wrote: > Have you considered partitioning? > > http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html > > If you can partition your timeblock table so that you archive an entire > partition at a time, then you can delete the archived rows by just > dropping (or truncating) that partition. AFAIK there's no way to > "re-parent" a partition (e.g., from the timeblock table to the > timeblock_archive table). > > If your app is particularly cooperative you might be able to use > partitioning to avoid moving data around entirely. If table accesses > are always qualified by something you can use as a partitioning key, > then partitioning can give you the speed benefits of a small table > without the effort of keeping it cleared out. 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? 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. > Another good read, if you haven't yet, is > http://powerpostgresql.com/Downloads/annotated_conf_80.html > especially the "Memory", "Checkpoints", and maybe "WAL options" > sections. If you're doing large deletes then you may need to increase > your free space map settings--if a VACUUM VERBOSE finishes by saying > that you need more FSM pages, then the table may have gotten bloated > over time (which can be fixed with a configuration change and a VACUUM > FULL, though this will lock everything else out of the table while it's > running). > Thanks, I will look into this as well.