PgBouncer is the secret weapon for taming your PostgreSQL connection overhead, and its magic lies in a deceptively simple trick: connection pooling.
Let’s see it in action. Imagine a tiny Go web app that needs to hit the database for every request. Without pooling, each request spins up a brand new PostgreSQL connection.
package main
import (
"database/sql"
"fmt"
"log"
"net/http"
"time"
_ "github.com/lib/pq"
)
var db *sql.DB
func init() {
// In a real app, this would be in a config file or env var
connStr := "user=test password=test dbname=test sslmode=disable host=localhost port=5432"
var err error
db, err = sql.Open("postgres", connStr)
if err != nil {
log.Fatal(err)
}
// Set max open connections. Without pooling, this is the absolute limit.
// For demonstration, we'll keep it low.
db.SetMaxOpenConns(2)
db.SetMaxIdleConns(2)
db.SetConnMaxLifetime(time.Minute * 5)
err = db.Ping()
if err != nil {
log.Fatal(err)
}
fmt.Println("Database connected!")
}
func handler(w http.ResponseWriter, r *http.Request) {
var count int
// This is where the overhead happens: a new connection for each request
err := db.QueryRow("SELECT 1").Scan(&count)
if err != nil {
log.Println("Database query failed:", err)
http.Error(w, "Database error", http.StatusInternalServerError)
return
}
fmt.Fprintf(w, "Hello, database responded with: %d\n", count)
}
func main() {
http.HandleFunc("/", handler)
fmt.Println("Server starting on :8080")
log.Fatal(http.ListenAndServe(":8080", nil))
}
If you were to hit this web server with ab -n 100 -c 10 http://localhost:8080/, you’d quickly see PostgreSQL complaining about too many connections, or your Go app struggling to acquire a connection from the pool if SetMaxOpenConns was higher.
Now, let’s introduce PgBouncer. We’ll run PgBouncer locally, pointing it at our PostgreSQL instance.
# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=test user=test password=test
[pgbouncer]
listen_addr = 0.0.0.0:6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
admin_users = admin
stats_users = stats
pool_mode = session
max_client_conn = 100
default_pool_size = 10
min_pool_size = 2
# userlist.txt
"test" "test"
"admin" "adminpass"
"stats" "statspass"
We’ll also create the pgbouncer user in PostgreSQL:
CREATE USER test WITH PASSWORD 'test';
GRANT CONNECT ON DATABASE test TO test;
And start PgBouncer:
pgbouncer -d pgbouncer.ini
Now, our Go app connects to PgBouncer on port 6432 instead of directly to PostgreSQL on 5432.
package main
import (
"database/sql"
"fmt"
"log"
"net/http"
"time"
_ "github.com/lib/pq"
)
var db *sql.DB
func init() {
// Connect to PgBouncer
connStr := "user=test password=test dbname=mydb host=localhost port=6432 sslmode=disable"
var err error
db, err = sql.Open("postgres", connStr)
if err != nil {
log.Fatal(err)
}
// These settings are now less critical for direct connection limits,
// as PgBouncer manages the backend connections.
db.SetMaxOpenConns(100) // Allow more connections from the app side
db.SetMaxIdleConns(50)
db.SetConnMaxLifetime(time.Minute * 5)
err = db.Ping()
if err != nil {
log.Fatal(err)
}
fmt.Println("Connected to PgBouncer!")
}
func handler(w http.ResponseWriter, r *http.Request) {
var count int
// This still looks like a direct DB call, but it's going through PgBouncer
err := db.QueryRow("SELECT 1").Scan(&count)
if err != nil {
log.Println("Database query failed:", err)
http.Error(w, "Database error", http.StatusInternalServerError)
return
}
fmt.Fprintf(w, "Hello, database responded with: %d\n", count)
}
func main() {
http.HandleFunc("/", handler)
fmt.Println("Server starting on :8080")
log.Fatal(http.ListenAndServe(":8080", nil))
}
With this setup, the Go app can open many connections (up to 100 in SetMaxOpenConns), but PgBouncer will multiplex these over a much smaller set of actual PostgreSQL connections defined by default_pool_size and min_pool_size. When a client disconnects, PgBouncer doesn’t tear down the PostgreSQL connection; it just marks it as available for another client. This drastically reduces the overhead of connection establishment and teardown, which can be expensive for PostgreSQL.
The core problem PgBouncer solves is the high cost of PostgreSQL connection management. Each new connection requires significant work from the PostgreSQL server: process forking, memory allocation, authentication, and setting up session state. For applications with many short-lived connections (like web services), this overhead can become a major bottleneck, consuming CPU and memory on the database server and limiting the number of concurrent clients the application can serve.
PgBouncer operates as a lightweight proxy. When your application connects to PgBouncer, it’s establishing a connection to the proxy, not directly to PostgreSQL. PgBouncer then maintains a pool of actual connections to PostgreSQL. When your application sends a query, PgBouncer grabs an available connection from its pool, forwards the query to PostgreSQL, receives the result, and sends it back to your application. When the application is done with that connection (e.g., the request finishes), PgBouncer doesn’t close the PostgreSQL connection; it returns it to the pool, ready for the next client.
The pool_mode setting in pgbouncer.ini is crucial. session mode is the most common and generally recommended. In session mode, a client connection is assigned a server connection for the entire duration of the client’s session. This means that if your application uses transaction blocks (BEGIN; ... COMMIT;), that transaction will be executed on the same backend connection throughout. This is important because some PostgreSQL features (like advisory locks or temporary tables) are session-specific. transaction mode is more aggressive: a server connection is reused for multiple transactions within a client session, but the connection is released back to the pool between transactions. statement mode is the most aggressive, releasing the server connection after each statement, but it’s rarely suitable due to the session-specific nature of many SQL operations.
The most common pitfall when setting up PgBouncer is misconfiguring the userlist.txt file and the PostgreSQL user. The user your application uses to connect to PgBouncer (e.g., test in user=test) must exist in PostgreSQL with the correct password, and must have GRANT CONNECT ON DATABASE <your_db_name> TO <your_user>;. The user listed in userlist.txt is the user PgBouncer uses to authenticate against PostgreSQL if auth_type is set to trust (not recommended for production). For md5 or scram-sha-256 authentication, the userlist.txt file stores the credentials that PgBouncer uses to authenticate your application clients. So, your application’s username and password must be in userlist.txt.
The next thing you’ll likely encounter is figuring out how to monitor PgBouncer’s performance and connection usage.