What stops working when an application encrypts a column's values before storing them in a relational database, and how do teams work around each limitation?

## Why anything breaks at all A relational engine's value comes from understanding values: B-trees exploit ordering, hash joins exploit equality, constraints exploit comparison, and the optimizer estimates selectivity from distribution statistics. Encrypt above the engine and it sees uniform random bytes — all of that machinery becomes inapplicable. Every workaround is a controlled leak of some property back to the server. ## Randomized vs deterministic **Randomized** (fresh IV per encryption) is the secure default: identical plaintexts produce different ciphertexts, so the server learns nothing beyond length. It also destroys equality lookup, uniqueness and joins. **Deterministic** (fixed or synthetic IV) yields the same ciphertext for the same plaintext, restoring equality predicates, unique constraints, GROUP BY and equi-joins. The leak is real: the server — and anyone who steals the data — sees which rows share a value and how often each value occurs. On a low-cardinality column such as country, diagnosis code or status, frequency analysis recovers the plaintext with no key at all. Deterministic encryption is defensible on high-cardinality identifiers, dangerous on categorical data. ## Blind indexes A blind index stores `HMAC(key, normalize(plaintext))` in a separate indexed column while the real column holds randomized ciphertext. Exact-match search works by computing the HMAC in the application and querying that column. Normalisation (case folding, trimming) must be defined once, or lookups silently miss. Truncating the HMAC deliberately creates collisions, blurring frequency at the cost of false positives the application filters after decryption — a genuine privacy/performance dial. ## Ranges and sorting There is no cheap safe answer. Options: store a coarse bucket in the clear (order year, salary band) and post-filter precisely in the application; maintain an encrypted index structure client-side; or accept application-side filtering over a bounded candidate set. Order-preserving and order-revealing schemes exist but leak the ordering of the whole column, which for many datasets is close to leaking the data. ## Operational consequences - **Size and type.** The column becomes binary and grows by IV, authentication tag and padding — relevant to row width, page fill and index size. - **Key rotation.** Rotating the data key means decrypting and re-encrypting every row online while both versions are valid. Store a key-version tag with every ciphertext from day one; retrofitting it is painful. - **Authenticated encryption and context binding.** Use an AEAD mode and bind associated data — table, column, primary key — so an attacker with write access cannot copy one row's ciphertext into another row and change what it means. - **Loss of server-side processing.** Aggregations, validation triggers, reporting and BI tools cannot see the value; analytics must be fed a separate, differently governed dataset. - **Availability and correctness.** Every read path needs the key. A key-service outage becomes an application outage; a lost key is permanent data loss — which is also the mechanism behind deliberate crypto-shredding. - **Backup and restore.** Ciphertext restores fine, but only if the key version used at write time still exists. ## The judgement to express Column-level encryption is the only layer that protects against an attacker already inside the database, so it is right for a small set of crown-jewel fields — government IDs, card data, secrets, health details — and wrong as a blanket policy. Decide per field: what queries must it support, what does the chosen scheme leak, and would tokenising the field out of the database entirely be simpler than making the database work with ciphertext?