CQRS With Read Models:
Stop Making One Database Do Two Jobs

Picture a typical business-to-business order platform on one Amazon Aurora PostgreSQL cluster. Order entry runs short transactions: insert an order, update stock, write an audit row. On a normal day, the 99th percentile for placing an order is 80 milliseconds.

On the first business day of every month, it is four seconds.

Nothing changed in the order code. What changed is that every customer's finance team opens the monthly dashboard on the same morning, and the dashboard runs aggregations across a year of orders on the same primary instance that takes the writes. Customer service is searching orders with ILIKE '%smith%' at the same time. The scans fill the buffer cache with old pages, compete for I/O, and hold the instance's CPU. Order entry waits behind them.

The team's fix, twice, is a larger instance class. That works until the next month's dashboard has more data.

This is a composite scenario, not one client's database. On a primary in this state, Performance Insights shows the top waits coming from queries that do not need to run there at all.

Two Jobs, One Schema

A transactional schema is designed for writes. It is normalized so each fact lives in one place, it carries constraints that keep data valid, and its indexes are the few that make writes and point lookups fast.

Reads want almost the opposite. A dashboard wants pre-aggregated totals by customer and month. Search wants tokenized text and facets. A mobile list view wants one denormalized row per order with the customer's name already on it. Every index added for those reads slows every write, and every large scan competes with the short transactions that make money.

So teams compromise. They add indexes until inserts slow down, add a materialized view and refresh it on a schedule, then scale the primary up because it is the only lever left. The schema ends up serving neither job well.

The underlying problem is that one model is being asked to answer two different questions: "Is this change valid?" and "What does the data look like from this angle?"

Commands, Changes, and Projections

Command Query Responsibility Segregation (CQRS) separates those questions. Its useful form on AWS has four parts.

The write model accepts commands (place an order, cancel it, change an address), validates them, and records the change. It stays small, normalized, and fast. It answers no reporting queries.

A change stream carries every change out of the write model, in order per item. On Amazon DynamoDB, that is DynamoDB Streams. On Aurora, it is either zero-ETL integration into Amazon Redshift for analytics, or a transactional outbox: the transaction that changes the order also inserts an event row, and a relay publishes it.

A projector consumes the stream and maintains each read model. It must be idempotent and must tolerate events that arrive late or twice, so every change carries a version and the projector writes only if the version is newer than what the read model already holds.

Read models are shaped for their queries and stored in the right engine: Amazon OpenSearch Service for search, a DynamoDB table keyed by customer and month for the dashboard, Redshift for analysts. Queries go only to them.

Living with lag

A read model is behind the write model by however long the projector takes, normally well under a second and sometimes much longer. Three practices make that acceptable.

Measure the lag and alarm on it. For a Lambda projector on a stream, the IteratorAge metric measures the time between the stream receiving a record and Lambda handing it to the function. It is the read side's freshness, and it deserves a service level of its own.

Read your own writes from the write model. After a customer places an order, the confirmation page reads that one order by key from the write model. The order list can catch up a moment later.

Keep the ability to rebuild. A read model is disposable by design. Keep the history (an event archive in Amazon S3, or a DynamoDB export to S3) so you can build a new read model, or repair a broken one, from scratch.

The stream will stop on a bad record

A detail that bites teams the first week: by default, Lambda retries a failed batch from a DynamoDB stream until it succeeds or the records expire, and the stream keeps records for 24 hours. One record the projector cannot handle stops that shard's projection for up to a day. Set MaximumRetryAttempts, turn on BisectBatchOnFunctionError so the bad record is isolated, and send failures to an on-failure destination. That is the same lesson as dead-letter queues in post 6.

Building It on AWS

A DynamoDB table with Streams, a Lambda projector, and one or two read models is the common starting shape.

The projector's core is a conditional write that refuses to move a read model backward:

def handler(event, context):
    for rec in event["Records"]:
        seq = rec["dynamodb"]["SequenceNumber"]
        try:
            new = deserialize(rec["dynamodb"].get("NewImage", {}))
            if rec["eventName"] == "REMOVE":
                summary.delete_item(Key=key_of(rec))
                continue
            summary.put_item(
                Item=to_summary(new),
                ConditionExpression="attribute_not_exists(pk) OR version < :v",
                ExpressionAttributeValues={":v": new["version"]},
            )
        except summary.meta.client.exceptions.ConditionalCheckFailedException:
            continue                   # a newer version is already projected
        except Exception:
            return {"batchItemFailures": [{"itemIdentifier": seq}]}
    return {"batchItemFailures": []}

The early return reports the first record that failed. Lambda honors that response only when the event source mapping lists ReportBatchItemFailures in its FunctionResponseTypes; it then checkpoints at that sequence number and retries from that record onward, so the records before it are not processed again.

The cheaper first step: a replica

Before building any of that, ask whether one schema still fits the reads and only the load is the problem. If so, an Aurora read replica and the cluster's reader endpoint move the dashboard and search off the primary in an afternoon. It is CQRS in its lightest form: separate read capacity, same shape. Move to projected read models when the reads need a different shape or a different engine, not before.

When Not to Use It

The reads match the writes. A CRUD application whose screens show records much as they are stored gains nothing from a second model and pays for a projector. Index the table well and stop there.

Every read must see the latest write. A balance check before a withdrawal or a seat map at the moment of booking must read the write model. CQRS is for reads that can be a second behind.

The team cannot operate two stores and a projector. CQRS adds a pipeline that can lag, fail, and need rebuilding. If no one will own projector lag as a metric, a read replica is the honest choice.

How We Check for It

This sits in the Performance Efficiency pillar, under PERF03, "How do you store, manage, and access data in your workload?", and its best practice PERF03-BP01, "Use a purpose-built data store that best supports your data access and storage requirements." The asynchronous projector falls under Reliability REL04-BP02, "Implement loosely coupled dependencies."

In a Vantalect review, we look at:

• The top queries by load on the primary in Performance Insights, and which of them are reports, dashboards, or search.
• Whether reporting or analytics runs against the primary instance.
• Projector lag as a metric, with an alarm and an owner.
• Whether the stream's event source mapping has bounded retries and an on-failure destination.
• Whether one read model has ever been rebuilt from scratch, and how long it took.

The Well-Architected Review checks these in your live accounts.

Related Patterns

• Event Fan-Out With Dead-Letter Queues: a projector is one more subscriber to the same events.
• Cache-Aside With Request Coalescing: when one hot query needs speed, not a new shape, a cache may be enough.
• Queue-Based Load Leveling: projectors drain at their own pace for the same reason.
• Observability That Answers Questions: projector lag is a service level for the read side.

This post is part 7 of Cloud Design Patterns.