Many have asked me under what conditions they should use PgBouncer with Odoo, and others want to know if specific configurations must be implemented to ensure enhanced performance.

I’ve, therefore, decided to use this article to answer these questions and explain the setup between Odoo and PgBouncer a little deeper than I did in my previous article, where I discussed how to handle big data in Odoo.

Here, I will explain what PgBouncer is, when and why you should use it in Odoo implementation, performance tuning parameters recommendations, and my cautions.

Let’s get to it.

Table of Content

What is PgBouncer?

PgBouncer is a lightweight connection pooler for PostgreSQL databases. Its primary purpose is to improve the performance and scalability of database servers by managing a pool of client connections to the database. 

When an application requests a connection to the database, instead of establishing a new connection to the server every time, PgBouncer creates a new connection or reuses an existing one from its pool.

This reduces the overhead of establishing new connections and can help alleviate performance issues on the database server caused by many concurrent connections. 

Additionally, PgBouncer can be configured to perform load balancing across multiple database servers to enhance the overall performance and scalability of the system.

Why should you use PgBouncer?

I recommend using a pooler such as PgBouncer for the following reasons:

• Odoo uses persistent connections, which usually crowd the connector while sometimes opening more connections than the actual number allowed by the cluster.
• Useless Odoo connections remaining in SLEEP state block and lock important amounts of memory (see columns STATE and RES in pg_activity, for instance).
• Lack of PostgreSQL memory severely impacts the database and overall Odoo system performance while forcing the database cluster to resort to SWAP while inactive tasks trap live physical memory.

Using PgBouncer with Odoo implementation ensures excellent memory management, better connection and improved Odoo response time through a higher TPS / second ratio under heavy loads with many users.

When should you apply the PgBouncer-Odoo solution?

Typically, you should only use PgBouncer with Odoo under the following circumstances:

• You have many concurrent users or databases on the same DB cluster.
• You always fall short of memory in your database cluster, which puts pressure on your I/O storage.
• The performance of your PostgreSQL database cluster is unsatisfactory under heavy concurrent usage.

Your PgBouncer-Odoo deployment has to fall under one of these categories:

1. A high number of Odoo concurrent users.
2. A high number of open PostgreSQL connections.
3. A distributed Odoo architecture involving a scalable VM/instance/ container Odoo facing a unique and non-scalable database cluster.
4. A complex architecture involving different direct access routes and queries to the Odoo database but not being managed by Odoo (Direct PostgreSQL access instead).

What is the expected PgBouncer performance gain?

Below is a chart illustrating how a cluster performs in terms of TPS when subjected to a greater number of concurrent requests:

This data is quite reliable. Our latest performance tests executed with PgBouncer version 1.17.0 along with Odoo 16 Enterprise and PostgreSQL 14 are giving very similar results. As expected, Pg-bouncer allows more performance whenever the number of concurrent threads/ clients begins to increase.

In the case of Odoo, performance gains begin at around 25 heavy users and above, depending on the following criteria:

Initial recommendations for an Odoo performance-oriented setup

If you intend to optimise Odoo performance with PgBouncer, I advise you to follow these practices:

1. Source: Compile your source and make your own service. Controlling the ins and outs will give a far better result than playing ‘apt-get install.’
2. Deployment instance location: Do not install PgBouncer on a different instance from the cluster unless this cluster is not accessible at the system level (example: GCP CLOUD SQL, AWS RDS etc.). Doing so would degrade the access time from the Odoo instance to the PostgreSQL cluster by inserting four network latencies between them:

a. 1 latency from the Odoo machine to PgBouncer.

b. 1 latency from PgBouncer to PostgreSQL.

c. The other two on the way back from PostgreSQL to Odoo.

• Pooling strategy: Only use transactional pooling. Any other mode will either conflict with Odoo long-polling or make using PgBouncer useless.
• Authentication: Try to use TRUST auth_type: This will cut off all the AUTH stage overhead of PostgreSQL by bypassing most of the pg_hba mechanism. Meanwhile, user credentials for the Odoo user will be stored in the memory and allow direct access from Odoo to the PostgreSQL cluster. 
• Listening port: Only let PgBouncer listen on localhost (127.0.0.1); otherwise, you are generating a very high-security risk. If you had to deploy PgBouncer outside the PostgreSQL service, ensure you control the IPs you are listening from.

Looking to enhance memory management and response time in Odoo? 

Let us help 

Note: PgBouncer is a great tool when finely tuned and the parameters are set up properly. Any default deployment without precisely calculated ratios and values will end up making the initial performance and situation a lot worse and can potentially cause service failure. 

Understanding PgBouncer major settings

From the initial pgbouncer.ini file, we shall focus on the main notions detailed below:

a. Server check delay

server_check_delay ⇒  How long to keep released connections available for immediate reuse, without running sanity-check queries on it. If 0, then the query is always run.

This defines how aggressively you want your pooler to shut an idle connection and reclaim the resources from the PostgreSQL cluster:

• If you put 30, PgBouncer will wait 30 seconds after the Odoo statement result was given before shutting down the connection.
• If you put 0, PgBouncer will not wait and shall kill the connection IMMEDIATELY after the transaction between Odoo and PostgreSQL is finished. This is the most aggressive setting.

Advantages and drawbacks of a high value (30 seconds in SQL timing is considered high):

• ADVANTAGE ⇒ No resource is kept spare uselessly. PgBouncer reclaims all possible memory and connections used by Odoo. This means the best possible memory optimisation is possible since there will be more available RAM at all times.

It also means that connections will be immediately available. So, you can minimise the Odoo “out of connection” issue since you are reclaiming all possible connections in real time.

• DRAWBACK ⇒ If you have many similar connections and requests coming from Odoo, killing connections too fast can decrease performance since connections need to be opened again:

For instance, with recurrent mechanisms like website browsing, Odoo workers will mostly use the same connections because the access types for reading the pages are the same ones for anonymous visitors.

b. Default pool size setting with Odoo systems

default_pool_size ⇒ This setting defines how many server connections you want to allow per user/database pair inside the PgBouncer pooler. The default value is 20.

Caution: if the number you set is too low, it might be too aggressive and impact performance negatively. If the number you set is too high, it might allow too many open connections instead; and your PgBouncer will become inefficient and useless.

What is the correct calculation with Odoo, then? Let’s do the ballpark figure math:

Let’s say that each Odoo user needs at least one connection open  (although we know that Odoo tends to open a lot more depending on the ORM needs)

Let’s also assume that only 30% of the heavy-duty users are perfectly concomitant (doing actions precisely at the same moment).

We can then assume that the business of requests requires connections to be open for a more or less long period, and let’s make this a variable impacting the above assumed 30%:

• More complex requests from high-profile users (approval/ mass moves/ dashboarding) will give an index of 2
• Standard user requests from average users (sales input/ purchase order inputs / Warehouse moves generation and follow-up of inbound / outbound) will give an index of 1
• Simple user requests from low-involved staff (Consultation only / small operations / limited access and other light users) will give an index of 0.5

The rounded calculation would be the following:

• With 100 heavy power users ⇒ (100 * (30% x 2) = 60
• With 300 medium power users ⇒ (300 * (30% x 1) = 100
• With 1000 low power users ⇒ (1000 * (30% x 0.5) = 150 

Therefore, with a total 50 human concurrent users of the Odoo system, we could make the following assumptions:

• 40 heavy power users ⇒ (40 * (30% x 2) = 24
• 10 low power users ⇒ (10 * (30% x 0.5) = 2

In that case, the recommended default_pool_size setting would be 24 + 2 = 26.

c. Min pool size

min_pool_size ⇒ This will add more server connections to the pool if the pool drops below this value. The value is effectively capped at the pool size, which means the minimum amount of connections cannot be superior to the maximum amount. The default setting is 0.

Using this setting improves performance and access time when the usual load suddenly comes back after a period of total inactivity. 

In practice, this can happen with Odoo since we usually witness two specific downtimes during lunch break and evening break while they are resumed at the same time by most users at the same business hours. 

Therefore, this makes sense for certain types of Odoo deployment with low and high usage periods and a high-standard user concurrency deviation.

How to calculate it with Odoo, then? In the above-mentioned case, we recommend taking the default_pool_size setting and restricting it by 40%. Therefore, to follow up on our previous example of 50 users, the calculation would be as follow:  26 - 40% = 18 (rounded to inferior) 

d. Max client conn

max_client_conn is the maximum number of client connections allowed. When increased, the file descriptor limits should also be increased. 

Note that the actual number of file descriptors used is more than max_client_conn. The theoretical maximum used is:

max_client_conn + (max pool_size * total databases * total users)

But in the case of Odoo, a database user is specified in the connection string (all users connect under the same user name “odoo” as specified in the odoo.conf file).

Therefore the theoretical maximum is:

max_client_conn + (max pool_size * total databases)

To make this calculation, you must first use the values calculated in the previous sections. 

Coherence between systems

This section is to draw your attention to the necessity of applying strict logic when setting up the max connections setting inside the following three systems:

1. Odoo engine (inside the odoo.conf with setting db_maxconn)
2. PgBouncer (inside the pgbouncer.ini with setting max_client_conn)
3. PostgreSQL (inside postgresql.conf with setting max_connections)  

Word (2 sentences) of advice: 

The Odoo max_connections parameter should NOT be smaller than the PgBouncer’s.

PostgreSQL connections should NEVER be inferior to the PgBouncer ones; otherwise, it might generate a connection shortage and a PostgreSQL failure.

Use cautiously

The messages and concerns I have received in my inbox in the past seems the senders were convinced there were some magic recipes here that would somehow solve all their Odoo performance problems. 

In the case of Odoo, there are many other angles of approach to tackle, which has nothing to do with PgBouncer, such as;

• Wrong infrastructure setup, specs, or settings
• Weak module/ flow processing logic design
• Mis-management of table concurrency
• Mis-management of query planning
• Mis-management of tuple provisioning
• Weak, missing, unadapted or redundant indexing strategies
• Absence of heavy statements management
• Weak Odoo ORM coding and/ or customised add-ons.
• Odoo Studio induced ETL and database performance damage
• And the list goes on…

To wrap it up, remember that PgBouncer is a beneficial middleware doing a lot of good-to-session management for Odoo with PostgreSQL. 

Nevertheless, this applies to the cases described before and nothing more. Otherwise, the result will be more complexity, extra overheads, and performance loss.

When you have questions about optimising Odoo's performance, don’t hesitate to contact our expert team.