← Back to the section

In an ordinary application, the same code reads and writes data. At first everything is simple, but then tension appears: a write query needs one set of indexes, a read query — another. The JOIN for writes and the JOIN for reads start getting in each other's way. The table grows, and no operation works optimally.

CQRS solves this by separation: data is stored in a form convenient for writing (aggregates, a normalized schema), and a separate representation is created for reading — the read-model. It's a pre-prepared, denormalized projection: a single SELECT without a JOIN returns a ready object for the UI or API.

In Go the reading side's contract is expressed through an <X>ViewRepository interface; sqlc generates the implementation for it.

Why read data looks different

Take an order. In the write schema it's spread across several tables:

order(id, customer_id, status)
order_item(order_id, qty, price)
customer(id, name, email_hash)

To show the order card to the user, you need a JOIN of three tables. At thousands of requests per second that's noticeable.

The read-model folds all of this into a single table in advance:

order_summary(
    order_id,
    customer_name,      ← already inserted from customer, no join needed
    customer_email_hash,
    status,
    item_count,         ← counted from order_item in advance
    total_amount        ← also precomputed
)

Now SELECT * FROM order_summary WHERE order_id = $1 — and you're done. No joins.

The price is that data isn't always up to date instantly: between the write on the write side and the read-model update, a short time passes (usually 100ms–1s). This is called eventual consistency. In most cases this is acceptable.

Where to store the read-model

The store is chosen to match the read pattern, not "one universal one".

A denormalized PG table is almost always the first option. It works with the standard relational database that's already in the project. Good for tabular queries with pagination, filtering, and sorting.

-- migration: create the read table for orders
CREATE TABLE order_summary (
    order_id       uuid PRIMARY KEY,
    customer_id    uuid NOT NULL,
    customer_name  text NOT NULL,
    status         text NOT NULL,
    item_count     int  NOT NULL,
    total_amount   bigint NOT NULL,
    currency       text NOT NULL,
    created_at     timestamptz NOT NULL,
    confirmed_at   timestamptz,
    updated_at     timestamptz NOT NULL,
    version        bigint NOT NULL DEFAULT 0
);
CREATE INDEX ix_os_customer    ON order_summary (customer_id, created_at DESC);
CREATE INDEX ix_os_status_date ON order_summary (status, created_at DESC);

The version field is needed so that processing a repeated event doesn't overwrite fresher data.

A PG materialized view — when you need heavy aggregation that's too expensive to recompute on the fly. For example, revenue by product over the last month. It's refreshed with REFRESH MATERIALIZED VIEW CONCURRENTLY — either on a schedule or by event.

CREATE MATERIALIZED VIEW product_revenue_daily AS
SELECT
    p.product_id,
    p.name,
    DATE(oi.created_at) AS day,
    SUM(oi.quantity * oi.unit_price) AS revenue,
    COUNT(DISTINCT o.id) AS order_count
FROM order_item oi
JOIN product p ON p.product_id = oi.product_id
JOIN "order" o ON o.id = oi.order_id
WHERE o.status IN ('CONFIRMED', 'SHIPPED', 'DELIVERED')
GROUP BY p.product_id, p.name, DATE(oi.created_at);

CREATE UNIQUE INDEX ux_prd_pk ON product_revenue_daily (product_id, day);

Redis — when latency is critical. Suitable for data that's read on every request by key: for example, the customer's current pricing plan. An important difference from an ordinary cache: a read-model in Redis is the primary source of the answer, not a fallback on a miss.

type CustomerPlanCache struct {
    rdb *redis.Client
}

func (c *CustomerPlanCache) Get(ctx context.Context, customerID string) (CustomerPlanDTO, error) {
    raw, err := c.rdb.Get(ctx, fmt.Sprintf("customer:%s:plan", customerID)).Bytes()
    if err != nil {
        return CustomerPlanDTO{}, fmt.Errorf("get plan %s: %w", customerID, err)
    }
    var plan CustomerPlanDTO
    if err := json.Unmarshal(raw, &plan); err != nil {
        return CustomerPlanDTO{}, fmt.Errorf("unmarshal plan: %w", err)
    }
    return plan, nil
}

ElasticSearch — for full-text search and complex filters with relevance. When you need search across several fields at once, ranking of results, or faceted filtering.

The ViewRepository interface

The read side in Go is described by a separate interface — independent of the write side's OrderRepository. This matters: different interfaces can evolve independently, be tested independently, and have their implementation swapped independently.

// core/order/port/view/order_view_repository.go
type OrderViewRepository interface {
    SummaryByID(ctx context.Context, orderID string) (viewdto.OrderSummaryDTO, error)
    ListByCustomer(ctx context.Context, customerID string, page Pagination) ([]viewdto.OrderSummaryDTO, error)
}

// core/order/dto/view/order_summary.go
type OrderSummaryDTO struct {
    OrderID      string
    CustomerID   string
    CustomerName string     // denormalized from customer
    Status       string
    ItemCount    int
    TotalAmount  int64
    Currency     string
    CreatedAt    time.Time
    ConfirmedAt  *time.Time
}

The DTO is structured for the UI's or API's needs — it doesn't mirror the aggregate one-to-one. sqlc generates the OrderViewRepository implementation directly from SQL queries.

The query handler reads only through the ViewRepository

The query handler works in a read-only transaction. pgx.TxOptions{AccessMode: pgx.ReadOnly} isn't just a marker: pgx will fail with an error if you try to write anything in such a transaction.

func (h *GetOrderSummaryHandler) Handle(ctx context.Context, q query.GetOrderSummary) (view.OrderSummaryDTO, error) {
    tx, err := h.db.BeginTx(ctx, pgx.TxOptions{AccessMode: pgx.ReadOnly})
    if err != nil {
        return view.OrderSummaryDTO{}, fmt.Errorf("begin read tx: %w", err)
    }
    defer tx.Rollback(ctx)

    summary, err := h.views.SummaryByID(ctx, q.OrderID)
    if err != nil {
        return view.OrderSummaryDTO{}, fmt.Errorf("read order summary %s: %w", q.OrderID, err)
    }
    return summary, nil
}

Updating via events, not synchronously

A frequent mistake is to update the read-model right inside the write transaction:

// don't do this
tx.Exec(ctx, "UPDATE order SET status = $1 ...", status)
tx.Exec(ctx, "UPDATE order_summary SET status = $1 ...", status) // coupling write to read

There are several problems at once:

  • A rollback of the write transaction won't roll back the already-applied change in the read DB if they're in different databases.
  • ALTER TABLE order_summary starts blocking write transactions — the two stores become entangled.
  • With different databases, synchronous sync requires two-phase commit, which is extremely complex and often forbidden in the architecture.

The right path is via events. The command handler saves the change and puts an event into the outbox table in the same transaction. A separate relay reads the outbox and publishes to Kafka. A consumer on the read side receives the event and updates the read-model.

command handler → saves Order + puts OrderConfirmed into the outbox (one pgx.Tx)
outbox relay → publishes the event to Kafka
read-side consumer → receives OrderConfirmed → UPDATE order_summary

// writing the event to the outbox — in the same transaction as the aggregate change
func (r *OrderOutboxRepository) Enqueue(ctx context.Context, tx pgx.Tx, evt OrderConfirmedEvent) error {
    payload, err := json.Marshal(evt)
    if err != nil {
        return fmt.Errorf("marshal event: %w", err)
    }
    _, err = tx.Exec(ctx,
        `INSERT INTO outbox (event_type, payload, created_at) VALUES ($1, $2, now())`,
        "order.confirmed", payload,
    )
    return err
}

The delay between the write and the read-model update is usually 100ms–1s. With Kafka problems it can be longer. This needs to be declared at the API level, so the UI isn't surprised.

More on event delivery and idempotent consumers in the article Sync via events.

How to rebuild the read-model from scratch

The read-model is a projection. The source of truth is the write side. This means the read-model can always be restored by walking over the aggregates and rebuilding the projection again.

For this you need a separate script (usually cmd/rebuild/main.go), unrelated to the main HTTP server.

func rebuildOrderSummaries(ctx context.Context, db *pgxpool.Pool, summaries OrderSummaryRepository) error {
    const batchSize = 500
    var lastID string

    for {
        orders, err := loadOrdersBatch(ctx, db, lastID, batchSize)
        if err != nil {
            return fmt.Errorf("load batch after %s: %w", lastID, err)
        }
        if len(orders) == 0 {
            break
        }
        for _, o := range orders {
            if err := summaries.Upsert(ctx, nil, toSummary(o)); err != nil {
                return fmt.Errorf("upsert summary for order %s: %w", o.ID, err)
            }
        }
        lastID = orders[len(orders)-1].ID
        slog.Info("rebuild progress", "last_id", lastID, "batch", len(orders))
    }
    return nil
}

When you need this:

  • Recovery after a failure. The Redis cluster went down, the read table was accidentally dropped, a migration to another store happened.
  • Connecting a new store. You added ElasticSearch — it's empty; you need to load existing data.
  • Changing the read-model schema. You added a field to order_summary — old records don't have it; the rebuild fills it in.

If there's no such script, the read-model de facto becomes the source of truth — and that violates the very idea of CQRS.

Common mistakes

Business logic in the read table. CHECK constraints with business invariants belong not in the read table but in the aggregate. The read-model is only a projection of data, there's no logic in it.

A reverse data flow. The flow is always one-way: write → events → read. You never read from the read-model to make a business decision on the write side.

A single repository for read and write. With an event-driven read-model you need a separate OrderViewRepository. A single OrderRepository for both sides breaks the separation.

In short

  • The read-model is a denormalized projection of data, optimized for a specific read pattern. A single SELECT without a JOIN.
  • The store is chosen for the job: a PG table for tabular queries, a materialized view for heavy aggregations, Redis for fast lookups by key, ElasticSearch for full-text search.
  • The read-model schema is independent of the write schema. Denormalization is its main tool.
  • Updating happens only via events (outbox + Kafka). A synchronous UPDATE in the write transaction breaks the separation and creates problems on rollback.
  • The read-model is always restorable from the write side. For this you need a separate rebuild script.
  • The source of truth is the write side. The read-model is derived from it.
  • Sync via events — how outbox + kafka-go delivers events to the read-model, idempotent consumer.
  • Query side — how the query handler reads from the read-model via pgx.ReadOnly.
  • Command side — how the command handler writes to the outbox in the same pgx transaction.
  • CQRS tier and evolution — when to move to an event-driven read-model.