On Thu, Jan 19, 2006 at 01:56:51AM -0500, Greg Stark wrote: > Tom Lane <[EMAIL PROTECTED]> writes: > > > Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > > >> Oracle does, but you pay in other ways. Instead of keeping dead tuples > > >> in the main heap, they shuffle them off to an 'undo log'. This has some > > >> downsides: > > >> Rollbacks take *forever*, though this usually isn't much of an issue > > >> unless you need to abort a really big transaction. > > > > > It's a good point though. Surely a database should be optimised for the > > > most common operation - commits, rather than rollbacks? > > > > The "shuffling off" of the data is expensive in itself, so I'm not sure > > you can argue that the Oracle way is more optimal for commits either. > > You pay in Oracle when you read these records too. If there are pending > updates you have to do a second read to the rollback segment to get the old > record. This hits long-running batch queries especially hard since by the time > they finish a large number of the records they're reading could have been > updated and require a second read to the rollback segments.
You pay the same cost in PostgreSQL though... If you index-scan to a dead tuple, you get pointed to where the new one is. And if you're seqscanning, well, you'll be reading everything anyway. > You also pay if the new value is too big to fit in the same space as the old > record. Then you get to have to follow a pointer to the new location. Oracle > tries to minimize that by intentionally leaving extra free space but that has > costs too. Again, similar to the cost with our MVCC. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
