On 10/19/2010 11:42 AM, Doug Chamberlin wrote:

> On 10/19/2010 6:30 AM, Kjell Rilbe wrote:
>> PostgreSQL or SQL Server, which are both MGA aren't they?
>
> Not that I'm aware of. Their heritage is certainly not MGA. They may
> have recently grafted on some MGA stuff but I wouldn't trust they got it
> right without serious investigation.

Kjell is right. PostgreSQL was multi-generational from the
time it was just Postgres and spoke QUEL. It was designed for
optical disks which were write-once, so nothing could ever be
overwritten. Records were chained backward for "time travel",
the ability to look at the state of the database at any time
in the past. As far as I know, that never worked. Postgres
used conventional two-phase locking for concurrency control
until about ten years ago. It now uses a system much like
Firebird with a few differences.

One difference comes from its optical heritage. Record
versions are never moved. In Firebird, the most recent
version of a record always occupies the primary location
and back versions are chained from most recent to oldest.
That can result in fragmenting the most recent version
when it's bigger than its predecessor and will not fit
on the page, and more often moving an older version off
page. However, because the most recently committed
record moves around in Postgres, every update requires
a change to every index on the table. That's a lot of
extra writes, and a lot of fluff in indexes.

Another difference that was introduced to Postgres with
MVCC is that their record headers contain both the id
of the transaction that created them and the id of the
transaction that invalidated them. Someone once explained
why that's a good idea but the explanation escapes me.

And, of course, PostgreSQL uses a write-ahead log.


Cheers,

Ann