> Christopher Browne <[EMAIL PROTECTED]> writes: >> After a long battle with technology, [EMAIL PROTECTED] ("Mark >> Woodward"), an earthling, wrote: >>> Not true. Oracle does not seem to exhibit this problem. > >> Oracle suffers a problem in this regard that PostgreSQL doesn't; in >> Oracle, rollbacks are quite expensive, as "recovery" requires doing >> extra work that PostgreSQL doesn't do. > > The Oracle design has got other drawbacks: if you need to access a row > version other than than the very latest, you need to go searching in the > rollback segments for it. This is slow (no index help) and creates > significant amounts of contention (since lots of processes are competing > to touch the rollback segments).
But, it is all probability, in most cases, the VAST majority, older versions aren't much needed outside the concurency of of active transactions. > Plus there's the old bugaboo that > long-running transactions require indefinite amounts of rollback space, > and Oracle is apparently unable to enlarge that space on-the-fly. > (This last seems like a surmountable problem, but maybe there is some > non-obvious reason why it's hard.) Yea, Oracle has a million way to die. And when you think you know all one million, you find one million and one. > > Basically there's no free lunch: if you want the benefits of MVCC it's > going to cost you somewhere. In the Postgres design you pay by having > to do VACUUM pretty often for heavily-updated tables. I don't think > that decision is fundamentally wrong --- the attractive thing about it > is that the overhead is pushed out of the foreground query-processing > code paths. Under certain circumstances, it is a very poor design. Think of a single row table that keeps a scoreboard or a session table that keeps a limited number of rows that are updated very frequently. > We still have lots of work to do in making autovacuum > smarter, avoiding vacuuming parts of relations that have not changed, > and so on. But I have no desire to go over to an Oracle-style solution > instead. We can't beat them by trying to be like them, and we run no > small risk of falling foul of some of their patents if we do. I proposed having a "key row entry" for each logical row. The key row entry points to the latest version of the row. There, each row entry is a linked list, in descending order, of previous row versions. The vast majority of the time, the latest version will be the first version. It is only when you have a previously started long running or concurrent transaction will you ever look at previous versions. I'm not saying it is an easy slam dunk, as I can think of a few difficulties off the top of my head, but it would solve the steady degradation of performance between vacuums and, to a possibly lesser extent, the cost of updating a row in a heavily indexed table. ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings