Re: Connection Pooling

On Thu, Oct 6, 2011 at 3:57 PM, Brandon Phelps <bphelps(at)gls(dot)com> wrote:

> Can anyone recommend a good solution for connection pooling? Here is our
> setup:
>
> 2 PostgreSQL 9.1 servers (1 master, 1 hot standby).
> 1 Apache 2.2.17
>
> We have a pretty extensive web application running on the apache server
> that talks to both of the database servers. Updates and small tasks (simple
> selects, etc) are directed to the master DB server while large reports that
> can take a while to run are directed to the hot standby, as not to affect
> performance of the master.
>
> Each page of the web app generally make a single connection to the database
> with the exception being the complicated reports which first make a
> connection to the master, verify that the user is allowed to access the page
> in question, close that connection, then open another connection to the hot
> standby for the report itself.
>
> One connection per page is not all that bad however the end users who make
> use of the web app are quite familiar with it and often fly through the
> pages very fast. We would like to implement some type of connection pooling
> so that these database connections (from web server to the DB servers) do
> not have to get created and torn down constantly.
>
> I have checked out the pg_pool website however was not very impressed with
> the documentation provided. Is pg_pool going to be our best solution or is
> there something better? Any advice would be appreciated.
>
> Thanks,
> Brandon
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>
>

If you want to do load balancing between your database servers (split reads
between the master and slave) or have auto failover then Pgpool-II is going
to be your best bet. I just did a similar setup and the documentation isn't
the best, but some googling turned up a few guides that I was able to jump
between to get it setup and going.

If you just want all of the DB traffic to go to the master (and deal with
failover on your own) you can use pgbouncer, which is much simpler, but also
less feature-rich.

Adam