How Field Indexing Improves Data Model Performance in Odoo 19

As an Odoo database grows, performance rarely drops suddenly. It usually starts with small delays — a list view that takes an extra second to load, a filter that feels slightly slower than before, or a report that takes longer than expected. Over time, these small delays become noticeable bottlenecks.

In many real-world projects, the root cause is not server configuration or worker limits. It is the way the data model is structured. One of the simplest yet most effective improvements you can make at the model level in Odoo 19 is proper field indexing.

This article explains how indexing actually works behind the scenes, when it makes sense to use it, and how to apply it correctly with practical examples.

Understanding Field Indexing in Odoo

Odoo 19 runs on PostgreSQL. Every search, filter, or domain you write in Python eventually becomes an SQL query executed by PostgreSQL.

When a table does not have an index on a searched column, PostgreSQL performs what is called a sequential scan. That means it checks every row in the table to find matching records. With a few hundred records, this is fine. With hundreds of thousands, it becomes expensive.

An index works like a reference structure that helps PostgreSQL locate matching rows without scanning the entire table. Instead of checking everything, it directly navigates to the relevant data.

In systems where read operations (searching, filtering, grouping) are frequent, indexes make a significant difference.

What Odoo Indexes by Default

Odoo already creates indexes for certain fields automatically:

• The id field (primary key)
• Many2one fields, because they are foreign keys
• Some internal relational and technical fields

However, custom fields that you create in your modules are not indexed unless you explicitly define them that way. Even if those fields are used in domains, filters, or reports, Odoo will not index them automatically.

That responsibility lies with the developer.

Adding an Index in Odoo 19

In Odoo 19, adding an index is straightforward. You only need to set index=True in the field definition.

For example:

reference = fields.Char(
   string="Reference",
   index=True
)

Once the module is upgraded, PostgreSQL creates the index for that column.

It looks simple — and it is — but deciding where to apply it requires some understanding of how the data is being used.

Real-World Scenarios Where Indexing Helps

1. Slow Search on a Custom Reference Field

Consider a custom model where users frequently search records using a reference code.

custom_ref = fields.Char(string="Custom Reference")

In the list view, users filter using:

[('custom_ref', "=ilike", "SO/%")]

If this table grows large and the field is not indexed, PostgreSQL scans the entire table every time someone searches. On a production database with large volumes, this becomes noticeably slow.

Adding the index:

custom_ref = fields.Char(
   string="Custom Reference",
   index=True
)

After upgrading the module, search performance improves because PostgreSQL no longer performs a full table scan for that column.

2. Heavy Filtering on Workflow State

Workflow-driven models often rely on a state field.

state = fields.Selection([
   ('draft', 'Draft'),
   ('confirm', 'Confirmed'),
   ('done', 'Done'),
])

List views, kanban views, dashboards, and server actions frequently use domains such as:

[('state', '=', 'confirm')]

Even though state has limited values, if the table contains a large number of records and filtering happens constantly, indexing this field reduces repetitive sequential scans.

state = fields.Selection(
   [
       ('draft', 'Draft'),
       ('confirm', 'Confirmed'),
       ('done', 'Done'),
   ],
   index=True
)

In high-traffic models, this small change can improve overall responsiveness.

3. Date Range Filtering in Reports

Date-based filtering is very common in reports and dashboards.

order_date = fields.Date(string="Order Date")

Typical search:

[
   ('order_date', '>=', start_date),
   ('order_date', '<=', end_date)
]

When generating monthly or yearly reports over large datasets, PostgreSQL must filter large volumes of data. Without an index, performance gradually declines.

Adding:

order_date = fields.Date(
   string="Order Date",
   index=True
)

ensures faster filtering during reporting and scheduled actions.

4. Multi-Company Filtering with Composite Indexes

In multi-company environments, domains often combine multiple fields.

reference = fields.Char(string="Reference")
company_id = fields.Many2one('res.company', string="Company")

Search example:

[('reference', '=', ref), ('company_id', '=', company_id)]

Even though company_id is already indexed, searching on both fields together repeatedly can benefit from a composite index.

A practical way to implement this is through SQL constraints:

_unique_reference_company = models.Constraint(
  'UNIQUE(reference, company_id)',
  "Reference must be unique per company.",
)

This not only enforces data integrity but also creates a combined index at the database level, improving lookup performance when both fields are used together.

When Indexing Makes Sense

Indexing is useful when:

• The field is frequently used in search domains
• The model stores a large number of records
• The field appears in reporting filters
• The data is queried often, but not updated constantly

In read-heavy scenarios, indexes provide clear benefits.

When You Should Be Careful

Indexes are not free. Every index consumes disk space and slightly slows down create and write operations because PostgreSQL must maintain the index.

Avoid indexing:

• Boolean fields
• Fields are rarely used in domains
• Highly volatile fields that update constantly
• Small tables where performance is already acceptable

Over-indexing is as problematic as under-indexing.

Practical Approach Before Adding Indexes

Before adding indexes blindly:

1. Identify slow list views or reports.
2. Check which fields are used in the domain.
3. Confirm the table size.
4. Test changes in a staging environment.
5. Monitor performance after deployment.

In performance tuning, small changes at the model level often produce better long-term results than increasing server resources.

Field indexing in Odoo 19 is not a complex feature, but it has a direct impact on scalability. As databases grow, proper indexing ensures that search operations, filters, and reports remain responsive.

Good data model design is not only about business logic. It is also about understanding how the database behaves underneath. By applying indexing carefully and only where necessary, you create systems that remain stable and efficient even as data volume increases.

To read more about How to Improve Data Model Efficiency with Computed Fields in Odoo 19, refer to our blog How to Improve Data Model Efficiency with Computed Fields in Odoo 19.