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
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