CQRS in Practice: Commands, Queries, and Projections

CQRS in practice looks nothing like the conference version. The systems that work in production usually have one PostgreSQL database, thin command handlers, a handful of denormalized read tables, and background workers that keep them current. No Kafka, no event store, no twelve microservices. This guide shows the CQRS implementation we actually ship: the schemas, the Python and Go handlers, the projection rebuild process, and the cases where CQRS is the wrong tool.

Key Takeaways:

• CQRS does not require event sourcing or microservices; a modular monolith with one database is the right starting point
• Keep write models normalized and optimize read models for each query pattern, with unique constraints on read tables
• Rebuild projections via background jobs, never synchronously inside command handlers
• Publish a projection lag SLA (typically 100ms-5s) in your API docs and monitor it as a first-class metric
• Write the full-rebuild runbook (blue/green read table swap) before launch; you will need it after a bad deploy
• Split read and write databases only when read replicas and connection pooling are exhausted

Table of Contents:

• CQRS Without the Buzzwords
• Commands: Write Side
• Queries: Read Side
• Projections and Read Models
• Event Handlers and Background Jobs
• When NOT to Use CQRS
• Projection Rebuilds and Migrations
• Production Case Study: SaaS Dashboard
• CQRS vs CRUD Comparison
• Frequently Asked Questions

CQRS Without the Buzzwords

Short answer: CQRS (Command Query Responsibility Segregation) separates write operations (commands that change state) from read operations (queries that return data) — often with different models, stores, or optimization strategies for each side. The term comes from Greg Young's work and is summarized well in Martin Fowler's CQRS note; the Azure Architecture Center pattern page is the other reference worth reading before any of the framework tutorials.

Conference talks show CQRS paired with event sourcing, Kafka, and microservices. In production, most successful CQRS implementations are modest: one Postgres database, a commands table, a denormalized read view, and background workers rebuilding projections. The theory is optional; the separation is what matters.

We apply CQRS in backend API engineering engagements when read patterns diverge sharply from write patterns — dashboards needing aggregates, mobile clients needing flat DTOs, search indexes needing denormalized documents. This guide covers CQRS in practice: schemas, handlers, and the operational patterns that survive deploys.

Commands: Write Side

Commands express intent to change system state: PlaceOrder, UpdateInventory, ApproveExpense. They validate, mutate authoritative data, and optionally emit events or enqueue projection jobs.

Command Handler Pattern (Python)

from dataclasses import dataclass
from datetime import datetime, timezone
import asyncpg

@dataclass
class PlaceOrderCommand:
    customer_id: int
    items: list[dict]
    idempotency_key: str

class OrderCommandHandler:
    def __init__(self, pool: asyncpg.Pool):
        self.pool = pool

    async def handle(self, cmd: PlaceOrderCommand) -> int:
        async with self.pool.acquire() as conn:
            async with conn.transaction():
                existing = await conn.fetchval(
                    "SELECT order_id FROM command_log WHERE idempotency_key = $1",
                    cmd.idempotency_key,
                )
                if existing:
                    return existing

                total = sum(i["qty"] * i["unit_price_cents"] for i in cmd.items)
                order_id = await conn.fetchval(
                    """
                    INSERT INTO orders (customer_id, total_cents, status)
                    VALUES ($1, $2, 'placed') RETURNING id
                    """,
                    cmd.customer_id, total,
                )
                for item in cmd.items:
                    await conn.execute(
                        """
                        INSERT INTO order_lines (order_id, sku, qty, unit_price_cents)
                        VALUES ($1, $2, $3, $4)
                        """,
                        order_id, item["sku"], item["qty"], item["unit_price_cents"],
                    )
                await conn.execute(
                    """
                    INSERT INTO command_log (command_type, idempotency_key, aggregate_id, payload)
                    VALUES ('PlaceOrder', $1, $2, $3::jsonb)
                    """,
                    cmd.idempotency_key, str(order_id), {"customer_id": cmd.customer_id},
                )
                # Enqueue projection rebuild — async, not inline
                await conn.execute(
                    """
                    INSERT INTO background_jobs (queue, job_type, payload, idempotency_key)
                    VALUES ('projections', 'rebuild_order_summary', $1::jsonb, $2)
                    ON CONFLICT (queue, idempotency_key) DO NOTHING
                    """,
                    {"order_id": order_id},
                    f"proj:order_summary:{order_id}",
                )
                return order_id

Commands stay thin: validate, write authoritative state, log, enqueue side effects. Heavy denormalization does not belong here.

Use PostgreSQL FOR UPDATE SKIP LOCKED workers to process projection jobs reliably.

Queries: Read Side

Queries never mutate state. They read optimized read models — tables, views, or caches shaped for specific access patterns.

Read Model Example

-- Denormalized table for customer order history API
CREATE TABLE order_summary_read (
    order_id       BIGINT PRIMARY KEY,
    customer_id    BIGINT NOT NULL,
    customer_name  TEXT NOT NULL,
    total_cents    INT NOT NULL,
    line_count     INT NOT NULL,
    status         TEXT NOT NULL,
    placed_at      TIMESTAMPTZ NOT NULL,
    updated_at     TIMESTAMPTZ NOT NULL
);

CREATE INDEX idx_order_summary_customer
    ON order_summary_read (customer_id, placed_at DESC);

Query handler — simple, fast, no joins at request time:

async def get_customer_orders(pool, customer_id: int, limit: int = 20) -> list[dict]:
    rows = await pool.fetch(
        """
        SELECT order_id, total_cents, status, placed_at, line_count
        FROM order_summary_read
        WHERE customer_id = $1
        ORDER BY placed_at DESC
        LIMIT $2
        """,
        customer_id, limit,
    )
    return [dict(r) for r in rows]

Mobile and dashboard clients get consistent response shapes. The write-side orders + order_lines normalization remains intact for integrity.

For search-heavy read models, sync to Elasticsearch via data pipeline integrations triggered by projection jobs.

Projections and Read Models

Projections transform write-side events or authoritative rows into read models. They run asynchronously — eventual consistency is explicit and acceptable for most read paths.

Projection Worker

async def rebuild_order_summary(conn, order_id: int) -> None:
    row = await conn.fetchrow(
        """
        SELECT o.id, o.customer_id, c.name, o.total_cents, o.status, o.created_at,
               COUNT(ol.id) AS line_count
        FROM orders o
        JOIN customers c ON c.id = o.customer_id
        LEFT JOIN order_lines ol ON ol.order_id = o.id
        WHERE o.id = $1
        GROUP BY o.id, c.name
        """,
        order_id,
    )
    if not row:
        return
    await conn.execute(
        """
        INSERT INTO order_summary_read
            (order_id, customer_id, customer_name, total_cents, line_count, status, placed_at, updated_at)
        VALUES ($1, $2, $3, $4, $5, $6, $7, NOW())
        ON CONFLICT (order_id) DO UPDATE SET
            total_cents = EXCLUDED.total_cents,
            line_count = EXCLUDED.line_count,
            status = EXCLUDED.status,
            updated_at = NOW()
        """,
        row["id"], row["customer_id"], row["name"], row["total_cents"],
        row["line_count"], row["status"], row["created_at"],
    )

Go Query Service (Read-Only)

type OrderSummary struct {
    OrderID    int64     `json:"orderId"`
    TotalCents int       `json:"totalCents"`
    Status     string    `json:"status"`
    PlacedAt   time.Time `json:"placedAt"`
    LineCount  int       `json:"lineCount"`
}

func (s *OrderQueryService) ListByCustomer(ctx context.Context, customerID int64, limit int) ([]OrderSummary, error) {
    rows, err := s.readDB.Query(ctx, `
        SELECT order_id, total_cents, status, placed_at, line_count
        FROM order_summary_read
        WHERE customer_id = $1
        ORDER BY placed_at DESC LIMIT $2`, customerID, limit)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    var out []OrderSummary
    for rows.Next() {
        var o OrderSummary
        if err := rows.Scan(&o.OrderID, &o.TotalCents, &o.Status, &o.PlacedAt, &o.LineCount); err != nil {
            return nil, err
        }
        out = append(out, o)
    }
    return out, nil
}

Deploy read APIs on read replicas when query load dominates — a common scaling step before splitting databases entirely.

Event Handlers and Background Jobs

CQRS pairs naturally with domain events. After a command succeeds, publish an event — ideally via the outbox pattern:

INSERT INTO outbox (aggregate_type, aggregate_id, event_type, payload)
VALUES ('order', '8812', 'OrderPlaced', '{"orderId": 8812, "customerId": 44}');

Multiple projection handlers subscribe:

Each handler runs as an independent background job — failure in analytics does not block order confirmation UI.

Cross-aggregate workflows use the saga pattern instead of synchronous coupling between command handlers.

Consistency Contract

Document what users see:

• Command response: authoritative ID and status from write model
• Read API: may lag 100ms–5s behind write (state projection lag on dashboard)
• Critical reads: optional read-your-writes by querying write model for N seconds after mutation

Our observability & monitoring service tracks projection lag as a first-class metric.

When NOT to Use CQRS

CQRS adds complexity. Skip it when:

• CRUD app with similar read/write shapes (admin panels, simple CRUD APIs)
• Team lacks operational maturity for projection lag and rebuilds
• Domain fits in one normalized schema with acceptable query performance
• You are pre-product-market-fit — shipping beats architectural purity

Warning sign you need CQRS: You keep adding materialized views, caching hacks, and read replicas to patch slow queries caused by write-optimized schema serving read-heavy endpoints.

CQRS is not an excuse to avoid database indexing. Index the write model properly first. A materialized view with REFRESH MATERIALIZED VIEW CONCURRENTLY on a schedule is a legitimate, zero-code first step toward a read model; graduate to a projection worker when refresh cost or staleness becomes the problem.

For AI products, CQRS often separates LLM enrichment (async command side effect) from instant query APIs serving cached embeddings — see embeddings guide and RAG pipeline debugging.

Teams evaluating event-driven architecture often adopt CQRS incrementally — one aggregate at a time — rather than rewriting the entire platform.

Projection Rebuilds and Migrations

Read models drift. Schema changes, bug fixes, and new fields require full or partial projection rebuilds — an operational reality CQRS advocates rarely emphasize.

Full Rebuild Strategy

-- Blue/green read model swap
CREATE TABLE order_summary_read_v2 (LIKE order_summary_read INCLUDING ALL);
-- Add new columns to v2

-- Enqueue rebuild for all orders (batched)
INSERT INTO background_jobs (queue, job_type, payload, idempotency_key)
SELECT 'projections', 'rebuild_order_summary_v2',
       jsonb_build_object('order_id', id), 'rebuild-v2:' || id
FROM orders
WHERE id BETWEEN $1 AND $2;

Workers populate order_summary_read_v2 while v1 serves traffic. Swap tables atomically when lag reaches zero. ALTER TABLE ... RENAME is transactional in PostgreSQL, so both renames commit together:

BEGIN;
ALTER TABLE order_summary_read RENAME TO order_summary_read_old;
ALTER TABLE order_summary_read_v2 RENAME TO order_summary_read;
COMMIT;

Use background job patterns with batch enqueue to avoid inserting millions of rows in one transaction.

Incremental Catch-Up

For smaller fixes, replay events from outbox or change log since timestamp T:

async def replay_orders_since(conn, since: datetime) -> int:
    rows = await conn.fetch(
        "SELECT payload FROM outbox WHERE event_type = 'OrderPlaced' AND created_at >= $1",
        since,
    )
    for row in rows:
        await rebuild_order_summary(conn, row["payload"]["orderId"])
    return len(rows)

Track projection_version on read models to detect stale rows without full rebuilds.

Migration Without Downtime

1. Deploy new projection handler writing to v2 table
2. Backfill historical data via batch jobs (SKIP LOCKED)
3. Dual-read during validation — compare v1 vs v2 responses
4. Flip query handlers to v2
5. Drop v1 after retention window

Document rebuild runbooks before you need them at 3 AM. Data pipeline integrations teams often own warehouse projections while product teams own API read models — clarify ownership.

Production Case Study: SaaS Dashboard

A B2B SaaS platform served account dashboards from the same normalized OLTP schema as write operations. Dashboard queries joined 6 tables with aggregations — p95 query time hit 2.8 seconds at 400 concurrent users despite indexes.

CQRS adoption (incremental):

Command handlers remained unchanged except outbox inserts. Query API served only read models — no JOINs at request time.

Lessons learned:

• Explicit lag SLA in API docs prevented support escalations ("why doesn't my chart update instantly?")
• Projection bugs duplicated rows until unique constraint on (account_id, metric_date) — enforce constraints on read models
• Full rebuild script saved production when a bad deploy corrupted 12% of dashboard rows

Cross-service billing updates still use saga orchestration — CQRS does not replace distributed workflow coordination.

Connection pooling on read replicas followed our database connection pooling guide — separate pools for command and query paths.

Anti-pattern observed: Team rebuilt projections synchronously inside command handlers "for simplicity" — reintroduced 1.2s write latency and duplicated projection logic across three handlers. Moved rebuilds back to async jobs within one sprint; p95 command latency returned to 90ms.

CQRS vs CRUD Comparison

Hybrid approach: CQRS inside one service boundary — commands and queries in one codebase, one Postgres instance, async projections. Split databases later if metrics justify it.

Pair with structured output APIs when command handlers invoke LLMs — validate generated payloads before persisting.

Naming Conventions That Scale

Use verb-noun commands (PlaceOrder, CancelSubscription) and noun-focused read queries (GetCustomerOrders, SearchProducts). Handler registration maps command type strings to functions — keeps routing explicit and grep-friendly. Avoid generic UpdateRequest commands that become god-objects carrying 40 optional fields.

Store command audit logs with actor, timestamp, and payload hash for SOC2 and GDPR requests. Read models never need audit detail — query the command log directly for compliance exports. Retain command logs independently of read model retention policies.

Frequently Asked Questions

What is CQRS in simple terms?

CQRS splits writes (commands) from reads (queries) so each side can be optimized independently. Writes keep business rules and integrity; reads serve fast, tailored data shapes.

Does CQRS require event sourcing?

No. CQRS and event sourcing are often combined but independent. You can use CQRS with relational tables and background projection jobs without storing an event log as the source of truth.

Does CQRS require microservices?

No. The most maintainable CQRS systems we deploy are modular monoliths — command handlers, query handlers, and projection workers in one deployable unit.

How do I handle read-your-writes?

After a command, return authoritative data from the write model. For subsequent reads, route to write DB briefly, or block read API until projection job completes for that aggregate ID.

What is a projection in CQRS?

A projection builds or updates a read model from write-side data or events. Example: denormalized order_summary_read rebuilt whenever an order changes.

How is CQRS different from caching?

Caching duplicates query results temporarily. CQRS read models are first-class persisted views optimized for specific queries — predictable latency, no cache stampede on cold keys.

When should I split read and write databases?

When read QPS, storage, or release cadence force it — typically after exhausting read replicas and connection pooling. Most teams never need separate databases.

How does CQRS relate to background jobs?

Projections, outbox relays, and saga steps run as background jobs. Job processing patterns and SKIP LOCKED workers are the execution layer beneath CQRS.

Can I use CQRS with a monolith?

Yes — and you should start there. A modular monolith with separate command/query modules and async projections delivers most CQRS benefits without network partitions between services. Extract read-side services only when independent scaling or team boundaries require it.

How do I version command schemas?

Treat commands like API requests: additive changes only in production. Use command_version field when breaking changes are unavoidable; support N and N-1 handlers during migration windows. Command log tables provide audit trail for compliance-sensitive domains.

Conclusion

CQRS in practice means:

• Commands validate and write authoritative state — nothing else
• Queries hit read models shaped for access patterns
• Projections rebuild asynchronously with explicit lag budgets
• Outbox and job queues connect write side to downstream consumers

Skip the enterprise diagram. Start with one database, one command handler per aggregate, one read model per query pattern, and background workers for projections. Split only when production metrics — not conference slides — demand it.

At HinterBuild:

• Backend API Engineering
• Data Pipelines & Integrations
• RAG & LLM Systems

Schedule a consultation for CQRS architecture review.

Resources:

• CQRS (Martin Fowler)
• CQRS pattern (Azure Architecture Center)
• PostgreSQL ALTER TABLE
• PostgreSQL CREATE MATERIALIZED VIEW