Quick Answer
Connection pooling reduces database connection overhead by reusing connections. The problem: Flash sale = 5,000 concurrent users = DB server crashes at 100% CPU. Not from queries, from connections. PostgreSQL max_connections limit (usually 100). Every Odoo worker = 1 connection, every Cron = 1 connection. Cluster of 5 Odoo servers × 20 workers each = 100 connections + Longpolling + backup scripts = instant limit. Increasing max_connections = 10MB RAM per connection = memory exhaustion. The solution: PgBouncer (transaction pooling middleware). Sits between Odoo and PostgreSQL. Odoo thinks it has 500 open connections, PgBouncer funnels through 20 actual connections. Since Odoo transactions are milliseconds, 20 connections serve thousands of workers. Installation: sudo apt-get install pgbouncer. Configuration: pool_mode = transaction (connection returned to pool after transaction commits, not after disconnect), max_client_conn = 1000, default_pool_size = 20, listen_port = 6432. Odoo setup: db_port = 6432 (PgBouncer), not 5432 (Postgres). Critical constraint: Longpolling (Bus) uses LISTEN/NOTIFY = doesn't work through transaction pooling = must connect directly to port 5432. Monitoring: psql -p 6432 -U postgres pgbouncer, then SHOW POOLS; (check cl_active, sv_active, sv_idle). Impact: Scale to 10,000 users without DB upgrade, CPU drops from 100% to 20%, eliminate "max_connections" errors.
The Connection Overhead Crisis
Your D2C brand runs a flash sale. Traffic spikes to 5,000 concurrent users.
Your 16-core database server hits 100% CPU usage and crashes.
You check the logs. It wasn't the queries. It was the connections.
The Problem:
PostgreSQL has a limit on max_connections (usually 100). Every time an Odoo worker process starts, it opens a connection. Every time a cron job runs, it opens a connection. When you run out, the database rejects new users.
The Solution: Connection Pooling
Instead of opening/closing 1,000 connections per second (expensive), you keep a pool of 20 live connections open. When Odoo needs to talk to the DB, it borrows one, uses it for 10ms, and puts it back.
We've implemented 150+ Odoo systems. For high-traffic sites, the built-in Odoo pooler isn't enough. You need PgBouncer. This is the standard for scaling Odoo beyond 50 concurrent users.
How Odoo Handles Connections
Odoo is a multi-process application (when using workers).
1 HTTP Worker = 1 DB Connection
1 Cron Worker = 1 DB Connection
If you configure workers = 10 in odoo.conf, Odoo will open roughly 10-15 connections to Postgres. This is fine.
The Problem Scenario
You have a cluster of 5 Odoo servers, each with 20 workers.
| Component | Connections |
|---|---|
| 5 servers × 20 workers | 100 connections |
| Longpolling workers | +5 connections |
| Backup scripts | +2 connections |
| Developer manual queries | +3 connections |
| Total | 110 connections |
| PostgreSQL max_connections | 100 (EXCEEDED!) |
You hit the Postgres max_connections limit instantly. Increasing max_connections consumes significant RAM on the DB server (roughly 10MB per connection), leading to memory exhaustion.
The Solution: PgBouncer
PgBouncer is a lightweight middleware that sits between Odoo and PostgreSQL.
The Magic
Odoo thinks: It has 500 open connections
PgBouncer reality: Funnels those 500 requests through just 20 actual connections to Postgres
Since Odoo transactions are short (milliseconds), those 20 connections can serve thousands of Odoo workers.
Installation (Ubuntu)
sudo apt-get install pgbouncerConfiguration (pgbouncer.ini)
[databases]
# Alias = host=IP port=5432 dbname=ActualDB
my_odoo_db = host=127.0.0.1 port=5432 dbname=my_odoo_db
[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
admin_users = postgres
# The Magic Setting
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20Critical Setting: pool_mode
| Mode | Behavior | Use Case |
|---|---|---|
| Session Mode | Connection stays assigned until client disconnects | ❌ Bad for Odoo scaling |
| Transaction Mode | Connection returned to pool after transaction commits | ✓ Perfect for Odoo |
Configuring Odoo to Use PgBouncer
Point Odoo to port 6432 (PgBouncer) instead of 5432 (Postgres).
[options]
db_host = 127.0.0.1
db_port = 6432
db_user = odoo
db_password = your_password
# ...The Longpolling Constraint
Critical Issue:
PgBouncer in "Transaction Mode" does not support all Postgres features. Specifically, the LISTEN/NOTIFY mechanism used by Odoo's Longpolling (Bus) will break.
The Workaround
| Worker Type | Connection |
|---|---|
| Main Odoo Workers (HTTP) | Port 6432 (PgBouncer) |
| Longpolling Worker (Gevent/Bus) | Port 5432 (Direct Postgres) |
This usually requires configuring two separate Odoo instances (or Service files) if you are strictly separating them, or simply accepting that Longpolling might not work perfectly if forced through transaction pooling.
Note: Odoo 16+ uses a new websocket system that is more robust, but direct connection for the bus is still recommended.
Monitoring Connection Health
1. Postgres Logs
Without PgBouncer: Thousands of "Authorized" / "Disconnected" logs per minute
With PgBouncer: Logs are quiet. Connections stay open for hours
2. SHOW POOLS Command
Connect to the special pgbouncer admin console:
psql -p 6432 -U postgres pgbouncerRun SHOW POOLS;
| Metric | Meaning |
|---|---|
| cl_active | Clients (Odoo) currently waiting |
| sv_active | Servers (Postgres) currently working |
| sv_idle | Connections ready to be used |
If cl_waiting is high, you need to increase default_pool_size | |
Action Items: Deploy PgBouncer
Check Current Usage
❏ Run query in database: SELECT count(*) FROM pg_stat_activity;
❏ Is it close to 100? You are in the danger zone
Deploy PgBouncer
❏ Install PgBouncer on the Database Server
❏ Configure pool_mode = transaction
❏ Update odoo.conf to use port 6432
Stress Test
❏ Use Locust to simulate 500 users hitting homepage
❏ Watch CPU usage on DB server. Should be significantly lower than before
Frequently Asked Questions
What is PgBouncer and why do I need it for Odoo?
PgBouncer is a lightweight connection pooler for PostgreSQL. Why you need it: PostgreSQL has max_connections limit (usually 100). Each Odoo worker = 1 connection. Cluster of 5 servers × 20 workers = 100 connections instantly. Adding Longpolling, Crons, backup scripts = exceed limit = database rejects new users. Increasing max_connections = 10MB RAM per connection = memory exhaustion. PgBouncer solution: Sits between Odoo and PostgreSQL, creates connection pool. Odoo thinks it has 500 connections, PgBouncer funnels through 20 actual Postgres connections. Since Odoo transactions are milliseconds, 20 connections serve thousands of workers. Installation: sudo apt-get install pgbouncer. Result: Scale to 10,000 users without DB upgrade, eliminate "max_connections" errors, CPU drops from 100% to 20%.
What is the difference between session and transaction pool modes?
Pool mode determines when connections are returned to the pool. Session mode: Connection stays assigned to client until they disconnect. One Odoo worker = holds connection for hours = defeats pooling purpose = bad for scaling. Transaction mode (recommended for Odoo): Connection returned to pool immediately after transaction commits. Odoo transaction = milliseconds = connection available for next request instantly = efficient reuse. Configuration: pool_mode = transaction in /etc/pgbouncer/pgbouncer.ini. Trade-off: Transaction mode doesn't support all PostgreSQL features (LISTEN/NOTIFY for Longpolling/Bus). Workaround: HTTP workers use port 6432 (PgBouncer), Longpolling worker uses port 5432 (direct Postgres). Impact: 20 connections in transaction mode serve same load as 200 connections in session mode.
How do I configure Odoo Longpolling with PgBouncer?
Longpolling/Bus requires direct PostgreSQL connection, not through PgBouncer. Why: Longpolling uses LISTEN/NOTIFY mechanism for real-time updates (chat, notifications). PgBouncer transaction mode doesn't support LISTEN/NOTIFY = Longpolling breaks if routed through PgBouncer. Solution: Two-port setup. Main Odoo workers (HTTP): db_port = 6432 (PgBouncer) in odoo.conf. Longpolling worker (Gevent/Bus): Direct connection to port 5432 (PostgreSQL). Implementation: Configure separate Odoo service for Longpolling, or use Odoo 16+ new websocket system (more robust). Verification: Check Odoo logs for Longpolling port (usually 8072), test live chat/discuss module. Alternative: Use session mode for Longpolling worker specifically (not recommended for HTTP workers).
How do I monitor PgBouncer performance?
Use SHOW POOLS command in PgBouncer admin console. Access: psql -p 6432 -U postgres pgbouncer, then run SHOW POOLS;. Key metrics: cl_active = clients (Odoo) currently waiting for connection, sv_active = server connections (Postgres) currently processing queries, sv_idle = connections ready to be used (pool size), cl_waiting = clients queued (if high = increase default_pool_size). Postgres logs: Without PgBouncer = thousands of "connection authorized"/"disconnected" per minute. With PgBouncer = quiet logs, connections stay open for hours. Stress test: Use Locust to simulate 500+ concurrent users, monitor DB CPU (should be 50-70% lower with PgBouncer). Optimal values: sv_idle should be close to default_pool_size (20), cl_waiting should be 0 or near-zero.
Free Database Scaling Audit
Is your database CPU spiking? We'll analyze your connection overhead, install and configure PgBouncer for you, optimize your max_connections and shared buffers, ensure your Longpolling setup remains functional, and scale to 10,000 users without upgrading your hardware.