Niyu Labs
Contact
Toggle navigation
AI Inventory ForecastingGoMCP ServerGoProductsGoServicesGoDocsGo
Contact

Blog

Odoo Reports Timeout When Filtering More Than 3 Months of Data? Here’s the Real Fix

If your Odoo reports work flawlessly for last week’s data but freeze, timeout, or trigger a 504 Gateway Timeout when you filter 3 months, 6 months, or a full financial year, the problem is usually not the dashboard UI. It is the reporting workload being pushed through a transactional ERP system.

Odoo Reports Timeout When Filtering More Than 3 Months of Data? Here’s the Real Fix

Quick Answer

Odoo reports timeout on large date filters because analytical queries (OLAP) are forced to scan massive transactional tables (OLTP) like sale.order, account.move, and stock.move. Odoo is excellent for operational reporting and day-to-day ERP views, but it is not designed to serve repeated, large-scale BI scans across millions of historical rows while also processing live transactions. The durable architectural fix is to move historical reporting workloads to Google BigQuery using an incremental sync, keeping Odoo primarily focused on operations while heavy historical queries run outside the ERP.

Developer Diagnostic Checklist: Confirming the Workload Problem

Before changing your architecture, confirm that the timeout is caused by analytical workload pressure and not a simple missing index or a badly written custom module.

  • Check the date range threshold: If the report loads for one week but fails for three months, the problem is data volume and serialization, not a fundamentally broken report.
  • Check for 502/504 Gateway errors: A 504 error usually means a large query or API request took longer than the configured proxy, worker, or request timeout.
  • Check operational user impact: If warehouse users cannot validate picking operations while the finance team is refreshing dashboards, the reporting query is consuming resources needed by live users.
  • Check the Odoo models involved: The bottleneck often appears on high-volume transactional models: sale.order.line, account.move.line, stock.move, stock.valuation.layer, or purchase.order.line.
  • Check external API logs: Look for Power BI, Looker Studio, or Python scripts repeatedly calling XML-RPC/JSON-RPC to pull massive historical batches.
  • Check for CSV workarounds: If analysts are manually exporting CSVs because the UI times out, the architecture is already failing your business process.

What to Try Before Moving to BigQuery

We do not pretend every slow Odoo report requires a data warehouse. Before investing in an external pipeline, try these native optimizations first:

  1. Check for missing indexes: Ensure custom fields used frequently in search domains or group-bys are properly indexed in PostgreSQL.
  2. Avoid computed fields in large reports: Non-stored computed fields must be calculated on the fly for every single row. On a 500,000-row report, this can easily become the difference between a slow report and a timeout.
  3. Enforce default date filters: Prevent users from accidentally running "All Time" queries by forcing a default 30-day filter on heavy views.
  4. Archive old data: If your database has 10 years of history, archive what isn't needed for daily operations.
  5. Review custom record rules: Complex, poorly optimized record rules (e.g., heavily nested domains) add severe overhead to every read operation.

The verdict: If you have optimized your views and indexes, but 6-month or 12-month filters still crash the system, you have outgrown native reporting. You need to move the analytical workload out.

Why Not Just Increase Odoo Workers or Server Size?

The most common developer objection is: "Why don't we just add more Odoo workers, upgrade the RAM, or scale up the PostgreSQL RDS?"

  • More workers can temporarily reduce queueing, but they do not solve the root inefficiency of ORM serialization on millions of rows.
  • More RAM/CPU helps short-term, but as the database grows, analytical queries will eventually eat the new capacity too.
  • The underlying conflict remains: Your reporting workload is still competing with your operational workload for the same database CPU, I/O, memory, connection pool, and Odoo worker capacity.

Scaling up the server treats the symptom. Architecture separation is the durable fix.

What Actually Breaks Inside Odoo on Large Filters

Odoo is an OLTP (Online Transaction Processing) system. It is brilliant at rapid, precise, single-row operations: creating an invoice, reserving stock, or confirming a sale. Reporting requires OLAP (Online Analytical Processing)—scanning, joining, and aggregating millions of historical rows.

When you mix them, three bottlenecks compound:

  1. PostgreSQL Table Scans: Complex joins and grouping logic across account.move.line or stock.move force PostgreSQL to work extremely hard.
  2. ORM Serialization Overhead: Odoo doesn't just return SQL rows. The ORM converts records into Python objects, applies record rules, checks access rights, and serializes them into JSON/XML. For 1 million rows, this serialization loop runs in a single Odoo request worker and becomes a massive bottleneck.
  3. Worker Starvation: When a dashboard refresh ties up a worker process for 90 seconds, that worker cannot serve live HTTP requests. If analysts refresh multiple dashboards, all workers saturate, and the ERP freezes.

Odoo Reporting Fix Options Compared

Option 1: Native Odoo Dashboards

Works well for small and moderate operational reporting.

Best for day-to-day views, pivots, lists, and simple dashboards.

Weakness: the query still runs against the operational Odoo database.

For long historical filters, native dashboards can still become slow.

Option 2: Increase Server Size or Workers

Can help temporarily.

Useful when the bottleneck is basic capacity.

Weakness: the reporting workload still competes with live ERP work.

As data grows, the same problem usually returns.

Option 3: Direct PostgreSQL BI

Can work in controlled environments.

It can be made safer with read-only replicas, views, masking, and strict governance.

Weakness: it becomes a separate data-governance project.

It can bypass Odoo’s application-level rules and still create heavy database load if not designed carefully.

Option 4: XML-RPC or JSON-RPC Extraction

Useful for integrations.

Good for controlled access and smaller data flows.

Weakness: repeated BI extraction over hundreds of thousands of rows creates serialization overhead and worker pressure.

These APIs are not ideal for large historical reporting refreshes.

Option 5: Odoo to BigQuery Incremental Sync

Best fit when the problem is historical reporting scale.

Odoo remains the source of truth.

BigQuery becomes the analytical copy for BI, dashboards, and large scans.

Weakness: it requires an initial warehouse setup, schema planning, and sync configuration.

But once the architecture is in place, BI tools no longer need to run heavy historical queries through live Odoo request paths.

The Real Fix: Odoo → Incremental Sync → BigQuery

The correct architecture completely decouples the analytical layer from the operational ERP. By placing the Niyu Labs BigQuery Connector as middleware, you export configured Odoo models asynchronously. Power BI, Looker, and Tableau then query BigQuery instead of Odoo.

Why BigQuery Fits This Workload

BigQuery is built specifically for analytical workloads where storage and compute are separated. It can scan massive datasets without relying on your ERP's production server. This makes it a better fit for large historical reporting workloads than repeatedly pulling data through the live Odoo API or production database.

How Incremental Sync Actually Works: write_date + id Cursor

A naive sync pulls the entire account.move table every night. If you have 2.4 million rows, that is slow, expensive, and puts heavy load on Odoo.

The first sync is a controlled full-load bootstrap. After that, the connector switches to incremental mode. For models where write_date is reliably available, a production-grade connector doesn't just rely on the timestamp (which can skip records if multiple transactions occur in the exact same second). It uses a composite cursor:

Python

# Production-grade incremental sync logic handling tie-breakers
domain = [
'|',
('write_date', '>', last_sync_timestamp),
'&',
('write_date', '=', last_sync_timestamp),
('id', '>', last_synced_id),
]
records = env['account.move'].search(
domain,
order='write_date asc, id asc',
limit=batch_size,
)

In production, many pipelines also use a small overlap window—for example, re-reading the last few minutes of changed records. In BigQuery, the synced table should be updated using an upsert or merge strategy keyed by the Odoo record id, so re-reading overlap-window records does not create duplicates. This makes the sync more tolerant of timestamp precision, concurrent writes, and retry behavior.

The connector updates the sync cursor only after the BigQuery write succeeds. Odoo reporting load drops sharply because the recurring BI refresh no longer scans the full historical table inside Odoo.

What About Deleted Odoo Records?

Incremental sync easily handles creations and modifications, but hard-deleted records require a specific strategy. A robust pipeline handles this by:

  • Preferring archiving (active = False) over deletion in Odoo.
  • Using a tombstone/delete log if the module actively controls deletion events.
  • Running a periodic reconciliation job to execute a BigQuery merge strategy, marking missing records as inactive in the warehouse.

What About Schema Changes? (Schema Drift)

Odoo schemas change when modules are installed, custom fields are added, or upgrades move data between models. A production-grade Odoo to BigQuery pipeline detects missing fields, type changes, and new columns before a sync job fails. At a minimum, the connector validates selected fields against the current Odoo model metadata and keeps BigQuery schema updates controlled instead of silently breaking dashboards.

Illustrative Performance Pattern: Before vs. After BigQuery Sync

The following is an illustrative benchmark pattern for a mid-sized Odoo environment. Replace these figures with measured results from your own demo or customer environment before treating them as product performance claims.

Detail

Example Benchmark Environment

Odoo Version

17.0

Hosting & Capacity

4 Workers, 16 GB RAM

Database Size

1.8M account.move.line, 2.3M stock.move rows

BI Tool

Looker Studio / Power BI

Before Timing (Direct)

70+ seconds / 504 Gateway Timeout

After Timing (BigQuery)

3–8 seconds

Sync Details

15-minute frequency, 5,000 row batch size

BigQuery Table Design

Partitioned by date, clustered by company/product

Which Odoo Models to Sync First (And What to Exclude)

Do not dump your entire Odoo database into BigQuery. Sync only what creates reporting value.

High-Value BI Models:

  • sale.order & sale.order.line
  • account.move & account.move.line
  • stock.move & stock.valuation.layer
  • purchase.order & purchase.order.line
  • res.partner & crm.lead

Exclude These (Cost Control):

  • ir.attachment (Binary files inflate storage costs rapidly)
  • mail.message (High-volume chatter, low BI value)
  • System logs and temporary wizard models.

BigQuery Cost-Control Rules for Odoo Data

  1. Partition large fact tables: Partition account.move.line and stock.move by date so BI queries scan less data.
  2. Cluster by common filters: Cluster by company_id, partner_id, or product_id.
  3. Avoid SELECT *: Ensure your BI dashboards only query the specific columns they need to render the visual.
BigQuery Schema

Readiness Checklist: Is This Overkill for You?

BigQuery is overkill if your Odoo reports load quickly, your core tables are under 100,000 records, and you don't use external BI tools. Stick to native Odoo reporting.

However, you need this architecture if:

  • [ ] You consistently hit 502/504 errors on 3-month+ filters.
  • [ ] Your finance team relies on manual CSV exports.
  • [ ] Warehouse/Sales users feel the ERP slow down during reporting windows.
  • [ ] You need to join Odoo data with external systems (Shopify, Salesforce).
  • [ ] You are managing multi-company Odoo setups requiring consolidated BI.

People Also Ask (FAQ)

Why does Odoo timeout on long date filters?

Odoo times out because analytical queries force PostgreSQL and the Odoo ORM to serialize millions of rows in a single request worker, eventually exceeding proxy or server time limits.

How do I fix Odoo 504 Gateway Timeout during reporting?

The most permanent fix is decoupling the analytical workload from the operational database by pushing historical data to a data warehouse like BigQuery using incremental sync.

Is direct PostgreSQL reporting safe for Odoo?

It is risky at scale. It bypasses Odoo's application-level security and record rules. While it can be made safe with read-only replicas and strict masking, it requires a dedicated data-governance project.

Is XML-RPC good for Odoo BI?

Odoo’s external APIs are useful for integrations and controlled data access, but they suffer from severe serialization overhead when pulling hundreds of thousands of historical rows for BI.

What is the best way to connect Odoo to BigQuery?

Using a dedicated Odoo module that performs asynchronous, batched incremental syncs using a tie-breaker cursor (write_date and id), ensuring Odoo worker processes are never blocked.

Which Odoo tables should be exported to BigQuery?

Focus on high-value transactional models like sale.order.line, account.move.line, stock.move, and stock.valuation.layer.

Does BigQuery replace Odoo reports?

No. Odoo remains the system of record for operational day-to-day data. BigQuery handles large-scale historical analytics, multi-company consolidation, and heavy BI dashboards.

Final Takeaway

If Odoo reports timeout when filtering more than 3 months of data, stop trying to redesign the dashboard. You have a workload placement problem.

Odoo should run the business. BigQuery should run the historical analytics.

For teams ready to implement this, the Niyu Labs BigQuery Connector provides the exact asynchronous, incremental sync pipeline described in this guide—designed around production concerns such as schema validation, deletion strategy, and tie-breaker cursor logic. It keeps Odoo fast and gives your analysts the data they need.

Technical References