Database Optimization for Odoo

Introduction#

Database teams sometimes optimize PostgreSQL without Odoo context — raising shared_buffers while a custom module runs sequential scans on sale_order_line filtered by an unindexed Char field. The fix is application-aware indexing and ORM fixes together.

This article covers PostgreSQL practices tied to Odoo models and operations. Use ORM best practices to reduce query count; use this article to make the queries that remain cheap at scale.

Indexes that match Odoo domains#

Identify top slow queries from pg_stat_statements and map them to Odoo list views, server actions, and connector searches. Partial indexes help when domains always include state= or company_id= filters.

Custom fields used in search panels and automation domains need explicit index planning — especially Selection and Many2one filters on transaction tables. Coordinate with functional owners before adding indexes on write-heavy columns during peak hours.

• ▸Index foreign keys on line tables touched by picking and invoicing
• ▸Consider pg_trgm for name search on large product and partner tables
• ▸Validate index use with EXPLAIN on staging, not production blindly
• ▸Remove unused indexes that slow writes on hot insert paths

Vacuum, bloat, and statistics#

High-churn Odoo tables — mail_message, stock_move, account_move_line — accumulate bloat when autovacuum cannot keep pace. Monitor dead tuple ratios and adjust autovacuum settings for large tables rather than manual vacuum-only firefighting.

Run ANALYZE after large data imports or module updates that add columns used in new reports. Bad row estimates cause nested loop disasters on joins Odoo generates for related fields.

Connections, locks, and reporting#

Each Odoo worker holds database connections. Pool sizing must match worker count plus cron and queue workers — excess connections waste memory and increase lock contention.

Reports that open transactions while scanning millions of rows block confirm operations on related records. Prefer read-only reporting replicas, materialized views maintained by scheduled jobs, or export-to-queue patterns for month-end packs.

Post-migration and growth validation#

Odoo version upgrades can change default indexes, ORM SQL, and report queries. Re-run slow query baselines after Migration cutover on the same workflows measured before upgrade.

Data growth without archival strategy eventually dominates query cost. Plan legal retention, message pruning, and attachment storage outside the database when volumes demand it.

Practical recommendations#

• ✓Maintain a living list of top ten slow queries mapped to Odoo modules.
• ✓Index custom fields before they appear in automation and list filters.
• ✓Schedule vacuum and analyze review after bulk imports and upgrades.
• ✓Cap report concurrency during warehouse peak if locks appear on stock tables.
• ✓Use connection pooler settings aligned to actual worker configuration.
• ✓Re-benchmark critical workflows after every major Migration release.

Common mistakes#

• !Creating indexes on every custom Char field without query evidence.
• !Running REINDEX or full vacuum during operational hours unannounced.
• !Pointing BI tools at primary PostgreSQL with unconstrained ad hoc SQL.
• !Ignoring autovacuum warnings until sequential scans dominate.
• !Assuming Odoo standard indexes cover all custom domain patterns.
• !Increasing max_connections instead of fixing worker and pool mismatch.

Conclusion#

PostgreSQL optimization for Odoo is indexing and maintenance aligned to ORM access — plus disciplined reporting that does not hold locks on operational tables.

Next, review caching and worker architecture when the database is healthy but response time still misses targets.