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
- Establish a high-performance, highly scalable PostgreSQL data architecture;
- Control the data size of a single table to improve query and write performance;
- Optimize the database storage structure to reduce maintenance costs;
- 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
-
Parameter Optimization
- 调整 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.
-
Index Strategy
- Create a GIN/BTREE composite index for high-frequency fields;
- Clean up redundant indexes to reduce write pressure;
- Perform REINDEX CONCURRENTLY periodically.
-
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.
-
Monitoring and Maintenance System
- 部署 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
- "PostgreSQL Sharding Architecture Design Document"
- Database Migration and Verification Plan
- Database Performance Tuning Report
- "Monitoring and Alerting System Deployment Guide"
- 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.
