If you have ever opened the Journal Items list in Odoo on a company with two million accounting entries and watched the browser spin for eight seconds, you already understand the problem this blog is about. Odoo is a powerful ERP, but it was designed to be flexible and general-purpose. Its reporting queries often aggregate large volumes of data on the fly, joining several tables, applying filters, and computing totals every single time a user opens a screen. At a small scale, this is fine. At the production scale, it becomes the thing your users complain about every morning.
PostgreSQL has a feature that directly addresses this class of problem, and it is significantly underused in the Odoo ecosystem. It is called a materialized view, and once you understand what it actually does, it becomes one of the most practical tools in the database administrator's toolkit.
A Regular View Is Just a Saved Query
Before explaining materialized views, it helps to understand what a regular view is. A regular view in PostgreSQL stores nothing. When you query a view, PostgreSQL substitutes the view's definition into your query and executes it as if you had typed it out by hand. The view is a shortcut for writing, not a shortcut for execution. Every query against a view does the same full work as the underlying query.
A materialized view is fundamentally different. When you create one, PostgreSQL runs the query and stores the actual result rows on disk as a real table. Querying the materialized view does not touch the underlying tables at all. It reads the pre-computed rows directly, the same way a table read works. You get the full benefit of indexes, statistics, and efficient sequential scans — on data that was already computed ahead of time.
Why This Matters for Odoo Specifically
Odoo's heaviest queries tend to be aggregations. The accounting reports group millions of journal lines by account, period, and partner. The stock valuation reports join move lines with product categories and locations. The CRM pipeline view aggregates opportunities by stage and salesperson. These queries can involve five or six table joins, several WHERE clauses, and GROUP BY operations across hundreds of thousands of rows.
When a user opens a report, none of that work needs to happen in real time. The data from yesterday's transactions did not change. The historical stock moves are not moving again. The committed journal entries are committed — they will not be revised. For these cases, pre-computing the result once and reading it many times is strictly better than computing it fresh every time someone refreshes the screen.
A materialized view on top of Odoo's account_move_line table, for example, could pre-aggregate journal entries by account and period. What currently takes three seconds on every page load could become a five-millisecond index scan. The user experience is unrecognisably faster.
The Refresh Question
The obvious question is: how does the materialized view stay current? The honest answer is that it does not update automatically. This is a deliberate design decision in PostgreSQL. When the underlying tables change, the materialized view keeps serving the old results until you explicitly tell it to refresh.
Refreshing a materialized view reruns the original query and replaces the stored data with fresh results. There are two ways to do this. The default refresh locks the view entirely during the operation — nobody can read from it while the new data is being loaded. This is fine for overnight batch jobs or maintenance windows when no users are active.
The concurrent refresh option is designed for live systems. It builds the new data in a temporary location alongside the existing data, computes the difference between old and new, and applies only the changed rows. Reads continue uninterrupted throughout. The trade-off is that concurrent refresh requires a unique index on the materialized view to identify which rows changed, and it runs somewhat slower than a full replacement. For Odoo environments, building the unique index on the primary key equivalent column is almost always straightforward.
For most Odoo reporting use cases — daily financial summaries, monthly sales figures, periodic inventory valuations — a scheduled refresh every few minutes or every hour is more than sufficient. Users do not need last-second accuracy on a summary report. They need it to load fast.
Indexes Work on Materialized Views
One detail that surprises people is that you can create indexes on a materialized view just like you would on a table. PostgreSQL stores the materialized view as a heap — the same physical storage format as a regular table. This means you can add a B-tree index on the account column, a composite index on period and company, or a partial index for specific filter conditions. The planner uses these indexes just as it would for any table, choosing between an index scan and a sequential scan based on the query and the available statistics.
This is the combination that makes materialized views so effective: the data is pre-computed, and then it is indexed. A query that previously required joining five tables and aggregating two million rows becomes an indexed lookup on a few thousand pre-computed summary rows.
A Practical Starting Point for Odoo
The most impactful place to start in most Odoo installations is the accounting module. Queries against account_move_line joined to account_move and filtered by company, period, and state are run constantly by every user who opens a journal, reconciliation screen, or financial report. A materialized view that pre-aggregates this data by account, period, and company — refreshed every ten minutes by a pg_cron job — eliminates the most common source of slowness in Odoo's accounting interface.
The same pattern applies to stock valuation, sales reporting, and any custom Odoo module that produces summary dashboards. Wherever you find a query that always runs the same shape of aggregation over data that does not change by the second, a materialized view is the right tool to reach for.