PostgreSQL Performance Tuning for Odoo: The Complete Guide

Why PostgreSQL Tuning Matters for Odoo

Odoo is extremely database-heavy. Every page load, every button click, every report generation hits PostgreSQL. The default PostgreSQL settings are designed for a laptop, not a production server. Proper tuning can improve Odoo performance by 2-5x.

Essential postgresql.conf Settings

# Memory
shared_buffers = 4GB            # 25% of total RAM (16GB server)
effective_cache_size = 12GB     # 75% of total RAM
work_mem = 64MB                 # Per-operation sort/hash memory
maintenance_work_mem = 1GB      # For VACUUM, CREATE INDEX

# WAL (Write-Ahead Log)
wal_buffers = 64MB              # 1/32 of shared_buffers, max 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB              # Reduce checkpoint frequency

# Query Planner
random_page_cost = 1.1          # SSD storage (default 4.0 is for HDD)
effective_io_concurrency = 200  # SSD concurrent I/O

# Connections
max_connections = 100           # Match Odoo's db_maxconn × workers + buffer

# Logging
log_min_duration_statement = 1000  # Log queries slower than 1 second
log_checkpoints = on
log_lock_waits = on

Memory Sizing Guide

Autovacuum Tuning

Autovacuum is critical for Odoo. Odoo creates and updates millions of rows (ir.logging, mail.message, bus.bus). Without proper autovacuum, tables bloat and queries slow down.

# Autovacuum
autovacuum = on
autovacuum_max_workers = 4           # Default 3 is often too few
autovacuum_naptime = 30s             # Check every 30s (default 1min)
autovacuum_vacuum_threshold = 50     # Vacuum after 50 dead rows
autovacuum_vacuum_scale_factor = 0.05  # + 5% of table size (default 0.2)
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.05

Connection Pooling

Odoo opens a database connection per worker. With 5 workers and db_maxconn=64, that is 320 potential connections. Use PgBouncer for connection pooling in high-traffic deployments:

# pgbouncer.ini
[databases]
production = host=localhost dbname=production

[pgbouncer]
pool_mode = transaction     # Reuse connections between transactions
max_client_conn = 400       # Max client connections
default_pool_size = 50      # Actual PostgreSQL connections
reserve_pool_size = 10
reserve_pool_timeout = 3

Finding Slow Queries

# Enable pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

# Top 10 slowest queries
SELECT calls, mean_exec_time::numeric(10,2) as avg_ms,
       total_exec_time::numeric(10,2) as total_ms,
       left(query, 100) as query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

# Queries consuming the most total time
SELECT calls, total_exec_time::numeric(10,2) as total_ms,
       mean_exec_time::numeric(10,2) as avg_ms,
       left(query, 100)
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Indexing for Odoo

# Common missing indexes for Odoo
CREATE INDEX CONCURRENTLY idx_mail_message_date ON mail_message (date DESC);
CREATE INDEX CONCURRENTLY idx_mail_message_model_res ON mail_message (model, res_id);
CREATE INDEX CONCURRENTLY idx_ir_attachment_res ON ir_attachment (res_model, res_id);
CREATE INDEX CONCURRENTLY idx_stock_move_product ON stock_move (product_id, state);

# Check for unused indexes (bloat without benefit)
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

Table Bloat Check

# Check table bloat
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) as dead_pct,
       last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;

DeployMonkey

DeployMonkey auto-tunes PostgreSQL based on your server's RAM and CPU. The AI agent monitors slow queries, table bloat, and connection usage — alerting you before performance degrades.