Message-ID: <4C50ABA5.5010408@emolecules.com>
Date: Wed, 28 Jul 2010 15:13:57 -0700
From: Craig James <craig_james@emolecules.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US;
	rv:1.9.1.11) Gecko/20100711 Thunderbird/3.0.6
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Connection pooling for a mixture of lightweight and heavyweight jobs?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I have a question that may be related to connection pooling.

We create a bunch of high-performance lightweight Postgres clients that serve up images (via mod_perl and Apache::DBI).  We have roughly ten web sites, with ten mod_perl instances each, so we always have around 100 Postgres backends sitting around all the time waiting.  When a lightweight request comes in, it's a single query on an primary key with no joins, so it's very fast.

We also have a very heavyweight process (our primary search technology) that can take many seconds, even minutes, to do a search and generate a web page.

The lightweight backends are mostly idle, but when a heavyweight search finishes, it causes a burst on the lightweight backends, which must be very fast. (They provide all of the images in the results page.)

This mixture seems to make it hard to configure Postgres with the right amount of memory and such.  The primary query needs some elbow room to do its work, but the lightweight queries all get the same resources.

I figured that having these lightweight Postgres backends sitting around was harmless -- they allocate shared memory and other resources, but they never use them, so what's the harm?  But recent discussions about connection pooling seem to suggest otherwise, that merely having 100 backends sitting around might be a problem.

Craig