Maintenance management database performance drops significantly after 50k records

ERP Odoo
, , , , , , , ,
1

We’re experiencing severe performance degradation in our maintenance management module after crossing 50,000 maintenance request records. Equipment search queries that used to complete in under 2 seconds now take 15-20 seconds. The preventive maintenance scheduler runs daily and has gone from 5 minutes to over 45 minutes execution time.

Our PostgreSQL database is on version 12.8, running on a dedicated server with 32GB RAM and SSD storage. We’ve noticed that queries involving the maintenance.request and maintenance.equipment tables are particularly slow. The pg_stat_statements extension shows these queries consuming 70% of our database time.

We have standard indexes on foreign keys, but I’m wondering if we’re missing critical indexes or if there are Odoo-specific optimizations we should implement. Our maintenance teams are frustrated with the system responsiveness, and management is questioning our Odoo implementation. Has anyone dealt with similar scaling issues in maintenance management?

2

Here’s a comprehensive solution that addresses all the performance issues:

1. Create Composite Indexes Connect to PostgreSQL and create these indexes targeting your query patterns:

CREATE INDEX idx_maint_req_equip_stage_date
ON maintenance_request(equipment_id, stage_id, schedule_date)
WHERE active = true;

CREATE INDEX idx_maint_req_category_team
ON maintenance_request(category_id, maintenance_team_id, request_date)
WHERE active = true;

CREATE INDEX idx_equipment_category_location
ON maintenance_equipment(category_id, location, active)
WHERE active = true;

The partial indexes with WHERE active = true are crucial - they only index active records, making them much smaller and faster.

2. Optimize PostgreSQL Configuration Edit postgresql.conf with these settings for your 32GB RAM server:


shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 64MB
maintenance_work_mem = 2GB
random_page_cost = 1.1
effective_io_concurrency = 200

3. Implement Archival Strategy Create a scheduled action in Odoo to archive closed maintenance requests older than 18 months:

from datetime import datetime, timedelta
from odoo import api, models

class MaintenanceArchive(models.TransientModel):
    _name = 'maintenance.archive.wizard'

    def archive_old_requests(self):
        cutoff_date = datetime.now() - timedelta(days=540)
        old_requests = self.env['maintenance.request'].search([
            ('stage_id.done', '=', True),
            ('close_date', '<', cutoff_date),
            ('active', '=', True)
        ])
        old_requests.write({'active': False})
        return len(old_requests)

4. Vacuum and Analyze Run immediate maintenance:

VACUUM ANALYZE maintenance_request;
VACUUM ANALYZE maintenance_equipment;
REINDEX TABLE maintenance_request;

5. Monitor Query Performance Enable pg_stat_statements and create a monitoring view:

CREATE VIEW slow_maintenance_queries AS
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
WHERE query LIKE '%maintenance%'
ORDER BY mean_exec_time DESC
LIMIT 20;

After implementing these changes, you should see:

  • Equipment searches: 15-20s → under 1s
  • Preventive maintenance scheduler: 45min → 3-5min
  • Dashboard kanban loads: significantly faster

The combination of proper indexing, archival, and PostgreSQL tuning will handle growth to 200k+ records. Monitor the slow_maintenance_queries view weekly to catch new performance issues early.

3

One more thing - check if you have proper indexes on the stage_id and user_id fields in maintenance.request. These are frequently used in dashboard queries and kanban views. Without indexes, every kanban refresh triggers a sequential scan.

4

I’ve seen this exact scenario multiple times. The issue is usually missing composite indexes on frequently queried field combinations. In maintenance management, you’re likely filtering by equipment_id, stage_id, and schedule_date together. Odoo’s ORM generates queries that need these composite indexes to perform well at scale.

Run EXPLAIN ANALYZE on your slowest queries to identify sequential scans. You’ll probably find that searches combining equipment category, maintenance type, and date ranges are doing full table scans. Create composite indexes targeting these specific query patterns. Also check if you have indexes on computed fields - Odoo sometimes creates stored computed fields that need explicit indexing.