PostgreSQL is memory-hungry per connection: each idle client connection can consume several megabytes of RAM, and on a small VPS with 2\u20134 GB of RAM, an application that opens hundreds of connections can exhaust memory before the database even does any real work. PgBouncer fixes that by sitting between your application and PostgreSQL and multiplexing many client connections onto a small pool of server connections. This guide covers installation, configuration, pool sizing, and the pitfalls to avoid.<\/p>\n\n
You need PgBouncer if your application opens many short-lived connections \u2014 typical for server-side frameworks, web apps with per-request connections, or ORMs that do not reuse connections well. You may not need it if your app uses a single long-lived connection pool that is already sized to your RAM. A quick check: count connections with PgBouncer is in the standard repositories of every major distribution:<\/p>\n\n Configuration lives in A minimal working configuration looks like this:<\/p>\n\n Create You can generate the hash with The Use PgBouncer should never be exposed to the public internet. Bind it to Keep The two numbers that matter are SELECT count(*) FROM pg_stat_activity;<\/code> and compare against your VPS memory.<\/p>\n\nInstalling PgBouncer<\/h2>\n\n
apt install pgbouncer<\/code><\/li>dnf install pgbouncer<\/code><\/li><\/ul>\n\n\/etc\/pgbouncer\/pgbouncer.ini<\/code>, with authentication in \/etc\/pgbouncer\/userlist.txt<\/code>.<\/p>\n\nConfiguring pgbouncer.ini<\/h2>\n\n
[databases]
mydb = host=\/var\/run\/postgresql port=5432 dbname=mydb
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = \/etc\/pgbouncer\/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20<\/code><\/p>\n\nlisten_addr = 127.0.0.1<\/code> \u2014 bind to localhost so clients reach PgBouncer through the same host; never expose 6432 publicly<\/li>pool_mode = transaction<\/code> \u2014 the right default for web workloads; connections are recycled after each transaction<\/li>max_client_conn<\/code> \u2014 the maximum number of client connections PgBouncer will accept<\/li>default_pool_size<\/code> \u2014 server connections kept open per database, sized to your CPU and RAM<\/li><\/ul>\n\nSetting up the auth file<\/h2>\n\n
userlist.txt<\/code> with the database users PgBouncer may authenticate. Passwords must be MD5 hashes in the format PostgreSQL uses:<\/p>\n\n\"myapp\" \"md5\" + md5(password + username)<\/code><\/p>\n\nSELECT md5('password' || 'myapp');<\/code> in psql, or copy the value of rolpassword<\/code> from pg_authid<\/code> if you have access. Then restart PgBouncer: systemctl restart pgbouncer<\/code>.<\/p>\n\nPool modes: transaction vs session<\/h2>\n\n
pool_mode<\/code> setting changes how aggressively connections are recycled. transaction<\/code> mode returns a server connection to the pool as soon as the current transaction finishes, which means 100 concurrent clients can share as few as 10\u201320 server connections. session<\/code> mode keeps the association for the entire client session \u2014 closer to what a single long-lived connection looks like, but it needs more server connections.<\/p>\n\ntransaction<\/code> mode for web applications and APIs: it gives the biggest memory savings. Use session<\/code> mode only for clients that rely on session-scoped state such as SET<\/code> statements, temporary tables, or advisory locks held across transactions.<\/p>\n\nSecuring the connection<\/h2>\n\n
127.0.0.1<\/code> and let your application connect over localhost, or put it behind a firewall if your application runs on another host. For remote clients, add TLS on top:<\/p>\n\nclient_tls_sslmode = require<\/code> \u2014 encrypt client-to-PgBouncer traffic<\/li>server_tls_sslmode = require<\/code> \u2014 encrypt PgBouncer-to-PostgreSQL traffic<\/li>client_tls_cert_file<\/code> and client_tls_key_file<\/code> at a certificate for your hostname<\/li><\/ul>\n\nauth_type<\/code> at md5<\/code> or scram-sha-256<\/code> rather than trust<\/code>, even on localhost \u2014 a misconfigured application should not get in without credentials.<\/p>\n\nSizing the pools for your VPS<\/h2>\n\n
default_pool_size<\/code> and max_client_conn<\/code>. A good starting point on a 2 vCPU \/ 4 GB VPS:<\/p>\n\n