On 2/17/07,
Joshua D. Drake <
jd@commandprompt.com> wrote:
My understanding is that the main difference is that rollbacks are
inexpensive for us, but expensive for Oracle. Talk to an Oracle DBA
about their Rollback logs :0.
Yes, I have seen cases where undo segments are thrashed. Generally it works well, and I agree it likely much cheaper on PostgreSQL as you would expect fewer scattered reads because the old version is inline with the rest of the data.
But if I recall undo segments are cached in Oracle, usually where I see problems is where the DBA is completely incompetent and has undersized the buffer cache. Oracle does direct reads (afaik) -- so undersizing the buffer cache can be brutal. A very common mistake with Oracle was undersizing the buffer cache and oversizing the shared pool (when the shared plans are stored), and with 9i and later they tried to have the management tools suggest the ideal values or have it automatically managed by the database. Probably a step in the right direction, but I know they still have bumps to iron it. :)
However, they don't have vacuum, we do.
Right, and I think that is more or less because Oracle doesn't need it. Vacuum's main purpose (correct me if I am wrong) is to recover/mark rows that are no longer used, and Oracle essentially reuses the space immediately.
Obviously with Oracle if you bloat out a table and delete a ton of rows then you have to rebuild the table, but that is more or less the same problem that PostgreSQL has and where vacuum full comes into play.
The only benefit with the Oracle model is that you can achieve flashback, which is a very rarely used feature in my book. The disadvantages is likely overhead to perform the "rollback" and possibly more scattered reads. I can say that I have used it, and it has come in handy, but hardly worth it. The benefit with the PostgreSQL model is the likelihood of the old rows being inline with the rest of the table data, potentially reducing scattered reads. The disadvantage is vacuuming, it seems to be often overlooked -- possibly solved by defaulting autovacuum to on? (seems to be the way Oracle is heading, defaulting statistics collection to on and other management features).
