Materialized View PostgreSQL: A Guide for Odoo ERP Speed

You're probably here because Odoo is doing the right job at the application level, but your reports still feel slow. An operations manager opens an inventory dashboard before a purchasing call. Finance runs a month-end summary. Production wants a quick MRP snapshot. Instead of getting answers, they get a spinner.

That usually isn't an Odoo problem. It's a query problem inside PostgreSQL. When the database has to repeat the same heavy joins and aggregations every time someone opens a dashboard, even a well-configured ERP starts to drag.

For UK businesses moving from spreadsheets into a unified platform, that's a common next-stage bottleneck. The data is centralised, which is good. The reporting load is centralised too, which can expose weak points. If you're reviewing your reporting stack, this Odoo ERP system guide gives a useful business-level overview of how Odoo fits broader ERP needs before you optimise the database layer.

Table of Contents

• The End of Slow Odoo Reports
• What Is a PostgreSQL Materialized View
  • A simple way to think about it
  • What matters in Odoo
• Materialized Views vs Regular Views in an ERP Context
  • The decision that matters
  • Materialized View vs. Regular View Key Differences for Odoo
• Creating and Refreshing Materialized Views for Odoo
  • A practical Odoo example
  • Refreshing without surprising your users
• Real-World Use Cases for Odoo ERP
  • Manufacturing and logistics dashboard
  • Sales trend reporting
  • Finance and month-end summaries
• Advanced Strategies and Common Pitfalls
  • What generic tutorials usually miss
  • Operational habits that work better
• Conclusion Your Path to a Faster Odoo Database

The End of Slow Odoo Reports

A slow Odoo report usually follows a pattern. The screen looks simple, but the SQL behind it isn't. A dashboard might join sales orders, order lines, products, stock moves, accounting entries, and company filters. Then it groups, sums, sorts, and does it all again for every user who opens the same page.

That approach works until data volume grows. Then the same report that felt acceptable in testing starts slowing down production users, especially in manufacturing and logistics setups where inventory, MRP, and accounting all feed management dashboards.

PostgreSQL materialized views solve that by storing the result of a complex query on disk instead of recalculating it on every read. In benchmarked comparisons, materialized views reduced execution time from 20 seconds to 130 milliseconds on 5 million records, which is about 154x faster than a regular view in that scenario, according to DbVisualizer's materialized view benchmark.

Practical rule: If a report is expensive to calculate but doesn't need second-by-second freshness, a materialized view is often the cleanest fix inside PostgreSQL.

In Odoo terms, think about reports like these:

• Sales summaries: Daily, weekly, or monthly rollups for managers who need trend visibility, not live transactional detail.
• Inventory snapshots: Stock position dashboards refreshed on a schedule for warehouse planning.
• Accounting overviews: Aggregated receivables, payables, and turnover reports that support decisions without querying every source table each time.

For SMEs in the UK, this matters because ERP adoption usually replaces siloed spreadsheets with a single source of truth. That improves control, but it also concentrates query pressure in one place. If the database has to recompute everything from scratch for each dashboard load, users blame Odoo when the issue is repeated database work.

Materialized views don't fix every reporting problem. They won't replace good indexing, clean Odoo customisations, or sensible dashboard design. But for the right workload, they turn a sluggish reporting layer into something people use.

What Is a PostgreSQL Materialized View

A PostgreSQL materialized view is a saved query result stored physically on disk. It behaves like a pre-built snapshot of data that PostgreSQL can read quickly.

A simple way to think about it

A regular view is like a recipe card. Every time someone asks for the dish, the kitchen cooks it from scratch.

A materialized view is the finished meal already prepared and stored. Serving it is faster because the work happened earlier.

That difference matters in Odoo. A regular view always reflects the latest state of the base tables, but PostgreSQL has to rerun the SQL each time. A materialized view reads stored results instead, so dashboards and reporting queries return faster.

If you want a useful refresher on SQL variables and query structure before building more advanced reporting SQL, this short guide to learn SQL variables with AuditReady is worth a read.

What matters in Odoo

In practical ERP work, the key thing isn't the database definition. It's the trade-off.

A materialized view gives you speed because the result is already stored, but it also gives you stale data until you refresh it. PostgreSQL materialized views also can't be directly updated. You must run REFRESH MATERIALIZED VIEW to rebuild them, and by default that refresh locks all rows and refreshes 100% of the data even for minor changes, as described in the PostgreSQL materialized views documentation.

That has direct consequences in Odoo:

• Good fit: Historical sales analysis, stock valuation snapshots, purchasing summaries, margin reporting.
• Poor fit: Live SLA queues, immediate support response boards, anything users expect to reflect changes instantly.
• Needs planning: Dashboards used by multiple departments during office hours.

Treat a materialized view as a reporting asset, not as transactional storage.

That distinction saves a lot of pain. I've seen teams try to use materialized views as if they were dynamic tables underneath custom Odoo modules. That usually ends with confusion, stale dashboards, and refresh jobs nobody owns.

For clients, the simplest definition is still the best one. A materialized view is a cached reporting dataset inside PostgreSQL. It's fast because the heavy SQL work happens ahead of time, not when your users click a dashboard.

Materialized Views vs Regular Views in an ERP Context

The question isn't whether materialized views are better than regular views in the abstract. The primary question is which one fits a specific Odoo report.

The decision that matters

If a warehouse lead opens a dashboard every few minutes and only needs a recent snapshot, a materialized view is often the better tool. If a support manager needs the exact live state of open tickets and SLA timers, a regular view or direct query is safer.

This gets even more important when Odoo connects to ecommerce channels, marketplaces, or external systems. If your reporting layer also needs to support connected operations, this overview of ERP and ecommerce integration is a useful companion read because integration design affects what “fresh enough” really means.

A good rule is simple. Choose based on business tolerance for lag, not on developer preference.

Materialized View vs. Regular View Key Differences for Odoo

Two mistakes show up repeatedly in Odoo projects.

First, teams use regular views for reporting that's obviously analytical. Then they wonder why dashboards become slower as order volume grows.

Second, teams use materialized views for operational widgets that users treat as real-time controls. Then the business sees stale information and loses trust in the report.

If the report answers “what happened over a period?”, a materialized view is usually suitable. If it answers “what is happening right now?”, be careful.

For teams pushing Odoo data into BI tooling, dashboard design matters too. This guide to Power BI custom visuals for Odoo ERP users is useful because a fast database object still needs a sensible reporting layer on top.

In most ERP environments, both view types belong in the stack. Regular views help organise logic and expose current-state data. Materialized views help absorb repeated reporting load. The trick is assigning each report to the right category before performance issues force the decision later.

Creating and Refreshing Materialized Views for Odoo

The SQL syntax is straightforward. The primary task involves choosing the right source query and refresh pattern.

A practical Odoo example

Suppose you want a fast sales summary for an Odoo instance. A common reporting need is total sales by customer, company, and order date, built from sale_order and sale_order_line.

CREATE MATERIALIZED VIEW mv_odoo_sales_summary AS
SELECT
    so.company_id,
    so.partner_id,
    DATE(so.date_order) AS order_date,
    COUNT(DISTINCT so.id) AS order_count,
    SUM(sol.price_total) AS total_sales
FROM sale_order so
JOIN sale_order_line sol
    ON sol.order_id = so.id
WHERE so.state IN ('sale', 'done')
GROUP BY
    so.company_id,
    so.partner_id,
    DATE(so.date_order);

You can then query it like a table:

SELECT *
FROM mv_odoo_sales_summary
WHERE company_id = 1
ORDER BY order_date DESC;

For many reporting workloads, that alone takes pressure off the live Odoo tables. It also gives BI tools, external APIs, and custom dashboards a simpler object to query. When teams start building more advanced reporting pipelines, Odoo development support often becomes necessary because the SQL object is only one part of the full reporting design.

After creation, add indexes based on how the view will be queried:

CREATE INDEX idx_mv_sales_summary_company_date
ON mv_odoo_sales_summary (company_id, order_date);

CREATE INDEX idx_mv_sales_summary_partner
ON mv_odoo_sales_summary (partner_id);

Refreshing without surprising your users

The default refresh is simple:

REFRESH MATERIALIZED VIEW mv_odoo_sales_summary;

That command rebuilds the whole view. It's fine for low-traffic systems or off-hours jobs, but it blocks reads while the refresh runs.

For user-facing dashboards, PostgreSQL supports:

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_odoo_sales_summary;

There's a catch. To enable non-blocking concurrent refreshes, you must add a qualifying UNIQUE INDEX to the materialized view. That requirement is explained in SitePoint's guide to concurrent materialized view refreshes.

A practical example:

CREATE MATERIALIZED VIEW mv_odoo_monthly_sales AS
SELECT
    so.company_id,
    DATE_TRUNC('month', so.date_order) AS sales_month,
    so.partner_id,
    SUM(sol.price_total) AS total_sales
FROM sale_order so
JOIN sale_order_line sol
    ON sol.order_id = so.id
WHERE so.state IN ('sale', 'done')
GROUP BY
    so.company_id,
    DATE_TRUNC('month', so.date_order),
    so.partner_id;

Then create the unique index:

CREATE UNIQUE INDEX ux_mv_odoo_monthly_sales
ON mv_odoo_monthly_sales (company_id, sales_month, partner_id);

Now PostgreSQL can run:

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_odoo_monthly_sales;

This short walkthrough shows the mechanics in a visual format:

A few practical refresh habits work well in Odoo environments:

• Use scheduled jobs: Refresh historical dashboards outside peak usage where possible.
• Match frequency to business need: Finance may accept daily refreshes. Sales may want hourly. Customer support may need a different pattern entirely.
• Keep SQL deterministic: If you can't define a stable unique key for the view, concurrent refresh becomes harder.

Don't start with the most ambitious refresh schedule. Start with the business question, then set the lowest frequency that still gives useful decisions.

Real-World Use Cases for Odoo ERP

The best use cases are the ones where users care about quick answers more than second-by-second precision.

In UK-based Odoo ERP implementations, materialized views can reduce complex report query times by 70–85% compared to standard views, enabling faster dashboards for inventory, MRP, and accounting, according to Cybrosys' Odoo and PostgreSQL materialized view analysis.

Manufacturing and logistics dashboard

A manufacturer often wants one screen combining Bills of Materials, stock on hand, replenishment status, production orders, and expected shortages. That query can be expensive because it touches several Odoo models and usually needs grouped totals.

A materialized view works well here when the dashboard is refreshed on a schedule. The production manager usually needs a reliable planning snapshot, not a live recalculation every second.

Sales trend reporting

Retail and wholesale teams often need quarter-based or month-based sales analysis by product, region, customer segment, or salesperson. That report usually aggregates large volumes of order data and tends to be opened repeatedly by different people.

A materialized view removes repeated calculation work from the live system. It also gives external analytics tools a stable object for reporting. If you're deciding how to move Odoo data into wider reporting pipelines, this guide to ETL vs ELT for Odoo ERP integration helps frame where a materialized view fits.

Finance and month-end summaries

Accounting teams regularly need summaries that combine invoices, journal entries, partner balances, and payment status. Those reports are often dense, and month-end usage tends to be concentrated.

A materialized view can make these summaries consistently quick while protecting the main transactional tables from repeated heavy reads.

Common examples include:

• Receivables ageing snapshots: Useful for finance reviews and collection meetings.
• Monthly margin summaries: Helpful where management wants a fast overview before drilling into detail.
• Tax and reporting packs: Better when prepared as controlled snapshots rather than live ad hoc queries.

A good materialized view use case usually has three signs. The query is expensive, users run it often, and the answer doesn't need to be live to the second.

That's why materialized views suit so many Odoo ERP dashboards in manufacturing, logistics, retail, and finance. They let PostgreSQL do the expensive work once, then serve the result repeatedly with much less friction.

Advanced Strategies and Common Pitfalls

The generic advice is often too simple. “Use REFRESH CONCURRENTLY and you're done” sounds nice, but it leaves out the awkward parts that matter in production Odoo systems.

What generic tutorials usually miss

The biggest gap is incremental refresh. Native PostgreSQL doesn't update only the changed rows in a materialized view. It recalculates the full result set on refresh. That's a structural limitation, not a tuning oversight.

Another common misunderstanding is concurrent refresh. PostgreSQL documentation notes that a common pitfall is the lack of native incremental refresh, which forces a full recalculation on every refresh, and that REFRESH CONCURRENTLY requires a unique index and can still cause brief latency spikes, as outlined in the PostgreSQL refresh materialized view reference.

That matters in Odoo when dashboards sit behind warehouse operations, customer service screens, or management BI. Users may keep read access during refresh, but “concurrent” doesn't mean “free”.

Here are the operational pitfalls I see most often:

• Refreshing too often: Teams schedule refreshes aggressively without checking whether the business needs that level of freshness.
• Skipping index design: A fast materialized view can still become slow if nobody indexes the fields used in filters and sorts.
• Treating stale data as a surprise: Staleness is the design trade-off. It needs ownership and communication.
• Forgetting dependency impact: Custom Odoo modules, reporting connectors, and BI layers all depend on stable schema and refresh timing.

Operational habits that work better

In practice, a few patterns reduce pain.

Use Odoo Scheduled Actions or an external cron job to refresh views at predictable times. Separate operational dashboards from analytical dashboards so you don't force one refresh policy onto every report. Monitor refresh duration and user impact, especially if the source query joins large stock or accounting tables.

For high-throughput environments, it also helps to think beyond the materialized view itself. Queueing, async processing, and controlled background jobs can protect the application from spikes. This article on using Redis as a queue for Odoo ERP is relevant because database optimisation and workload orchestration usually need to work together.

A practical checklist:

1. Choose reports carefully: Use materialized views for repeated analytical reads, not for live operational state.
2. Design a unique key early: If you'll need concurrent refresh, solve this before users rely on the report.
3. Schedule around business hours: Especially for logistics, customer support, and finance review periods.
4. Document freshness: Users should know whether they're seeing a live view, an hourly snapshot, or a daily summary.

The mistake isn't using materialized views. The mistake is using them without a refresh policy, indexing plan, and clear business expectation.

When those three pieces are in place, PostgreSQL materialized views are reliable and boring in the best possible way. When they aren't, they become one more hidden dependency behind every slow or confusing report.

Conclusion Your Path to a Faster Odoo Database

A PostgreSQL materialized view is one of the most practical ways to speed up Odoo reporting when the bottleneck comes from repeated heavy SQL. It's especially useful for UK manufacturing and logistics businesses that need fast dashboards across inventory, MRP, sales, and accounting without constantly hammering live transactional tables.

The trade-off is straightforward. You gain speed by accepting controlled staleness and refresh management. That's why materialized views work well for summaries, trends, and dashboards, but not for every operational screen.

Used properly, they can make Odoo feel much faster without changing the core application workflow. Used carelessly, they create stale reports, awkward refresh windows, and confusion about what data users are seeing.

If your Odoo instance is under reporting pressure, start with the reports people open most often, then review whether those reports need live data or just fast access to recent data. Hosting and infrastructure choices matter too, especially when refresh jobs, BI tools, and application traffic all compete for the same database resources. Consequently, a good Odoo hosting setup becomes part of the performance discussion, not a separate one.

If your Odoo reports are slow, or your dashboards are putting too much load on PostgreSQL, ERP Artists can help you design the right reporting architecture, build safe materialized views, and set refresh strategies that fit your business instead of disrupting it.