Why Odoo's built-in connection pooling keeps PostgreSQL connections open, how PgBouncer shares them across workers, and the OCA module Trobz built to make Odoo work with it.

PgBouncer is a connection pooler for PostgreSQL. It is useful in Odoo deployments, especially when a large number of users and Odoo workers are involved.

Why Odoo’s Connection Pooling Is Not Enough

Odoo’s built-in connection pooling works at process level: each Odoo process has its own ConnectionPool, limited to db_maxconn.

It does the job of reusing open connections available in the pool. But it never closes those connections, unless it reaches db_maxconn.

In practice, we observe that each Odoo worker ends up with up to 3 open connections in its pool. With 10 HTTP workers, that is up to 30 connections kept open continuously for a single instance.

Here Comes PgBouncer

PgBouncer limits the number of open connections by sharing one pool of connections between all workers at instance level. Odoo workers still have up to 3 open connections each, but these are connections to PgBouncer, which in turn closes unnecessary connections to PostgreSQL.

This has proven to help performance on Odoo deployments with multiple instances.

PgBouncer also lets you define how resources are shared, according to your priorities. For example:

  • the key Odoo instance on host A can open up to 30 connections;
  • an Odoo instance on host B, dedicated to reports, can open only up to 10.

Most importantly, it helps ensure that max_connections is never reached on the PostgreSQL server.

Odoo Needs Some Changes to Work With PgBouncer

When configuring PgBouncer, you can choose between two pooling modes:

  • pool_mode = session
  • pool_mode = transaction

With pool_mode = session, a server connection stays tied to a given Odoo process until that process ends, which is exactly what we are trying to change. To release the server connection once each transaction is complete, we use pool_mode = transaction.

This works fine, except for Odoo’s longpolling features, which rely on LISTEN/NOTIFY. LISTEN is not compatible with transaction mode.

To be more precise, PgBouncer passes NOTIFY statements through correctly in that mode. Only LISTEN fails, because it needs to keep the server connection open.

So for the single “listening” connection per instance that needs this statement, Odoo has to connect directly to the PostgreSQL server, bypassing PgBouncer.

A New Module: bus_alt_connection

At Trobz, we created bus_alt_connection to make these changes, by overriding the relevant method of the Dispatcher.

The module has been running successfully in production for months, and we recently open-sourced it in OCA/server-tools. If you plan to use PgBouncer in your Odoo deployments, give it a try. Feedback and reviews are welcome.