After 12+ years of managing production Odoo deployments, we've found the same pattern over and over: a slow Odoo instance is almost always a poorly-tuned PostgreSQL instance. The default PostgreSQL configuration is designed to run on a system with 128MB of RAM. Your production server deserves better.
Why Default PostgreSQL Settings Are Terrible for Odoo
PostgreSQL ships with conservative defaults that prioritize compatibility over performance. For Odoo, a few things make this especially painful:
- Odoo's ORM generates complex, multi-join queries that need ample work_mem
- The ir.attachment and ir.translation tables grow large and need proper caching
- Odoo opens many database connections — one per worker, plus cron and longpolling
The Essential PostgreSQL Settings for Odoo
These are the settings that matter most, based on an 8GB RAM server (adjust proportionally for your setup):
shared_buffers25% of RAM(e.g., 2GB for 8GB server)Main memory cache for PostgreSQL. Odoo reads heavily from the database, and setting this to 25% of total RAM gives the best balance between PostgreSQL and OS caching.
effective_cache_size75% of RAM(e.g., 6GB for 8GB server)Tells the query planner how much combined PostgreSQL + OS cache to expect. Higher values push the planner toward index scans instead of sequential scans for Odoo's multi-join queries.
work_mem64MB-256MB(e.g., 64MB for <20 users, 256MB for 50+ users)Per-operation memory for sorts and joins. Odoo's ORM generates multi-join queries that need plenty of work_mem. Scale this with your user count.
maintenance_work_mem512MB(e.g., 512MB)Speeds up VACUUM, CREATE INDEX, and ALTER TABLE. Odoo tables like mail_message grow fast and benefit from faster maintenance.
random_page_cost1.1 (SSD)(e.g., 1.1)Default 4.0 is for HDDs. All modern servers use SSDs. Setting this to 1.1 tells PostgreSQL that random reads are nearly as fast as sequential.
effective_io_concurrency200 (SSD)(e.g., 200)Allows PostgreSQL to issue multiple I/O requests in parallel on NVMe/SSD storage.
max_connections(workers x 2) + 20(e.g., 150 for 6 Odoo workers)Each Odoo worker uses ~2 connections. Add 20 for cron, longpolling, and admin. Too many wastes RAM (each connection costs ~5-10MB).
Example postgresql.conf for 8GB Server
# =========================================== # postgresql.conf — Optimized for Odoo # Server: 8 GB RAM, SSD/NVMe, 6 Odoo workers # =========================================== # Memory shared_buffers = 2GB # 25% of RAM effective_cache_size = 6GB # 75% of RAM work_mem = 64MB # 64MB-256MB depending on users maintenance_work_mem = 512MB # Faster VACUUM & CREATE INDEX # Disk I/O (SSD/NVMe) random_page_cost = 1.1 # Default 4.0 is for HDDs effective_io_concurrency = 200 # Parallel I/O on SSD # Connections max_connections = 150 # (6 workers x 2) + cron + admin # Write-Ahead Log wal_buffers = 64MB checkpoint_completion_target = 0.9 max_wal_size = 2GB # Query Planner default_statistics_target = 100 # Logging (for debugging slow queries) log_min_duration_statement = 1000 # Log queries > 1s log_lock_waits = on # Log lock waits > 1s
Scaling: postgresql.conf for 32 GB Server
Running a larger instance with 50+ concurrent users? Here are the settings for a 32 GB server with 12 Odoo workers:
# =========================================== # postgresql.conf — Large Odoo Instance # Server: 32 GB RAM, NVMe, 12 Odoo workers # =========================================== # Memory shared_buffers = 8GB # 25% of 32GB effective_cache_size = 24GB # 75% of 32GB work_mem = 256MB # More users = more concurrent sorts maintenance_work_mem = 1GB # Large tables need fast VACUUM # Disk I/O (NVMe) random_page_cost = 1.1 effective_io_concurrency = 200 # Connections max_connections = 250 # (12 workers x 2) + cron + admin + buffer # Write-Ahead Log wal_buffers = 128MB checkpoint_completion_target = 0.9 max_wal_size = 4GB # Query Planner default_statistics_target = 200 # More accurate plans for large tables # Logging log_min_duration_statement = 500 # Tighter threshold for large deployments log_lock_waits = on
Indexing Strategies for Odoo
Odoo creates indexes automatically for most fields, but custom indexes can still help in specific cases:
Partial indexes on active records
Many Odoo tables have an 'active' field. Indexing only active=true rows shrinks the index and speeds up common queries.
CREATE INDEX idx_partner_active ON res_partner (name) WHERE active = true;Composite indexes for common filters
If you frequently filter by company_id + state (e.g., in sale.order), a composite index avoids sequential scans.
CREATE INDEX idx_sale_order_company_state ON sale_order (company_id, state);GIN indexes for JSONB fields
If you use custom JSONB fields, GIN indexes make containment queries fast.
CREATE INDEX idx_custom_jsonb ON your_table USING gin (jsonb_field);Monitoring: Know Before It Breaks
Tuning without monitoring is guessing. These are the metrics worth watching:
- Cache hit ratio: should be > 99%. Check heap_blks_hit vs heap_blks_read in pg_statio_user_tables.
- Active connections: if consistently near max_connections, increase it or add PgBouncer.
- Long-running queries: anything over 5 seconds needs investigation via pg_stat_activity.
- Table bloat: large, frequently-updated tables (like mail_message) need regular VACUUM ANALYZE.
- Index usage: unused indexes waste disk and slow writes. Check pg_stat_user_indexes.
Before & After: Real Benchmark Data
These are actual measurements from a production Odoo 18 instance (8 GB RAM, 4 vCPUs, NVMe SSD, 35 concurrent users) after applying the settings above:
Before Tuning (defaults)
- Avg query time: 450ms
- Page load (list views): 2.8s
- Cache hit ratio: 87%
- Slow queries (>1s): ~40/hour
- Concurrent users before lag: ~15
After Tuning
- Avg query time: 120ms (73% faster)
- Page load (list views): 1.1s (61% faster)
- Cache hit ratio: 99.2%
- Slow queries (>1s): ~3/hour
- Concurrent users before lag: ~50+
The biggest single improvement came from shared_buffers and random_page_cost. Together, they moved the cache hit ratio from 87% to over 99% and pushed the query planner toward index scans instead of sequential scans.
Let OEC.sh Handle the Tuning
Every OEC.sh deployment ships with PostgreSQL settings tuned for your server's resources and workload. No manual configuration needed. We apply these settings automatically and monitor performance on your behalf.
Related Resources
Server Size Calculator
Find the right RAM, CPU, and workers for your Odoo workload.
Best VPS for Odoo
Compare Hetzner, DigitalOcean, Vultr, and AWS for Odoo hosting.
Best Cloud for Odoo
Cloud provider benchmarks and pricing breakdowns for Odoo.
Odoo Hosting Cost Optimization
Cut your Odoo infrastructure costs without sacrificing performance.
Frequently Asked Questions
How do I tune PostgreSQL for Odoo?
Start with four key settings: set shared_buffers to 25% of your server RAM, effective_cache_size to 75%, work_mem between 64MB and 256MB depending on your user count, and random_page_cost to 1.1 if you use SSDs. Then set max_connections to match your Odoo workers (workers x 2 + 20). Monitor your cache hit ratio and slow queries after applying changes, and adjust from there.
What is the best shared_buffers for Odoo?
Set shared_buffers to 25% of your total server RAM. For an 8 GB server, that means 2 GB. For a 32 GB server, 8 GB. Going higher than 25% rarely helps because PostgreSQL also relies on the OS page cache. Odoo's ORM reads heavily from the database, so this setting has the single biggest impact on query performance.
How many PostgreSQL connections does Odoo need?
Each Odoo HTTP worker opens about 2 PostgreSQL connections. Add connections for cron workers (2-4), longpolling (1-2), and admin overhead (~10). The formula is: (HTTP workers x 2) + (cron workers x 2) + 20 buffer. For a typical 6-worker setup, 150 max_connections is sufficient. Avoid setting this too high — each idle connection consumes 5-10 MB of RAM.
Does Odoo need a separate database server?
For small deployments (under 30 users), running PostgreSQL on the same server as Odoo works fine. Beyond 30-50 concurrent users, a dedicated database server improves performance significantly — PostgreSQL and Odoo compete for RAM and CPU on the same machine. With a separate server, you can tune PostgreSQL independently and allocate more shared_buffers without starving Odoo workers.
Auto-Tuned PostgreSQL, Out of the Box
OEC.sh configures PostgreSQL for your Odoo workload based on your server resources. No tuning required.
Start Free Trial