A team building a product catalog on a key-value store creates four separate index tables alongside the base product table — one each to look up products by category, by brand, by warehouse, and by price tier. What operational cost does this design add to every single product write, and how does that cost scale as more index tables are added?

## The shape of the cost Write amplification is the direct, mechanical cost of the index table pattern, and it's worth being precise about how it accumulates rather than treating it as a vague 'more writes are slower' hand-wave. In the scenario described, the base product table holds the authoritative row keyed by SKU or product ID. Each of the four additional tables — by category, by brand, by warehouse, by price tier — exists purely to answer a query the base table's key can't answer directly. None of these four tables are optional extras the store maintains for you; every single one is a hand-maintained structure the application is responsible for writing to, in addition to the base write, on every mutation. ## Counting the writes Concretely: - **Creating a brand-new product** means one write to the base table plus one write to each of the four index tables that has to include this new product — five writes total for one logical 'create a product' operation. - **Updating an existing product's price** (assuming price tier is a bucketed value, like 'under $25' or '$25–$100') means: one write to the base table, plus — if the price tier bucket changed — a delete of the old price-tier index entry and an insert of the new one (two operations), plus, if category/brand/warehouse didn't change, no writes to those three index tables at all. So the actual amplification factor per write is not a fixed constant; it depends on how many of the indexed attributes changed in that particular mutation: 1. a worst case of roughly 2N+1 operations (N deletes + N inserts + 1 base write) if every indexed attribute's value changed; 2. a best case of N+1 (N inserts, no deletes) on initial creation; 3. down to just 1 (the base write) if the mutation touched no indexed attribute at all. ## Three dimensions of scale This cost scales in three dimensions as more index tables are added. 1. **First, throughput/capacity cost scales roughly linearly with N**: on a provisioned-capacity system like DynamoDB, each of those N extra writes consumes its own write capacity units against its own table, so the effective write cost of maintaining a heavily-indexed entity can be several times the cost of the base write alone, even before accounting for the delete+insert doubling on attribute changes. 2. **Second, latency scales with N** if the writes are done sequentially and synchronously — a caller waiting for a product creation to fully complete is waiting on the slowest of five (or more) round trips, unless the store's transaction API lets some of them be batched into one round trip (with the scope caveats discussed for consistency strategies) or the writes are parallelized. 3. **Third, and often underweighted, failure surface scales with N**: every one of those N+1 writes is a place a partial failure can happen, and each partial failure is a potential drift between the base table and one specific index table, so operationally, N index tables mean N independent 'is this index correct?' questions to monitor and N independent repair paths to build, not one. ## How it surfaces in production The failure mode this produces in production is a slow accumulation of small inconsistencies plus a very real throttling risk: a hot product catalog under load that updates category, brand, warehouse, and price tier in bursts (a bulk re-pricing job, a seasonal recategorization) can see the index tables throttle or queue up well before the base table does, because the index tables are absorbing a multiple of the base table's write volume. Teams that hit this commonly report the symptom as 'writes are slow/throttled' on a table that, looked at in isolation, has moderate load — the real load is the sum across all the index tables plus the base table. ## The standard mitigations The standard mitigations, once a design like this starts to hurt, fall into a few buckets: - **batch** what can be batched into a single transactional write where the store's transaction scope allows it; - **move index maintenance off the synchronous write path** entirely via async propagation (accepting eventual consistency in exchange for bounding the caller-visible cost to just the base write); - **collapse indexes** that are always queried together into a single composite-key index table rather than maintaining them separately; or - past some number of indexed attributes (a rule of thumb many teams use is once you're maintaining more than three or four hand-rolled index tables for one entity), **reconsider the store choice altogether** — a relational database with native multi-column indexing, a managed secondary-index feature like a DynamoDB Global Secondary Index, or a dedicated search index like Elasticsearch/OpenSearch fed by a change stream, all of which push the index-maintenance cost into infrastructure that's built and optimized for exactly this problem rather than reinventing it per attribute in application code.