pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Simon Riggs <simon@2ndquadrant.com>
To: Jim Nasby <decibel@decibel.org>
Cc: pgsql-admin@postgresql.org
Subject: Re: Deletes hurt
Date: Fri, 01 Jun 2007 21:07:46 +0100
Message-ID: <1180728467.26297.267.camel@silverbirch.site> (raw)
In-Reply-To: <734907A8-F30D-4924-8C97-30D2898CA43C@decibel.org>
References: <734907A8-F30D-4924-8C97-30D2898CA43C@decibel.org>

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





view thread (8+ messages)

Message-ID: <1180728467.26297.267.camel@silverbirch.site>
Permalink:  ../1180728467.26297.267.camel@silverbirch.site/
Also on:    postgresql.org/message-id/1180728467.26297.267.camel@silverbirch.site

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: simon@2ndquadrant.com, decibel@decibel.org
  Subject: Re: Deletes hurt
  In-Reply-To: <1180728467.26297.267.camel@silverbirch.site>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox