CQRS and Database Schema Design for Read/Write Optimization
This article explains the fundamental conflict between normalized schemas optimized for writes and denormalized views optimized for reads in high-traffic applications. It introduces CQRS (Command Query Responsibility Segregation) as the most effective architectural pattern to resolve this conflict, detailing its benefits and trade-offs compared to less optimal solutions like read replicas or schema denormalization.
Read original on Dev.to #systemdesignDatabase schema design often presents a fundamental tension: what's optimal for writes is rarely optimal for reads. A normalized schema with multiple tables, foreign keys, and ACID compliance is ideal for data integrity and efficient writes (inserts, updates), preventing duplication and anomalies. However, this structure often leads to complex, multi-table joins for read-heavy operations like dashboards and reporting, which can severely impact performance at scale.
The Read/Write Dilemma
Consider an e-commerce orders service processing 8,000 writes and 40,000 reads per minute. The write side requires a clean schema (orders, line items, payments, shipments, addresses). The read side, however, needs aggregated data for an order card or summary reports, which would involve joining these 7 tables. Basic optimizations like indexing and caching address symptoms but not the core architectural mismatch.
Evaluating Solutions
- Read Replicas: While helpful for offloading write contention, read replicas duplicate the *same* schema and query complexity. They move the performance bottleneck but don't solve the underlying impedance mismatch between read and write data shapes.
- Denormalizing the Write Schema: Flattening the primary schema for faster reads introduces significant data integrity risks. Updates to duplicated data become complex and error-prone, leading to data drift and maintenance nightmares. This is a common trap for short-term gains at the expense of long-term stability.
- GraphQL + DataLoader: These tools optimize client-side fetching by batching N+1 queries. However, they operate at the application layer and do not alleviate the complex query load on the underlying database. The database still performs expensive joins, just fewer network round trips are made.
CQRS: The Architectural Solution
CQRS (Command Query Responsibility Segregation)
CQRS is an architectural pattern that separates the model used for updating information (the command side) from the model used for reading information (the query side). This allows each side to be independently optimized.
With CQRS, writes continue to target the clean, normalized, ACID-compliant PostgreSQL database. A background process then projects this data into a separate, read-optimized store. This read store is often denormalized (e.g., Elasticsearch, a separate denormalized Postgres instance, or a NoSQL database) specifically to serve dashboards and reports with minimal joins. The key trade-off is eventual consistency, meaning the read store might lag the write store by milliseconds or seconds, which is typically acceptable for analytical or display purposes where immediate consistency is not critical.
CQRS Implementation Example
Imagine an order system. When an order is placed (write), it goes into a normalized PostgreSQL database. A change data capture (CDC) mechanism or an event handler picks up this event and transforms the data into a flat, denormalized 'OrderDisplay' object. This object is then stored in an Elasticsearch index or a separate denormalized table. When a user requests to view an order (read), the query goes directly to the optimized Elasticsearch index, retrieving all necessary order details in a single, fast lookup without any complex joins on the primary database.