Re: Deletes hurt - Mailing list pgsql-admin

From Simon Riggs
Subject Re: Deletes hurt
Date
Msg-id 1180728467.26297.267.camel@silverbirch.site
Whole thread Raw
In response to Deletes hurt  (Jim Nasby <decibel@decibel.org>)
List pgsql-admin
On Thu, 2007-05-31 at 09:14 -0700, 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.

I believe it has more to do with the cost of removing each row from the
index, which might then require parent uplink updates also, which is a
major source of contention. DB2 type 2 indexes deliberately avoid that
issue, and opt for an approach that requires an offline utility to clear
up afterwards. Sounds like a great plan to me...

--
  Simon Riggs
  EnterpriseDB   http://www.enterprisedb.com



pgsql-admin by date:

Previous
From: "Simon Riggs"
Date:
Subject: Re: Attempt to re-archive existing WAL logs afterrestoringfrom backup
Next
From: Jon Colverson
Date:
Subject: Re: Attempt to re-archive existing WAL logs afterrestoringfrom backup