Hello Cary,
I'm following this list on the PostgreSQL Home Page, and I'm not
a PostgreSQL hacker, but I do know my acronyms and my database
theory.
MVCC stands for Multi Version Concurrency Control.
It is a feature which is very, very powerful, and deals with the
fact that multiple requests are handled by the database-server.
Lets say multiple request come in at the same time, open a
transaction-block, and try to access the same data.
In the previous versions of PostgreSQL the whole table was locked
exclusively until the transaction-block was commited.
This means the second transaction-request that comes in, is delayed from
processing until the lock has been released by the previous transaction.
With MVCC, the database-server detects the second transaction-request,
scans it, looks for interfering requests, and, if possible, creates a
second version of the data being requested. Hence the name Multi
Version.
It creates multiple versions of the data, in memory only ofcourse, if it
were on the disk, things would get out of hand pretty quickly,
and only on ONE occasion it does not.
If the first request that comes in is a "writer" (a transaction-block
that updates data) and the second request is also a "writer" on the same
data, then it detects an exclusive write-lock.
If the first is "reader" (a transaction-block that only uses "select" to
"read" the data) and the second is a "reader", or a "writer" it creates
a second version of the same data and hands it to the second
transaction.
This means that if MVCC is stable, and you are using it on a heavily
multi-user environment, you will see a very, very big improvement in
responsiveness and overall performance of the database.
It does mean however, that it uses more resources on the server, because
it can now handle the load better.
(hackers, please feel free to correct me on any of my statements)
Hope that helped.
Walter van der Schee
(not a PostgreSQL hacker)
