On 16 Jun 2006 07:23:26 -0400, Greg Stark <gsstark@mit.edu> wrote:
> 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.
Not disagreeing here at all, but considering that Oracle, DB2, and SQL
Server, et al have proven themselves to perform extremely well under
heavy load (in multiple benchmarks), the overhead of an UNDO
implementation has a calculable break even point.
Feel free to debate it, but the optimistic approach adopted by nearly
every commercial database vendor is *generally* a better approach for
OLTP.
Consider Weikum & Vossen (p. 442):
We also need to consider the extra work that the recovery algorithm
incurs during normal operation. This is exactly the catch with the
class of no-undo/no-redo algorithms. By and large, they come at the
expense of a substantial overhead during normal operations that may
increase the execution cost per transaction by a factor of two or even
higher. In other words, it reduces the achievable transaction
throughput of a given server configuration by a factor of two or more.
Now, if we're considering UPDATES (the worst case for PostgreSQL's
current MVCC architecture), then this is (IMHO) a true statement.
There aren't many *successful* commercial databases that incur the
additional overhead of creating another version of the record, marking
the old one as having been updated, inserting N-number of new index
entries to point to said record, and having to WAL-log all
aforementioned changes. I have yet to see any successful commercial
RDBMS using some sort of no-undo algorithm that doesn't follow the,
"factor of two or more" performance reduction. However, if you
consider an INSERT or DELETE in PostgreSQL, those are implemented much
better than in most commercial database systems due to PostgreSQL's
MVCC design. I've done a good amount of research on enhancing
PostgreSQL's MVCC in UPDATE conditions and believe there is a nice
happy medium for us.
/me waits for the obligatory and predictable, "the benchmarks are
flawed" response.
--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/
