Tuning PostgreSQL itself - Your first port of call

PostgreSQL's default configuration isn't designed for high performance, but rather for maximum compatibility, assuming it might be installed on a low-spec machine like a laptop with limited resources. Meaning that out of the box, it won't take full advantage of a server's available memory, CPU cores, or fast storage. 

For demanding applications like an Odoo tuning PostgreSQL correctly is critical. PostgreSQL can do a lot with a little.

While settings like shared_buffers​ get a lot of attention, random_page_cost​ is the unsung hero of PostgreSQL query performance. This single parameter tells the query planner how expensive it is to fetch a random page from the disk compared to a sequential one. Think of it like this: reading a book chapter by chapter is fast (sequential), but finding 100 different page numbers from the index is slow (random).

The default value of 4.0​ was designed for old-school spinning hard drives (HDDs), where moving the physical read head made random access incredibly slow. On modern SSDs, the penalty for random reads is drastically lower. By leaving this at its default, you're essentially telling PostgreSQL that using an index is much slower than it really is, and the PostgreSQL query planner will take different actions.

By simply appropriately tuning PostgreSQL you can take a 4s query down to fractions of a second.

So, how do you know what values to actually use? Guessing is a recipe for disaster, but thankfully you don't have to. The best place to start is with a brilliant tool called pgtune.

Read scalability using replica databases

Odoo 18.0 added support for read-only queries to be redirected to a secondary PostgreSQL installation. 

By offloading read traffic, you free up the main PostgreSQL copy to process transactions faster, which can dramatically improve the responsiveness and throughput of your entire Odoo instance, especially for high-traffic e-commerce sites or during intensive reporting tasks.

With Glo all production installations get their own PostgreSQL instance and at least 1 secondary copy for DR purposes, so we're actually reusing that in this instance. 

Profiling: Finding the needles in the haystack

It's impossible to fix what you can't measure. 

Guessing which part of your Odoo site is slow is like searching for a needle in a haystack blindfolded. This is where profiling and analysis come in, and luckily, Odoo has a powerful built-in profiler to help you pinpoint the exact causes of a slowdown, which we always use as our first port of call once we've tuned PostgreSQL.

To find the specific areas (think slow SQL queries or inefficient code paths) you can activate Odoo's profiler. With the profiler activated Odoo will run the page request as normal, but it will also record every single database query and RPC call made in the process. The output is a detailed flamegraph, showing how long it took to execute (the duration), and how many times it was called. 

This instantly reveals the most common culprits, whether it's a single, monstrously complex query that takes seconds to run or a seemingly harmless query that is being executed hundreds of times in a loop (a classic "N+1" problem). This tool transforms the vague complaint of "slowness" into a concrete, actionable list of the exact database operations that are bogging your system down, telling you precisely where to focus your optimization efforts.

In a lot of cases the slowdown is frequently Odoo waiting for data from PostgreSQL, so we'll focus on that for now.

When you can't use the Odoo profiler the PostgreSQL extension pg_stat_statements​ is your next most valuable tool. This powerful PostgreSQL extension acts like a flight recorder for your database, tracking execution statistics for every single query that runs. Now this isn't 100% perfect - it only logs SQL queries. But it's a good start.

The power of proper indexing

A database without the right indexes is like a library with no card catalogue; to find a single piece of information, you have to search every book on every shelf. This is called a "Sequential Scan," and it was the culprit behind many of our customer's slowest queries.

Using Odoo's profiler we were able to find the most common queries that were slow. Once you extract those queries from the profiler output you can use the very powerful PostgreSQL command EXPLAIN (ANALYZE, BUFFERS). This command doesn't just guess what the database will do; it actually runs the query and provides a detailed report of the execution plan, including the real time taken at each step and how much data was read from memory and disk.

While incredibly useful, the raw output can be difficult to interpret. That's where the excellent online tool explain.depesz.com comes in. By pasting the EXPLAIN output into this tool, we get a colour-coded, visual breakdown of the query plan that instantly highlights the most expensive operations that I personally find easier to read quickly.  This made it obvious where PostgreSQL was spending most of it's time. Armed with this knowledge, we could strategically add indexes on the specific columns being used in the WHERE clauses of those slow queries, giving the database a direct path to the data it needed.

However, adding indexes is a double-edged sword and must be done carefully. While an index dramatically speeds up read operations (SELECT), it comes at a cost. Every INSERT, UPDATE, or DELETE on the table now requires an additional write operation to update the index. Adding indexes indiscriminately can slow down write-heavy operations, such as posting invoices or processing stock moves in Odoo. Therefore, the goal is not to index everything, but to surgically add indexes only where they are proven to solve a significant performance problems.

Patching Odoo for immediate gains

Sometimes, even with perfect indexes, a query is inherently complex and slow due to the logic, or contraints of the database. When database tuning and budget hits its limit, you must look to change how Odoo works to achieve immediate performance gains.

A prime example was our customer's product search. Out of the box, when a user searches on the e-commerce shop on 18.0, Odoo performs a search that includes the default_code (Internal Reference or SKU) of every single product variant. For a product with dozens of variants, this requires a computationally expensive LEFT JOIN operation across a massive table, which can be slow.

Our solution was to override this behaviour. We created a new, hidden field on the product template. This field automatically computes and stores a simple text string containing all the default_codes from its variants, and then indexed appropriately. The shop search function was then modified to perform a simple text search on this single, pre-computed field, completely eliminating the slow join. 

Of course, this introduces a trade-off. This new field adds a small amount of "database bloat" and requires a recomputation whenever a variant is updated. However, in this scenario, the enormous improvement in user-facing search speed far outweighed the minor overhead on the less frequent product update operations.

Workarounds

One of the surprising bottlenecks we discovered was related to product tags. 

In Odoo 18.0, a large number of product tags can significantly slow down shop pages, as the system works to filter and display them. While we could have tried to optimise the code, the simplest solution is often the most effective.

In this case, the customer decided to sidestep the issue entirely. We worked with them to perform a data cleanup, removing a vast number of non-essential tags. 

For product differentiation, they transitioned to using product variant attributes with the no_create option enabled. This provides structured options (like 'Size' or 'Colour') without the performance overhead of thousands of loose tags. 

This highlights a crucial lesson: sometimes the most powerful performance fix isn't a complex code change, but a simple and strategic adjustment to your data structure.

Beyond Tuning: Scaling your infrastructure for peak performance

Once you've addressed the low-hanging fruit of slow queries, missing indexes and started patching Odoo, the next cost effective frontier is scaling your entire infrastructure to handle a heavier load. In our case study this wasn't really an option at this point.

The most significant step is moving beyond a single Odoo process and configuring multiple Odoo workers. Each worker is a separate copy of the application capable of handling user requests, allowing you to serve more people concurrently. 

Depending on your infrastructure you may also want to consider Redis for session storage, a high-speed, in-memory database that provides a centralized location for all workers to read and write session information seamlessly. 

This entire setup is underpinned by getting the hardware balance right; you must allocate sufficient Memory and CPU to not only power your hungry PostgreSQL instance but also to support each individual Odoo worker, ensuring the whole system operates in harmony.

This itself could be an entire post.