What can we still query once it's encrypted?
- Date
- By
- CipherStash Team
Four families. Equality (=, IN, GROUP BY, joins), text match, ordering and range (ORDER BY, BETWEEN, MIN and MAX), and JSON containment, path queries and ordering on leaves. Each is an index type you opt into per column. SUM, AVG, regex and LIKE do not work on ciphertext, and the docs say so.
You will not get everything. Anything that needs the database to do arithmetic on the value is out, and so is any pattern language richer than approximate text matching. What you do get covers most of what application code asks a database for, and each capability is a column-level choice that costs storage and leaks a little, so you turn it on only where you need it.
Equality
=, <>, IN (including IN (subquery)), DISTINCT and equijoins all work on an equality-capable column. The term is an HMAC-SHA-256 of the value, so two rows with the same plaintext share a term. That is what makes equality work, and it is also what leaks.
Two rules from the docs are worth knowing before you ship. GROUP BY must be written as GROUP BY eql_v3.eq_term(col). A bare GROUP BY col runs and returns wrong groups. And both sides of a join must be encrypted under the same keyset and typed as a matching variant, or the join returns no rows without an error.
Text match
The @@ operator does probabilistic n-gram bloom matching. The value is downcased, split into 3-grams, and hashed into a bloom filter. A query is the same transform on the needle, then array containment. Matching is case-insensitive, the needle must be at least 3 characters, a true can be a false positive and a false is never a false negative. LIKE, ILIKE and regex raise on encrypted columns. If your product needs ranked full-text search, this is not that.
Ordering and range
<, <=, >, >=, BETWEEN, ORDER BY, MIN and MAX work on an ordered column, plus equality. The default term is CLLW order-preserving encryption, which indexes with a plain bytea_ops class anywhere you can CREATE INDEX. Block ORE is available as a pinned alternative where you have superuser. ORDER BY on a column that was not given an ordering index does not error. The order is meaningless.
JSON
An encrypted JSON column supports containment (@>, <@), field access (->, ->>), jsonb_path_query, jsonb_path_exists, array length and elements, and range and ORDER BY on string and number leaves. Fields are addressed by a selector hash rather than a plaintext path. Key-existence operators, concatenation, deletion and equality on the root document are blocked, and extracted leaves cannot be grouped by equality.
What is refused, on purpose
SUM, AVG and every other arithmetic aggregate. The docs put it plainly: those would require homomorphic encryption, which EQL does not do. COUNT and COUNT(DISTINCT eql_v3.eq_term(...)) are fine.