Mark Lewis <mark.lewis@xxxxxxxx> writes: > On Thu, 2006-06-15 at 14:05 -0400, John Vincent wrote: > > Now I've been told by our DBA that we should have been able to wholy > > satisfy that query via the indexes. > > DB2 can satisfy the query using only indexes because DB2 doesn't do > MVCC. Well it's more subtle than that. DB2 most certainly does provide MVCC semantics as does Oracle and MSSQL and any other serious SQL implementation. But there are different ways to implement MVCC and every database makes decisions that have pros and cons. Postgres's implementation has some big benefits over others (no rollback segments, no expensive recovery operations, fast inserts and updates) but it also has disadvantages (periodic vacuums and indexes don't cover the data). The distinction you're looking for here is sometimes called "optimistic" versus "pessimistic" space management. (Not locking, that's something else.) Postgres is "pessimistic" -- treats every transaction as if it might be rolled back. Oracle and most others are "optimistic" assumes every transaction will be committed and stores information elsewhere to implement MVCC And recover in case it's rolled back. The flip side is that Oracle and others like it have to do a lot of extra footwork to do if you query data that hasn't been committed yet. That footwork has performance implications. -- greg
