The Time Tracking Performance Crisis
Your D2C brand manages a warehouse with 200 staff. Every scan, every pick, every pack is logged as a timesheet entry or an analytic line.
Or perhaps you are a Service company with 500 consultants logging hours daily.
Year 1: "Odoo is blazing fast!"
Year 3: You have 4 million rows in account.analytic.line. Opening the "Project Overview" takes 12 seconds. Running payroll takes 4 hours.
The Problem
The account.analytic.line (Analytic Items) table is one of the heaviest tables in Odoo. It is the central junction for accounting, projects, and HR. As it grows, standard SUM() and GROUP BY queries become exponentially slower.
The Solution
You need specific indexing strategies, archival policies, and "Materialized Views" for reporting.
We've implemented 150+ Odoo systems. We manage databases with 10M+ analytic lines. Performance doesn't degrade by accident; it degrades because of architecture.
Strategy 1: The "Composite Index"
By default, Odoo indexes individual fields like project_id and date.
However, your reports usually ask: "Show me hours for Project X in January."
Postgres has to scan the project_id index, then scan the date index, and merge the results. This is slow for millions of rows.
The Fix: Composite Index
Create a composite index (Multi-Column Index) covering the most common search pattern.
CREATE INDEX CONCURRENTLY idx_analytic_project_date
ON account_analytic_line (project_id, date);
2s → 50ms
Postgres jumps directly to the intersection of Project + Date
Strategy 2: Pre-Computation (Materialized Views)
Your "Monthly Profitability" report runs a massive aggregation query every time a manager loads the page.
The Fix: Materialized View
Create a Materialized View in Postgres. This is a snapshot table that pre-calculates the totals.
CREATE MATERIALIZED VIEW view_monthly_project_stats AS
SELECT
project_id,
date_trunc('month', date) as month,
sum(unit_amount) as total_hours,
sum(amount) as total_cost
FROM
account_analytic_line
GROUP BY
project_id, date_trunc('month', date);
Odoo Integration
Create a read-only Odoo model backed by this view.
class ProjectMonthlyStats(models.Model):
_name = 'project.monthly.stats'
_auto = False # Prevent Odoo from creating a table
project_id = fields.Many2one('project.project')
month = fields.Date()
total_hours = fields.Float()
Refresh Strategy
Create a Cron Job to run REFRESH MATERIALIZED VIEW CONCURRENTLY view_monthly_project_stats every night (or every hour).
The dashboard now reads from a tiny table of 5,000 rows instead of summing 4,000,000 rows on the fly.
Strategy 3: Archival (Partitioning)
Do you really need instant access to timesheets from 2019? Probably not.
Keeping them in the main "Hot" table slows down every insert and update for 2024 data.
The Fix: Table Partitioning (Postgres 10+)
Concept: You split account.analytic.line into:
account_analytic_line_2023
account_analytic_line_2024
account_analytic_line_2025
Postgres handles this transparently. When you query data, it only scans the relevant year's partition.
Note: Implementing partitioning on an existing Odoo table requires advanced migration scripts and downtime.
A simpler alternative is an "Archive" flag domain.
The "Soft" Archive
Add an active field (or is_archived boolean) to the model
Set active=False for all records older than 2 years
Odoo's default search domain [('active', '=', True)] effectively filters them out, keeping the index scan small
Strategy 4: Preventing "Chatter Bloat"
Every time an employee edits a timesheet ("Changed 2 hours to 3 hours"), Odoo logs a message in the Chatter (mail.message).
If you have 4 million timesheets, you might have 10 million chatter messages linked to them.
The Fix: Disable Tracking
Disable tracking on high-volume fields.
class AccountAnalyticLine(models.Model):
_inherit = 'account.analytic.line'
# Remove tracking=True
unit_amount = fields.Float(tracking=False)
This saves massive amounts of space in the database and speeds up write operations.
Your Action Items
Analyze Volume
❏ Connect to PSQL
❏ Run SELECT count(*) FROM account_analytic_line;
❏ If > 1,000,000, you need to act
Index Audit
❏ Identify your top 3 reports (e.g., Payroll, Project Profitability)
❏ Check the WHERE and GROUP BY clauses
❏ Do you have a composite index matching those fields?
Cleanup
❏ Check the mail_message table size. Is it bloated with timesheet edits?
❏ Disable field tracking on the model
Free Database Performance Audit
Is your timesheet reporting sluggish? We'll analyze your query logs (pg_stat_statements) to find the slow bottlenecks, implement the correct composite indexes for your specific reporting needs, design a Materialized View strategy for your executive dashboards, and optimize your data retention policies. Don't let history slow down your future.
