Odoo PostgreSQL sharding performance optimization direction plan

This solution is prepared by Party B's technical team. In response to the performance issues caused by the growth of the PostgreSQL database capacity (4.5TB) in the Odoo 18 system, it proposes a database optimization architecture plan centered on "database and table sharding, partition optimization, cold data archiving, and index and storage tuning." The goal is to achieve a system query performance improvement of over 40% within the next three years, keep the main database capacity within 500GB, and reduce backup and maintenance cycles by 60%.

I. Project Background

The current Odoo18 system runs on a single PostgreSQL instance, with a total database size of approximately 4.5TB. The data volume of core business tables continues to grow, and the size of individual tables is approaching performance limits.

As business expands and user numbers increase, the database experiences:

  • Query performance degradation;
  • Index bloat;
  • VACUUM overload;
  • Backup and recovery time is too long;
  • Historical data is difficult to archive.

It is planned to systematically optimize the existing architecture through the Sharding + Partitioning solution to ensure database performance and maintainability for the next three years.

II. Overall Project Objectives

  1. Establish a high-performance, highly scalable PostgreSQL data architecture;
  2. Control the data size of a single table to improve query and write performance;
  3. Optimize the database storage structure to reduce maintenance costs;
  4. Support Odoo18's business growth needs over the next three years.

III. Overall Technical Approach

Stage Optimization direction Main Objective
1️⃣ Data Partitioning Business Table by Year/Company Control single table size to improve query speed
2️⃣ Database Sharding Split the database by business module or data ownership Reduce single-instance load and improve concurrency capability
3️⃣ Data Archiving and Hot-Cold Separation Historical data stored independently Ensure the primary database only retains active data from the last 3 years
4️⃣ Index and Storage Optimization Simplify indexes and rebuild storage structure Reduce IO and index fragmentation
5️⃣ Data Migration and Monitoring Automated migration + performance monitoring Ensure migration safety and long-term maintainability

4. Sharding and Partitioning Design Plan

1️⃣ Partitioning Strategy

Core idea: Partition the largest and fastest-growing tables by time or company dimension.

Target table Partition type Partition Rules Target capacity
account_move RANGE By year (move_date) Per partition ≤ 100GB
stock_move RANGE + HASH By year and warehouse Per partition ≤ 50GB
sale_order RANGE By quarter (create_date) Per partition ≤ 30GB
sale_order_line RANGE By quarter Per partition ≤ 50GB
account_move_line LIST By company ID (company_id) Each company has its own separate partition

Technical Solution:

  • Use PostgreSQL native partitioned tables;
  • Automatically create new partitions (pg_partman);
  • Partition naming convention: sale_order_y2025_q1;
  • Historical partitions are set to read-only and enter the cold storage archive area.

2️⃣ Sharding Scheme

Goal: Reduce single-instance load through logical database sharding.

Sharding dimension Instructions
Separate databases by business module Sales, inventory, and accounting independent databases (read-write isolation)
Shard by data heat The primary database retains data from the past three years, and the historical database archives it.
Cross-database access method PostgreSQL FDW (Foreign Data Wrapper) implements transparent queries
Synchronization mechanism Regular logical synchronization (pg_dump + cron) or streaming replication (hot standby)

Architecture Diagram:

Primary Instance (Active Data)      Historical Instance (Archived Data)
 ├── sale_db             ├── sale_db_archive
 ├── stock_db            ├── stock_db_archive
 ├── account_db          ├── account_db_archive

5. Data Migration and Implementation Steps

Stage Job Responsibilities Party B's main tasks Output Results
Phase 1: Baseline Assessment Collect performance metrics, analyze heat maps SQL analysis, index and table scan statistics "Performance Evaluation Report"
Phase 2: Partition Design and Testing Design partition rules, create test partitions Implement partitioned table migration and index rebuild "Partition Design and Verification Report"
Phase 3: Database Sharding Structure Setup Plan database instances and connections Build sub-database architecture, configure FDW "Sub-database Architecture Deployment Document"
Phase 4: Data Migration and Validation Migrate historical data, compare and verify Write migration scripts, perform consistency checks "Data Migration and Validation Report"
Phase 5: Optimization and Monitoring System Construction Tuning parameters, establishing monitoring Adjust PostgreSQL parameters, deploy Grafana monitoring Optimization Summary and Monitoring Report

6. Database Layer Technology Optimization Content

  1. Parameter Optimization
    • Adjust shared_buffers, work_mem, maintenance_work_mem, effective_cache_size;
    • Independent WAL disk, optimize checkpoint_timeout, wal_compression;
    • Enable autovacuum to adjust frequency and avoid table bloat.
  2. Index Strategy
    • Create a GIN/BTREE composite index for high-frequency fields;
    • Clean up redundant indexes to reduce write pressure;
    • Perform REINDEX CONCURRENTLY periodically.
  3. Table Structure Adjustment
    • Compress and store historical tables;
    • Use UNLOGGED for log-type tables to reduce WAL pressure;
    • Large fields (JSON/Text) use TOAST compression.
  4. Monitoring and Maintenance System
    • Deploy pg_stat_statements, pg_partman_bgw, pg_cron;
    • Integrate Grafana + Prometheus to display query time, IO, and cache hit rate;
    • Regularly output the "Performance Monitoring Report".

7. Risks and Control Measures

Potential Risks Response measures
Data migration interrupted Enable transaction-level migration + validation script
Partition strategy error Pre-test environment verification + partition simulation
Archived data is unavailable FDW query verification + dual backup mechanism
Performance did not meet expectations Continuously optimize index and cache parameters

8. Content Delivered by Party B

  1. "PostgreSQL Sharding Architecture Design Document"
  2. Database Migration and Verification Plan
  3. Database Performance Tuning Report
  4. "Monitoring and Alerting System Deployment Guide"
  5. Project Summary and Performance Evaluation Report

9. Project Value

  • Single table query performance improved by 40%+;
  • The main database data is controlled within 500GB, and VACUUM efficiency is improved by 60%;
  • Backup cycle shortened to 1/3;
  • The system architecture has the capability for sustainable expansion over three years;
  • Provide long-term support for Odoo application layer upgrades.


关于我们

​我们致力于帮助中小企业实现数字化转型，我们的团队由一群充满激情和创新思维的专业人士组成，他们具备丰富的行业经验和技术专长。

扫一扫获取顾问以及手册

Tags
Odoo 数据库冷热表 Odoo共用数据库 odoo 分库分表 odoo10数据库表 odoo8数据库还原 odoo使用数据库 odoo修改数据库名 odoo写数据库 odoo创建数据库 odoo初始化数据库 odoo删除数据库 odoo千万条数据库 odoo域名数据库 odoo基础数据库 odoo多数据库 odoo大数据库 postgesql
Our blogs
归档
Sign in to leave a comment
Does odoo19 community edition provide a Chinese language pack?
This article introduces whether Odoo 19 Community version supports Chinese language packs, how to install Simplified/Traditional Chinese, precautions for using language packs, and best practices.