X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 70A38D1B914 for ; Fri, 29 Aug 2003 16:59:24 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 40703-05 for ; Fri, 29 Aug 2003 13:58:54 -0300 (ADT) Received: from dp100.dp100.com (dsl093-082-153.wdc1.dsl.speakeasy.net [66.93.82.153]) by svr1.postgresql.org (Postfix) with ESMTP id A00B3D1B8CC for ; Fri, 29 Aug 2003 13:58:51 -0300 (ADT) Received: from i386.dp100.com (i386.dp100.com [10.0.0.1]) by dp100.dp100.com (Postfix) with ESMTP id 5F3C12064 for ; Fri, 29 Aug 2003 12:58:51 -0400 (EDT) Received: by i386.dp100.com (Postfix, from userid 501) id 07877491F; Fri, 29 Aug 2003 12:58:51 -0400 (EDT) Date: Fri, 29 Aug 2003 12:58:51 -0400 From: Richard Ellis To: pgsql-performance@postgresql.org Subject: Re: Indexing question Message-ID: <20030829165850.GA28458@i386.dp100.com> Mail-Followup-To: pgsql-performance@postgresql.org References: <012f01c36dfa$0f4a8760$b696a8c0@APR> <3F4EFAB6.7030104@klaster.net> <015f01c36e02$134fe960$b696a8c0@APR> <3F4F07CE.6090803@klaster.net> <018c01c36e09$4893e980$b696a8c0@APR> <3F4F1575.8090600@klaster.net> <01bf01c36e0d$18769280$b696a8c0@APR> <15992.1062165614@sss.pgh.pa.us> <027701c36e40$2851aef0$b696a8c0@APR> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <027701c36e40$2851aef0$b696a8c0@APR> User-Agent: Mutt/1.4.1i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/506 X-Sequence-Number: 3322 On Fri, Aug 29, 2003 at 05:13:52PM +0200, Alexander Priem wrote: > Well, the intention is to hold every record that ever existed in the table. > Therefore, records do not get deleted, but they get a date in the > deleteddate field. This way, we can track what changes were made to the > table(s). > > So if a record gets 'deleted', the field 'deleted' is set to today's date. > If a record gets 'updated', a new record is made containing the new data, > and the old record is marked as 'deleted'. > > So the percentage of 'deleted' records will grow with time, if you > understand what I mean. Did you consider a two table implimentation. 1 table "live_table" containing the non-deleted records, a second table "deleted_table" containing the deleted records, along with the "deleted_date" field. Keep the two in sync column type/number wise, and use a before delete trigger function on "live_table" to actually insert a copy of the deleted row plus "deleted_date" into "deleted_table" before performing the delete on "live_table". You could also use a before update trigger to keep old copies of updated records in the same way. Then you would only incur the performance loss of scanning/etc. the deleted records when you actually need to pull up deleted plus live records.