Production Data Systems Handbook / Chapter 63
Appendix I: Comparison Matrices
Vendor-neutral matrices for comparing production data-system classes, consistency mechanisms, operational burden, and migration risk.
Purpose
Use these matrices after the workload fingerprint and invariant register are complete. Begin with the system classes, then test the surviving candidates against consistency, operation, failure, and migration. The result should be a shortlist with evidence and an explicit rejection record, not a product name chosen from one attractive row.
The fit marks below are priors. Strong means the class commonly offers a natural mechanism for the need. Medium means the fit depends heavily on modeling, extensions, or a constrained workload. Weak means the team is likely to build substantial machinery or place the need at another system boundary. A managed service can change operational burden; it does not change the application’s invariant.
Before using the table, write the scale, latency and freshness budgets, authority, access paths, failure tolerance, recovery target, security boundary, and team ownership beside each workload need. Replace every generic mark that affects the decision with local evidence: a prototype, production measurement, restore or rebuild drill, cost model, or documented operating history.
System-Class Fit Matrix
| Workload need | Relational | Key-value | Document | Wide-column | Search | Event log | Warehouse/lakehouse | Time-series | Graph | Vector |
|---|---|---|---|---|---|---|---|---|---|---|
| Multi-row invariants | Strong | Weak | Medium | Weak | Weak | Weak | Weak | Weak | Medium | Weak |
| Point lookup by key | Strong | Strong | Strong | Strong | Medium | Weak | Medium | Medium | Medium | Medium |
| Range scan by ordered key | Strong | Medium | Medium | Strong | Medium | Weak | Medium | Strong | Weak | Weak |
| Flexible ad hoc query | Strong | Weak | Medium | Weak | Medium | Weak | Strong | Medium | Medium | Weak |
| High-volume append events | Medium | Medium | Medium | Strong | Weak | Strong | Strong | Strong | Weak | Weak |
| Full-text relevance | Weak with extensions | Weak | Weak | Weak | Strong | Weak | Weak | Weak | Weak | Medium with hybrid retrieval |
| Historical analytics | Medium | Weak | Weak | Medium | Weak | Medium | Strong | Medium | Weak | Weak |
| Time-window metrics | Medium | Medium | Weak | Medium | Weak | Medium | Medium | Strong | Weak | Weak |
| Variable-depth traversal | Medium | Weak | Weak | Weak | Weak | Weak | Weak | Weak | Strong | Weak |
| Similarity search | Weak with extensions | Weak | Weak | Weak | Medium | Weak | Weak | Weak | Weak | Strong |
Object storage is deliberately absent from the columns. If the workload includes images, archives, backups, large immutable files, or a lakehouse data layer, compare object stores as a separate candidate class. Putting large objects in a database merely because the database won another row is a boundary decision, not a neutral default.
No candidate needs to win every row. A transactional source of truth, an event log, and a search index may be the honest answer when the workload requires strong order acceptance, replayable change, and low-latency relevance. That composition also creates synchronization, deletion, recovery, and ownership obligations. Record those obligations before treating specialization as a win.
System-Class Caveats
| Class | Good default when | Be careful when |
|---|---|---|
| Relational | Invariants, joins, transactions, familiar operations, and flexible queries matter. | Tenant skew, write amplification, unbounded analytical scans, or multi-region active writes dominate. |
| Key-value | Access is mostly by primary key and the value can be treated as an aggregate. | Queries need secondary access paths, cross-key invariants, or ad hoc analysis. |
| Document | Records are naturally document-shaped and reads often need the whole document. | Partial updates, cross-document consistency, schema evolution, or reporting become central. |
| Wide-column | Writes are large, partitioned, append-heavy, and access paths are known in advance. | Query patterns are still changing or hot partitions are likely. |
| Search | Relevance, tokenization, faceting, ranking, and text retrieval are core. | Search becomes source truth or delete propagation is not monitored. |
| Event log | Ordered replay, fan-out, audit trail, and integration streams matter. | Consumers are not idempotent or retention is shorter than rebuild needs. |
| Warehouse or lakehouse | Historical analysis, large scans, joins, and batch transforms dominate. | Low-latency transactional correctness or row-level operational writes are required. |
| Time-series | Time-window ingestion, retention, downsampling, and metrics queries dominate. | High-cardinality labels or arbitrary joins drive cost and latency. |
| Graph | Relationship traversal is the primary workload, especially variable-depth traversal. | The graph is small enough for simpler relational queries or transactional source truth is elsewhere. |
| Vector | Similarity search over embeddings is a core retrieval path. | Access control, deletion, re-embedding, and evaluation are not designed. |
Consistency Mechanism Matrix
| Need | Candidate mechanism | Watch-outs |
|---|---|---|
| Unique username or tenant slug | Unique constraint, conditional write, reservation service. | Cross-region uniqueness is harder; retries must distinguish conflict from timeout. |
| Prevent lost update | Transaction isolation, optimistic version, compare-and-swap. | Test concurrent updates and stale clients; define retry semantics. |
| Preserve ledger balance | Append-only ledger, transactional ledger entry, double-entry model, reconciliation. | External side effects need idempotency and compensating entries. |
| Keep source and search in sync | Transactional outbox, CDC, replayable indexer, tombstones. | Define delete freshness, replay behavior, and rebuild evidence. |
| Process event once in effect | Idempotency key, deduplication table, transactional side-effect boundary. | Transport-level exactly-once does not guarantee external side-effect correctness. |
| Maintain derived aggregate | Materialized view, stream processor, batch rebuild, reconciliation. | Late events, corrections, deletes, and replay can change totals. |
| Enforce tenant isolation | Authorization middleware, row-level security, scoped service token, audit. | Support tools, exports, caches, and analytics often bypass the main path. |
| Global active writes | Consensus, regional ownership, conflict-free model, conflict resolution workflow. | Latency, split-brain prevention, and conflict semantics dominate design. |
| Read-your-writes | Primary read, session token, monotonic read routing, bounded staleness guarantee. | Replica lag and failover can break assumptions unless measured. |
| Delete propagation | Tombstone, deletion log, consumer replay, verification job. | Compaction and backup retention must not erase required deletion evidence too early. |
Candidate Burden Matrix
Generic labels such as “moderate on-call demand” hide the decision. Copy one column per actual candidate and fill it with mechanisms, limits, owners, and evidence. Include every supporting system introduced by the design.
| Operating concern | Candidate A | Candidate B | Candidate C |
|---|---|---|---|
| Provisioning and change authority | [owner, approval path, lead time] | ||
| Backup source, isolation, and retention | [mechanism and evidence] | ||
| Point, tenant, or object restore | [granularity, measured time, proof] | ||
| Rebuild and replay | [source, retained history, duration, side-effect safety] | ||
| Schema and contract evolution | [compatibility mechanism and enforcement] | ||
| Partitioning and skew | [key, largest tenant or key range, rebalance path] | ||
| Capacity and cost | [dominant unit, forecast, budget alarm, exhaustion point] | ||
| Query and workload isolation | [limits, admission control, noisy-neighbor boundary] | ||
| Observability and diagnosis | [SLIs, per-tenant visibility, explainability, evidence retention] | ||
| Access, tenant isolation, and audit | [enforcement points and bypass paths] | ||
| Deletion and privacy propagation | [SLO, tombstone or lineage path, verification] | ||
| Upgrade, failover, and vendor exit | [tested procedure, rollback window, export boundary] | ||
| On-call ownership | [named team, skills, escalation, after-hours dependency] |
Failure-Mode Matrix
| Failure mode | Early signal | Design control |
|---|---|---|
| Hot key or hot tenant | Per-key latency, throttling, lock wait, uneven partition load. | Partition strategy, tenant isolation, rate limits, queueing, rebalancing plan. |
| Stale derived view | Freshness SLI, lag, old watermark, user mismatch reports. | Outbox or CDC, replay, rebuild plan, user-visible freshness where needed. |
| Duplicate side effect | Duplicate idempotency key, repeated ledger entry, repeated webhook response. | Idempotency, deduplication, transactional side-effect boundary, reconciliation. |
| Lost update | Conflicting writes, version mismatch, unexpected overwrite. | Optimistic version, stronger isolation, conditional writes, conflict workflow. |
| Query storm | Saturated CPU or I/O, queue depth, warehouse spend spike. | Workload isolation, quotas, query limits, cached aggregates, admission control. |
| Backup cannot restore | Failed restore drill, missing logs, unknown credentials. | Routine drills, immutable backups, documented access, validation queries. |
| Delete does not propagate | Deleted record visible in cache, search, export, or analytics. | Tombstones, deletion SLO, consumer tests, sampled verification. |
| Schema change breaks consumer | Deserialization errors, null spikes, contract test failure. | Compatibility policy, schema registry, dual-publish, consumer discovery. |
Migration Risk Matrix
| Migration shape | Primary risk | Safer pattern |
|---|---|---|
| Add nullable column or optional field | Old readers misinterpret null or missing values. | Define null semantics, deploy readers before writers, add contract tests. |
| Add required field | Old writers cannot populate it. | Add optional field first, backfill, verify, then enforce requirement. |
| Change field meaning | Consumers keep old interpretation. | New field or versioned contract, deprecation window, consumer sign-off. |
| Repartition or reshard | Hot partitions, routing mistakes, partial movement. | Shadow routing, per-partition verification, staged cutover, rollback window. |
| Move source truth | Dual-write drift and unclear authority. | Single authority rule, outbox or CDC, shadow reads, reconciliation, explicit cutover. |
| Rebuild search or vector index | Missing deletes, stale embeddings, ranking drift. | Versioned index, tombstone replay, evaluation set, side-by-side comparison. |
| Backfill derived data | Production overload and non-idempotent retries. | Chunking, throttle, checkpoint, retry-safe writes, per-chunk checksums. |
| Retire data store | Unknown consumers and lost recovery path. | Access-log observation, contract deprecation, dark launch removal, archive plan. |
Review Questions
Do not average the cells into a score. A compensating strength cannot cancel a broken invariant, an unowned recovery path, or a migration the team cannot reverse. Close the comparison by answering:
- Which workload need eliminates at least one candidate class?
- Which invariant requires a specific consistency mechanism?
- Where will authority live, and which stores are derived or rebuildable?
- Which operating burden is the team least prepared to carry?
- Which failure mode would cause the worst user or business harm?
- Which migration risk needs a prototype or drill before approval?
- What observation would invalidate the preferred candidate?
Record the preferred candidate, the strongest rejected alternative, the decisive evidence, the unresolved risk and its owner, and the date or condition that forces review. Turn any unproven recovery, replay, migration, access, or deletion claim into a drill from the next appendix before approval.