On Wed, Feb 10, 2010 at 4:48 PM, Dave Crooke <dcro...@gmail.com> wrote:
> On Wed, Feb 10, 2010 at 5:30 PM, Jon Lewison <jlewis...@gmail.com> wrote: > >> >> >> Just a nit, but Oracle implements MVCC. 90% of the databases out there >> do. >> > > Sorry, I spoke imprecisely. What I meant was the difference in how the rows > are stored internally .... in Oracle, the main tablespace contains only the > newest version of a row, which is (where possible) updated in place - > queries in a transaction that can still "see" an older version have to pull > it from the UNDO tablespace (rollback segments in Oracle 8 and older). > > In Postgres, all versions of all rows are in the main table, and have > validity ranges associated with them ("this version of this row existed > between transaction ids x and y"). Once a version goes out of scope, it has > to be garbage collected by the vacuuming process so the space can be > re-used. > > In general, this means Oracle is faster *if* you're only doing lots of > small transactions (consider how these different models handle an update to > a single field in a single row) but it is more sensitive to the scale of > transactions .... doing a really big transaction against a database with an > OLTP workload can upset Oracle's digestion as it causes a lot of UNDO > lookups, PG's performance is a lot more predictable in this regard. > > Both models have benefits and drawbacks ... when designing a schema for > performance it's important to understand these differences. > Yes, absolutely. It's not unusual to see the UNDO tablespace increase in size by several gigs for a large bulk load. Speaking of rollback segments I'm assuming that since all storage for non-visible row versions is in the main table that PostgreSQL has no equivalent for an ORA-01555. - Jon