PostgreSQL Performance Optimization for Odoo: Tuning, Indexing, and Monitoring

A properly tuned PostgreSQL instance can improve Odoo response times by 2-5x compared to default settings. Most Odoo performance problems trace back to database configuration -- default PostgreSQL settings are designed for minimal resource usage, not for powering a multi-user ERP system.

Key Takeaways

• Default PostgreSQL settings use only 128MB shared buffers -- production Odoo needs 25% of RAM
• Missing indexes on frequently queried columns cause full table scans and slow page loads
• Connection pooling with PgBouncer reduces database connection overhead by 80%
• Regular VACUUM and ANALYZE prevent table bloat and keep query plans optimal

---

PostgreSQL Configuration Tuning

Memory Settings

Edit postgresql.conf with settings appropriate for your hardware. For a server with 16GB RAM, set shared_buffers to 4GB (25% of total RAM), effective_cache_size to 12GB (75% of total RAM), work_mem to 64MB per operation, maintenance_work_mem to 1GB, and wal_buffers to 64MB.

For query planning, set random_page_cost to 1.1 for SSD storage (default 4.0 assumes HDD), effective_io_concurrency to 200 for SSDs, and default_statistics_target to 200 for more accurate query plans.

Sizing guidelines:

| Server RAM | shared_buffers | effective_cache_size | work_mem | |-----------|---------------|---------------------|----------| | 4GB | 1GB | 3GB | 16MB | | 8GB | 2GB | 6GB | 32MB | | 16GB | 4GB | 12GB | 64MB | | 32GB | 8GB | 24GB | 128MB | | 64GB | 16GB | 48GB | 256MB |

Connection Settings

Set max_connections to at least Odoo workers x 2 plus buffer. With 4 workers and 2 cron threads, you need at least 12 connections. Add connections for admin tools, monitoring, and background tasks. A value of 200 provides comfortable headroom.

---

Indexing Strategies for Odoo

Identifying Missing Indexes

Enable slow query logging by setting log_min_duration_statement to 500ms. Then analyze the slow query log to identify full table scans.

Common Odoo Indexes

Odoo creates indexes on primary keys and foreign keys automatically. Add indexes on frequently filtered columns like sale_order(state), account_move(state), stock_move(state), account_move(date), and sale_order(date_order).

Multi-column indexes improve common filter combinations: account_move_line(account_id, date) and stock_quant(product_id, location_id).

For tables with status columns, partial indexes on active records are more efficient -- index only rows where state is not cancelled or done.

---

Query Analysis with EXPLAIN ANALYZE

Run EXPLAIN (ANALYZE, BUFFERS) on slow queries to understand execution plans. Look for:

• Seq Scan: Full table scan indicating a missing index
• Nested Loop: Can be expensive with large result sets
• Sort: In-memory sorts exceeding work_mem spill to disk
• Buffers shared read: High values mean data is not cached

Common performance killers:

1. Missing indexes on WHERE clause columns
2. Large IN clauses generated by Odoo ORM
3. Stored computed fields triggering recalculation on writes
4. Complex report queries joining 5+ tables

---

VACUUM and Maintenance

PostgreSQL MVCC creates dead tuples when rows are updated or deleted. VACUUM reclaims this space and updates statistics.

Configure autovacuum for Odoo workloads: enable autovacuum with 3 max workers, 60-second naptime, vacuum scale factor of 0.05, and analyze scale factor of 0.02. For high-write tables (account_move_line, stock_move, mail_message), set more aggressive per-table settings.

Monitor table bloat by checking total relation sizes and dead tuple counts. Use VACUUM FULL only for extreme bloat (over 50% dead space) and only during maintenance windows since it locks the table.

---

Connection Pooling with PgBouncer

PgBouncer sits between Odoo and PostgreSQL, pooling connections to reduce overhead. Use transaction pool mode for Odoo, which releases connections between transactions. Set default_pool_size to 40 and max_client_conn to 200. Point Odoo to the PgBouncer port instead of PostgreSQL directly.

---

Monitoring

Essential Metrics

• Active connections: Should stay well below max_connections
• Cache hit ratio: Should be above 99%
• Transaction rate: Baseline and watch for anomalies
• Slow query count: Queries exceeding your threshold
• Replication lag: If using read replicas
• Disk usage: Database size growth rate
• Table bloat: Dead tuple ratio per table

Use pg_stat_statements extension to track query performance over time. It records execution count, total time, mean time, and rows returned for every distinct query pattern.

---

Frequently Asked Questions

Q: How do I know if PostgreSQL is the bottleneck?

Enable slow query logging and check Odoo performance logs. If most slow requests correspond to slow queries, tuning will help. If queries are fast but Odoo is slow, the bottleneck is in application code or network.

Q: Should I use PostgreSQL replicas for Odoo?

Read replicas offload reporting queries from the primary database. Odoo does not natively support read/write splitting, so custom configuration routes read-only queries to replicas. This is mainly useful for very large deployments.

Q: What PostgreSQL version should I use with Odoo?

Use the latest stable release supported by your Odoo version. Newer versions include query optimizer improvements and better vacuum performance. PostgreSQL 16 or 17 are recommended for current Odoo versions.

Q: How much does proper tuning actually help?

In our experience, moving from default settings to tuned configuration typically improves average page load times by 40-60% and reduces slow query frequency by 80-90%. The improvement is dramatic and immediate.

---

What Is Next

PostgreSQL tuning is the single highest-impact optimization for Odoo performance. Start with memory settings and indexing -- these two changes alone often cut response times in half.

Contact ECOSIRE for database optimization help, or explore our Odoo support services for ongoing performance management.

---

Published by ECOSIRE -- helping businesses scale with enterprise software solutions.