Re: Scalability in postgres

On Thu, 4 Jun 2009 06:57:57 -0400, Robert Haas <robertmhaas(at)gmail(dot)com> wrote
in http://archives.postgresql.org/pgsql-performance/2009-06/msg00065.php :

> I think I see the distinction you're drawing here. IIUC, you're
> arguing that other database products use connection pooling to handle
> rapid connect/disconnect cycles and to throttle the number of
> simultaneous queries, but not to cope with the possibility of large
> numbers of idle sessions. My limited understanding of why PostgreSQL
> has a problem in this area is that it has to do with the size of the
> process array which must be scanned to derive an MVCC snapshot. I'd
> be curious to know if anyone thinks that's correct, or not.
>
> Assuming for the moment that it's correct, databases that don't use
> MVCC won't have this problem, but they give up a significant amount of
> scalability in other areas due to increased blocking (in particular,
> writers will block readers). So how do other databases that *do* use
> MVCC mitigate this problem?

I apologize if it is bad form to respond to a message that is two months old,
but I did not see this question answered elsewhere and thought it
would be helpful
to have it answered. This my rough understanding. Oracle never
"takes" a snapshot,
it computes one the fly, if and when it is needed. It maintains a
structure of recently
committed transactions, with the XID for when they committed. If a
process runs into
a tuple that is neither from the future nor from the deep past, it
consults this structure
to see if that transaction has committed, and if so whether it did so before or
after the current query was started. The structure is partionable so
it does not have
one global lock to serialize on, and the lock is short as it only gets
the info it needs, not the
entire set of global open transactions.

> The only one that we've discussed here is
> Oracle, which seems to get around the problem by having a built-in
> connection pooler.

There are several reasons to have something like Oracle's shared
server (or whatever they
call it now), and I don't think global serialization on snapshots is
high among them, at
least not for Oracle. With shared server, you can (theoretically)
control memory usage so that 10,000 independent processes don't all
decide to do a large in-memory sort or hash join at the same time.

It is also a bit more than a standard connection pooler, because
multiple connections can
be in the middle of non-read-only transactions on the same backend at
the same time. I
don't think client-based pools allow that.

Jeff