Practice

LSM-tree, and this workload sits close to the middle of what LSM is for.

Working through the amplifications:

• Writes dominate absolutely — 20,000 appends per second, no updates. LSM turns those into sequential writes to a memtable and flushes them in bulk. A B-tree at that rate is doing random page updates plus WAL writes, which is where its write amplification hurts most.
• Records are immutable, so there are no superseded versions to reclaim. Space amplification, normally LSM’s weak point, barely applies here — compaction is merging rather than garbage collecting.
• Reads are range scans over a sorted key space. With the key as {accountId}:{timestamp}, one account’s events in a window are contiguous: one seek, then sequential reading. That is what SSTables are good at.
• Read amplification is the real cost, since a scan may touch several levels. Bloom filters do not help — they answer point-lookup questions, not range ones — so this is a price you pay rather than one you tune away.

What would flip it:

• Reads become point lookups on a low-traffic system. If writes dropped to hundreds per second and the dominant query became “fetch this one event by id”, the B-tree’s near-1 read amplification and steadier latency would win, and the write advantage would stop mattering.
• A hard p99.9 latency target on reads. LSM latency is usually better and occasionally much worse, when a large compaction competes for I/O. If the service must never be slow rather than usually be fast, predictability can be worth more than throughput — the Module 1 argument, arriving here.
• The events stop being immutable. A compliance requirement introducing redactions or in-place edits brings space amplification and transactional behaviour back into play, and the comparison has to be redone.

A strong answer names which amplification each argument turns on. A weak one says “LSM is for writes” without saying which of the three costs is being traded.

The tenth index is not obviously wrong, but nobody has done the arithmetic that would tell us — and the slow inserts are evidence the first nine were added the same way.

The governing fact: an index is derived data that must be updated on every write touching its columns. Nine indexes means a single-row insert performs ten write operations. The slow inserts are not a mystery to investigate; they are a bill arriving.

What I would measure first:

1. Which indexes are actually used. Every serious database tracks index scan counts — pg_stat_user_indexes in Postgres. In a table with nine it is common to find two or three never scanned since the last restart: pure write cost, zero read benefit. Dropping those may fix the insert problem outright.
2. Whether an existing index already covers the report. A composite index on (a, b) serves queries filtering on a alone. The proposed index may be a prefix of one that already exists.
3. What the report’s plan actually does. If it is slow for some other reason — a bad join order, an N+1, a function wrapped around an indexed column defeating it — the new index will not help, and every writer will still pay for it.

What I would propose:

• Drop the unused indexes first, then re-measure the report. That change has a benefit and no cost, which makes it the easy one to agree on.
• If the report still needs an index, add it — but as a trade stated out loud: this report goes from 40s to 200ms, and every insert gets roughly 10% slower. That is frequently a good deal. It is only a bad one when nobody noticed they were making it.
• If the report scans most of the table and aggregates, the right answer may not be an index at all. That is the OLAP shape from lesson five, and it belongs on a replica or a column store rather than competing with the transactional workload.

The principle from the module: every index speeds up reads that use it and slows every write. Nine indexes is not automatically wrong. Nine indexes nobody can justify individually is.