Thread: Deletes hurt
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)
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. Right. Where we just mark the row as dead and have to vacuum. We just delay the pain ;). Joshua D. Drake > -- > 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 > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
On Thu, May 31, 2007 at 09:23:01AM -0700, Joshua D. Drake wrote:
> Right. Where we just mark the row as dead and have to vacuum. We just
> delay the pain ;).
They've delayed the pain too. This is actually an illustration of
what Tom Lane said recently -- that the strategy of paying the cost
of maintenance outside the main transaction path is intrinsically
superior, because you don't have to pay it while your user is sitting
there waiting for you.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The plural of anecdote is not data.
--Roger Brinner
On 5/31/07, Andrew Sullivan <ajs@crankycanuck.ca> wrote: > They've delayed the pain too. This is actually an illustration of > what Tom Lane said recently -- that the strategy of paying the cost > of maintenance outside the main transaction path is intrinsically > superior, because you don't have to pay it while your user is sitting > there waiting for you. Or, you just use the features you pay for and use COMMIT NOWAIT. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 3rd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
On 5/31/07, Jonah H. Harris <jonah.harris@gmail.com> wrote: > Or, you just use the features you pay for and use COMMIT NOWAIT. Doh! Got mixed up on the conversation, that wouldn't help. But you could use Oracle's in-memory UNDO, which would not be as costly as a normal update. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 3rd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
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
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
Chris Hoover wrote: > 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 > I do this on a number of tables in my database, I use a byte to store a status, with each 'bit' meaning something, usually the 128 bit indicates deleted, so any value over 128 means the record is not current and that is tested for in any standard queries. This doesn't get around allowing multiple rows with the same data, with one being current and the rest deleted - but that's not an issue in my system. If it's deleted it's deleted, it doesn't get recreated, but can be restored, thus not using an actual delete fits my scenario quite well. -- Paul Lambert Database Administrator AutoLedgers
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