Offline Batch Reporting with PostgreSQL JSON Read Models for ERP
This article explores an architectural approach for generating offline batch reports in a startup-scale ERP system. It leverages local compute resources and PostgreSQL's JSON capabilities to create read models, thereby offloading reporting workload from the primary transactional database and improving performance for core ERP operations.
Read original on Medium #system-designThe article proposes a solution for offline batch reporting in a small-scale ERP system (under 50 users), focusing on efficiency and minimizing impact on the primary transactional database. The core idea is to separate reporting concerns from the operational database using a dedicated read model built with PostgreSQL's JSON data type capabilities.
Architectural Overview
The architecture centers around the concept of a read model, which is a denormalized, optimized view of data specifically tailored for reporting. Instead of complex joins on the transactional database, reports can directly query this simpler structure. The choice of PostgreSQL JSON fields allows for flexible schema evolution without altering table structures, which is beneficial for rapidly changing reporting requirements.
Key Components
- Primary ERP Database: Serves transactional operations, optimized for writes and real-time data.
- Local Compute Environment: A dedicated machine or container for executing batch processes.
- PostgreSQL Read Model Database: A separate database or schema in PostgreSQL storing denormalized JSON documents for reporting.
- ETL Process: A batch job that extracts data from the ERP database, transforms it into JSON documents, and loads it into the read model database.
Considerations for Read Models
Read models are powerful for improving query performance and isolating workloads. However, they introduce eventual consistency challenges. Data in the read model will always be slightly behind the primary transactional data, which is acceptable for most batch reporting scenarios but critical to understand for real-time applications.
The use of local compute for the ETL process allows for efficient data transformation without burdening the ERP server. The PostgreSQL JSON read models facilitate complex queries against semi-structured data, often outperforming traditional relational queries for certain report types due to fewer joins and optimized indexing strategies for JSONB data.
Advantages and Trade-offs
- Improved Performance: Offloads heavy reporting queries from the transactional database.
- Scalability: Allows scaling reporting infrastructure independently.
- Flexibility: JSON schema allows easier adaptation to new report requirements.
- Decoupling: Separates concerns between operational and analytical workloads.
- Increased Complexity: Requires managing an additional database and an ETL pipeline.
- Eventual Consistency: Reports are based on slightly stale data.