Implementing Field-Level Encryption for Personal Data Without Breaking Search
Column-level encryption looks simple until someone runs a WHERE clause on an encrypted field. Here is how to encrypt without losing search, filtering, and joins.
The feature that breaks first is search
Field-level encryption is usually pitched as a low-risk win: encrypt the sensitive columns, leave the schema otherwise untouched, ship it. The first thing that breaks in practice is not storage or performance, it is any query that filters, sorts, or joins on an encrypted column. A ciphertext produced with a random IV looks different every time you encrypt the same plaintext, so an equality check like WHERE email = ? against an encrypted column simply stops matching rows.
Teams that discover this late tend to solve it badly, by decrypting entire tables into application memory and filtering there, or by keeping a shadow plaintext column for search and quietly defeating the point of encrypting in the first place. The fix has to be designed in before encryption is turned on, not patched around afterward. That means deciding, field by field, what kind of lookups the application actually needs against that data.
Deterministic encryption and blind indexes
For fields that need exact-match lookups, such as an email used as a login identifier, deterministic encryption or a blind index is usually the right tool. A blind index stores a keyed HMAC of the normalized plaintext alongside the ciphertext; you query the HMAC column for equality and decrypt only the row that matches, rather than decrypting to search. This keeps the searchable value indexable while the encrypted column itself remains randomized and non-correlatable.
Deterministic encryption of the value itself is faster to implement but weaker: identical plaintexts always produce identical ciphertext, which leaks frequency information to anyone with database access, similar to an unsalted hash. Blind indexes avoid this by separating the search token from the stored ciphertext and by including a per-tenant or per-purpose key in the HMAC so cross-context correlation is harder. Range queries, partial matches, and free-text search are the hard cases; those generally require either giving up server-side search on that field, or accepting a searchable-encryption scheme with a documented leakage profile the security team has signed off on.
Key management is the part people underinvest in
Encrypting a column with a key hardcoded in application config is not meaningfully different from not encrypting it, since anyone who can read the config can read the data. Use envelope encryption: a data encryption key (DEK) per field or per record, itself encrypted by a key encryption key (KEK) held in a KMS or HSM. The application only ever handles DEKs in memory transiently and never touches the KEK directly.
Rotation needs to be a designed capability, not an afterthought, because Section 8(5) of the DPDP Act frames security safeguards as an ongoing obligation, not a one-time control. Rotating a KEK should not require re-encrypting every row; re-wrapping the DEKs under the new KEK is enough, and that operation should be scriptable and tested. Build the ability to re-encrypt with a new DEK per record as a background job too, so a suspected key compromise has a real remediation path instead of a theoretical one.
Choosing which fields actually need this
Encrypting every column uniformly is expensive and usually unnecessary; it also makes debugging and operational work harder without a proportionate security gain for low-sensitivity fields. Start from a data inventory that classifies fields by sensitivity and by how they are actually queried in production, then apply field-level encryption to the fields where exposure risk is highest and searchability requirements are known and narrow — identifiers, contact details, financial references, government ID numbers.
Fields that are only ever displayed back to the same user, and never filtered or joined on by the backend, are the easiest wins: encrypt them and decrypt only at the point of display. Fields used heavily in analytics or reporting pipelines are the hardest, and often the better answer there is to route them through pseudonymization or aggregation before they reach the analytics layer, rather than trying to make an encrypted production column play nicely with an OLAP query.
Where to go next
Before deciding which fields to encrypt and how, it helps to have an accurate picture of what personal data you actually hold and where it lives — the Personal Data Inventory tool on this site is built for exactly that mapping exercise, and it makes the field-by-field encryption decision much easier to reason about.