How to Optimize PostgreSQL for Odoo

The most impactful PostgreSQL optimizations for Odoo are setting shared_buffers to 25% of RAM, increasing work_mem to at least 16 MB, and adding targeted indexes on the ir_attachment, mail_message, and stock_move tables — the most frequently scanned large tables in a typical Odoo install. These changes alone can reduce average query time by 50% or more on a default PostgreSQL configuration.

Why PostgreSQL Tuning Matters for Odoo

Odoo is an ORM-heavy application that generates a high volume of SQL queries for every user action. PostgreSQL's default configuration is intentionally conservative — designed to work on any hardware — which means it leaves significant performance on the table on a dedicated server.

The PostgreSQL defaults that cause the most pain for Odoo:

• shared_buffers = 128MB — far too small for any real workload; means frequent disk reads
• work_mem = 4MB — causes disk spills for sorting and hashing operations on larger Odoo tables
• effective_cache_size = 4GB — correct but often left as default even on servers with less RAM
• No autovacuum tuning — Odoo's write-heavy patterns cause table bloat without aggressive autovacuum

Key postgresql.conf Settings for Odoo

# /etc/postgresql/15/main/postgresql.conf
# (adjust path for your PostgreSQL version)

# ---- Memory ----
# shared_buffers: PostgreSQL's internal cache — set to 25% of total RAM
shared_buffers = 1GB             # for a 4 GB server; use 2GB for 8 GB, 4GB for 16 GB

# effective_cache_size: hint to query planner about total OS cache available
# Set to 75% of total RAM (OS + PostgreSQL cache combined)
effective_cache_size = 3GB       # for a 4 GB server

# work_mem: memory per sort/hash operation — can be used by multiple operations per query
# Start at 16–64 MB; too high risks OOM with many concurrent sessions
work_mem = 32MB

# maintenance_work_mem: used by VACUUM, CREATE INDEX, ANALYZE
# Safe to set higher since only a few operations use it at once
maintenance_work_mem = 256MB

# ---- Write performance ----
# checkpoint_completion_target: spread checkpoint writes to reduce I/O spikes
checkpoint_completion_target = 0.9

# wal_buffers: WAL write buffer — 16MB is sufficient for most workloads
wal_buffers = 16MB

# ---- Query planner ----
# random_page_cost: lower value for SSD storage (default 4.0 is for spinning disks)
random_page_cost = 1.1

# effective_io_concurrency: number of concurrent I/O operations the disk can handle
# Set to 200 for SSD; leave at 2 for HDD
effective_io_concurrency = 200

# ---- Connections ----
# max_connections: set just above your expected peak connections
# Odoo workers each maintain up to db_maxconn connections
max_connections = 200

# Apply changes (requires restart)
sudo systemctl restart postgresql

# Or for non-memory settings, just reload
sudo systemctl reload postgresql

Connection Pooling with PgBouncer

Odoo workers each open a pool of PostgreSQL connections. With many workers and multiple Odoo instances, you can easily hit PostgreSQL's max_connections limit. PgBouncer is a lightweight connection pooler that sits between Odoo and PostgreSQL.

# /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type    = md5
auth_file    = /etc/pgbouncer/userlist.txt

# Transaction pooling is safe for Odoo (do NOT use session pooling with Odoo)
pool_mode       = transaction
max_client_conn = 1000
default_pool_size = 20

Point Odoo at PgBouncer by changing db_port = 6432 in odoo.conf. Use transaction pooling mode — Odoo is compatible with it and it gives the best connection reuse.

VACUUM and Table Bloat

Odoo's write patterns (frequent updates to mail_message, stock_move, account_move_line) create dead tuples that need vacuuming. The default autovacuum settings are too conservative for Odoo's update rate.

-- Check table bloat — identify tables needing manual VACUUM
SELECT schemaname, relname,
   n_dead_tup,
   n_live_tup,
   round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
   last_autovacuum,
   last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Manual VACUUM ANALYZE on specific heavy tables
VACUUM ANALYZE mail_message;
VACUUM ANALYZE ir_attachment;
VACUUM ANALYZE stock_move;
VACUUM ANALYZE account_move_line;

Odoo-Specific Index Recommendations

The Odoo ORM creates basic indexes on foreign keys and a few common fields, but there are several high-impact indexes it does not create by default.

-- ir_attachment: lookups by res_model + res_id are extremely common
CREATE INDEX IF NOT EXISTS ir_attachment_res_field_idx
ON ir_attachment (res_model, res_id)
WHERE res_model IS NOT NULL;

-- mail_message: inbox and chatter queries filter heavily on model + res_id
CREATE INDEX IF NOT EXISTS mail_message_res_idx
ON mail_message (model, res_id)
WHERE model IS NOT NULL;

-- stock_move: warehouse reports filter by state + date
CREATE INDEX IF NOT EXISTS stock_move_state_date_idx
ON stock_move (state, date)
WHERE state NOT IN ('cancel', 'draft');

-- sale_order_line: order lines are frequently joined with product_id
CREATE INDEX IF NOT EXISTS sale_order_line_product_idx
ON sale_order_line (product_id, order_id);

-- res_partner: name/email search in many2one fields
CREATE INDEX IF NOT EXISTS res_partner_name_lower_idx
ON res_partner (lower(name));

CREATE INDEX IF NOT EXISTS res_partner_email_lower_idx
ON res_partner (lower(email))
WHERE email IS NOT NULL;

Monitoring PostgreSQL Health

-- Active queries right now
SELECT pid, now() - query_start AS duration, query, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- Cache hit ratio (should be > 99%)
SELECT sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio
FROM pg_statio_user_tables;

-- Index usage — find unused indexes (candidates for removal)
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 20;

-- Database size
SELECT pg_size_pretty(pg_database_size('mydb'));

How DeployMonkey Handles PostgreSQL Tuning

Every DeployMonkey-managed Odoo instance deploys with a PostgreSQL configuration tuned for the server's RAM and storage type. shared_buffers, effective_cache_size, work_mem, and random_page_cost are all set correctly at provisioning time — none of the PostgreSQL defaults are used.

The platform runs a nightly VACUUM ANALYZE on all Odoo databases to keep table bloat under control. Table statistics are also refreshed automatically after large data imports or bulk operations, which prevents the query planner from choosing bad execution plans in the hours following a migration.

Pro and Agency plan customers have access to the query advisor in the DeployMonkey dashboard, which surfaces the top-10 slow queries for each instance, ranked by cumulative execution time, along with index suggestions specific to the tables and Odoo modules in use.

Related Articles

• Odoo Slow? How to Diagnose and Fix Performance Issues
• How to Configure Odoo Workers for Best Performance
• How to Fix Odoo Out of Memory Error
• Odoo Server Requirements

Frequently Asked Questions