CQRS Pattern

Separate read and write models to optimize for different access patterns. Covers command-query separation, read model projections, event-driven synchronization, eventual consistency, and the patterns that scale read-heavy and write-heavy workloads independently.

CQRS (Command Query Responsibility Segregation) separates read and write operations into distinct models. This pattern is useful when queries and commands have different performance requirements, data shapes, or access patterns.

When to Implement CQRS

Separate Data Models for Reads and Writes

Use CQRS when write operations modify data in ways that don't align with query projections. The write model handles business logic and state changes, while the read model provides optimized views for queries.

Example: A user profile system where updating a user's email requires validation and audit logging, but reading user information needs to include computed fields like "last login time" or "account age".

High-Volume Write Workloads

Implement CQRS when write operations are frequent and complex, while reads are simpler. This allows scaling write and read operations independently.

Multiple Query Patterns

Use CQRS when different consumers need different views of the same data. For instance, a dashboard requires aggregated metrics, while an audit log needs individual transaction details.

Core Implementation

Basic Architecture

The pattern defines two separate models:

  1. Write Model: Processes commands and updates domain state
  2. Read Model: Provides optimized queries for read operations

Commands are sent to the write model, which may update a database or trigger events. Read models are updated through event handlers or periodic synchronization.

Command Handling

Commands are processed by a command handler that validates input and modifies domain state:

-- Example write model table
CREATE TABLE user_profiles (
    id UUID PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

Query Handling

Queries access a separate read model optimized for the specific query pattern:

-- Example read model table
CREATE TABLE user_profile_views (
    id UUID PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    account_age_days INTEGER,
    last_login TIMESTAMP
);

Event Sourcing Integration

CQRS often integrates with event sourcing where domain events are stored and used to update read models:

Event Storage

Events are stored in an event store or journal table:

CREATE TABLE domain_events (
    id UUID PRIMARY KEY,
    aggregate_id UUID NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    payload JSONB NOT NULL,
    occurred_at TIMESTAMP NOT NULL
);

Event Handlers

Event handlers update read models when events occur:

# Example event handler
def handle_user_email_changed(event):
    # Update read model
    cursor.execute(
        "UPDATE user_profile_views SET email = %s WHERE id = %s",
        (event.payload['new_email'], event.aggregate_id)
    )

Synchronization Strategies

Real-time Updates

Use event-driven updates to keep read models current:

-- Event subscription table
CREATE TABLE event_subscriptions (
    id UUID PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    handler_url TEXT NOT NULL,
    last_processed_event_id UUID
);

Batch Updates

For less urgent updates, use periodic synchronization:

# Example batch update script
#!/bin/bash
psql -c "REFRESH MATERIALIZED VIEW user_profile_views"

Configuration Options

Read Model Refresh Strategy

Configure how often read models are updated:

Event Processing Mode

Configure event handling behavior:

-- Configuration table
CREATE TABLE cqrs_config (
    key VARCHAR(100) PRIMARY KEY,
    value TEXT NOT NULL
);

INSERT INTO cqrs_config VALUES 
    ('read_model_refresh_strategy', 'periodic'),
    ('event_processing_mode', 'synchronous');

Error Handling

Write Model Failures

Write operations may fail due to validation errors or concurrency issues:

-- Example error handling in write model
BEGIN;
UPDATE user_profiles SET email = '[email protected]' WHERE id = 'uuid';
-- Handle constraint violation
EXCEPTION WHEN unique_violation THEN
    RAISE EXCEPTION 'Email already exists';
END;

Read Model Synchronization Errors

Read models may fall out of sync due to event processing failures:

-- Example recovery mechanism
UPDATE event_subscriptions 
SET last_processed_event_id = 'event_uuid' 
WHERE name = 'user_profile_handler';

Common Failure Modes

Event Handler Crashes

When an event handler crashes, it can leave the read model inconsistent:

# Monitor for failed events
psql -c "
SELECT count(*) FROM domain_events 
WHERE processed_at IS NULL 
AND occurred_at < now() - interval '1 hour'
"

Read Model Inconsistency

Read models may become stale if event processing fails:

-- Check read model freshness
SELECT 
    max(occurred_at) as last_event_time,
    max(updated_at) as last_update_time
FROM domain_events de
JOIN user_profile_views upv ON de.aggregate_id = upv.id;

Command Validation Failures

Commands may fail validation without proper error reporting:

# Example command validation
def process_update_email_command(command):
    if not command.email:
        raise ValueError("Email cannot be empty")
    # Proceed with business logic

Performance Considerations

Write Throughput

The write model should be optimized for transactional performance:

-- Index optimization for write operations
CREATE INDEX idx_user_profiles_email ON user_profiles(email);

Read Performance

Read models should be optimized for query patterns:

-- Index optimization for read operations
CREATE INDEX idx_user_profile_views_account_age ON user_profile_views(account_age_days);

Data Consistency Patterns

Eventually Consistent Reads

Read models may temporarily show stale data:

-- Example of checking consistency
SELECT 
    wp.email as write_model_email,
    rv.email as read_model_email
FROM user_profiles wp
JOIN user_profile_views rv ON wp.id = rv.id
WHERE wp.email != rv.email;

Consistency Boundaries

Define when read models should be consistent with writes:

-- Configuration for consistency requirements
CREATE TABLE consistency_rules (
    model_name VARCHAR(100),
    consistency_level VARCHAR(20) CHECK (consistency_level IN ('eventual', 'immediate')),
    tolerance_minutes INTEGER
);

Monitoring and Observability

Event Processing Metrics

Monitor how many events are processed and how long they take:

-- Query event processing times
SELECT 
    event_type,
    count(*) as event_count,
    avg(extract(epoch from (processed_at - occurred_at))) as avg_processing_time_seconds
FROM domain_events 
WHERE processed_at IS NOT NULL
GROUP BY event_type;

Read Model Health

Check if read models are up to date:

-- Check for outdated read models
SELECT 
    count(*) as stale_records,
    max(occurred_at) as latest_event_time
FROM domain_events de
LEFT JOIN user_profile_views upv ON de.aggregate_id = upv.id
WHERE de.occurred_at > upv.updated_at OR upv.updated_at IS NULL;

Implementation Patterns

Command-Query Separation

Separate command and query handling logic:

# Command handler
class UpdateUserEmailHandler:
    def handle(self, command):
        # Validate and update domain state
        pass

# Query handler  
class GetUserProfileQueryHandler:
    def handle(self, query):
        # Return read-optimized data
        pass

Event Projection Management

Manage how events update read models:

-- Event projection table
CREATE TABLE event_projections (
    name VARCHAR(100) PRIMARY KEY,
    last_processed_event_id UUID,
    last_processed_at TIMESTAMP,
    status VARCHAR(20)
);

Migration Strategies

Gradual Migration

Gradually move from a single model to CQRS:

-- Add read model alongside existing write model
CREATE TABLE user_profile_read_model AS 
SELECT * FROM user_profiles WHERE false;

Data Synchronization

Ensure data consistency during transition:

-- Synchronize existing data
INSERT INTO user_profile_read_model 
SELECT * FROM user_profiles;

Deployment Considerations

Separate Services

Deploy write and read models as separate services:

# Example service configuration
write-model:
  replicas: 3
  resources:
    cpu: "500m"
    memory: "512Mi"

read-model:
  replicas: 2
  resources:
    cpu: "250m"
    memory: "256Mi"

Resource Allocation

Allocate resources based on workload patterns:

# Monitor resource usage
kubectl top pods | grep cqrs

Testing Patterns

Write Model Testing

Test command processing and domain logic:

def test_update_email_command():
    # Setup
    handler = UpdateUserEmailHandler()
    
    # Execute
    result = handler.handle(UpdateEmailCommand(user_id='123', email='[email protected]'))
    
    # Verify
    assert result.success

Read Model Testing

Test query projections and data consistency:

def test_user_profile_query():
    # Setup
    query_handler = GetUserProfileQueryHandler()
    
    # Execute
    profile = query_handler.handle(GetUserProfileQuery(user_id='123'))
    
    # Verify
    assert profile.email == '[email protected]'

Production Considerations

Recovery Procedures

Handle event processing failures:

# Restart event processor
systemctl restart cqrs-event-processor

# Check status
systemctl status cqrs-event-processor

Rollback Strategy

Revert to single model if needed:

-- Drop read model tables
DROP TABLE user_profile_views;

Backup and Recovery

Ensure both write and read models are backed up:

# Backup write model data
pg_dump -t user_profiles mydb > write_model_backup.sql

# Backup read model data  
pg_dump -t user_profile_views mydb > read_model_backup.sql

The CQRS pattern provides a clean separation between data modification and data access patterns, enabling independent scaling and optimization of each. The sharp edges include event consistency, read model staleness, and the complexity of maintaining two separate data models.

This page was rewritten on 15 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.