PostgreSQL tuning for Odoo

Why PostgreSQL tuning matters for Odoo

Odoo keeps everything in PostgreSQL: records, settings, translations, attachments' metadata and the queue of scheduled actions. Python code builds the screens, but it waits on the database for almost every click. When Odoo feels slow, the database is often where the time goes, either because PostgreSQL has too little memory to work with, or because a few heavy queries run far more often than anyone realised.

A fresh PostgreSQL installation is set up to start on almost any machine, so its defaults are small. They are safe, but they leave most of a modern server unused. Tuning is about telling PostgreSQL what hardware it really has, then removing the specific bottlenecks your Odoo database shows.

Odoo PostgreSQL memory settings

Four settings decide how PostgreSQL uses memory: shared_buffers, effective_cache_size, work_mem and maintenance_work_mem. On most Odoo servers PostgreSQL shares the machine with the Odoo workers, so it should take a smaller share than it would on a dedicated database server. Our Odoo server sizing guide gives the formulas and a ready-made table from 2 to 16 vCPUs, so we won't repeat them here.

One warning about work_mem: it applies per sort or hash operation, not per connection. A complex report can use several at once, in several connections at once. Raise it carefully, and watch memory after you do.

Settings for SSD storage

Almost every cloud server today runs on SSDs, but some PostgreSQL defaults still assume spinning disks.

random_page_cost

This tells the query planner how expensive it is to read a page from a random place on disk. The default is 4.0. PostgreSQL's documentation notes that solid-state drives may be better modelled with a lower value, and around 1.1 is a common choice on SSD. A lower value makes the planner use indexes more readily, which suits Odoo's many lookups by ID and foreign key.

effective_io_concurrency

This tells PostgreSQL how many reads the storage can handle in parallel. SSDs handle far more than a single disk, so a value in the low hundreds is common.

Checkpoints and WAL

PostgreSQL writes changes to its write-ahead log (WAL) and flushes them to the data files at checkpoints. Too-frequent checkpoints cause bursts of disk writes, which you may notice during large imports or module updates. Raising max_wal_size to a few GB and spreading checkpoints out with checkpoint_completion_target smooths this out. If the PostgreSQL log warns that checkpoints are occurring too frequently, that is the setting to look at.

Odoo database connections and db_maxconn

Each Odoo process keeps its own pool of database connections, limited by the db_maxconn option in the Odoo configuration. PostgreSQL has its own limit, max_connections, which is typically 100 by default. If the Odoo workers, cron processes and any other tools together try to open more connections than PostgreSQL allows, users see errors like "too many clients already".

A few rules keep this under control:

• Add it up. Count the Odoo processes across every instance on the server, multiply by their pool size, and keep the total under max_connections with some room for backups and maintenance.
• Lower db_maxconn before raising max_connections. Each PostgreSQL connection uses memory, so a very high limit can cause trouble of its own.
• Watch for idle transactions. A connection left "idle in transaction" holds locks and blocks cleanup. Setting idle_in_transaction_session_timeout ends them after a while.

Find slow Odoo queries first

Settings help everywhere a little. Fixing the few slowest queries often helps a lot more. Two tools show where the time goes.

pg_stat_statements

This extension, which ships with PostgreSQL, records every query shape with how often it ran and how much time it took in total. Sort by total time to find the queries worth fixing. A query that takes 50 ms but runs 100,000 times a day matters more than one that takes 5 seconds once a week.

Slow-query logging

The log_min_duration_statement setting logs every statement that runs longer than the time you give it. It is off by default. Setting it to, for example, one second shows the slow queries along with the time they ran, so you can match them to what users were doing.

What to do with a slow query

• Run EXPLAIN ANALYZE on it, on a staging copy, to see whether it scans a whole table.
• Add an index where a custom module filters or sorts on a field that has none. In Odoo you do this in the module, with index=True on the field, so it survives updates.
• Fix the code. Many slow queries come from custom modules that read records one at a time in a loop, or from computed fields that are stored and recomputed too often.

Test index changes on a copy of production first. Our article on Odoo staging environments explains how to make one safely.

Autovacuum and bloat in Odoo databases

When Odoo updates or deletes a row, PostgreSQL keeps the old version until a vacuum cleans it up. Autovacuum does this in the background. On busy tables, such as mail messages, stock moves and queue tables, it can fall behind. Tables then grow larger than their data (bloat), and queries slow down.

• Leave autovacuum on. Turning it off is one of the most harmful changes you can make.
• Make it start sooner on big tables. By default it waits until a fixed share of a table has changed, which on a table with millions of rows means a lot of dead rows. Lowering autovacuum_vacuum_scale_factor, for the whole server or for specific large tables, makes it run earlier and more often.
• Keep statistics fresh. The planner relies on statistics that autovacuum's analyze step updates. After a large import, run ANALYZE yourself.

How to change PostgreSQL settings safely

1. Take a backup, or at least note the current values.
2. Change one setting, or one related group, at a time.
3. Check whether the setting needs a reload or a full restart. Settings such as shared_buffers and max_connections only change on restart, which briefly interrupts every Odoo instance on the server.
4. Make sure PostgreSQL starts again, then watch response times and memory.
5. Write down what you changed and why.

The full list of parameters, with what each one does, is in the PostgreSQL server configuration documentation.

PostgreSQL tuning in CloudPilot

On servers in your own cloud account or connected over SSH, CloudPilot shows the PostgreSQL settings on the server page:

• One-click optimize. CloudPilot sizes memory and parallel workers to the server, applies SSD-friendly values for random_page_cost and effective_io_concurrency, sets checkpoint and WAL values, and turns on pg_stat_statements for query statistics.
• Edit individual parameters, including connection limits, autovacuum, timeouts and log_min_duration_statement. Parameters that need a restart are marked.
• A safety net. If PostgreSQL fails to start after a change, CloudPilot restores the previous settings and starts it again.

Running your own servers is part of the Pro and Business plans; on Starter, CloudPilot runs the server and its database for you. See pricing for each plan, and managed Odoo hosting for what CloudPilot looks after on every plan.

PostgreSQL tuning for Odoo: questions