Django opens database connection at the start of request, and closes it at the end of response: it’s not a secret. Authors of framework believe that pooling belongs to external tools, not ORM, that’s why “big” Django projects use connection poolers to avoid overhead.
First, install it via APT:
$ sudo apt-get install pgbouncer
auth_type = md5 auth_file = /var/lib/postgresql/8.4/main/global/pg_auth
pg_auth file looks like this:
"user1" "md5 of password" "" "user2" "md5 of password" "" "user3" "md5 of password" ""
This format is specific to PostgreSQL < 9.0, but it is solved problem: there is Python script to generate such file from
pg_shadow table (PostgreSQL 9.*).
server_reset_query = DISCARD ALL;
If you want, you can change
pool_mode, but following is default (and offers full transparency):
pool_mode = session
If you don’t know what to put into
databases section, make it look like this:
[databases] ; fallback connect string * = host=127.0.0.1
Set host and port:
listen_addr = 127.0.0.1 listen_port = 6432
Of course there are other settings, but it is sufficient to use mentioned ones, say, to try PgBouncer on your machine. Note that I didn’t write anything about
pidfile, and other settings that are already defined in default config on Debian.
/etc/default/pgbouncer and set
1 (it is
0 by default). Finally, start PgBouncer:
$ sudo /etc/init.d/pgbouncer start
By default log is located at
/var/log/postgresql/pgbouncer.log. There is bunch of useful settings (in
/etc/pgbouncer/pgbouncer.ini) related to logging:
log_connections = 1 log_disconnections = 1 ; log error messages pooler sends to clients log_pooler_errors = 1 stats_period = 60
If you find log too verbose, you can set first two to zeroes, and increase last one. Last one, that is,
stats_period, defines how often PgBouncer will log statistics that looks like this:
2012-09-25 00:00:00.000 12345 LOG Stats: N req/s, in N b/s, out N b/s,query N us
If you changed configuration when PgBouncer is already running, reload it:
$ sudo /etc/init.d/pgbouncer reload
All you need to change in Python code is
PORT setting (corresponds to
This blog is about things I encounter while doing web and non-web software development.