Time Tracking Performance: Managing Millions of Entries

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.

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.

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);

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:

Postgres handles this transparently. When you query data, it only scans the relevant year's partition.

The "Soft" Archive

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

Index Audit

Cleanup