The profile lookup takes 3 ms. Why does a new filter scan every customer?

A fast lookup only proves one access pattern is fast

The current service has a straightforward contract: given the full key, retrieve the value. A request for user:4729 goes to the partition owning that key and reads one record. No coordinator has to search for a matching city across the whole cluster.

Now consider the new request. It does not know a user ID. It asks for a set of users sharing a property, ordered by another property. The key-value API has not suddenly acquired an efficient route from (verified, city, lastActive) to user IDs.

A scan may return the correct answer for a quiet test database. For 120 million records, it also means reading data that does not match, sending it somewhere, applying the filter and collecting results. Adding a filter expression to a scan does not necessarily spare the underlying read. DynamoDB, as one concrete key-value product, documents that scan filtering happens after items are read and still consumes read capacity for scanned items. DynamoDB Scan

The first good interview question is not “Which NoSQL database is fastest?” It is “Which requests must be cheap, and what information does each request already know?”

Give the discovery query its own route

I would keep the authoritative profile record keyed by user ID for this scenario. Then I would ask what kind of discovery functionality actually matters. If the filters are stable and limited, a purpose-built secondary index may be sufficient. If users need evolving filters, text relevance and facets, a separate search representation might be easier to operate than manufacturing a growing family of manual key prefixes.

A possible secondary access path is (city, verified, lastActive, userId). The exact ordering and physical representation depend on the database. It is not a universal schema that every key-value engine can query efficiently.

For a datastore such as DynamoDB, a global secondary index can use different key attributes from the base table. It is maintained asynchronously and permits eventually consistent reads. A local secondary index has different key restrictions and read behavior. Those differences are product contracts, not just vocabulary. DynamoDB secondary indexes

For the discovery page, an eventually consistent index might be acceptable. A creator who verifies a profile may appear in results slightly later. But a stale index must not authorize access to private profile fields. We should fetch current permitted details or apply an authorization decision at a trusted boundary.

This separation makes the architecture more honest. The profile is the source of truth. Discovery is a derived read model optimized for a different question.

Check what happens when the query grows

Suppose the page initially shows verified creators in one city. A month later product wants topic filters, follower-count ranges and full-text matching. An index optimized for the first request may not give you a cheap query for the second.

At this point, compare two costs. One is maintaining additional representations every time a profile changes. The other is repeatedly scanning most of the corpus at request time. The right choice depends on update frequency, read frequency, freshness and operational complexity.

Even a secondary index has its own workload. A city such as Bengaluru might have millions of eligible users, and a sort by recent activity may concentrate writes in its leading range. Partition placement and a query's sort shape still matter. We must not assume the word “index” eliminates hot partitions or write amplification.

I would put numbers on the new path: expected search requests per second, candidate-set sizes, pagination depth, acceptable staleness, and update rate. A million profiles changing activity status every few seconds could make a constantly reordered index expensive. We might instead rank from a periodic snapshot or separate coarse freshness buckets.

That is a design decision driven by the query contract rather than by a database family label.

When would I change the primary store?

Keeping the key-value primary is not a religious preference. If the application evolves so that nearly every operation involves multi-record joins, flexible filtering and strong cross-record constraints, a relational or document database with appropriate indexing may reduce complexity. Conversely, high-rate simple point reads and conditional per-item updates can remain a very good match for a key-value service.

I would also ask about atomic operations. An external index cannot be treated as the authority for uniqueness or checkout correctness merely because it can find a matching record. A query returning no result from an eventually consistent index is not proof that no matching authoritative record exists.

The simplest acceptable architecture might be two representations and one deliberate freshness contract. It need not be seven microservices, a general-purpose search platform and a streaming cluster on day one.

If the interviewer says the new filter will be used twice a day by an internal analyst, my answer changes. A bounded offline scan may be cheaper than maintaining a live index. If it becomes a high-traffic customer-facing page, the same scan is no longer a sensible hot-path design.

The interesting consequence is that an access path is part of the product contract. Once another team depends on searching profiles by an attribute, that attribute can change. A phone number is an especially awkward example because it also has uniqueness and ownership implications.