Skip to content

CQRS vs Traditional CRUD

1. Decision

Choose Traditional CRUD for simple applications where read and write data models map 1:1 to the same relational tables; choose CQRS (Command Query Responsibility Segregation) for complex domain systems where read and write workloads have radically different performance, scaling, structural, or authorization requirements.

2. 30-Second Answer

Traditional CRUD (Create, Read, Update, Delete) uses a single unified data model and database schema for both reading and writing. A domain entity (e.g., Order) is loaded, modified, and persisted using the same relational table or document. While simple and easy to maintain, CRUD creates performance bottlenecks when read queries require complex N-table joins, aggregations, or specialized search indexes that clash with normalized write schemas. CQRS explicitly splits the architecture into two distinct paths: Command Model (handles state mutations via INSERT/UPDATE optimized for write integrity and validation) and Read Model (handles queries using denormalized, pre-aggregated storage like Elasticsearch or Redis optimized for sub-millisecond reads).

3. Decision Matrix

Option Best For Avoid When Latency Scalability Consistency Operational Complexity
Traditional CRUD Simple web applications, standard administrative backends, early-stage MVPs, 1:1 data models Asymmetric read/write scale (>100:1), complex multi-table analytical joins, search engine integration Low for point reads/writes; High for complex joins Bounded by single database read/write schema constraints Immediate ACID (Single database engine) Low
CQRS (Command Query Responsibility Segregation) High-scale systems with radical read/write asymmetry, complex domain models, search engine integrations, Event Sourcing Simple CRUD domain entities with matching read/write payloads Sub-millisecond Read (Denormalized Read Model); Low Command Write Extremely High (Scale Write DB and Read Model independently) Eventual Consistency (Async synchronization between Command and Read models) High (Dual models, CDC/Event bus, eventual consistency sync)

4. Selection Rules

Requirement Best Choice Rationale / Example
Simple Web Application CRUD Operations Traditional CRUD User profile or product catalog where read payload matches written table structure
Massive Read/Write Asymmetry (1000:1 Reads) CQRS Command writes hit PostgreSQL OLTP; Read model populates Elasticsearch & Redis
Complex Aggregations & Search Engine Filtering CQRS Denormalize complex multi-table relationships into pre-aggregated JSON read models
Pairing with Event Sourcing Architecture CQRS Event Sourcing stores raw event streams (Commands); CQRS projects events into Read Views
Avoid Distributed System Overhead in MVP Traditional CRUD Keep single database engine; optimize reads using standard SQL indexes and views

5. Tradeoffs

  1. Optimized Read Performance vs Architecture Complexity
  2. Gain (CQRS): Read queries fetch pre-aggregated denormalized JSON models in sub-milliseconds without joins.
  3. Lose (CQRS): Must manage two separate data models, schemas, and synchronization pipelines.
  4. Independent Read/Write Scaling vs Eventual Consistency Lag
  5. Gain (CQRS): Scale Read models (Elasticsearch/Redis) to 100k QPS without overloading Write DB.
  6. Lose (CQRS): Updates written to Command DB take time (10-100ms) to reflect in Read models (stale reads).
  7. Single Domain Model vs Code Duplication
  8. Gain (CRUD): Single set of DTOs, tables, and ORM entities to maintain.
  9. Lose (CRUD): Write normalization forces expensive runtime joins for read queries.
  10. Command Validation Simplicity vs Projection Re-Building
  11. Gain (CQRS): Commands execute strict domain business validation logic cleanly.
  12. Lose (CQRS): If Read Model schema changes, entire read database must be re-projected from Command events.
  13. Operational Infrastructure Cost
  14. Gain (CRUD): Operates on a single relational database instance (e.g. PostgreSQL).
  15. Lose (CQRS): Requires running message brokers (Kafka), CDC pipelines (Debezium), and secondary read stores.

6. Interview Counter Questions

  1. What is the fundamental principle of CQRS and how does it separate Commands from Queries?
  2. How is data synchronized between the Command Model and the Read Model in CQRS?
  3. What is the difference between CQRS and Event Sourcing (why are they often paired)?
  4. How do you handle Stale Reads (Eventual Consistency) in user interfaces when using CQRS?
  5. What is a Projection in CQRS architecture?
  6. When is implementing CQRS an anti-pattern (Over-Engineering)?
  7. How do you rebuild a corrupted CQRS Read Model from scratch?
  8. How does Command validation differ from Query execution in CQRS?
  9. Can CQRS be implemented within a single relational database without separate physical databases?
  10. How do you handle authorization in Command models versus Query models?

7. Gold Standard Answers

  1. CQRS segregates state-mutating operations (Commands: CreateOrder, return void or ID) from data-retrieval operations (Queries: GetOrderDetails, return DTOs without side effects).
  2. Synchronized asynchronously via Change Data Capture (Debezium CDC reading Command DB WAL) or Domain Events published to Kafka. Event handlers consume events and update the Read Model (Redis/Elasticsearch).
  3. CQRS: Separates read and write data models. Event Sourcing: Stores state changes as an immutable sequence of events instead of current state. They pair well: Event Store = Command Model; Projections = Read Model.
  4. UI patterns: (1) Optimistic UI updates (UI updates locally immediately), (2) Return updated entity state in Command response, (3) Poll or use WebSockets for confirmation, (4) Read-Your-Own-Writes routing.
  5. A Projection is an event-handler function that listens to Command events (OrderPlaced, ItemAdded) and computes/updates a specific Read Model view optimized for a specific query screen.
  6. Anti-pattern for standard CRUD applications where read and write models are identical, team size is small, or eventual consistency is unacceptable to business stakeholders.
  7. Replaying: Drop the corrupted Read Model index/table, replay all historical Command events from the beginning of the event log (or CDC WAL), and re-project data into the new Read Model.
  8. Commands execute rich business domain validation (checking account limits, inventory rules) and mutate state. Queries execute zero business validation or side effects; they simply fetch and project raw data.
  9. Yes! "Logical CQRS": Use standard PostgreSQL tables for Commands, and PostgreSQL Materialized Views or separate denormalized read tables updated via triggers for Queries within 1 DB.
  10. Commands validate write authorization (e.g., "Can User X modify Order Y?"). Queries validate read access filtering (e.g., "Return only orders where tenant_id = X").

8. Common Mistakes

  • Applying CQRS to simple CRUD applications where read and write payloads are identical.
  • Returning modified entity data payloads from Command handlers (Commands should return void or simple IDs).
  • Synchronously updating the Read Model inside the Command execution thread, losing performance benefits.
  • Failing to build automated projection replay scripts to recover from corrupted Read Models.
  • Expecting instant strong consistency between Command writes and Read Model queries without handling stale reads.

9. Keywords

CQRS, Command, Query, Traditional CRUD, Command Model, Read Model, Event Sourcing, Projection, Eventual Consistency, Debezium CDC, Materialized Views, Optimistic UI, Read-Your-Own-Writes, Re-projection.

10. Cheat Sheet

  • Core Concept: Split Write Operations (Commands: CreateOrder, mutate state, no return data) from Read Operations (Queries: GetOrder, return data, no side effects).
  • Architecture: Command Model (PostgreSQL OLTP - Write validation) \(\xrightarrow{\text{Async CDC / Kafka}}\) Read Model (Elasticsearch / Redis - Denormalized sub-ms reads).
  • Pairing: Frequently paired with Event Sourcing (Event Store = Command Model; Projections = Read Model).
  • Projections: Event handlers that process Command events to build pre-aggregated read views.
  • Stale Read Fix: Use Optimistic UI Updates or return the newly created ID/payload directly in the Command response.
  • Logical CQRS: Can be implemented inside 1 PostgreSQL DB using write tables + denormalized read tables/materialized views.

11. Related Decisions