# Data Store Selection: [Workload or service]

> Building-block decision record for choosing where a workload's data
> lives: relational, key-value, document, wide-column, search engine,
> time-series, graph, object storage or vector store. It describes the
> workload, applies a weighted decision matrix, checks the shortlist
> against consistency, scale, operations and cost, and records the
> decision and its exit plan. The categories and the matrix are an
> original method of this collection; for vector search it refers to the
> HNSW paper (Malkov and Yashunin, IEEE TPAMI, 2020). Replace every
> `[bracketed]` value and every row marked *Example*. Worked examples use
> Quillmere, an invented book-review and reading-list platform shared by
> the other building-block templates in this category.

<!--
Guidance: How to use this template
- Describe the workload before naming products. Most bad choices start
  from a favourite technology and work backwards to the requirements.
- Default to the store the team already runs well. A second database
  type doubles on-call knowledge, backups, upgrades and security reviews;
  the matrix must show a clear gain to justify it.
- Use one record per workload. A product usually needs more than one
  store (polyglot persistence), but each choice should stand on its own
  evidence.
- Score categories first (section 3), then specific products (section 4).
  Product facts such as licence, managed offerings and limits change;
  date them and check the vendor's current documentation.
-->

## Document control

<!-- Guidance: Include the team that will operate the store; selection and operation are often split and the operating team carries the cost. -->

| Field | Value |
|---|---|
| Document ID | [STORE-REC-001] |
| Workload | [*Example:* Book reviews: write, edit, list by book, list by author, moderate] |
| Decision type | [New workload / Migration / Additional store] |
| Version | [0.1] |
| Status | [Draft / In review / Approved / Implemented / Superseded] |
| Owner | [@reviews-lead] |
| Operating team | [@data-platform-lead] |
| Reviewers | [@sre], [@security], [@finance-partner] |
| Last updated | YYYY-MM-DD |
| Classification | [Internal] |
| Related | Database Design (`architecture/database-design`), Build vs Buy Analysis (`architecture/build-vs-buy-analysis`), Replication and Consistency Models (`system-design/replication-consistency-models`) |

### Approvals

| Role | Name | Decision | Date |
|---|---|---|---|
| Workload owner | [@name] | [Approved / Changes requested] | YYYY-MM-DD |
| Operating team | [@name] | [Decision] | YYYY-MM-DD |
| Architecture review | [@name] | [Decision] | YYYY-MM-DD |

### Revision history

| Version | Date | Author | Change summary |
|---|---|---|---|
| 0.1 | YYYY-MM-DD | [@name] | First draft |
| 0.2 | YYYY-MM-DD | [@name] | *Example:* Search moved to a separate index after full-text requirement |

## 1. Workload profile

<!-- Guidance: Quantify the workload. Volume, growth, read/write mix, latency targets, query shapes and consistency needs decide the category; product preferences do not. -->

| Attribute | *Example* value |
|---|---|
| Records today | 50,000,000 reviews |
| Average record size | 1.5 KB |
| Raw data | 50,000,000 x 1.5 KB = 75 GB; 225 GB with 3 replicas |
| Growth | 30% per year |
| Peak reads | 12,000 per second |
| Peak writes | 400 per second |
| Main queries | By book (sorted by helpfulness or date), by author, by moderation status |
| Full-text search | Yes, within reviews of a book and across all reviews |
| Transactions | Review and rating summary updated together |
| Consistency | Author reads own review immediately; others may lag seconds |
| Retention | Kept while account is active; deleted within 30 days of erasure request |

## 2. Store categories

<!-- Guidance: Use this table to shortlist categories. A workload often needs a primary store plus derived stores (search index, cache, analytics copy) fed from it. -->

| Category | Data model | Strong at | Weak at | Typical fit |
|---|---|---|---|---|
| Relational | Tables, joins, SQL, multi-row transactions | Integrity constraints, ad hoc queries, transactions | Horizontal write scaling without sharding | Orders, accounts, most business records |
| Key-value | Value by key | Very high throughput, simple scaling | Queries other than by key | Sessions, carts, feature flags, caches |
| Document | Nested documents by key, secondary indexes | Flexible schema per record, read whole aggregate | Cross-document transactions and joins (varies by product) | Catalogue items, user profiles |
| Wide-column | Rows of sparse columns grouped by partition key, sorted by clustering key | Large write volume, time-ordered data per key | Ad hoc queries, joins | Event histories, messaging, IoT per device |
| Search engine | Inverted index, relevance ranking | Full-text, faceting, fuzzy match | Primary system of record, transactions | Product and content search |
| Time-series | Timestamped points, downsampling, retention | High-rate ingestion, range aggregation over time | Updates, joins with business data | Metrics, sensor data |
| Graph | Nodes and edges with properties | Multi-hop traversals | Bulk analytics over all data, wide scans | Fraud rings, social graph queries |
| Object storage | Immutable blobs by key | Cheap, very durable large files | Small random updates, low-latency queries | Media, backups, data lake files |
| Vector | Embeddings with approximate nearest-neighbour index | Similarity search, retrieval for AI features | Exact filtering at scale, transactions | Recommendations, semantic search |

Shortlist for this workload: [*Example:* Relational, document and
wide-column as primary store. Search engine as a derived index for
full-text queries, fed by a change stream.]

## 3. Weighted decision matrix

<!-- Guidance: Agree weights before scoring (they sum to 100) and score each option 1 (poor) to 5 (excellent) with a one-line reason. The weighted score is the sum of weight times score, divided by 100. Pitfall: changing weights after seeing the totals. -->

| Criterion | Weight | Relational | Document | Wide-column |
|---|---|---|---|---|
| Query fit (by book, by author, sorted, moderation) | 30 | 4 | 4 | 2 |
| Consistency and transactions (review plus summary) | 20 | 5 | 3 | 3 |
| Scale headroom (5 years at 30% growth) | 15 | 3 | 4 | 5 |
| Operational maturity in this team | 20 | 5 | 4 | 2 |
| Cost (licence, infrastructure, people) | 15 | 4 | 4 | 3 |
| **Weighted score (of 5.00)** | **100** | **4.25** | **3.80** | **2.80** |

*Example* reasons:

- Relational scores 3 on scale: 75 GB grows to about 280 GB in 5 years
  at 30% per year, which one primary with replicas can hold; write rate
  of 400 per second is far below single-node limits.
- Wide-column scores 2 on query fit: listing by author would need a
  second table per query shape, maintained by the application.
- Operational maturity reflects that the team runs a relational store
  today and has no wide-column experience.

Five-year size check: 75 GB x 1.3^5 = 278 GB (computed: 1.3^5 = 3.71).

## 4. Product shortlist

<!-- Guidance: Only now name products within the winning category. Record facts with a check date: licence, managed availability in your regions, version support policy, backup and encryption features. Product facts change; never copy them from an old record. -->

| Product option | Licence (checked YYYY-MM-DD) | Managed option in our regions | Support policy | Fits matrix assumptions? | Notes |
|---|---|---|---|---|---|
| [Option A] | [Licence] | [Yes/No] | [Versions and dates] | [Yes/No] | [Notes] |
| [Option B] | [Licence] | [Yes/No] | [Versions and dates] | [Yes/No] | [Notes] |
| [Option C] | [Licence] | [Yes/No] | [Versions and dates] | [Yes/No] | [Notes] |

Check each candidate against the organisation's dependency policy (long
term support versions, pinned releases) and against the Build vs Buy
Analysis template (`architecture/build-vs-buy-analysis`) when a managed
service is an option.

## 5. Derived and secondary stores

<!-- Guidance: List every copy made from the primary store, how it is fed and how far behind it may be. Each one needs a rebuild procedure from the primary. -->

| Derived store | Category | Fed by | Allowed lag | Rebuild from primary | *Example* use |
|---|---|---|---|---|---|
| Review search index | Search engine | Change stream from primary | 10 s | Full reindex, about 2 hours | Full-text search |
| Review cache | Key-value | Cache-aside (`system-design/caching-strategy`) | TTL 600 s | Not needed | Hot review lists |
| Book embeddings | Vector | Nightly batch from catalogue | 24 hours | Recompute, about 6 hours | "Readers also liked" |
| [Store] | [Category] | [Feed] | [Lag] | [Procedure] | [Use] |

Vector index sizing, *Example:* 5,000,000 books x 768 dimensions x 4
bytes (32-bit floats) = 15.36 GB (14.3 GiB) of raw vectors before index
overhead; 8-bit quantisation reduces the raw vectors to 3.84 GB. Graph
indexes such as HNSW add link storage per vector and trade recall for
latency through their build and search parameters; measure recall on
your own queries.

## 6. Non-functional checks

<!-- Guidance: Run the chosen option through the checks that most often fail late. -->

- [ ] Backup and point-in-time restore tested, restore time measured
      against RTO.
- [ ] Encryption in transit and at rest; key management owner named.
- [ ] Data residency: regions where data may be stored are supported.
- [ ] Erasure: a user's records can be found and deleted in the primary
      and every derived store within the retention rule.
- [ ] Schema change process works online at the expected table size.
- [ ] Observability: query latency, replication lag, storage growth and
      slow queries exported to monitoring (`ops/observability-spec`).
- [ ] Capacity: partitioning plan exists if growth exceeds one node
      (`system-design/partitioning-sharding-guide`).
- [ ] Consistency model per operation recorded
      (`system-design/replication-consistency-models`).

## 7. Risks and exit plan

<!-- Guidance: State how you would leave this store if the product, licence or cost changes. Proprietary query languages and features raise the exit cost; record which ones you depend on. -->

| ID | Risk | Likelihood | Impact | Mitigation | Exit path |
|---|---|---|---|---|---|
| DS-R01 | *Example:* Licence change of chosen product | Low | High | Prefer options with a widely used open licence or several vendors | Logical dump and reload into a compatible store |
| DS-R02 | *Example:* Search index drifts from primary | Medium | Medium | Daily count reconciliation, rebuild procedure | Rebuild from primary |
| DS-R03 | *Example:* Growth faster than planned | Medium | Medium | Storage alert at 70%, partitioning plan prepared | Shard by `book_id` |
| [DS-RNN] | [Risk] | [L] | [I] | [Mitigation] | [Exit] |

## 8. Decision

<!-- Guidance: Record the decision, the main reason and the conditions that would reopen it. Link to an ADR (`architecture/adr-full`). -->

| ID | Decision | Reason | Revisit when | Owner | Status |
|---|---|---|---|---|---|
| DS-01 | *Example:* Relational primary store for reviews | Highest weighted score (4.25); transactions and query fit; team already runs it | Writes exceed 5,000 per second or data exceeds 2 TB | @reviews-lead | Decided |
| DS-02 | *Example:* Search engine as derived index | Full-text requirement | Search needs exceed what the primary's text search can serve | @search-lead | Decided |
| DS-03 | *Example:* Vector store for book embeddings | Similarity queries | Recall below target | @recs-lead | Open |
| [DS-NN] | [Decision] | [Reason] | [Trigger] | [@owner] | [Status] |

## Glossary and references

<!-- Guidance: Keep definitions short; link deeper templates for each area. -->

| Term | Meaning |
|---|---|
| Polyglot persistence | Using different store types for different workloads in one product |
| Derived store | Copy or index built from a primary store, rebuildable from it |
| Secondary index | Index on a field other than the primary key |
| Approximate nearest neighbour (ANN) | Search that returns close vectors quickly without checking every vector |
| Recall | Share of the true nearest neighbours that an ANN search returns |

References:

- Malkov Y., Yashunin D., "Efficient and Robust Approximate Nearest
  Neighbor Search Using Hierarchical Navigable Small World Graphs", IEEE
  Transactions on Pattern Analysis and Machine Intelligence 42(4), 2020,
  pages 824 to 836, https://doi.org/10.1109/TPAMI.2018.2889473.
- Related templates: Database Design (`architecture/database-design`),
  Build vs Buy Analysis (`architecture/build-vs-buy-analysis`), Partitioning
  and Sharding Plan (`system-design/partitioning-sharding-guide`),
  Replication and Consistency Models
  (`system-design/replication-consistency-models`), Caching Strategy
  (`system-design/caching-strategy`), Full-Text Search Engine System Design
  (`system-design/design-full-text-search`), Enterprise RAG Search System
  Design (`system-design/design-enterprise-rag-search`), Back-of-Envelope
  Estimation (`system-design/back-of-envelope-estimation`).

## Standard and licence note

Based on general database engineering practice; no standard governs data
store selection. The categories, decision matrix, weights, scores and
examples are original to this collection, and Quillmere is an invented
organisation. Product capabilities and licences change: check the current
official documentation of each candidate on the day you decide. Template
licence: MIT.