Choosing Storage Patterns

Think in three axes:

• Access pattern: point lookup, range scan, aggregate, join, search, traversal, stream replay.
• Data shape: structured rows, wide/columnar, semi-structured documents, key-value, time-stamped series, graph.
• Freshness & scale: latency targets (ms/sec/min), read/write ratios, total volume and growth.

Then add guardrails: consistency, governance/security, cost, and lifecycle.

Best for: transactions, strong consistency, small point reads/updates.

• Pros: ACID, indexes, joins, mature tooling.
• Cons: Costly for scans/aggregations at scale.
• Typical use: orders, users, payments, inventory counts.

Best for: analytical scans and aggregations over large datasets.

• Pros: High compression, vectorized reads, partition pruning.
• Cons: Poor for frequent single-row updates.
• Typical use: BI dashboards, batch analytics, ML feature backfills.

Best for: cheap, scalable storage with schema evolution and separation of storage/compute.

• Pros: Cost-effective, open formats, flexible processing engines.
• Cons: Requires good file sizing/partitioning; small-files problem.
• Typical use: raw to curated layers (bronze/silver/gold) and replayable history.

Best for: semi-structured data, flexible attributes per entity.

• Pros: Schema-on-read per document, secondary indexes.
• Cons: Joins are limited; careful index planning needed.
• Typical use: product catalogs, user profiles, content.

Best for: ultra-low-latency point lookups.

• Pros: Simple access, very fast reads/writes.
• Cons: Limited query expressiveness.
• Typical use: session state, feature serving cache, id→entity map.

Best for: telemetry with time as primary dimension.

• Pros: Time-based partitions, retention policies, downsampling.
• Cons: Not ideal for complex ad-hoc joins.
• Typical use: IoT sensor data, metrics, logs.

Best for: relationship queries and traversals.

• Pros: Efficient traversals, relationship-first modeling.
• Cons: Not for heavy aggregations or wide scans.
• Typical use: social graph, recommendations, fraud rings.

Best for: full-text search and relevance scoring.

• Pros: Inverted indexes, facets, highlighting.
• Cons: Eventual consistency, extra ingestion pipeline.
• Typical use: product/site search, log search.

Best for: ordered events, replay, backpressure handling.

• Pros: Retention windows, consumer groups.
• Cons: Not a general-purpose store; retention-limited.
• Typical use: event sourcing, streaming ETL, CDC pipelines.

Best for: cheap, infrequently accessed historical data.

• Pros: Very low cost.
• Cons: High retrieval latency.
• Typical use: compliance archives, historical snapshots.

• If you need millisecond point lookups → key-value store.
• If you need big scans and aggregates → columnar warehouse/lake.
• If you need flexible attributes per item → document store.
• If you need time-first queries with retention policies → time-series DB.
• If you need graph traversals → graph DB.
• If you need full-text search → search index.
• If you need event replay → streaming log with retention + sink to durable store.

Needs: append-only events, minute-level freshness, aggregates by time, device, campaign.

• Ingest: streaming log topic with 24–72h retention for replay.
• Durable store: data lake using Parquet, partitioned by date/hour; compact small files.
• Serving: columnar warehouse/lakehouse tables for BI.
• Why: columnar files excel at scans/aggregates; streaming log allows backfill/replay.

Needs: flexible per-product attributes, text search, facet filters.

• Main store: document DB (one document per product; secondary indexes on category, brand).
• Search: search index for full-text, synonyms, facets.
• Why: document pattern matches semi-structured data; search index boosts relevance and speed.

Needs: single-digit ms reads by key during model inference, high write rate from streaming features.

• Online store: key-value DB (id → latest features) with TTL for ephemeral fields.
• Offline store: columnar lake/warehouse for history and training.
• Why: KV gives low-latency lookups; columnar keeps costs low for historical analytics.

Needs: time-based writes, downsampling, retention by device type.

• Primary: time-series DB with time-partitioned shards, retention policies, rollups.
• Archive: cold tier for aged raw data (cheap storage).
• Why: TSDB optimizes time-window queries; cold tier minimizes cost.

• [ ] Access patterns identified and prioritized.
• [ ] Latency and throughput targets quantified.
• [ ] Data volume, growth, and hot/cold split estimated.
• [ ] Consistency/transaction needs clarified.
• [ ] Governance, security, and audit requirements documented.
• [ ] Cost model compared across options.
• [ ] Backup/DR and region strategy defined.
• [ ] Operational tasks (compaction, reindex, vacuum) planned.

Scenario: A support system must load a customer record by id in under 5 ms and update contact preferences instantly. Nightly analytics compute churn metrics over 2 years.

1. Choose the primary serving store.
2. Choose the analytics store.
3. Explain partitioning/format choices.

Deliverable: 3–5 bullet rationale.

Scenario: IoT sensors write 50k events/s. Product wants 7-day hot queries (sec-level), 90-day warm analytics, 2-year cold archive.

1. Select stores for hot, warm, and cold tiers.
2. Define retention and downsampling rules.
3. Describe compaction/partitioning strategy.

Deliverable: A one-page plan with SLAs per tier.