The Database Bloat Problem
Your D2C brand's Odoo system was flying fast 6 months ago. Now, creating a Sales Order takes 4 seconds. Searching for a customer takes 2 seconds. You haven't changed the code. You haven't added new modules.
The Diagnosis: Your database is bloated.
PostgreSQL uses a system called MVCC (Multi-Version Concurrency Control). When you "update" a record (e.g., change Order status from Draft to Sale), Postgres doesn't overwrite the old data. It marks the old row as "dead" and creates a new row.
Over time, your tables become filled with millions of "dead rows." Odoo has to scan through this graveyard to find the live data.
VACUUM (clean up the dead rows) and ANALYZE (update the map so Postgres knows where the data is).
We've implemented 150+ Odoo systems. Neglecting database maintenance is the #1 reason for slow performance in mature systems. This guide covers how to automate these tasks so your system stays fast forever.
What is Dead Tuple Bloat?
Imagine a book.
Update: You rip out page 5 and glue a new page 5 at the end of the book.
Delete: You cross out page 10 with a black marker.
After 1,000 updates: Your 100-page book is now 1,100 pages long, but only 100 pages are readable. Reading it takes 11x longer. This is Bloat.
VACUUM: The librarian goes through the book, removes the ripped/crossed-out pages, and compacts the book back to 100 pages.
Understanding the Query Planner (ANALYZE)
When you run self.env['sale.order'].search(...), Postgres has to decide:
"Should I scan the whole table?"
"Should I use the Index?"
It makes this decision based on Statistics. It guesses how many rows match your query.
If the statistics are old (ANALYZE hasn't run), Postgres might guess wrong. It might scan a 10-million-row table sequentially instead of using the index.
10ms → 10 seconds
Query runs 1000x slower without proper statistics
Configuring Autovacuum (The "Set and Forget" Method)
PostgreSQL has a daemon called Autovacuum. It runs in the background. It is enabled by default, but the default settings are often too conservative for a high-traffic Odoo database.
Edit postgresql.conf
# Enable it
autovacuum = on
# Aggressiveness
# Run when 10% of rows have changed (Default is 20%)
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05
# Resources
# Allow it to use more memory so it finishes faster
autovacuum_work_mem = 64MB
# Don't let it kill your CPU
autovacuum_vacuum_cost_limit = 200
autovacuum_vacuum_cost_delay = 20msWhy Change This?
For a table with 1,000,000 rows, the default settings wait for 200,000 updates before cleaning up.
That creates huge spikes of lag. Lowering the scale factor makes it run more often but for shorter durations (micro-cleaning).
Manual Maintenance: VACUUM FULL (The Nuclear Option)
Sometimes, Autovacuum isn't enough. If you deleted 50% of your data (e.g., archived old logs), standard VACUUM marks space as "reusable" but doesn't shrink the file on disk.
To shrink the file size, you need VACUUM FULL.
Warning: This command LOCKS THE TABLE. Users cannot read or write to Odoo while it runs. It can take hours.
When to Use
During scheduled downtime (e.g., Sunday 2 AM)
After a massive data cleanup/migration
Command
VACUUM FULL VERBOSE;Reindexing (Fixing Corrupt/Bloated Indexes)
Indexes get bloated too. Sometimes they become larger than the table itself.
Command
REINDEX DATABASE my_odoo_db;Note: In Postgres 12+, use REINDEX CONCURRENTLY to avoid locking the production system.
Monitoring Bloat
You can't fix what you can't see. Use this query to find the "fattest" tables in your Odoo database.
-- Find Top 10 Bloated Tables
SELECT
schemaname,
relname,
n_dead_tup,
n_live_tup,
round(n_dead_tup::numeric / (n_live_tup+1)::numeric, 2) AS ratio,
last_vacuum,
last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;What to Look For
| Indicator | What It Means |
|---|---|
| High n_dead_tup | Thousands of dead rows |
| High ratio (> 0.2) | 20%+ dead rows, autovacuum falling behind |
| Old last_autovacuum | Hasn't run in weeks, configuration broken |
Common Offenders in Odoo
ir_attachment - Files
mail_message - Chatter history
stock_move - Inventory logs
bus_bus - Longpolling notifications (highly volatile!)
Your Action Items
Run the Health Check
❏ Connect to your database (psql)
❏ Run the "Bloat Query" above
❏ Identify the top 3 tables
Tune Config
❏ Edit postgresql.conf. Lower the scale_factor to 0.1
❏ Reload Postgres (sudo service postgresql reload)
Schedule Deep Cleaning
❏ Plan a 1-hour maintenance window this weekend
❏ Run VACUUM FULL ANALYZE (Warning: Downtime!)
❏ Run REINDEX DATABASE CONCURRENTLY
Free Database Health Check
Is your database 50GB+? Are you afraid to run commands in production? We'll analyze your table bloat ratios, tune your autovacuum settings for your specific server RAM, safely perform a VACUUM FULL to reclaim disk space, and setup monitoring alerts for future bloat. A clean database is a fast database.