Environment: PostgreSQL 9.2 on separate server, PHP 5.3, Zend Framework 1
I execute on few servers every minute hundreds PHP scripts (launched from shell, not as WWW server request) which use in Zend Framework pdo_pgsql connection to PostgreSQL. Average script execution time is about 15s, most of the time they don't use database. Right now each script opens database connection at the beginning and closes it at the end. It is inefficient so I decided that script will close and reopen db connection few times during execution.
But reopening connection to database in also inefficient. PHP persistent connection is impossible to use because of my PHP scripts run as separate processes (not as WWW server worker). In my opinion the best solution will be using connection pooling system on each server, which will store connections to PostgreSQL for PHP scripts.
Am I right? If yes, which connection pooling system do you recommend to use with PHP, Zend Framework (pdo_pgsql adapter) and PostgreSQL?
PgBouncer is ideal for this job. It'll reduce connection overhead without adding other unnecessary complexity if used in session pooling mode (the default). The more aggressive pooling modes like transaction- or statement-pooling do affect the client application so you should only use them if you actually need them.
More information can be found on the PostgreSQL wiki as James points out.