Fix Odoo Database Size Growing Fast: Identify & Clean Bloat

Database Growing Too Fast

Odoo databases can grow unexpectedly large, consuming disk space and degrading performance. Common causes include attachment bloat, excessive logging, orphaned records, and PostgreSQL table bloat. Here is how to diagnose and fix rapid growth.

Identify Large Tables

# Find largest tables in Odoo database:
SELECT
  schemaname || '.' || tablename AS table_name,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename) -
    pg_relation_size(schemaname || '.' || tablename)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

# Common largest tables:
# ir_attachment — file metadata and binary data
# mail_message — chatter messages
# mail_tracking_value — field change history
# ir_logging — server logs
# bus_bus — live notifications
# fetchmail_log — email fetch logs

Attachment Bloat

# ir_attachment is usually the largest table

# Check attachment size:
SELECT
  res_model,
  COUNT(*) as count,
  pg_size_pretty(SUM(file_size)) as total_size
FROM ir_attachment
WHERE res_model IS NOT NULL
GROUP BY res_model
ORDER BY SUM(file_size) DESC NULLS LAST
LIMIT 20;

# Common bloat sources:
# - Asset bundles (regenerated on every update)
# - Email attachments (stored for every message)
# - Report PDFs (cached in attachments)
# - Image thumbnails (multiple sizes per image)

# Clean old asset bundles:
DELETE FROM ir_attachment
WHERE name LIKE '/web/assets/%'
  AND create_date < NOW() - INTERVAL '7 days';

# Clean orphaned attachments (no parent record):
# Use Odoo's built-in cleanup:
# Settings → Technical → Database → Cleanup Unlinked

Mail/Chatter Bloat

# mail_message grows with every log note, email,
# and tracking change

# Check mail_message size:
SELECT
  message_type,
  COUNT(*) as count,
  pg_size_pretty(pg_total_relation_size('mail_message')) as table_size
FROM mail_message
GROUP BY message_type;

# Clean old notification messages:
DELETE FROM mail_message
WHERE message_type = 'notification'
  AND date < NOW() - INTERVAL '180 days';

# mail_tracking_value — field change history
# Each field change creates a tracking record
SELECT COUNT(*) FROM mail_tracking_value;

# If very large, consider reducing tracked fields
# Remove track_visibility from less important fields

Log Tables

# ir_logging stores server logs in database
# Can grow extremely fast in production

# Check size:
SELECT COUNT(*),
  pg_size_pretty(pg_total_relation_size('ir_logging'))
FROM ir_logging;

# Clean old logs:
DELETE FROM ir_logging
WHERE create_date < NOW() - INTERVAL '30 days';

# bus_bus — live chat / notification bus
# Self-cleaning but can accumulate
DELETE FROM bus_bus
WHERE create_date < NOW() - INTERVAL '3 days';

# Disable database logging if using file logs:
# odoo.conf: log_db = False

Filestore Cleanup

# Odoo stores files in filestore directory:
# /var/lib/odoo/filestore/[database_name]/

# Check filestore size:
du -sh /var/lib/odoo/filestore/*/

# Find large files:
find /var/lib/odoo/filestore/mydb/ -size +10M -exec ls -lh {} \;

# Clean orphaned filestore files:
# Files in filestore not referenced by ir_attachment
# Odoo provides cleanup via:
# Settings → Technical → Database → Cleanup Filestore

# Or manual (careful!):
# 1. Get all store_fname from ir_attachment
# 2. Compare with files on disk
# 3. Delete files not in ir_attachment

PostgreSQL Bloat

# PostgreSQL tables bloat from UPDATE/DELETE
# Dead tuples accumulate until VACUUM runs

# Check bloat:
SELECT
  relname,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup::numeric / greatest(n_live_tup, 1) * 100, 1) as dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

# Run VACUUM on bloated tables:
VACUUM ANALYZE ir_attachment;
VACUUM ANALYZE mail_message;

# Full vacuum (recovers disk space, locks table!):
VACUUM FULL ir_attachment;
# Only during maintenance window — locks table

# Configure autovacuum for aggressive cleanup:
# postgresql.conf:
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02

DeployMonkey

DeployMonkey monitors database size and automatically runs cleanup routines. Get alerts before disk space becomes critical.