pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDeletes hurt
8+ messages / 7 participants
[nested] [flat]
* Deletes hurt
@ 2007-05-31 16:14 Jim Nasby <decibel@decibel.org>
0 siblings, 3 replies; 8+ messages in thread
From: Jim Nasby @ 2007-05-31 16:14 UTC (permalink / raw)
To: pgsql-admin
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)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Deletes hurt
@ 2007-05-31 16:23 Joshua D. Drake <jd@commandprompt.com>
parent: Jim Nasby <decibel@decibel.org>
2 siblings, 1 reply; 8+ messages in thread
From: Joshua D. Drake @ 2007-05-31 16:23 UTC (permalink / raw)
To: Jim Nasby <decibel@decibel.org>; +Cc: pgsql-admin
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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Deletes hurt
@ 2007-05-31 16:58 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 8+ messages in thread
From: Andrew Sullivan @ 2007-05-31 16:58 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Deletes hurt
@ 2007-05-31 18:53 Jonah H. Harris <jonah.harris@gmail.com>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 8+ messages in thread
From: Jonah H. Harris @ 2007-05-31 18:53 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-admin
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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Deletes hurt
@ 2007-05-31 18:54 Jonah H. Harris <jonah.harris@gmail.com>
parent: Jonah H. Harris <jonah.harris@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jonah H. Harris @ 2007-05-31 18:54 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-admin
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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Deletes hurt
@ 2007-05-31 19:10 Chris Hoover <revoohc@gmail.com>
parent: Jim Nasby <decibel@decibel.org>
2 siblings, 1 reply; 8+ messages in thread
From: Chris Hoover @ 2007-05-31 19:10 UTC (permalink / raw)
To: Jim Nasby <decibel@decibel.org>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Deletes hurt
@ 2007-05-31 22:42 Paul Lambert <paul.lambert@autoledgers.com.au>
parent: Chris Hoover <revoohc@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Paul Lambert @ 2007-05-31 22:42 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; +Cc: Jim Nasby <decibel@decibel.org>; pgsql-admin
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Deletes hurt
@ 2007-06-01 20:07 Simon Riggs <simon@2ndquadrant.com>
parent: Jim Nasby <decibel@decibel.org>
2 siblings, 0 replies; 8+ messages in thread
From: Simon Riggs @ 2007-06-01 20:07 UTC (permalink / raw)
To: Jim Nasby <decibel@decibel.org>; +Cc: 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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-06-01 20:07 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-31 16:14 Deletes hurt Jim Nasby <decibel@decibel.org>
2007-05-31 16:23 ` Joshua D. Drake <jd@commandprompt.com>
2007-05-31 16:58 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-05-31 18:53 ` Jonah H. Harris <jonah.harris@gmail.com>
2007-05-31 18:54 ` Jonah H. Harris <jonah.harris@gmail.com>
2007-05-31 19:10 ` Chris Hoover <revoohc@gmail.com>
2007-05-31 22:42 ` Paul Lambert <paul.lambert@autoledgers.com.au>
2007-06-01 20:07 ` Simon Riggs <simon@2ndquadrant.com>
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