Why does a wide-column store make a query that filters on a non-key column expensive, and what should you do instead?
answer
- the key is the only index
- read everything, discard most
- cost grows with the table
- a layout for that read
basics
~20 sThe key is the only structure that narrows a read, so a filter on another column makes the store read and discard data across partitions. Serve that read with a layout keyed by the field instead.
solid answer
~50 sA wide-column store locates data only by key. A predicate on a non-key column gives it nothing to seek on, so it must **scan**: read every row in the partitions or key range involved, evaluate the filter, discard what fails. The work is proportional to data **read**, not data **returned**, so a query returning ten rows can read millions, and it gets slower as the table grows. Stores make this visible: some refuse such queries unless explicitly allowed, others simply run a full table scan. The fix is to make the field part of a key: create a **layout keyed by it** and write to it alongside the main one. Secondary indexes and materialised views are alternatives with their own costs. Filtering is fine only when it runs **inside** an already-narrowed slice, such as one small partition.
go deeper
Know that filtering on a column outside the key makes the store scan large amounts of data.
Explain locate versus scan, why cost follows data read, and why the default fix is a layout keyed by the filtered field.
Choose between a new layout, a secondary index, a materialised view or an analytical export for a given read, and justify filtering inside a bounded slice.
Be ready to set team rules for when non-key filtering is allowed and how new read paths get their own layouts.
## What "filtering" means to the store A read in a wide-column store has two parts: 1. **Locate**: use the key to find the partition or key range, which is cheap and bounded. 2. **Scan and filter**: read the rows found and drop those that fail a predicate. When the predicate is on the key, step 1 does all the work. When it is on a non-key column, step 1 narrows nothing, so step 2 must cover **every partition or the whole table**. ## Why it is expensive - **Work follows data read, not data returned.** Ten matching rows may require reading millions. - **It grows with the table.** A query that is fast in testing slows steadily in production as data accumulates. - **It touches every server.** A cross-partition scan contacts all nodes or all ranges, competing with live traffic. - **It interacts badly with deletes**: scans also walk over deleted-but-not-yet-purged data. Stores signal the danger differently: some reject a query that would need filtering across partitions unless the client explicitly opts in; others run it as a table scan with a server-side filter. Either way, the cost is the same. ## What to do instead | option | how it works | cost | |---|---|---| | **new layout keyed by the field** | write the data a second time, keyed for this read | extra writes and storage, keeping copies in step | | **secondary index** | the store maintains an index on the column | index writes; reads may fan out across nodes | | **materialised view** | the store or a pipeline maintains a re-keyed copy | lag, and the maintenance machinery | | **filter inside a narrow slice** | key narrows to one small partition, then filter | fine when the slice is small and bounded | | **send it to an analytical system** | export the data for ad-hoc queries | pipeline and latency | Indexes and materialised views are patterns in their own right; the point here is that the **default answer in query-first design is a layout keyed for the read**. ## When filtering is acceptable 1. The key has already narrowed the read to one partition or a short key range. 2. That slice is **bounded** by design, for example one device-day. 3. The filter discards a modest fraction of it. For example, "readings for device D today where value > threshold" reads one bounded partition and filters it — fine. "All readings where value > threshold" scans the table — not fine. ## Interview angle Explain locate versus scan, why cost follows data read, and propose a keyed layout first, mentioning indexes and views as alternatives with trade-offs. Also say when filtering inside a slice is acceptable.
- A query with a non-key filter runs fast in staging. Why can it still be a problem?Staging holds little data, so the scan is short. Cost grows with total data read, so the same query slows steadily in production and competes with live traffic on every server.
- When is a store's built-in secondary index a reasonable choice over a new layout?When the indexed value is selective enough, and the read is rare enough that fanning out to several nodes is acceptable. For hot, latency-sensitive paths a dedicated layout is more predictable.
It is like asking a library sorted by author for every book with a green cover; the catalogue cannot help, so someone must walk every shelf, and the walk gets longer as the library grows.
saying these in an interview costs you the question
- Enabling a store's allow-filtering option to make a slow query run
- Believing a query returning few rows must be cheap
- Relying on a non-key filter because it was fast on a small test data set
- Treating a secondary index as free of write cost and read fan-out