4) Storage & databases — detailed guide

• Start from access patterns: top reads/writes, latency SLOs, query shapes.
  If you can’t list the top 3 reads and writes, you’re not ready to pick a database.
• Correctness needs: transactions, constraints, unique keys, referential integrity.
  Money, permissions, account state → strong correctness. Feeds/counters often tolerate bounded staleness.
• Scale shape: read-heavy vs write-heavy, hotspot skew, partitionability.
  Horizontal scale is easiest when you can partition by a stable key (e.g., userId/viewerId).
• Operational reality: backups, migrations, multi-region, observability, cost.
  Managed services reduce ops; but understand limits (indexes, query flexibility, transactional scope).

• SQL (relational): joins, constraints, ACID, ad-hoc queries.
  Great for accounts, payments, inventory, and workflows that need multi-row transactional correctness.
• NoSQL (family): key-value, document, wide-column, time-series, graph, search.
  Great for narrow, high-scale access patterns, denormalized read models, sessions/caching, logs/telemetry.

• Source of truth (correctness): Users, privacy settings, follows, and posts require strong correctness.
  These can live in SQL or a strongly consistent document store depending on scale and transactional needs.
• Derived read model (scale): FeedEntry index is optimized for fast per-viewer scans (top-N).
  Use a KV/wide-column store partitioned by viewerId (or time-bucketed + viewerId).
• Cache: Redis/Memcached for feed head pages and hot hydration (post summaries).
  Cache absorbs peak reads; correctness-sensitive reads (privacy) still require enforcement at read time or strict invalidation.

• B-tree: ordered, supports ranges/sorts.
  Best default for WHERE + ORDER BY patterns (time-based queries especially).
• Hash: equality lookups only.
  Good for exact key lookups, but doesn’t help ranges/sorts.
• Covering index: index contains all needed columns → index-only scan.
  Great for hot endpoints; but can bloat writes if you include too many columns.
• Composite index: multi-column; leftmost prefix matters.
  Equality/high-selectivity columns first; range columns later.

• Posts lookup: hydrate posts by postId in batch.
  Primary key / hash index on postId. Keep the row/doc small; put large media elsewhere.
• FeedEntry scan: query “top-N for viewer”.
  Partition by viewerId; sort by (rankScore, createdAt) or time bucket + createdAt. This avoids global secondary indexes for the main read.
• Follow edges: followers(authorId) and/or following(viewerId).
  Two access patterns may require two tables/indexes (or denormalized edges) depending on fan-out logic.

• Read Committed: no dirty reads; non-repeatable reads possible.
  Common default; fine for many apps with careful update patterns.
• Repeatable Read / Snapshot (MVCC): consistent snapshot; readers don’t block writers.
  Great for read-heavy workloads; watch long transactions causing bloat/compaction pressure.
• Serializable: strongest; can abort more transactions under contention.
  Use for money, inventory decrements, unique constraints under high concurrency.

• Privacy/block rules: must apply immediately and correctly.
  Privacy changes may require cache invalidations and re-checks at read time to avoid leaking content.
• Post creation: once acknowledged, the post must be durable.
  Write post to durable store, then emit PostCreated event to build feed/search indexes asynchronously.
• FeedEntry building: derived/indexed; eventual consistency is acceptable (seconds).
  If fan-out pipeline lags, degrade gracefully (recency fallback) rather than blocking writes.

• Hash sharding: even spread; great for KV/doc stores.
  Downside: range queries become scatter/gather unless you add secondary structures.
• Range sharding: good locality for time/id; risk hot ‘latest’ range.
  Use time buckets to avoid hot partitions and to simplify TTL/retention.
• Consistent hashing + virtual nodes: smooth rebalancing.
  Common for caches and large KV partitions; reduces reshard pain.
• Hot-key mitigation: salting, dedicated partitions, batching, queues.
  Celebrities, trending keys, and ‘global counters’ need special treatment.

• FeedStore: shard by viewerId (many virtual shards).
  This matches the read pattern (top-N for viewer) and distributes reads evenly.
• Hot creators: celebrity posts are not fanned-out to every follower partition.
  Naive fan-out can be huge (≈ 358 GB/day at ~64B/entry with avg followers ~300). Use hybrid: fan-in merge for celebrities.
• Time buckets: bucket feed entries by day/hour to control partition size.
  Buckets make retention/cleanup easy and reduce compaction pressure in LSM/wide-column stores.

• Leader/follower (primary/replica): simple writes, great read scaling.
  Sync replication lowers RPO but raises latency; async replication is faster but can lag.
• Quorum (Dynamo-style): write to N with W, read with R; R + W > N gives quorum overlap.
  Use hinted handoff/read repair; define conflict resolution strategy.
• Client semantics: read-your-writes, monotonic reads, session stickiness.
  Often implemented by routing a session to leaders or tagging reads for consistency when needed.

• Strong where needed: privacy settings, blocks, and account security.
  Reads can be pinned to leader (or use strongly consistent reads) for these entities.
• Eventual where acceptable: feed entries, counters, ranking signals.
  Followers may see posts within seconds; like counts may lag and reconcile later.
• Read-your-writes: author should see their new post immediately.
  Write-through cache or leader reads for author; followers rely on async fan-out with bounded lag.

• Key-value: O(1) lookups, sessions, rate limiting, caching.
  Limited ad-hoc queries → design keys and GSIs carefully.
• Wide-column: huge scale, time-ordered scans, tunable consistency.
  Schema equals access patterns; great for feed/time-series partitions.
• Document: flexible JSON, nested data, secondary indexes.
  Govern schema growth; large documents increase write amplification.
• Relational (OLTP): transactions, constraints, joins.
  Scale via partitioning, read replicas, caching; avoid hot joins on critical paths.
• Search: full-text and facets; requires indexing pipeline.
  Typically eventual; treat as derived view with backfill/reindex plans.
• OLAP/columnar: analytics at scale; not for OLTP.
  Push exploration/BI here to keep OLTP fast and cheap.

• PostStore: durable metadata/text (SQL/doc) + object store for media.
  Keep blobs out of OLTP rows/docs; serve media via CDN.
• FeedStore: wide-column/KV for fast per-viewer scans.
  Supports top-N reads with predictable latency under high QPS.
• Search index: posts/users search is derived and asynchronously updated.
  Define index lag SLO and fallbacks when indexing is behind.

• Normalization: fewer duplicates, easier updates, correctness.
  But joins on hot paths raise p95; careful indexing and caching becomes mandatory.
• Denormalization: fast reads, fewer joins.
  Costs: write amplification + propagation complexity (async updates, backfills).
• Amplification: read amplification (many reads per request) and write amplification (many writes per logical write).
  Indexes, LSM compaction, and multiple projections increase write amplification.
• Patterns: CQRS and event sourcing to build read models safely.
  Keep OLTP clean; build read-optimized views asynchronously; monitor lag and correctness.

• Source-of-truth: Post is canonical; user/profile/privacy are canonical.
  These are updated with strong correctness requirements.
• Read model: FeedEntry is derived (viewerId → ordered list of postIds + scores).
  This avoids expensive joins on every feed read; hydration is a batched lookup.
• Amplification trade: we accept extra writes in the fan-out pipeline to keep read latency low.
  Posts ingest ~37.3 GB/day (metadata only, rough). Feed projections can be much larger, so we use hybrid fan-out to control write amplification.

• Avro/Protobuf: typed, compact, supports compat rules.
  Add optional fields with defaults; never reuse field IDs (Proto); use a registry and CI validation.
• JSON: flexible but needs discipline.
  Use versioned envelopes + JSON Schema; tolerant readers; never break old consumers.

• Expand: add nullable columns/tables/indexes first.
  Deploy readers/writers that understand both old and new shape.
• Migrate/backfill: backfill data + dual-write if needed; validate.
  Use idempotent jobs; measure progress; keep rollback path.
• Contract: remove old columns/paths only after traffic cutover.
  Canary + rollback to reduce risk.

• Event evolution: PostCreated adds an optional language field (default “unknown”).
  Old consumers keep working; new consumers can use the field for ranking or filtering.
• Feed model upgrade: add rankScoreV2 while keeping rankScore.
  Backfill/compute V2 scores asynchronously; switch readers; then remove old score later (contract).

Core (canonical):
- User/Privacy/Blocks: SQL or strong document store (correctness)
- FollowStore: edges indexed for follower/following lookups
- PostStore: durable post metadata/text (media in Object Store + CDN)

Derived (read-optimized):
- FeedStore (viewerId partition): ordered FeedEntry list (postId + score + ts)
- Search index: posts/users full-text (async)

Caching:
- Redis: feed head pages + post summary hydration + rate limits

Consistency:
- Strong for privacy/block/security
- Eventual for feed projections/counters/ranking signals (bounded seconds)

Hotspots:
- Hybrid fan-out: normal authors fan-out on write
- Celebrity authors: fan-in merge at read time to avoid extreme write amplification

• “Pick the database after you write down access patterns and SLOs.”
• “Indexes reduce reads but increase writes—keep OLTP indexes lean.”
• “Shard by a stable key; use virtual nodes; plan for hotspots.”
• “Strong consistency for security/privacy; eventual for feeds/counters with bounded staleness.”
• “Expand → backfill/dual-write → cut over → contract.”