Skip to content

PATTERN Cited by 1 source

Schema iteration via load testing

Intent

Treat schema design as an empirical optimization problem — each candidate schema is held up against a fixed hardware envelope under a production-representative load and measured on the resource metric most recently seen to be saturated; the next candidate is chosen based on the observed remaining bottleneck, not on a priori architecture intuition.

Output: a schema whose performance profile on the target envelope is evidence-based rather than guess-based, and whose chosen form encodes the specific trade-offs made visible by the rig.

Context

The schema-design space for any non-trivial workload has many locally-viable options: field-name shortening, data-type tightening, the Bucket / Computed / Extended Reference / Attribute / Subset patterns, secondary indexes, compound indexes, TTL indexes, partial indexes. Which combination wins depends on the resource saturation order under the specific workload — and this is not predictable from schema shape alone.

Architects who pick a schema and ship it often discover post-hoc that the dominant cost wasn't what they planned for: smaller documents didn't help because the index was the bottleneck; bigger buckets didn't help because the aggregation pipeline ran out of memory; better compression didn't help because CPU was already saturated.

Solution — the loop

  1. Define the load test surface. Concrete representative operations (e.g. 500 M event inserts, then 5 different date-range report aggregations) with concrete hardware (e.g. 4 GB RAM, WiredTiger default cache, single mongod instance, no replication). The hardware envelope must expose the trade-offs — a benchmark rig that fits everything in cache teaches nothing.

  2. Establish a baseline schema. Usually the naïve, easy-to- query, pattern-free version. Measure all top-level resources (CPU %, disk %util, cache hit rate, memory pressure, query rates, latency percentiles). Identify the saturated resource.

  3. Apply the smallest schema change that targets the current bottleneck. Don't combine multiple techniques; don't optimize for "best imagined" performance. One lever at a time.

  4. Re-measure the same surface. Compare new bottleneck + new rates vs baseline. Two outcomes:

  5. Improvement + same bottleneck — the lever worked, apply more of it (wider bucket, smaller fields, etc.).
  6. Improvement + different bottleneck — the lever worked but moved the constraint; the next iteration targets the new constraint.

  7. No improvement — the schema change didn't address the actual saturated resource; revert and try something else.

  8. Repeat until the bottleneck is the target hardware's fundamental limit (e.g. network egress, disk throughput, CPU) rather than a schema-level inefficiency.

Canonical example: MongoDB Cost of Not Knowing series

The entire three-part case study is a public instance of this pattern executed over ~25 schema revisions (appV1 → appV6R4):

  • Parts 1 + 2 iteratively apply field-name shortening → data- type tightening → concatenated _id composition → Bucket Pattern → Computed Pattern, each guided by the previous revision's saturation signal. Winner of Part 2 (appV5R3): quarterly-bucketed + per-day-pre-aggregated, 385 B / doc, 11.96 GB data, 1.11 GB index.
  • Part 3 observes appV5R4's remaining bottleneck is disk throughput. Applies the dynamic-schema field-name encoding → appV6R0 with 125 B documents. Re-measures: document shrank 67.5 % but throughput barely moved. Diagnoses new bottleneck as index > WiredTiger cache (3.13 GB > 1.5 GB). Applies the targeted fix — quarter-bucketing while keeping the dynamic schema — → appV6R1. Index drops to 1.22 GB (under cache), per-event total size drops 28.1 %. The loop found the right schema by observing the rig, not by predicting.

Consequences

Benefits

  • Schema chosen for the actual bottleneck, not the imagined one. The appV6R0 → appV6R1 pivot is the clearest illustration: an architect optimizing for disk-throughput would have stopped at appV6R0 and declared victory on document size; the load test forced the pivot.
  • Surfaces bottleneck migration. Schema changes dislodge constraints in predictable ways; having the rig makes these transitions visible and treatable.
  • Produces defensible numeric comparisons. Each candidate has a number; the best one wins. No architectural opinion required for arbitration.
  • Decouples schema design from deployment scale. A 4 GB-RAM rig can teach which trade-offs matter; the chosen schema then carries those trade-offs into production regardless of production hardware.

Costs

  • Rig construction is expensive. Representative data, realistic operation mix, stable timing. 500 M events per revision is not cheap to generate + ingest + re-measure.
  • Bottleneck-dependent conclusions don't transfer. appV6R1's "quarter bucket wins over month bucket" is true on the 4 GB- RAM rig because of the cache ceiling. On a 64 GB-RAM production server, appV6R0 might win — the index would fit in cache and document-size would dominate. The methodology transfers; specific winners don't.
  • Iteration discipline required. Changing multiple variables between revisions produces ambiguous attribution. The MongoDB case study is careful to isolate each lever — one schema change per revision.
  • Slow. Optimizing a single workload through 6 major schema versions with ~4 revisions each is the work of weeks to months, not days.

Seen in

  • sources/2025-10-09-mongodb-cost-of-not-knowing-mongodb-part-3-appv6r0-to-appv6r4 — explicit public execution of the pattern across the Part-3 appV6R0 → appV6R1 pivot: load test observes appV6R0's document- size win was neutralized by index-in-cache pressure, targeted next iteration to re-shrink the index via quarter-bucketing while keeping the dynamic-schema compression win. MongoDB Cost of Not Knowing Parts 1 + 2 (not yet ingested) applied the same loop over simpler levers.
Last updated · 200 distilled / 1,178 read