On Fri, Mar 20, 2009 at 9:27 AM, Scott Ribe <scott_ribe@killerbytes.com> wrote:
>> That's why I was looking for a more balanced benchmark that exercises
>> said capabilities.
>
> OK, here's the thing, I will give you *one* sample issue to think about, as
> an illustration of the kinds of differences there are.
>
> - PostgresQL uses MVCC instead of row or page locking, which means only
> writer vs writer locks, which means in many situations less contention and
> better throughput as your concurrent load goes up.
It's also important to point out that writers don't necessarily block
other writers. As long as they're operating on different ranges of
the data set. You can have dozens of writers streaming data in with
differening primary keys all running together.
Then of course, you have the ability to have readers block writers
using for update, which turns a reader into a writer-to-(possibly)-be.
A lot of older dbs had locking by the page, not by the record, or as
with myisam, by the whole table. Page locking lead to records that
shared pages locking each other needlessly. Table locking leads to
even longer queues forming under heavy write load. This does NOT
happen in pgsql if you're updating / inserting independent records.
One of the reasons postgres scales so well is it keeps writes 'cheap'
in that they don't have to interact with anything other than writes to
the same records. If you've got millions of records and thousands
being updated, writes not blocking writes combined with record level
locking versus page level locking (or worse table level locking), pg
can handle pretty high concurrent write loads on the right hardware
and still maintain a good throughput on reads. And concurrent write
load is what usually cripples a server.
