The Hidden Cost of Secondary Access Patterns in NoSQL
A secondary access path is another maintained data structure with its own placement, storage, failure, and consistency behavior—not a free query option.
Situation
Operational NoSQL schemas are designed around known keys. Requirements then expand: find an order by customer, locate a device by status, or filter accounts by region. The tempting answer is “add an index,” but that phrase hides four materially different implementations across DynamoDB, MongoDB, Cassandra, and Bigtable.
The Problem
It is misleading to assign each engine a universal write-amplification number. Amplification depends on projected attributes, item size and billing rounding, index count, compaction, replication, update frequency, selectivity, and cache residency. It also has at least three units:
logical_mutations = base_mutation + affected_secondary_mutations
billed_write_units = sum(ceil(bytes_written_to_each_structure / billing_quantum))
physical_bytes = logical_bytes * measured_storage_engine_amplification
These quantities must not be compared as though they were the same ratio.
Model the Access Path Explicitly
flowchart TD
Q["New query requirement"] --> B["Bound cardinality, selectivity, freshness, and result size"]
B --> O{"Can an existing primary key answer it"}
O -->|Yes| R["Reuse bounded key lookup"]
O -->|No| C["Compare maintained index, derived table, and analytical path"]
C --> W["Calculate writes, bytes, storage, and failure semantics"]
W --> T["Load-test updates, backfill, rebuild, and skew"]
T --> D["Choose only with an SLO and removal plan"]
DynamoDB global secondary indexes
A GSI has its own partition and sort key, provisioned or on-demand capacity behavior, and projected attributes. Base-table writes propagate to each affected GSI; large projections and billing-unit rounding change cost. GSI key choice can create throttling independent of the base table. Use sparse indexes where the business predicate is naturally sparse, project only required attributes, and monitor both table and index throttles. AWS documents item-size and capacity-unit mechanics; calculate them from actual encoded items rather than a fixed multiplier. DynamoDB data-modeling blocks, capacity guidance.
MongoDB secondary indexes
MongoDB maintains index keys with the collection mutation. Cost depends on index key size, array multikey expansion, update pattern, working-set residency, and write concern. An index may accelerate a selective query while increasing cache pressure and replication work. background: true is not a modern tuning lever: current index builds use an optimized build process, and obsolete options must not appear in a new runbook. Validate with explain, index-usage statistics, cache and eviction metrics, replication lag, and a production-shaped update test. MongoDB index documentation.
Cassandra Storage-Attached Indexing
Cassandra 5.0 SAI attaches index components to SSTables and follows their lifecycle. It broadens supported predicates but does not repeal partitioning physics: query fan-out, selectivity, tombstones, compaction, and replica availability still define cost. Prefer a dedicated query table when an access pattern is high-volume, latency-critical, and stable; consider SAI when the operationally tested predicate and cardinality fit its documented behavior. Apache Cassandra SAI documentation.
Bigtable continuous materialized views
Bigtable’s primary index is the row key. A continuous materialized view creates a separate automatically updated table with a schema optimized for another query; it is not “zero amplification on the primary.” It consumes storage and processing and has product limitations that must be checked at design time. Use the documented create and management interfaces rather than invented SQL provisioning syntax. Bigtable continuous materialized views, tables and views.
A Reproducible Decision Table
| Question | Evidence required |
|---|---|
| How many secondary mutations does one business update cause? | Mutation trace by create, update, delete, and key change |
| How many bytes and billed units are added? | Serialized production-shaped records and documented billing quantum |
| What happens under a hot secondary key? | Skewed-key test, not a uniform benchmark |
| How is backfill or rebuild isolated? | Measured foreground tail latency and recovery time |
| What consistency can readers observe? | Documented propagation semantics plus failure injection |
| Can the access path be removed safely? | Usage telemetry and rollback procedure |
Benchmark protocol
Run four phases against production-shaped cardinality and skew: base table without the secondary path, steady-state writes with it enabled, historical backfill or index build, and rebuild after an injected failure. Keep acknowledgement level, replication, payloads, and hardware constant. Record client p50 and p99, database CPU and storage latency, cache eviction, replication lag, bytes stored, compaction or background work, and recovery time. Publish the configuration with the results. A benchmark that changes durability or omits the build phase cannot support an architectural decision.
In Practice
The documented pattern is that each system pays in a different layer: DynamoDB exposes a separately capacity-accounted index; MongoDB synchronously maintains B-tree structures as part of writes; Cassandra SAI stores index components alongside SSTables; Bigtable materializes a second maintained view table. No primary source supports a universal ordering such as “engine A is 1.2× and engine B is 5×.” Those figures require a named benchmark with schema, versions, hardware, and workload.
Where It Breaks
| Failure mode | Mechanism | Control |
|---|---|---|
| Hot secondary key | Alternate key has lower cardinality than the primary | Model skew and bucket only with bounded read fan-out |
| Oversized projection | Index duplicates attributes the query never reads | Minimize projection and measure encoded size |
| Cache displacement | Index working set evicts base data | Track cache residency, eviction, and tail latency |
| Rebuild overload | Backfill competes with foreground writes | Rate-limit, checkpoint, and prove restartability |
| Stale derived path | Asynchronous pipeline lags or loses ordering | Version mutations; monitor age; reconcile by key |
| Index sprawl | Old access paths remain after clients leave | Record owner and usage; delete through a measured change |
What to Do Next
- Problem: “One more index” hides independent cost and failure behavior.
- Solution: Model logical mutations, billed units, and physical bytes separately.
- Proof: Benchmark skew, updates, backfill, rebuild, and failure recovery with production-shaped data.
- Action: Require an owner, SLO, cost worksheet, and removal test for every secondary access path.
Interactive tools for this topic