When choosing a store for a new projection — e.g., relational table vs. search index vs. key-value cache — and deciding whether to maintain multiple projections off the same event stream, what factors drive the decision?

## What the choice actually is Choosing a store for a projection means picking the persistence technology used to hold that projection's materialized data. The core principle in CQRS is that this choice should be driven **entirely by how the data will be queried**, not by whatever technology the write side already uses — CQRS explicitly permits, and often expects, the read store to diverge from the write store, since decoupling them is the whole point of the pattern. ## The store archetypes A few common store archetypes recur and each fits a different access pattern. | Store | Access pattern it serves | |---|---| | **A relational store** | gives flexible ad hoc querying, joins, and transactional guarantees, making it a reasonable default when query patterns aren't fully settled yet or reporting-style access is needed | | **A document store** | fits data that's naturally fetched and updated as one self-contained nested unit — for example, an order together with its embedded line items, matching an API response shape one-to-one without needing joins | | **A key-value store or cache** | is built for fast, O(1)-ish point lookups by a known key, and is poor at range or secondary-attribute queries | | **A search engine** | is built for full-text search, faceting, and relevance ranking, at the cost of weaker consistency and transactional guarantees | | **A columnar or OLAP-style store** | fits analytics workloads with large scans and aggregations | | **A graph database** | fits relationship-traversal queries | Running 'multiple projections' means having separate handlers — possibly each with its own consumer group and checkpoint, even when reading the same underlying event stream — that each independently write into one of these stores. ## Why one shape rarely serves every need This exists because a single query shape rarely serves every read need well. Forcing all reads back through one general-purpose, normalized store reproduces the exact problem CQRS was meant to solve in the first place — slow joins, a shape that fits no particular feature especially well. Because adding a new projection is comparatively cheap — it's just another independent consumer of the same event stream, requiring no coordination with the write model's schema — the natural design that emerges is **'one projection per genuinely distinct query need,'** letting each feature or team own its read shape without having to negotiate schema changes with everyone else who happens to read related data. ## Benefits, and the costs that multiply The benefits are real: each projection can be scaled, cached, indexed, and evolved completely independently of the others, and can be changed or dropped without touching anything else. The costs are equally real and multiply with each additional projection: - more infrastructure to run, monitor, back up, and secure; - N separate handlers, each of which needs to be correctly idempotent and versioned, multiplying the surface area for bugs and drift; - a harder system-wide consistency story, because different projections can lag the event stream by different, uncorrelated amounts, meaning two features can present a user with two different 'current' pictures of the same underlying event at the very same moment. Building a genuinely new projection is also bounded by the rebuild/replay cost discussed elsewhere — if the event history is large, standing up a brand-new projection isn't instantaneous. ## Failure modes A handful of failure modes show up repeatedly. - **'Projection sprawl'** is common — a projection outlives the feature that needed it, nobody remembers who owns it or why it still exists, and it keeps quietly consuming infrastructure and consumer capacity. - **Two sibling projections can show contradictory numbers** in the same UI — a dashboard total from one projection disagreeing with a list count from another — purely because they lag independently, which damages user trust even though nothing is actually 'wrong.' - **Teams sometimes pick a store for familiarity rather than fit** — cramming a search feature into a relational LIKE-query projection instead of standing up a real search index — and the mismatch is often only discovered once real usage volume exposes the performance gap. - **As projection count grows**, a shared event broker or consumer capacity budget can become under-provisioned, causing all projections to start lagging together rather than independently. ## A worked example A concrete, realistic example: a ride-sharing platform might run - a relational projection for 'trip history' that supports joins and filters for a rider's account page, - a search-engine projection for support agents to search across trip and message text, - a fast key-value projection holding a driver's current live location and status for sub-second reads. All three are fed by the same underlying `TripRequested/DriverAssigned/TripCompleted` event stream. This general pattern — a shared event backbone fanning out into several purpose-built read stores — is broadly consistent with how large event-driven backends at companies like Uber and Lyft have been described in public engineering write-ups.