Data Storage and Data-Model Selection - Complete Notes

A language-neutral guide to choosing data models and storage engines from access patterns, correctness, scale, retention, recovery, security, operations, and cost instead of choosing from product labels.

00. The storage-selection mental model

A data store is a machine for preserving facts and answering specific questions under specific failure, latency, and cost constraints.

Storage design begins with facts and access patterns. Facts describe what must remain true, such as "an order has one immutable identifier" or "available inventory never becomes negative." Access patterns describe the questions and changes the system must perform, including their filters, sort order, result size, write rate, concurrency, and latency target. The data model is the shape used to preserve those facts and serve those patterns. The storage engine is the machinery that places bytes on memory and durable media.

Business invariants and trust boundaries
                |
                v
Reads, writes, scans, aggregates, traversals, and retention
                |
                v
Consistency, durability, recovery, latency, and throughput targets
                |
                v
Logical model, ownership boundary, keys, partitions, and indexes
                |
                v
Store category and storage-engine behavior
                |
                v
Measured production evidence and review triggers

01. Precise terminology and prerequisites

Logical models, physical layouts, consistency guarantees, and deployment topologies are separate choices even when a product bundles them.

Entity

A thing with identity, such as an order, tenant, user, file, or device.

Attribute

A named fact about an entity, such as order status or file media type.

Relationship

A fact connecting entities, such as a customer placing an order.

Record, row, or document

A stored grouping of values. Its atomic update boundary depends on the system.

Key

A value used to identify, locate, group, order, or partition data. A primary key identifies the authoritative record. A partition key decides placement. A sort key orders records within that placement.

Index

An additional structure that maps searchable values to records. It trades storage and write work for faster reads.

Schema

The contract for names, types, constraints, meanings, compatibility, and lifecycle. A store that does not enforce a schema still has one in every writer and reader.

Source of truth

The authoritative representation used to settle conflicts and rebuild derived copies.

Materialized view

A stored result derived from source data for a particular read pattern. It needs a refresh or incremental maintenance rule.

Durability

The promise that an acknowledged write survives the failures named by the system contract.

Consistency

The rules governing which writes a read may observe and how concurrent operations appear.

Recovery point objective, or RPO

The maximum acceptable amount of data change lost after a disaster.

Recovery time objective, or RTO

The maximum acceptable time to restore the required service after a disaster.

"NoSQL," "distributed," "serverless," and "managed" do not describe one data model or one correctness contract. Ask which operations are atomic, what a successful acknowledgement means, how replicas behave, and how backup recovery is demonstrated.

02. Gather decision inputs

The best store is the simplest one that meets measured access, correctness, lifecycle, and operational requirements with safe headroom.

Write an access-pattern card

Record every critical operation in a table before drawing the architecture.

Operation: list a customer's recent orders
Filter: tenant_id = ? and customer_id = ?
Order: created_at descending, then order_id descending
Page: 50 items, cursor based
Traffic: 2,000 average reads/s, 8,000 peak reads/s
Freshness: committed orders visible immediately in the home region
Latency: p99 under 200 ms
Retention: seven years, last 18 months online
Skew: largest tenant is 20% of traffic
Failure rule: a stale page is acceptable for 30 seconds, missing an accepted order is not

List invariants before tables

• An order total equals the recorded line totals, tax, shipping, and adjustments.
• A payment provider reference is associated with at most one internal payment attempt.
• A chat message identifier is unique within a conversation.
• A file metadata record never points to an object that was never finalized.
• A tenant cannot read, change, index, back up, or export another tenant's protected data.

Put invariants inside one atomic boundary when practical. If an invariant spans independently committed stores, the application must manage intermediate states, retries, reconciliation, and compensation. That complexity is a design cost, not free flexibility.

03. Store-category decision matrix

Categories describe useful tendencies. Verify the actual product and configuration because many systems support several models.

Use a weighted decision, not a checkbox contest

for each candidate:
  score = 0
  for each requirement:
    evidence = benchmark, documentation, prototype, or restore drill
    rating = 0 if unmet, 1 if risky, 2 if adequate, 3 if strong
    score += requirement.weight * rating

reject candidate immediately if it fails:
  a correctness invariant
  required data residency
  required RPO or RTO
  sustainable peak write rate
  supportable operational model

A high total cannot compensate for a blocking correctness or compliance failure. Store the score, assumptions, evidence, alternatives, and review triggers in an architecture decision record.

04. Relational model

The relational model represents facts as relations with declared attributes and integrity constraints, then derives results through relational operations.

Rows are not merely objects serialized into tables. A relation describes a set of facts. Primary keys identify facts, foreign keys preserve references, unique constraints prevent duplicates, check constraints restrict valid values, and transactions protect multi-step changes. A query can select, project, join, group, and order without storing a copy for every access path.

Normalization

Normalization separates independently changing facts so each is recorded in one authoritative place. In practical terms:

• Each field contains one value at the model's chosen granularity.
• Non-key facts depend on the entity identified by the key.
• Facts about another entity move to that entity's relation.
• Many-to-many relationships use an explicit association relation.

Normalization reduces update anomalies. If a customer's current email is copied into ten thousand order rows, changing the email risks mixed values. However, an order may intentionally snapshot the billing address and product price because those are historical order facts, not references to the customer's current profile or catalog price.

Choose relational storage when

• Constraints and transactions are central to correctness.
• Entities have meaningful relationships and query shapes evolve.
• Joins, aggregates, and administrative analysis are important.
• The scale fits one system or has a clear partitioning strategy.
• The team values mature query planning, migrations, and recovery tooling.

05. Key-value and document models

Key-value

A key-value store exposes a map from an opaque or structured key to a value. Its strongest path is usually get(key), put(key, value), conditional update, and bounded range scan when ordered keys are supported. The application encodes relationships and alternate access paths.

conversation/{conversation_id}/message/{time_bucket}/{message_id} -> message
user/{user_id}/conversation/{last_activity}/{conversation_id}     -> summary
message/{message_id}/dedup/{client_request_id}                     -> accepted_id

Lexicographically ordered keys can make adjacent business data adjacent on storage. The first component often defines locality, so placing an ever-growing conversation under one key range can create a hotspot. Add a stable time bucket or shard component only when measured traffic requires it, and preserve a route that can read the buckets in order.

Document

A document groups named and nested values around an aggregate. Reading an order with its shipping address and line snapshot can take one fetch. The model is strongest when the embedded data is normally read, written, retained, authorized, and versioned together.

Embed when the child:

• belongs to one parent and has no independent identity or write rate,
• is bounded in count and document size,
• is normally required with the parent, and
• shares the parent's transaction and lifecycle.

Reference when the child:

• is shared, independently updated, high-cardinality, or unbounded,
• has independent permissions or retention,
• is queried without the parent, or
• would make updates rewrite a large document.

06. Wide-column and time-series models

Wide-column

A wide-column model commonly groups rows by partition key and orders them by clustering or sort columns. A useful table is designed around one bounded query. Duplication across query tables is deliberate, and each copy needs a reliable update and repair strategy.

Need: latest messages in one conversation before a cursor

partition key = hash(conversation_id, month)
sort key      = event_time descending, message_id descending
value         = sender_id, body_ref, edit_version, deletion_state

Query:
  identify current and previous month buckets
  scan each bounded range from cursor
  merge by (event_time, message_id)
  stop after page_size rows

Partition size must be bounded in bytes and operations, not only row count. A popular conversation, device, or tenant can overwhelm one partition even when the global average is small. Bucketing spreads growth but increases read fan-out and lifecycle complexity.

Time-series

Time-series data consists of observations associated with time and an identity such as metric name plus labels. Stores can optimize ordered appends, timestamp compression, time-window scans, retention, and downsampling. They are less natural for frequently mutating old observations or rich cross-entity transactions.

Cardinality is the number of distinct series. A label such as region has bounded cardinality. A label containing request_id creates a new series per request and can dominate memory and index cost. Put high-cardinality details in logs or traces and connect them through exemplars or correlation identifiers when supported.

07. Graph and search models

Graph

A property graph represents nodes, relationships, labels, and properties. An RDF-style graph represents subject-predicate-object statements. Both make connected facts first-class. Graph storage is valuable when queries ask for variable-depth paths, neighborhoods, reachability, or changing relationship patterns.

START account A
FOLLOW "controls" or "beneficiary_of" edges up to depth 4
FILTER edges active at transaction_time
STOP paths that revisit a node
RETURN paths reaching any sanctioned entity

A fixed one-hop relationship is often just a relational join or key lookup. A graph becomes compelling when traversal is the primary question. Protect against supernodes, unbounded depth, cycles, expensive fan-out, and authorization leaks across edges. Set path depth, visited-node, time, and result limits.

Search

Search systems transform source text through analysis and build an inverted index. Instead of mapping each document to its words, the index maps a term to the documents and positions where it occurs. Query-time scoring can combine term statistics, fields, filters, recency, and business signals.

Authoritative document
  -> normalize and validate
  -> tokenize and analyze fields
  -> write term postings and stored fields
  -> publish a searchable segment

User query
  -> apply compatible analysis
  -> retrieve posting lists
  -> intersect filters
  -> score candidates
  -> return top K with a source version

Search is normally a derived serving model, not the authority for orders or permissions. Refreshes can lag. Deletes and permission changes need bounded propagation plus query-time enforcement where exposure would be unsafe. Reindexing into a new versioned index allows analyzer and mapping changes without editing every old segment in place.

08. Object and in-memory storage

Object storage

Object storage keeps a blob under a key with limited metadata. It suits images, videos, documents, exports, model artifacts, backups, and archives that are usually streamed as whole objects. Application metadata, relationships, permissions, and workflows often belong in a separate authoritative metadata store.

Treat upload as a state machine. Do not publish metadata before the blob is durable, and do not leave completed blobs forever when metadata creation fails. Use checksums, immutable object versions, size and type limits, malware scanning where required, short-lived upload capability, and a reconciler for abandoned staging objects.

In-memory storage

Memory reduces device latency but does not eliminate serialization, networking, locks, garbage collection, replication, or queueing. It is appropriate for caches, bounded session state, counters, rate-control state, and derived indexes when the durability and eviction contract is explicit.

09. Storage-engine internals

The logical model says what can be asked. The storage engine determines how bytes are written, found, rewritten, cached, and recovered.

Append-only logs and write-ahead logging

Sequential append is efficient and preserves an ordered history. A write-ahead log, or WAL, records enough change information before modified data pages are considered durable. After a crash, the engine replays committed log records and discards or rolls back incomplete work according to its recovery protocol.

BEGIN transaction
validate constraints and conflicts
append change records to WAL
flush required WAL bytes to durable media
record commit
ACKNOWLEDGE client

later:
write dirty pages, compact files, or checkpoint state

after crash:
load checkpoint
replay durable committed changes after checkpoint
restore a consistent state before serving traffic

An acknowledgement can mean accepted into memory, written to one device, replicated to several failure domains, or archived elsewhere. The application must know which. A filesystem call returning does not necessarily prove stable media without the engine's required synchronization protocol.

B-trees

A B-tree is a balanced, ordered search tree with nodes sized to storage pages. Internal nodes guide searches through key ranges; leaves contain entries or references. High fan-out keeps the tree shallow. Equality, prefix, ordered range, and sorted retrieval are natural operations.

                 [ 40 | 80 ]
                /     |      \
          keys < 40  40..79  keys >= 80
              |        |          |
          leaf page  leaf page  leaf page

Inserts may split full pages; deletes may leave reusable space or trigger rebalancing depending on the engine. Random updates can cause scattered I/O and page churn. Cache hit rate, key width, fill policy, page splits, and concurrency affect real performance.

Log-structured merge trees

An LSM design commonly appends changes to a log and inserts them into an ordered memory table. When the memory table fills, it becomes an immutable sorted file. Background compaction merges sorted files, keeps newer versions, and eventually removes tombstones or expired data.

write -> WAL -> mutable memory table
                    |
                    | full
                    v
              immutable table -> sorted file in L0
                                      |
                                      | background compaction
                                      v
                           larger sorted files in lower levels

read -> memory tables -> newest candidate files -> older levels
      use indexes and membership filters to skip impossible files

LSM engines can turn random writes into sequential batches, but reads may inspect several structures and compaction rewrites data. If incoming writes exceed flush and compaction capacity, files accumulate, read cost rises, disk fills, and the engine may stall writers.

Amplification

These measures move together. Aggressive compaction may improve reads and reclaim space while increasing physical writes. Tiered compaction can reduce write work while keeping more overlapping runs, increasing read and temporary space costs. Measure on the intended device, data distribution, update rate, compression, and retention policy.

10. Indexes and materialized views

Every index is a maintained copy of selected information, optimized for an access pattern.

Primary and secondary indexes

A primary index follows the main record identity or physical ordering. A secondary index maps another field or expression to primary records. In a partitioned system, a local secondary index is colocated with one partition; a global secondary index spans partitions and therefore adds routing, consistency, and recovery work.

For each index, record:

• the exact query it serves and expected selectivity,
• column or field order and supported range direction,
• whether it covers the returned fields or requires record lookups,
• write, storage, cache, replication, and rebuild cost,
• uniqueness or other correctness meaning, and
• usage, size, fragmentation, and removal criteria.

Compound index order

Query:
  WHERE tenant_id = ?
    AND customer_id = ?
    AND created_at < ?
  ORDER BY created_at DESC, order_id DESC
  LIMIT 50

Candidate ordered index:
  (tenant_id, customer_id, created_at DESC, order_id DESC)

Reasoning:
  equality keys identify a narrow ordered range
  created_at and order_id provide stable cursor order
  LIMIT bounds work
  tenant_id remains explicit for isolation and locality

An index is not useful merely because it contains the filtered fields. Leading fields, predicate type, collation, null handling, sort direction, data distribution, and optimizer statistics all matter. An index on a low-selectivity status may cost more than a scan unless combined with other keys or filtered to active rows.

Inverted indexes

An inverted index maps terms or contained values to posting lists of matching records. It supports full-text search, array membership, tags, and document-field containment. Posting lists may store frequency, positions, payloads, or skip information. Updating one document can touch many term entries.

Materialized views and denormalized projections

A materialized view stores a derived result, such as daily tenant revenue, conversation summaries, or search documents. Define:

• source-of-truth records and source version,
• full refresh or incremental update algorithm,
• freshness and convergence objective,
• ordering and duplicate behavior during replay,
• rebuild and cutover procedure, and
• how readers detect partial or stale results.

on event(entity_id, source_version, payload):
  current = projection.get(entity_id)
  if current exists and current.source_version >= source_version:
    acknowledge duplicate or old event
    return

  next = derive(payload)
  projection.compare_and_set(
    entity_id,
    expected_version = current.source_version,
    value = next with source_version
  )

11. Schema design, denormalization, and ownership

Model business facts once at the authority, then create explicit, rebuildable serving shapes for access patterns that need them.

Identity and keys

• Use stable identifiers that do not embed mutable business facts.
• Separate an opaque entity ID from a user-visible number when their lifecycles differ.
• Include tenant identity in authorization and often in locality or uniqueness keys.
• Use a deterministic tie-breaker with timestamps for stable pagination.
• Avoid timestamps alone as unique keys because clocks collide and can move backward.
• Do not place secrets or sensitive meaning in guessable identifiers.

Denormalization

Denormalization intentionally duplicates data to remove a join, avoid cross-partition work, preserve a historical snapshot, or meet a latency target. It is safe only when ownership and convergence are explicit.

Data ownership

One bounded component should own authoritative mutation rules for an entity. Other components use an API, event, snapshot, or approved read model. A shared table written by several services hides contracts, makes migrations unsafe, and allows one deployment to violate another component's invariants.

Polyglot persistence

Polyglot persistence uses different models for genuinely different access patterns. For example, orders remain authoritative in a transactional relational store, searchable descriptions are projected to a search index, receipts live in object storage, and short-lived catalog results are cached in memory.

Each additional store adds credentials, patching, drivers, capacity, backup, monitoring, incident paths, data movement, reconciliation, privacy deletion, and expertise. Start with one capable durable system and add another only when measured requirements justify the operational tax.

12. Schema evolution and online migrations

A migration is a distributed protocol between old code, new code, existing data, replicas, backups, background jobs, and operators.

Compatibility rules

• Backward-compatible reader: new code can read data written by old code.
• Forward-compatible reader: old code can tolerate data written by new code.
• Full compatibility: both directions work for the supported version window.
• Semantic compatibility: the same field still means the same thing, not merely the same wire type.

Be tolerant of unknown fields when the format contract allows it, but validate required invariants. Never silently reinterpret a unit, time zone, currency, identifier namespace, null, or default. A field rename is usually add, copy, switch, then remove, not an in-place rename across independently deployed consumers.

Expand and contract

1. EXPAND
   add given_name and family_name as optional fields
   deploy readers that understand old and new representations

2. DUAL WRITE OR DERIVE
   write new fields for new mutations
   keep old field while old readers exist

3. BACKFILL
   process stable key ranges in small resumable batches
   record progress, rate, errors, and source version
   do not overwrite a newer concurrent update

4. VERIFY
   compare counts, samples, checksums, nulls, and business invariants

5. SWITCH READS
   gradually read new fields with rollback available

6. CONTRACT
   stop old writes
   wait past rollback and retention window
   remove old readers, index, and field in a later release

Safe backfills

A backfill competes with production traffic for I/O, CPU, locks, cache, log bandwidth, and replica apply capacity. Use bounded batches, deterministic key-range checkpoints, rate limits, pause controls, idempotency, retry classification, and lag-aware throttling. Avoid offset pagination on a changing large table; resume from a stable key cursor.

cursor = load_checkpoint(job_id)

while not finished:
  rows = read_after(cursor, limit = 500)
  if rows is empty:
    mark_complete(job_id)
    break

  for row in rows:
    derived = transform(row.old_value)
    update row
      set new_value = derived
      where id = row.id
        and version = row.version
        and new_value is absent

  cursor = rows.last.id
  save_checkpoint(job_id, cursor)
  throttle_using(primary_load, replica_lag, log_rate, error_rate)

Small TypeScript boundary decoder

The principle is language-neutral: decode untrusted stored or received data at a boundary and turn it into one internal representation. TypeScript's static types alone do not validate runtime JSON.

type OrderName = { given: string; family: string };

function decodeName(value: unknown): OrderName {
  if (!value || typeof value !== "object") throw new Error("invalid record");
  const record = value as Record<string, unknown>;

  if (record.schemaVersion === 2) {
    if (typeof record.given !== "string" || typeof record.family !== "string") {
      throw new Error("invalid v2 name");
    }
    return { given: record.given, family: record.family };
  }

  if (record.schemaVersion === 1 && typeof record.fullName === "string") {
    const [given, ...rest] = record.fullName.trim().split(/\s+/);
    return { given: given ?? "", family: rest.join(" ") };
  }

  throw new Error("unsupported schema version");
}

Real name parsing is domain-specific and cannot reliably infer family structure from one string. This example demonstrates version control, not a universal human-name algorithm. Preserve original values when a migration could lose meaning, and use domain-approved rules.

13. Retention, deletion, archival, and legal hold

Data lifecycle is part of the model. Keeping everything forever increases cost, breach impact, restore time, and compliance risk.

Classify data

Deletion is a workflow

A soft delete marks a record hidden and preserves recovery or audit history. A hard delete removes it from active storage. Neither automatically removes replicas, indexes, caches, change logs, object versions, analytics, exports, or backups.

authorized erasure request
  -> place subject on deletion ledger
  -> block new processing where required
  -> delete or irreversibly anonymize authoritative records
  -> publish idempotent deletion event
  -> purge indexes, caches, features, objects, and derived tables
  -> verify every registered data sink
  -> record completion evidence without retaining erased content

backup policy:
  expire backups on a documented schedule
  prevent deleted data from returning to active use after restore
  replay the deletion ledger before reopening restored service

Legal hold can override ordinary expiry for a defined scope. It must be authorized, auditable, reversible, and narrow. Avoid one global flag that silently retains unrelated tenants or data classes.

Archival

Move cold data only after defining how it is found, authorized, restored, and deleted. Archive formats should be self-describing, checksummed, versioned, encrypted, and independent enough to be read after the serving schema changes. Maintain a small searchable catalog with object location, time range, schema version, checksum, encryption key reference, and retention status.

14. Durability, backup, restore, and disaster recovery

Replication keeps service running through some failures. Backups recover earlier state after corruption, deletion, ransomware, software defects, or correlated replica damage.

Backup choices

• Logical backup: exports records and schema in a portable logical form. It can support selective restore but may be slower for very large systems.
• Physical backup: copies engine files in a recovery-safe way. It is often faster for full restore but tied to engine format and recovery protocol.
• Snapshot: captures storage state at a point. Application consistency depends on coordination with the engine and other stores.
• Continuous log archive: retains change logs after a base backup, enabling point-in-time recovery when the sequence is complete.

A complete backup contract

Restore drills must start from the same artifacts available during a real disaster. Build a new isolated environment, retrieve keys through the recovery process, restore the data, replay logs to a target, run integrity and business checks, measure time, and document every manual dependency. A dashboard saying backups succeeded is not a restore test.

Multi-store recovery

If one workflow spans several stores, independent snapshots taken at different times can produce an impossible combination. Prefer one authority plus rebuildable projections. Otherwise capture a consistent logical recovery marker, retain replayable events, and define the order for restoring and reconciling components.

15. Security, privacy, multi-tenancy, and locality

The storage boundary contains high-value state. Authentication is only the first control.

Encryption and key management

• Use authenticated encryption for protected data at rest and authenticated transport in transit.
• Keep encryption keys separate from encrypted data and restrict key-use permissions.
• Rotate keys through versioned key references and background re-encryption where required.
• Back up key metadata and test recovery without exposing raw key material.
• Do not log plaintext values, queries, bind parameters, or object keys containing secrets.
• Understand leakage that encryption does not hide, including size, timing, access frequency, and some indexes.

Multi-tenant placement models

Carry tenant identity from authenticated principal to every query, key, index, cache, event, log, export, and backup authorization decision. Prefer database-enforced row or namespace policies as defense in depth, while testing privileged roles and administrative paths that may bypass them. Rate and capacity controls must also be tenant-aware to prevent one noisy tenant from consuming shared connections, I/O, memory, or compaction bandwidth.

Data locality and residency

Locality means placing data near the compute and other data used with it. Good locality reduces latency and network cost. Residency is a policy constraint about where data may be stored or processed. They overlap but are not identical.

Map authoritative records, replicas, logs, backups, indexes, support access, and analytics flows. A record stored in the correct region can still violate policy if its search index, backup, or telemetry leaves the boundary. Keep a placement registry and make movement an audited workflow with source cleanup and rollback.

16. Performance, capacity, and cost

Capacity planning includes logical data, physical amplification, working set, background work, failure headroom, and recovery space.

Useful estimates

logical_data =
  records * average_encoded_record_bytes

live_primary_footprint =
  logical_data
  + primary_index_bytes
  + secondary_index_bytes
  + row_or_document_overhead

steady_physical_footprint =
  live_primary_footprint
  * replica_count
  * observed_space_amplification

provisioned_capacity =
  steady_physical_footprint
  + retained_logs
  + compaction_or_migration_temporary_space
  + restore_workspace
  + growth_and_failure_headroom

physical_write_bandwidth =
  logical_write_bandwidth
  * observed_write_amplification
  * replica_factor

Backups are budgeted separately because retention can multiply storage. Cross-region replication, egress, requests, provisioned IOPS, memory, licenses, and operator time can dominate raw capacity. Estimate cost per useful unit such as one million writes, one retained tenant-month, one search query, or one restored terabyte.

Working set and cache

The working set is the data and index pages needed in the important time window. A 20 TB database with a 50 GB hot set may perform well with 100 GB of cache, while a 500 GB database with uniform random reads may be device-bound. Measure page-cache and block-cache hit rate, but also miss latency and the cost of refilling after restart.

Latency budget

client to service network          20 ms
queue and connection acquisition    8 ms
storage network                     5 ms
index traversal                    12 ms
record fetch                       20 ms
decode and authorization            5 ms
service to client network          20 ms
----------------------------------------
planned p99 budget                 90 ms
objective                         120 ms
remaining uncertainty/headroom     30 ms

Measure p50, p95, p99, and maximum over a stated window. Segment by operation, tenant class, partition, cache state, object size, and result count. A good global p99 can hide one consistently failing partition or tenant.

Hotspots and skew

• Monotonically increasing leading keys can direct writes to one range.
• A celebrity account, large tenant, or active conversation can dominate one partition.
• Low-cardinality indexes can concentrate updates on a few pages or posting lists.
• A scheduled expiry or backfill can create a synchronized deletion and compaction storm.
• A popular large object can consume bandwidth even when request rate looks small.

Track the top partitions and tenants, not only averages. Mitigations include better key distribution, bounded buckets, admission control, caching, precomputation, isolation, and workload-specific placement. Every extra bucket adds fan-out and reassembly cost.

17. Domain design: orders

Orders favor strong invariants and historical truth, with separate projections for search and analytics.

Assumptions

• 5,000 orders per second at peak and a ten-times larger read peak.
• Order creation updates order header, lines, totals, and an idempotency record atomically.
• Payment and inventory are separate domains with their own authoritative state.
• Seven-year retention, immutable financial history, and tenant isolation are required.
• Customers list recent orders; support looks up by order number; analytics may lag minutes.

Authoritative relational model

orders(
  tenant_id, order_id, display_number, customer_id,
  status, currency, subtotal, tax, shipping, total,
  shipping_address_snapshot, created_at, version
)
primary key (tenant_id, order_id)
unique (tenant_id, display_number)

order_lines(
  tenant_id, order_id, line_number,
  product_id, product_name_snapshot, unit_price_snapshot,
  quantity, line_total
)
primary key (tenant_id, order_id, line_number)
foreign key (tenant_id, order_id) references orders

order_requests(
  tenant_id, idempotency_key, request_hash, order_id, outcome
)
unique (tenant_id, idempotency_key)

Product name and price are intentional snapshots because an accepted order must not change when the catalog changes. Customer identity remains a reference because ownership persists. The shipping address can be encrypted at field or record level according to classification and retention policy.

Indexes and flows

• Recent history: (tenant_id, customer_id, created_at DESC, order_id DESC).
• Support lookup: unique (tenant_id, display_number).
• Fulfillment work: a narrow projection or filtered access path by fulfillment state and stable claim cursor, not repeated scans of every order.
• Analytics: replayable order events or change capture into a columnar or aggregate serving model.
• Search: a derived index containing permitted order fields and the authoritative source version.

BEGIN
  existing = find request by (tenant_id, idempotency_key)
  if existing:
    require existing.request_hash == hash(normalized_request)
    return existing.outcome

  validate money using exact decimal or integer minor units
  calculate totals from accepted line snapshots
  insert order and lines
  insert idempotency record
  append durable order-created record in the same atomic boundary
COMMIT

asynchronously:
  publish durable record
  update search, analytics, and customer-history projections idempotently

If one relational deployment meets scale and failure requirements, it minimizes correctness and operational risk. Partition later by tenant or stable order identity when measured contention, capacity, or locality demands it. Cross-partition uniqueness for display numbers then needs a tenant-local allocation rule.

18. Domain design: chat messages

Chat favors append-heavy, conversation-local ordered reads, but edits, deletion, moderation, and membership still need explicit models.

Assumptions

• 100,000 messages per second at peak across many conversations.
• Ordering is required within one conversation, not globally.
• Most reads request the latest 50 messages or page backward from a cursor.
• Messages are retained one year unless policy or legal hold changes the lifecycle.
• Large attachments live in object storage; message rows hold verified references.

Partition-local ordered model

partition key:
  (tenant_id, conversation_id, time_bucket)

sort key:
  (message_sequence_or_time, message_id)

value:
  sender_id
  body_or_body_reference
  created_at
  edit_version
  deleted_at
  reply_to_message_id
  moderation_state
  schema_version

A conversation sequencer can assign monotonic local sequence numbers, but it becomes part of the write path and failure model. A time-sortable unique ID reduces coordination but gives only the ordering promised by its generator and clock assumptions. The API should define a stable order and tie-breaker rather than promise a universal notion of simultaneous message time.

Edits, duplicates, and deletion

• Client-generated request IDs deduplicate retries after ambiguous timeouts.
• Edits use a version condition so concurrent updates do not silently overwrite each other.
• Deletion becomes a tombstone immediately in serving views, then physical cleanup follows policy.
• Membership and authorization are checked independently of message storage.
• A rebuilt conversation view replays immutable message events and latest edit or delete versions.
• Time buckets prevent unbounded partitions; pagination merges only the few adjacent buckets needed.

Search is a derived index scoped by tenant and conversation access. Membership revocation should not wait only for eventual index removal. Enforce current authorization at the query or result boundary and measure revocation propagation.

19. Domain design: searchable documents

Search documents are versioned projections built for retrieval, ranking, filters, and safe rebuilds.

Assumptions

• 200 million content documents, 2,000 source updates per second, and 20,000 searches per second.
• Results filter by tenant, visibility, language, type, and publication state.
• Text changes should become searchable within 30 seconds.
• Permission revocation has a stricter exposure objective than ordinary content freshness.
• The authoritative content store remains the source for complete records and conflict decisions.

Projection shape

{
  "tenantId": "...",
  "documentId": "...",
  "sourceVersion": 42,
  "title": "...",
  "body": "...",
  "language": "en",
  "contentType": "article",
  "visibilityScopeIds": ["..."],
  "publishedAt": "...",
  "deleted": false
}

Analyze language-specific text fields separately. Keep exact filter fields un-analyzed. Treat source version as a monotonic guard so replayed old updates cannot resurrect deleted or outdated documents. Limit visibility lists or use another permission filter design when groups are unbounded.

Zero-downtime reindex

create index_v2 with new mapping and analyzer
record source change position P0
bulk index a consistent source snapshot into index_v2
replay source changes after P0 using source-version guards
compare counts, samples, permissions, and representative queries
dual query a small shadow sample and compare quality and latency
atomically switch read alias from index_v1 to index_v2
monitor and keep rollback window
retire index_v1 after approval

Search relevance tests need judged query-result pairs, not only latency. Operational tests must also cover malformed documents, hot terms, large posting lists, segment merges, node loss, refresh lag, and full rebuild time.

20. Domain design: time-series metrics

Metrics storage is optimized for append, time-window retrieval, aggregation, and expiration, with strict controls on series cardinality.

Assumptions

• Two million active series and 500,000 samples per second.
• Raw samples stay online for 15 days; 5-minute aggregates stay 13 months.
• Most queries cover one hour to seven days and aggregate by service, region, or status class.
• Samples can arrive five minutes late; older corrections are exceptional.
• Monitoring data is operationally important but must not contain secrets or user identifiers.

Series and chunks

series_id = canonical_hash(
  metric_name,
  sorted bounded labels:
    service, region, environment, method, status_class
)

samples:
  (series_id, timestamp, numeric_value)

avoid labels:
  request_id, session_id, raw_url, email, stack_trace, random_value

Group consecutive samples into immutable or mostly immutable time chunks. Compress timestamps and values, maintain a label-to-series index, and partition retention by time so expiration drops whole chunks instead of deleting individual samples. Downsampling jobs write versioned aggregates and track completeness per interval.

Capacity sketch

500,000 samples/s * 86,400 s/day = 43.2 billion samples/day

At an observed compressed 2 bytes/sample:
86.4 GB/day of sample payload

Add:
series and label indexes
chunk metadata
replication
write-ahead logs
compaction and merge space
downsampled series
backup or recovery policy
failure and growth headroom

The two-byte number is an assumption that must be benchmarked with real series continuity and values. Short-lived series, sparse samples, labels, and metadata can make actual bytes per sample much larger.

21. Domain design: file metadata and blobs

Separate large immutable content from transactional metadata while joining them through a checksummed state machine.

Assumptions

• Files can be 1 KB to 20 GB and are uploaded directly to object storage.
• Users list folders, inspect versions, share files, and delete or restore within 30 days.
• Malware scanning must finish before a file becomes downloadable to others.
• Metadata changes need strong tenant, parent-folder, quota, and permission checks.
• Content retention, legal hold, and regional residency vary by tenant policy.

Metadata authority

files(
  tenant_id, file_id, parent_id, current_version_id,
  name, normalized_name, owner_id, lifecycle_state,
  created_at, deleted_at, version
)

file_versions(
  tenant_id, file_id, version_id,
  object_key, object_version, byte_size, content_type,
  cryptographic_checksum, scan_state, encryption_key_ref,
  created_by, created_at
)

upload_sessions(
  tenant_id, upload_id, file_id, expected_size,
  expected_checksum, expires_at, state
)

Upload state machine

INITIATED
  -> client receives narrow, expiring upload capability
UPLOADING
  -> object storage accepts bytes
UPLOADED
  -> service verifies size, checksum, and object ownership
SCANNING
  -> asynchronous security inspection
READY
  -> metadata transaction points current_version_id at safe object

Failure paths:
  expired or invalid upload -> ABANDONED -> object cleanup
  checksum mismatch         -> REJECTED  -> object quarantine or cleanup
  malware detected          -> QUARANTINED, never publicly downloadable

The object key should be opaque and non-authorizing. Every download verifies current metadata and permissions before issuing a short-lived, least-privilege retrieval capability. A background reconciler compares upload sessions, metadata, and object inventory to find orphaned objects and broken references. It uses age thresholds so it does not delete an upload still in progress.

22. Production architecture and operations

Operability is a storage requirement. A model that cannot be deployed, observed, repaired, and restored safely is not production-ready.

Deployment questions

• Which process, host, rack, zone, region, credential, and control plane can fail together?
• Where do authoritative data, replicas, logs, backups, indexes, and encryption keys live?
• Can remaining replicas carry peak traffic during maintenance or a failure?
• How are schema and software changes kept compatible during a rolling deployment?
• How are connections drained, writes fenced, and clients redirected during failover?
• Which operation is automatic, and which requires an authorized human decision?
• How long do restart, cache warming, replica catch-up, reindex, and full restore take?

Observability

Avoid placing raw SQL, document bodies, personal identifiers, or secrets in telemetry. Prefer normalized operation names, query fingerprints, bounded error codes, tenant tier, and hashed or sampled diagnostic identifiers under controlled access.

Alert on user risk and exhausted margin

High CPU by itself may be healthy utilization. Alert when latency or error objectives burn too quickly, free space will cross a safety threshold before response time, compaction debt grows without recovery, replication lag threatens failover or freshness, backup age exceeds RPO, or a restore verification fails. Every page needs an owner, impact statement, dashboard, and runbook.

Minimum runbooks

• Disk or object-capacity exhaustion and safe emergency reclamation.
• Slow query, lock contention, hotspot, and connection-pool exhaustion.
• Replica lag, failed replica, unsafe failover, and split-brain prevention.
• Compaction backlog or write stall.
• Corruption detection and isolation.
• Expired credential or encryption-key access failure.
• Bad schema migration and feature rollback.
• Point-in-time restore and post-restore reconciliation.
• Privacy deletion backlog and unauthorized-access investigation.

23. Failure mechanics, edge cases, and corrections

Storage failures are often ambiguous: the client does not know whether a timed-out write committed, and operators do not know whether a replica contains the latest safe state.

Failure matrix

Common mistakes

• Choose by category slogan. Correct by writing access-pattern cards, invariants, failure promises, and benchmarks first.
• Index every field. Correct by tying each index to a measured query and charging its write, storage, cache, and rebuild cost.
• Use a document as an unbounded container. Correct by moving independently changing or unbounded children into separately keyed records.
• Normalize or denormalize dogmatically. Correct by preserving authoritative facts and adding deliberate serving projections with repair paths.
• Treat a replica as a backup. Correct by keeping independently protected, versioned backups and practicing restore.
• Dual-write without a protocol. Correct by committing one authority and a durable change record atomically, then updating projections idempotently.
• Use offset pagination at large scale. Correct with stable keyset cursors that match the ordered index and include a unique tie-breaker.
• Assume a TTL is precise deletion. Correct by learning enforcement timing, tombstone behavior, backup retention, and verification needs.
• Hide tenant identity in application context only. Correct by including it in keys, policies, queries, caches, observability, and defense-in-depth tests.
• Run an unlimited backfill. Correct with bounded batches, checkpoints, load-aware throttling, pause controls, and verification.

Troubleshooting slow storage

Confirm user impact and affected operation
  |
  +-> Did demand, payload, result size, or tenant skew change?
  |
  +-> Is time spent waiting for pool, lock, queue, network, or storage?
  |
  +-> Did the plan or access path change?
  |      inspect query fingerprint, plan, statistics, and scanned/returned ratio
  |
  +-> Is cache cold or memory pressured?
  |
  +-> Is device, WAL, checkpoint, compaction, or replication saturated?
  |
  +-> Did deployment, migration, backup, expiry, or reindex add background work?
  |
  v
Mitigate the named bottleneck, preserve evidence, verify recovery,
and add a prevention or earlier-detection action

24. Testing strategy

Tests should prove business invariants, access performance, failure behavior, compatibility, lifecycle, and recovery at realistic scale.

Unit and property tests

• Key encoding preserves uniqueness, ordering, escaping, and round-trip decoding.
• Money, timestamps, nulls, defaults, and schema versions preserve domain meaning.
• State machines reject invalid transitions and accept idempotent repeats.
• Projection updates ignore old versions and converge under duplicates and reordering.
• Retention and authorization policy produce the expected decision for generated cases.

Integration and contract tests

• Run against the real engine version and relevant durability configuration.
• Verify transactions, uniqueness, foreign references, conditional writes, and isolation assumptions.
• Explain and assert important query plans, scanned rows, and index use with representative distributions.
• Test old reader/new writer and new reader/old writer combinations across the rollout window.
• Verify tenant isolation through ordinary, privileged, export, search, cache, and backup paths.

Load, stress, and soak tests

Generate the operation mix, payload sizes, hot-key skew, cardinality, retention churn, and concurrency expected in production. Run cold-cache and warm-cache cases. Include replica traffic, backups, compaction, backfill, expiry, and reindex work. A test that preloads uniform tiny records and sends only point reads is not evidence for a skewed mixed workload.

• Load test: prove latency and throughput at planned normal and failure peaks.
• Stress test: pass saturation and observe controlled rejection, recovery, and no invariant loss.
• Soak test: expose leaks, fragmentation, compaction debt, tombstone buildup, and slow replication drift.
• Capacity test: repeat with production-shaped data and index sizes, not an empty store.

Fault and recovery tests

• Kill a process before and after commit acknowledgement and verify retry outcomes.
• Lose a replica, delay a network path, exhaust a connection pool, and inject device latency.
• Pause compaction or change consumption and observe bounded backlog plus recovery load.
• Corrupt a test artifact and prove checksum detection, isolation, and recovery procedure.
• Restore a production-sized backup to a new environment and measure application-ready RTO.
• Recover to just before a destructive change and verify the achieved recovery point.
• Rebuild every derived view from source and compare versions, counts, samples, and permissions.

Migration tests

Copy a recent sanitized schema and distribution into a safe environment. Rehearse expand, backfill, verification, read switch, rollback, and contract. Measure locks, log generation, replica lag, cache effects, and total temporary space. Test interruption at each phase and resume from the durable checkpoint.

25. Hands-on exercises

Exercise 1: choose the order authority

An order platform expects 500 writes per second, needs unique per-tenant order numbers, changes three related records atomically, and supports unpredictable support queries. Compare relational, document, and key-value models.

Expected reasoning: start with the invariant boundary and changing query set. Relational storage is a strong default because constraints, transactions, joins, and mature indexing directly serve the workload. A document could hold a bounded order aggregate but shared idempotency and numbering still need atomic rules. Key-value storage adds manual access paths without a demonstrated scale need.

Exercise 2: repair a hot chat partition

One live-event conversation receives 30% of all messages. The model partitions only by conversation ID. Design a change without losing order or making every normal conversation expensive.

Expected reasoning: confirm the bottleneck and required ordering scope. Add time-bucket or bounded shard placement only for oversized conversations, preserve a conversation routing record, and merge bounded reads by stable sequence. If strict one-order append is required, the sequencer remains a possible bottleneck. Test transition while both old and new buckets are readable.

Exercise 3: change a search analyzer

A new language analyzer changes tokenization for 200 million documents. Describe a safe rollout.

Expected reasoning: create a versioned index, snapshot and capture source changes, bulk build, replay with version guards, verify relevance and permissions, shadow traffic, switch an alias, monitor, retain rollback, then remove the old index. In-place partial analysis would mix incompatible terms.

Exercise 4: cardinality incident

A deployment adds user_id to every request-latency metric and memory usage grows rapidly. What should happen?

Expected reasoning: stop or drop the unbounded label at ingestion, roll back the producer, preserve aggregate service metrics, and expire the accidental series. Identify the responsible producer through bounded metadata. Add label allowlists, per-tenant series budgets, cardinality dashboards, and pre-deployment tests.

Exercise 5: design a restore drill

The order store promises a 15-minute RPO and 2-hour RTO. Write the acceptance test.

Expected reasoning: restore a base backup and continuous changes into an isolated production-sized environment using disaster credentials, stop at a chosen target, apply schema and deletion-ledger requirements, run integrity and business queries, rebuild or reconnect projections, measure from declaration to safe traffic, and record achieved data point and time.

Exercise 6: find orphaned file objects

Direct uploads sometimes finish after the upload session expires. Design a safe cleanup job.

Expected reasoning: inventory staged objects by opaque upload ID, compare them with metadata and session state, ignore objects inside a conservative age window, finalize only after checksum and authorization checks, quarantine uncertain security cases, and delete confirmed orphans idempotently. Track bytes, age, errors, and deletion evidence.

26. Interview questions and model answers

1. How do you choose a database?

Start with business invariants, critical reads and writes, query shape, consistency, durability, RPO, RTO, scale, skew, retention, security, locality, operations, and cost. Derive keys, indexes, and ownership. Compare the simplest credible candidates using documented guarantees, a production-shaped benchmark, and a restore drill. Reject any candidate that fails a hard correctness or policy constraint.

Follow-up: What would make you revisit the choice? A new invariant crossing the store boundary, sustained load beyond measured headroom, unacceptable tail latency, residency change, operational incidents, or a new dominant access pattern.

2. Relational or document storage for orders?

Relational storage is usually the safer authority when order creation has multi-record invariants, uniqueness, audit queries, and evolving relationships. A document can fit a bounded aggregate read and preserve snapshots, but large or independently changing children should not be embedded. Scale alone does not decide. Explain the atomic boundary, access patterns, and partition plan.

Follow-up: Is duplicating a product name wrong? Not when it is an intentional historical snapshot of the accepted order. It would be wrong if treated as the current catalog name without a convergence rule.

3. What problem does normalization solve?

It records independently changing facts once, reducing insert, update, and delete anomalies. References and constraints preserve relationships. It does not mean every read must perform an expensive join. Add explicit projections or snapshots when an access or historical requirement justifies them.

Follow-up: When do you denormalize? When measured latency, locality, or scale requires a serving copy and the design defines authority, update path, staleness, drift detection, and rebuild.

4. What makes a good key?

It is stable, unambiguous, efficiently encoded, and compatible with identity, locality, partitioning, ordering, and privacy needs. A primary ID should not change with mutable business data. Ordered scans need a deterministic tie-breaker. A partition key must distribute load without destroying the locality required by reads.

Follow-up: Why not use timestamp alone? Clocks can collide, have limited resolution, and move. Pair time with a unique ID or use a generator whose exact ordering contract is understood.

5. Compare a B-tree and an LSM tree.

A B-tree maintains a balanced page-oriented ordered structure, naturally serving equality and range reads with a shallow traversal. An LSM commonly buffers writes in memory, flushes sorted files, and compacts them later, converting random writes into batched sequential work. LSM reads may examine more structures and compaction adds write and space costs. Actual performance depends on cache, workload, device, concurrency, and implementation.

Follow-up: Which is faster? The question is incomplete. Specify read/write mix, range scans, update rate, key distribution, cache, durability, data size, and compaction state, then benchmark.

6. Explain write, read, and space amplification.

Write amplification is physical bytes written per logical byte. Read amplification is physical work examined per useful result. Space amplification is physical live footprint relative to logical live data. Indexes, page updates, logs, replicas, versions, tombstones, and compaction contribute. Optimizing one may worsen another.

Follow-up: Why can deleting data increase work? An engine may first write a tombstone, then later compact or vacuum old versions before space is reusable.

7. Why does an index speed reads but slow writes?

It stores an alternate mapping so the reader can avoid scanning unrelated records. Every relevant insert, update, or delete must maintain that mapping, write logs, use cache, replicate, and eventually clean obsolete entries. Index selectivity, order, width, and covering fields determine whether the read benefit justifies the cost.

Follow-up: Why might an index not be used? The query may not match its leading fields or operators, selectivity may be poor, statistics may be stale, a cast may change the expression, or a scan may genuinely cost less.

8. Why are global secondary indexes difficult?

The primary record and index entry may live on different partitions. An update then needs a distributed atomic protocol or accepts temporary inconsistency and repair. Unique global indexes add coordination. Rebuild, resharding, lag, and failure handling are also cross-partition operations.

Follow-up: Alternative? Make the secondary access a derived projection with a stated freshness bound, or choose a partition key that keeps the needed uniqueness and query local.

9. What is a materialized view?

It is a stored query result or derived projection. It reduces read-time work by paying storage and maintenance cost earlier. The design must define source, freshness, incremental or full refresh, idempotency, version ordering, rebuild, and cutover.

Follow-up: Can it be the source of truth? Only if ownership is intentionally transferred with new invariants and mutation rules. A normal projection remains rebuildable from its authority.

10. Why is a search index usually not authoritative?

Search indexing commonly refreshes asynchronously, transforms source fields, accepts partial updates, and optimizes relevance rather than transactional invariants. Keep business truth in its authority, carry a source version, and rebuild the index. Recheck current authorization where stale permission data could expose content.

Follow-up: How do you change analyzers? Build and verify a versioned new index, catch up changes, switch an alias, and retain rollback.

11. When is polyglot persistence justified?

When one important access pattern has requirements that the existing store cannot meet safely or economically, and a specialized derived or authoritative store has evidence of material benefit. Charge the decision for operations, security, backups, schema evolution, data movement, reconciliation, privacy, and expertise.

Follow-up: What is the default? One capable durable authority plus explicit projections, adding systems only as requirements and measurements demand.

12. Is a document store schema-free?

No. Field names, types, meanings, constraints, defaults, and compatibility still form a schema, even if enforcement lives in application code. Validate at boundaries, version formats, monitor unknown shapes, and migrate semantics deliberately.

Follow-up: Benefit of flexible enforcement? Different record versions can coexist during controlled rollout and optional fields can evolve without an immediate full rewrite.

13. How do you change a large schema without downtime?

Use expand and contract: add a compatible representation, deploy tolerant readers, start new writes, backfill in bounded resumable batches, verify, gradually switch reads, stop old writes, wait beyond rollback needs, then remove old data later. Throttle on primary load, replica lag, logs, and errors.

Follow-up: How do you avoid overwriting concurrent writes? Compare the source version read by the backfill and update only if it is unchanged and the new field is absent.

14. Why is replication not backup?

Replication rapidly copies desired writes but can also copy accidental deletion, corruption, malicious changes, and bad migrations. Backups preserve recoverable historical states under separated controls. Both require tests: failover for replication and restore for backup.

Follow-up: What makes a backup usable? Complete artifacts, keys, schema and configuration, verified checksums, documented dependencies, and a timed application-level restore.

15. Explain RPO and RTO.

RPO bounds acceptable data loss measured in time or business position. RTO bounds time from disaster to required service restoration. Continuous logs may improve RPO, while automation, bandwidth, restore parallelism, data size, validation, and dependency readiness determine RTO.

Follow-up: How do you prove them? Run a production-sized disaster drill, choose a recovery target, restore from protected artifacts, validate, and record achieved point and time.

16. What are the risks of TTL-based retention?

Expiry may be asynchronous, create tombstones and compaction work, conflict with legal hold, leave derived copies, and not remove backups immediately. A synchronized expiry wave can overload storage. Define semantic expiration, physical cleanup, exceptions, propagation, and verification.

Follow-up: How do you handle a legal hold? An authorized, scoped policy prevents ordinary expiry, is audited, and can later resume lifecycle without retaining unrelated data.

17. Compare tenant isolation models.

Shared tables pool resources efficiently but need tenant keys, enforced policies, and noisy-neighbor controls. Per-namespace increases logical isolation but expands migration and catalog work. Per-database or cluster improves isolation and custom recovery or residency but costs more and requires fleet automation. A hybrid places tenants by risk and size.

Follow-up: Is row-level policy enough? No. Use it as defense in depth alongside authenticated tenant context, compound keys, least privilege, cache and search isolation, privileged-path tests, and audit.

18. How do you diagnose a hot partition?

Compare per-partition operations, bytes, latency, queueing, throttles, key distribution, and top tenants against the global workload. Identify whether the cause is a monotonic key, celebrity entity, large object, low-cardinality index, or scheduled job. Fix the access and placement model, not just fleet size.

Follow-up: Why not always add random sharding? Reads must fan out, ordering and uniqueness become harder, and small entities pay needless complexity. Apply bounded sharding to measured hotspots with a routing rule.

19. Why separate file blobs and metadata?

Large immutable blobs need streaming durability and capacity, while folder listings, permissions, versions, quotas, and lifecycle need transactional queries. The split uses each model for its access pattern. A state machine, checksum, versioned object reference, and reconciler protect the boundary.

Follow-up: What if upload succeeds but metadata fails? Keep the object staged and use a grace-period reconciler to finalize valid sessions or delete confirmed orphans.

20. Which storage signals matter most?

User-visible latency and errors by operation, demand and bytes, connection and lock wait, device saturation, cache effectiveness, log rate, checkpoint or compaction debt, disk headroom, replication lag, partition skew, derived-view lag, backup age, and verified restore time. Segment by tenant and partition so aggregates do not hide harm.

Follow-up: When should on-call be paged? When user objectives burn rapidly or a margin such as disk, replication, compaction, or backup age will soon violate a correctness or recovery promise and requires immediate action.

27. Concise cheat sheet

• Start with facts, invariants, access patterns, lifecycle, and failure promises.
• Logical model, storage engine, consistency, and deployment topology are separate choices.
• Relational: constraints, transactions, joins, and evolving queries.
• Key-value: predictable direct lookup when the request can name its key.
• Document: bounded aggregate read and write boundary, not an unbounded container.
• Wide-column: partition-local ordered queries designed in advance.
• Graph: variable-depth relationship traversal is the primary question.
• Time-series: append, time windows, retention, downsampling, and bounded label cardinality.
• Search: inverted-index projection for relevance and filters, usually not authority.
• Object: large whole blobs with transactional metadata stored separately.
• In-memory: low latency with explicit eviction, restart, and rebuild semantics.
• B-trees favor shallow ordered lookup and ranges.
• LSM trees batch writes into sorted files and pay background compaction.
• Measure write, read, and space amplification together.
• Every index buys a read path with write, space, cache, and rebuild cost.
• Normalize authoritative facts; denormalize deliberately with a repair path.
• One authority settles conflicts; projections carry source versions and are rebuildable.
• Use expand, backfill, verify, switch, and contract for online schema changes.
• Replication supports availability; independently protected backups support historical recovery.
• A backup is proven only by a timed, verified restore.
• TTL, privacy erasure, archival, legal hold, and backup expiry are one lifecycle system.
• Carry tenant identity through keys, policies, indexes, caches, telemetry, exports, and backups.
• Capacity includes indexes, logs, replicas, amplification, migration space, backups, and headroom.
• Watch per-partition and per-tenant distributions, not only global averages.
• Benchmark the real data distribution, mixed workload, engine settings, failures, and recovery.

28. Current official references

• PostgreSQL Documentation: Constraints
• PostgreSQL Documentation: Index Types
• PostgreSQL Documentation: GIN Introduction
• PostgreSQL Documentation: Materialized Views
• PostgreSQL Documentation: Row Security Policies
• PostgreSQL Documentation: Continuous Archiving and Point-in-Time Recovery
• PostgreSQL Documentation: Backup and Restore
• FoundationDB: A Distributed Unbundled Transactional Key Value Store
• FoundationDB Documentation: Data Modeling
• RocksDB Official Wiki: Storage Engine Overview
• RocksDB Official Wiki: Compaction
• Apache Lucene Documentation: Index File Formats
• Prometheus Documentation: Data Model
• Prometheus Documentation: Storage
• W3C RDF 1.2 Concepts and Abstract Syntax
• RFC 3339: Date and Time on the Internet
• Node.js Documentation: Crypto
• OWASP Database Security Cheat Sheet
• NIST SP 800-57 Part 1 Revision 5: Key Management
• NIST SP 800-209: Security Guidelines for Storage Infrastructure