A restricted column must be encrypted in the application before it reaches the database, and support staff must still look customers up by that exact value. 30 million rows and a p95 budget of 200 ms for the lookup. Which design?

What is being tested

Whether you know that encryption and lookup are in tension, and which way to resolve it without leaking the distribution of the data. Application-layer encryption is chosen so the database never holds plaintext. Any scheme that lets the database index the value gives something back.

The mechanism

Randomised encryption produces a different ciphertext each time, so two identical plaintexts are indistinguishable and the column is useless for lookup. A keyed hash fixes that without weakening the stored value: compute HMAC(key, normalised_value), store it in a second indexed column, and keep the real value under randomised encryption. A lookup hashes the search term and hits a b-tree, so the query is an index seek well inside 200 ms at 30 million rows, and the stored ciphertext still leaks nothing about equality.

What it costs is specific and worth saying out loud. You get exact match only: no prefix, no range, no sort. Normalisation is now a correctness requirement, because a trailing space or a different case produces a different index value and the row becomes invisible. The hash key is as sensitive as the encryption key, held in the same custody, and rotating it means recomputing every index value, which is a background job over the whole table rather than a configuration change.

Why the other options fail

• Deterministic encryption. Equality works and the index works, and the column now leaks the frequency distribution of the plaintext. For a low-cardinality value such as a country, a status or a date of birth, counting how often each ciphertext appears recovers the mapping without touching a key. It is defensible only for high-entropy values where no two rows repeat, which is rarely the column anyone wants to search.
• Randomised encryption with decrypt-and-scan. Correct and far outside the budget. Thirty million decrypts per query is seconds of CPU, and it scales with table size rather than result size.
• Searchable encryption with ranges. Real schemes exist and they leak order, which for dates and amounts is most of the information. Library maturity and operational support are thin, and this is the wrong problem to spend novelty on.

When this is the wrong answer

Before building any of it, check whether the lookup can move to a non-sensitive key. If support can find the customer by account number or email and then read the decrypted field, the entire mechanism disappears along with its key rotation job and its normalisation bugs. Blind indexes exist because somebody insisted on searching by the sensitive value itself, and that requirement is negotiable more often than it is negotiated. If the database engine offers transparent encryption and the threat you actually care about is a stolen disk rather than a compromised application credential, application-layer encryption is the wrong layer entirely.