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

系统设计标准data store selection更新于 2026年10月29日

在编辑器中打开下载 .md

使用方法: 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.

预览

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.