Thread: Question about the Internals

I'm a consultant for a large company and they are looking at a database to
use.

I made the suggestion about going with Postgresql.

I told him about it's MVCC design, he liked that ideal, but wanted to know
exactly HOW did it handle the multiple versions of records.  He's concerned
because he was burn very early on by another database that was MVCC (won't
mention any names) and his system became corrupt.  That database has since
improved and fixed all of their problems.

But the question is, how does Postgresql handle the multiple versions?
How does it handle the records BEFORE they are committed and how does it
handle the records AFTER they are committed that allows different users to
possible have a different view of the data and indexes. And also how does
the transactional part of the system place a role?

Any help here would be great appreciated!

Thanks

Relaxin wrote:
> I'm a consultant for a large company and they are looking at a database to
> use.
>
> I made the suggestion about going with Postgresql.
>
> I told him about it's MVCC design, he liked that ideal, but wanted to know
> exactly HOW did it handle the multiple versions of records.  He's concerned
> because he was burn very early on by another database that was MVCC (won't
> mention any names) and his system became corrupt.  That database has since
> improved and fixed all of their problems.
>
> But the question is, how does Postgresql handle the multiple versions?
> How does it handle the records BEFORE they are committed and how does it
> handle the records AFTER they are committed that allows different users to
> possible have a different view of the data and indexes. And also how does
> the transactional part of the system place a role?

Each record has a transction id of creation and an expire transaction
id.  When you add a record, you put your xid on the old record and
create a new one.  No one has reported any corruption problems with our
database for a long time, so you should be fine.

If you want details, see the developers web page and see my internals
presentation --- it has a diagram showing old/new rows and their
visibility to a single transaction.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Sun, Oct 26, 2003 at 02:42:27PM -0800, Relaxin wrote:
> But the question is, how does Postgresql handle the multiple versions?

On the developers site, there is an MVCC internals presentation.
Have a look at it -- even I (think I) understand it.

A
--
----
Andrew Sullivan                         204-4141 Yonge Street
Afilias Canada                        Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110