| name | storage-selection |
| description | Select the right storage engine per workload using polyglot persistence. Use when designing a new bounded context, splitting a monolith, or replacing a hot-spotted database. |
Storage Selection
Pick storage by access pattern, consistency need, and cardinality of relationships, not by team familiarity. In 2026 the default stack is Postgres 17 plus one purpose-built engine per non-OLTP pattern, federated through a lakehouse.
Stack Baseline (2026)
| Concern | Recommended |
|---|
| OLTP / general | Postgres 17 (logical replication, MERGE, SQL/JSON) |
| OLTP / Microsoft stack | SQL Server 2022 / Azure SQL (T-SQL, ledger tables, columnstore) |
| Embedded / edge / on-device | SQLite 3.46, libSQL, Turso (replicated SQLite) |
| Wide-column / massive write | ScyllaDB 6, Cassandra 5 |
| Document | MongoDB 8, Couchbase 7.6 |
| Key-value / cache | Redis 7.4, DragonflyDB, Valkey |
| Search | OpenSearch 2.14, Elasticsearch 8.14, Typesense |
| Graph | Neo4j 5, Memgraph, Apache AGE on Postgres |
| Vector | pgvector 0.7, Qdrant 1.10, Weaviate, Milvus 2.4 |
| Time-series | TimescaleDB, InfluxDB 3, QuestDB |
| Analytics | ClickHouse 24, DuckDB 1.0, StarRocks |
| Lakehouse | Apache Iceberg + Polaris REST catalog, Delta + Unity |
| Object | S3 / R2 / GCS with Iceberg manifests |
When to Use
- New bounded context needs persistent state.
- Existing store shows lock contention, scan amplification, or schema friction.
- Adding semantic search, recommendations, or geospatial features.
- Designing CDC + lakehouse offload for analytics.
Prerequisites
- Documented access patterns: read/write ratio, p99 latency target, payload size, query shapes.
- Consistency requirement (linearizable, read-your-writes, eventual).
- Data volume and growth model for 24 months.
- Tenancy and residency constraints (GDPR, DPDP, schrems).
Instructions
1. Map workload to a CAP/PACELC quadrant
flowchart TD
A[Workload] --> B{Strong consistency?}
B -- yes --> C{Relational joins?}
C -- yes --> D[Postgres 17]
C -- no --> E[Spanner / CockroachDB]
B -- no --> F{Access pattern}
F --> G[Key lookup -> Redis/Dynamo]
F --> H[Time-series -> Timescale]
F --> I[Vector ANN -> pgvector/Qdrant]
F --> J[Wide scan analytics -> ClickHouse/DuckDB]
F --> K[Full text -> OpenSearch]
2. Quantify the access pattern
Record QPS, payload, selectivity, and join depth. Reject any engine whose published p99 at target QPS exceeds the SLO by >2x.
3. Prefer Postgres until proven insufficient
Postgres 17 covers JSONB, vectors (pgvector), time-series (Timescale), queues (SKIP LOCKED), and AGE for graph. Add a second engine only when a specific pattern dominates cost.
4. Define the system of record vs. derived stores
CREATE TABLE orders (
id uuid PRIMARY KEY,
customer_id uuid NOT NULL,
total_cents bigint NOT NULL,
status text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
5. Plan the replication path
Use Debezium 2.6 CDC into Kafka 3.7, land raw events in Iceberg via Estuary Flow or Kafka Connect, and materialize per-engine projections with Flink 2.0 or Materialize.
6. Validate with a load test
Run k6 or Gatling with production-shaped traffic for >=30 minutes. Capture p50/p95/p99, error rate, and storage IOPS before approving.
7. Document the decision in an ADR
Record: workload profile, candidates, benchmark results, operational cost, exit strategy. Link to the data contract and lineage entry in OpenLineage.
Common Pitfalls
| Pitfall | Why it hurts | Mitigation |
|---|
| MongoDB for relational data | $lookup explodes, no FK | Use Postgres + JSONB columns |
| Elasticsearch as source of truth | No durability guarantees | Treat as derived index only |
| Redis for analytics | Single-thread scan kills latency | Use ClickHouse/DuckDB |
| Vector DB without filter strategy | ANN recall collapses with metadata filters | Pre-filter via SQL; HNSW + payload index |
| Multiple OLTP engines per service | Distributed transactions, dual writes | One SoR, derive the rest via CDC |
| Choosing by hype | Operational cost dominates | Score on TCO over 3 years |
Output Format
Deliver an ADR containing: workload table, shortlist with scoring matrix, chosen engine, replication topology diagram, SLOs, and rollback plan. Attach load-test report and data contract reference.
Related Skills
Once an engine is chosen, switch to implementation skills:
Authoritative References
