PostgreSQL tuning for Odoo: what actually moved p95

TL;DR

Most "Odoo is slow" tickets are not Odoo. They are a handful of indexable queries doing full scans on tables that grew faster than anyone updated the schema for. A focused two-day Postgres pass on a real ERP deployment gave a measurable 25% improvement at p95.

This is a short writeup of what worked, what didn't, and the order I'd do it in again.

Step 1: stop guessing, start measuring

Before touching anything, turn on pg_stat_statements and let it collect a representative sample (a couple of hours during the busy period is usually enough).

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

You are looking for two things:

1. Queries with high total_exec_time (cumulative pain)
2. Queries with high mean_exec_time but low calls (single slow reports)

The first usually drives p95. The second annoys executives more.

Step 2: index the hot foreign keys

Odoo creates a lot of foreign keys. It does not index all of them. Find the missing ones:

SELECT
  c.conrelid::regclass AS table_name,
  a.attname            AS column_name,
  c.confrelid::regclass AS references_table
FROM pg_constraint c
JOIN pg_attribute a
  ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND a.attnum = ANY(i.indkey)
  );

Index the ones used in filters (WHERE, JOIN) — not blindly all of them.

Step 3: partial indexes for stateful tables

account_move, stock_picking, crm_lead — these all have a small set of "active" states and a long tail of historic rows. A partial index on the active states is small, fast, and exactly what listings hit:

CREATE INDEX CONCURRENTLY idx_account_move_partner_active
  ON account_move (partner_id)
  WHERE state IN ('draft', 'posted');

This is where I saw the biggest single wins.

Step 4: kill the N+1 in custom reports

A surprising amount of slow report behavior comes from custom reports that look fine in Python but generate one query per row through ir.translation or related-field lookups.

Fix patterns:

• Read related fields via read_group or a single SQL query, not a loop
• Cache translation lookups outside the row loop
• For huge reports, materialize an aggregate table and refresh it

Step 5: tune the obvious server knobs once

Then leave them alone:

shared_buffers = 25% of RAM
effective_cache_size = 70% of RAM
work_mem = small, raise per-session for big reports
maintenance_work_mem = 1GB if you have it
random_page_cost = 1.1 on SSD

I have rarely seen these knobs be the bottleneck in Odoo before the index/query work above is done.

What didn't help (for me)

• Switching connection poolers in isolation
• Aggressive autovacuum tweaks without a measured bloat problem
• Reaching for read replicas before fixing the obvious indexes
• Caching at the Odoo layer when the Postgres layer was the slow one

The mindset

Postgres tuning for ERP is unsexy. It is mostly:

1. Measure
2. Index the hot foreign keys
3. Partial-index the stateful tables
4. Kill the N+1 reports
5. Then think about replicas / sharding / poolers

Do it in that order and you will get the boring 25% before you spend a month on something fancy.