Re: Scalability in postgres

On Thu, 4 Jun 2009, Robert Haas wrote:

> On Wed, Jun 3, 2009 at 5:09 PM, Scott Carey <scott(at)richrelevance(dot)com> wrote:
>> On 6/3/09 11:39 AM, "Robert Haas" <robertmhaas(at)gmail(dot)com> wrote:
>>> On Wed, Jun 3, 2009 at 2:12 PM, Scott Carey <scott(at)richrelevance(dot)com> wrote:
>>>> Postgres could fix its connection scalability issues -- that is entirely
>>>> independent of connection pooling.
>
> 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? The only one that we've discussed here is
> Oracle, which seems to get around the problem by having a built-in
> connection pooler. That gets me back to thinking that the two issues
> are related, unless there's some other technique for dealing with the
> need to derive snapshots.

if this is the case, how hard would it be to have threads add and remove
themselves from some list as they get busy/become idle?

I've been puzzled as I've been watching this conversation on what internal
locking/lookup is happening that is causing the problems with idle threads
(the pure memory overhead isn't enough to account for the problems that
are being reported)

David Lang