Operational Datastore Selection Relational And NoSQL
Overview
Use this skill when the real decision is not warehouse modeling, but what operational datastore should hold state, serve requests, or back data-driven application flows. It helps agents choose intentionally between relational systems such as MySQL and PostgreSQL versus NoSQL families such as document, key-value, wide-column, or cache-oriented stores.
When to Use
- choosing
MySQL versus NoSQL for application-facing or service-facing data
- selecting stores for metadata, idempotency keys, session state, event state, or operational APIs
- deciding whether flexible schema or strict relational constraints matter more
- evaluating consistency, scale, and access-pattern trade-offs
- preventing analytics or pipeline systems from being misused as OLTP stores
Do not treat datastore selection as a brand preference or trend decision.
Workflow
Define the access pattern first.
Clarify:
- read versus write ratio
- request latency expectations
- query shapes
- update frequency
- retention and archival behavior
Define integrity and consistency needs.
Include:
- transactions
- joins and relational constraints
- uniqueness guarantees
- conflict handling
- tolerance for eventual consistency
Match the workload to the right store family.
Common guidance:
- relational stores such as
MySQL fit well when transactions, constraints, and predictable relational queries matter
- document stores fit when entity shapes vary and document-oriented retrieval dominates
- key-value stores fit when lookup by key is primary and access patterns are narrow
- wide-column stores fit when scale and partition-oriented access dominate
- caches should accelerate another source of truth, not silently replace one
Check downstream and operational impact.
Consider:
- CDC and replication support
- backup and restore behavior
- schema evolution pain
- observability and operational maturity
- how analytics or warehouse ingestion will work
Record the trade-off explicitly.
A good decision names what the chosen store is not good at.
Common Rationalizations
| Rationalization |
Reality |
| "NoSQL scales better, so we should use it." |
Scale is only one axis; transactions, operability, and query ergonomics still matter. |
"MySQL is too old-fashioned for modern systems." |
Mature relational systems remain strong choices when consistency and relational integrity are central. |
| "The schema changes a lot, so we need schemaless storage." |
Frequent shape change often signals unclear contracts, not necessarily a datastore requirement. |
Red Flags
- a warehouse or lakehouse is being used like an operational key-value store
NoSQL is chosen with no explicit access-pattern justification- relational transactions are required but omitted from the design
- a cache is treated as the only source of truth
- CDC, backup, or migration behavior is unknown
Verification
1---2name: operational-datastore-selection-relational-and-nosql3description: Guides agents through choosing relational operational stores such as MySQL versus NoSQL options such as document, key-value, wide-column, or cache-backed systems. Use when deciding where application-adjacent or pipeline-adjacent operational data should live.4---56# Operational Datastore Selection Relational And NoSQL78## Overview910Use this skill when the real decision is not warehouse modeling, but what operational datastore should hold state, serve requests, or back data-driven application flows. It helps agents choose intentionally between relational systems such as `MySQL` and `PostgreSQL` versus `NoSQL` families such as document, key-value, wide-column, or cache-oriented stores.1112## When to Use1314- choosing `MySQL` versus `NoSQL` for application-facing or service-facing data15- selecting stores for metadata, idempotency keys, session state, event state, or operational APIs16- deciding whether flexible schema or strict relational constraints matter more17- evaluating consistency, scale, and access-pattern trade-offs18- preventing analytics or pipeline systems from being misused as OLTP stores1920Do not treat datastore selection as a brand preference or trend decision.2122## Workflow23241. Define the access pattern first.25 Clarify:26 - read versus write ratio27 - request latency expectations28 - query shapes29 - update frequency30 - retention and archival behavior31322. Define integrity and consistency needs.33 Include:34 - transactions35 - joins and relational constraints36 - uniqueness guarantees37 - conflict handling38 - tolerance for eventual consistency39403. Match the workload to the right store family.41 Common guidance:42 - relational stores such as `MySQL` fit well when transactions, constraints, and predictable relational queries matter43 - document stores fit when entity shapes vary and document-oriented retrieval dominates44 - key-value stores fit when lookup by key is primary and access patterns are narrow45 - wide-column stores fit when scale and partition-oriented access dominate46 - caches should accelerate another source of truth, not silently replace one47484. Check downstream and operational impact.49 Consider:50 - CDC and replication support51 - backup and restore behavior52 - schema evolution pain53 - observability and operational maturity54 - how analytics or warehouse ingestion will work55565. Record the trade-off explicitly.57 A good decision names what the chosen store is not good at.5859## Common Rationalizations6061| Rationalization | Reality |62| --- | --- |63| "NoSQL scales better, so we should use it." | Scale is only one axis; transactions, operability, and query ergonomics still matter. |64| "`MySQL` is too old-fashioned for modern systems." | Mature relational systems remain strong choices when consistency and relational integrity are central. |65| "The schema changes a lot, so we need schemaless storage." | Frequent shape change often signals unclear contracts, not necessarily a datastore requirement. |6667## Red Flags6869- a warehouse or lakehouse is being used like an operational key-value store70- `NoSQL` is chosen with no explicit access-pattern justification71- relational transactions are required but omitted from the design72- a cache is treated as the only source of truth73- CDC, backup, or migration behavior is unknown7475## Verification7677- [ ] Access patterns and latency needs are explicit78- [ ] Integrity and consistency requirements are named79- [ ] The chosen store family matches the actual workload80- [ ] CDC, backup, migration, and analytics implications are understood81- [ ] Trade-offs and non-goals are documented
vaquarkhan