Data Store Selection Template (Relational, NoSQL, Search, Vector)
Workload profile, store categories (relational, KV, document, wide-column, search, time-series, vector), weighted matrix, derived stores
How to use: Use it when choosing where a workload's data lives or adding a second store. Quantify the workload and agree the matrix weights before naming products. The main pitfall is starting from a favourite technology and ignoring the cost of operating one more database type.
Preview
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.
Document control
| 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
| 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
| 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
| 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
| 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
| 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
- 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
| 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
| 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
| 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.
Related templates
Distributed Key-Value Store System Design Template (Dynamo-Style)
Consistent hashing, vnodes, quorums (R+W>N), vector clocks vs LWW, hinted handoff, Merkle anti-entropy, gossip, LSM compaction, cache eviction, hot keys
System DesignFeature Store System Design Template (Online, Offline, Point-in-Time)
ML feature store: registry, batch and streaming pipelines, offline and online stores, point-in-time joins with TTL, training-serving skew, monitoring
System DesignSocial Graph Store System Design Template
Objects and associations model, leader and follower cache tiers, read-after-write, hot vertices, inverse edges, sharding, failure modes