Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id CD7309FB649 for ; Thu, 31 May 2007 16:10:19 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 27546-05 for ; Thu, 31 May 2007 16:10:20 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.244]) by postgresql.org (Postfix) with ESMTP id D654C9FB6D9 for ; Thu, 31 May 2007 16:10:15 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c36so104716ana for ; Thu, 31 May 2007 12:10:14 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=fXrSo+lCOJIW0R91a+0dIqAQV3trr/95zhuN9BW37MdluT7faOdmjBQDbuu9F/BXUnwx0rVQJ4WXOYEJJ9oH3E2pYXUvjskjHoEwvcaP3ujzqPUesLZozOQXf/3n4rW/1TupZcl3BBNzZO85WL0iSlmIHPflh18g5NEoUluQft0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=JzbaRoQPXDsUpNBF3mWNEccR5IT/EIs8fVJv3EHCBs2+KKwXdiNU7dawqUJHnxvPzphITgxCNLpO0jEDb2IX8TCsroWsRzu3Tk6wjzsleggy3hjq0fSXHeO1ouxvKLujZ+D5U0qjrEGba/oz87T6zB1ykpeEo12SBQENwx5vIPI= Received: by 10.78.56.19 with SMTP id e19mr595807hua.1180638613802; Thu, 31 May 2007 12:10:13 -0700 (PDT) Received: by 10.78.201.16 with HTTP; Thu, 31 May 2007 12:10:13 -0700 (PDT) Message-ID: <1d219a6f0705311210r26e696afi24356ee428b73a33@mail.gmail.com> Date: Thu, 31 May 2007 15:10:13 -0400 From: "Chris Hoover" To: "Jim Nasby" Subject: Re: Deletes hurt Cc: "pgsql-admin@postgresql.org Admin" In-Reply-To: <734907A8-F30D-4924-8C97-30D2898CA43C@decibel.org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_20468_21506952.1180638613760" References: <734907A8-F30D-4924-8C97-30D2898CA43C@decibel.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/346 X-Sequence-Number: 25666 ------=_Part_20468_21506952.1180638613760 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Just curious, what sort of field do they use to mark the rows? I'm assuming a timestamp since it would half to be part of the primary key, or am I way off? This has really gotten me thinking about how I might implement this in my database. Thanks, Chris On 5/31/07, Jim Nasby wrote: > > I'm currently doing EnterpriseDB training at a well-known > entertainment company. I found out something yesterday that I thought > the community would find interesting... > > In their game (MMORPG) databases, they have fields on all their > tables that indicate whether a record has been deleted or not. I've > seen this done before, typically for data retention reasons. But they > had a daily process that went through each night and physically > deleted the records that had been marked as deleted. > > The reason they weren't actually deleting rows real-time is because > it cost to much in Oracle to do so. My guess is it's because Oracle > has to copy the entire deleted row to the undo log as part of the > delete, which would be pretty costly. > -- > Jim Nasby jim@nasby.net > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > ------=_Part_20468_21506952.1180638613760 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Just curious, what sort of field do they use to mark the rows?  I'm assuming a timestamp since it would half to be part of the primary key, or am I way off?  This has really gotten me thinking about how I might implement this in my database.

Thanks,

Chris

On 5/31/07, Jim Nasby <decibel@decibel.org> wrote:
I'm currently doing EnterpriseDB training at a well-known
entertainment company. I found out something yesterday that I thought
the community would find interesting...

In their game (MMORPG) databases, they have fields on all their
tables that indicate whether a record has been deleted or not. I've
seen this done before, typically for data retention reasons. But they
had a daily process that went through each night and physically
deleted the records that had been marked as deleted.

The reason they weren't actually deleting rows real-time is because
it cost to much in Oracle to do so. My guess is it's because Oracle
has to copy the entire deleted row to the undo log as part of the
delete, which would be pretty costly.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com       512.569.9461 (cell)



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

------=_Part_20468_21506952.1180638613760--