Stash Indexing

Indexing Encrypted Columns (EQL v3)

Encrypted columns can be indexed, and on any non-trivial table they should be. The model is one rule, uniform across every encrypted domain: index a functional expression over the column's term extractor — never an operator class on the column itself. The extractors are inlinable SQL functions, so bare-form predicates (WHERE col = $1, WHERE col < $1, col @@ $1) engage the index with no query rewriting.

This covers EQL v3 — the bundle stash eql install applies (@cipherstash/eql). An integration that is otherwise correct (encrypted at rest, searchable, exact round-trip) but has no index on its encrypted predicates will sequential-scan every encrypted query; that is the default outcome unless you put these indexes in place — the integrations emit query operators, not index DDL (see Where the Index DDL Goes).

When to Use This Skill

• Writing or reviewing a schema migration that adds or changes an encrypted (eql_v3_*) column.
• Deciding which indexes an encrypted column supports — or explaining why a column has none.
• An encrypted query is slow, or EXPLAIN shows a Seq Scan where you expected an index.
• stash eql validate reports "No functional index over eql_v3.…" for a queryable column.
• Answering whether encrypted columns can be indexed on Supabase or managed PostgreSQL (yes — see the superuser section).

Which Columns Support Which Index

Capability is fixed by the column's domain type, chosen at schema definition via the types.* factories. N ranges over the numeric-and-time base types Integer, Smallint, Bigint, Date, Timestamp, Numeric, Real, Double; text is listed separately because its ordering domains behave differently (see the note below the table). <t> is the lowercase SQL name (eql_v3_integer_eq, eql_v3_text_ord, …).

Why the numeric/text split: the numeric-and-time ordering terms (OPE and ORE alike) are injective — distinct plaintexts produce distinct terms — so equality can ride the ordering term. Those domains carry no hm, the bundle defines no eq_term overload for them, and eql_v3.eq inlines to ord_term(a) = ord_term(b): one ordering btree serves =, range, and ORDER BY. Text ordering terms are non-injective and cannot be relied on for equality, so text_ord / text_ord_ore also carry hm and answer = via eq_term — give those columns both indexes. Do not add an eq_term index to a numeric _ord / _ord_ore column; the overload does not exist.

The last row is deliberate, not a gap: a bare types.Text / types.Integer / types.Boolean column carries no query terms, so there is nothing to index and nothing to query server-side. If a column needs an index, it needs a term-carrying domain first.

Choosing the domain in the first place is the stash-encryption skill's capability matrix (### The types Namespace) — all 40 factories, one row each, with the predicates and the index each supports. Use it to pick the type; use this page to index it.

The Recipes

Every recipe is a functional index over the extractor, followed by ANALYZE (see Making a Query Engage the Index for why ANALYZE is mandatory). Name indexes descriptively (users_email_eq, events_at_ord) — it makes EXPLAIN output and maintenance legible.

Equality — eql_v3.eq_term

For the domains carrying hm: _eq, text_ord, text_ord_ore, and text_search. (On numeric/date/timestamp _ord / _ord_ore columns, equality rides the ordering index below instead — no eq_term overload exists for them.)

CREATE INDEX users_email_eq ON users USING btree (eql_v3.eq_term(encrypted_email));
ANALYZE users;

SELECT * FROM users WHERE encrypted_email = $1;
-- Index Scan using users_email_eq
--   Index Cond: (eql_v3.eq_term(encrypted_email) = eql_v3.eq_term($1))

btree is the safe default: it serves = exactly as well as hash with no query-side cost, and its build scales (see Building Indexes at Scale). USING hash is fine for small and mid-size tables, but a hash index build degrades badly past a few million rows.

Ordering and Range — eql_v3.ord_term

For the OPE-backed ordering domains: _ord and text_search.

CREATE INDEX events_at_ord ON events USING btree (eql_v3.ord_term(encrypted_at));
ANALYZE events;

SELECT * FROM events WHERE encrypted_at < $1;

eql_v3.ord_term returns a bytea-backed domain, so this btree binds PostgreSQL's default bytea_ops operator class — nothing to install, no privilege required, works on Supabase and managed Postgres. The < <= > >= operators inline to comparisons on the extractor, so natural-form range predicates match the index — and on the numeric/date/timestamp _ord domains so does =, since their injective ordering term also answers equality. One index, every scalar predicate. (A text_ord column answers = via eq_term instead — pair this index with the equality one. ORDER BY needs the extractor form — see Query-Shape Traps.)

ORE Ordering — eql_v3.ord_term_ore (superuser installs only)

For the _ord_ore domains only:

CREATE INDEX events_at_ord_ore ON events USING btree (eql_v3.ord_term_ore(encrypted_at));
ANALYZE events;

This one depends on a custom operator class the EQL installer must be privileged enough to create — see Supabase and Managed Postgres for which platforms allow it and for the failure mode to check. Prefer types.NOrd / types.TextOrd unless you specifically need ORE ordering on a platform whose installer could create the opclass.

Free-Text Match — eql_v3.match_term

For the bloom-filter domains: text_match and text_search. Engages the @@ match operator:

CREATE INDEX users_name_match ON users USING gin (eql_v3.match_term(encrypted_name));
ANALYZE users;

SELECT * FROM users WHERE encrypted_name @@ $1;
-- Bitmap Index Scan on users_name_match

JSON Containment — eql_v3.to_ste_vec_query

For public.eql_v3_json_search (types.Json) document containment (@>):

CREATE INDEX orders_data_gin
  ON orders USING gin ((eql_v3.to_ste_vec_query(data_encrypted)::jsonb) jsonb_path_ops);
ANALYZE orders;

SELECT * FROM orders WHERE data_encrypted @> $1::eql_v3.query_json;
-- Bitmap Index Scan on orders_data_gin

The needle must be typed — $1::eql_v3.query_json or another public.eql_v3_json_search value. A bare untyped literal falls through to native jsonb @> and skips the index. Note jsonb_path_ops indexes @> only, not <@.

Field-Level Ordering Inside Encrypted JSON

For ordered access to a single field of a types.Json document, index the ordering extractor over the path:

CREATE INDEX orders_total_ord
  ON orders USING btree (eql_v3.ord_term(data_encrypted -> '<selector>'::text));
ANALYZE orders;

SELECT * FROM orders ORDER BY eql_v3.ord_term(data_encrypted -> '<selector>'::text) LIMIT 10;

• <selector> is the deterministic selector hash the encryption client emits (each sv element's s field) — not a plaintext JSONPath. To obtain it, encrypt the path through the client — encryptQuery('$.total', { table, column, queryType: 'steVecSelector' }) — and read the selector from the returned query envelope; it is stable for a given column + path, so it can be pasted into migration DDL. (Cross-check: the same hash appears as s in the sv entries of any stored row that has the field.)
• The -> operand must be typed (::text); a bare literal falls through to native jsonb ->.
• The extracted term is bytea-backed like top-level ord_term — default btree opclass, no superuser.
• Entry-to-entry = / <> and exact GROUP BY / DISTINCT on extracted fields are not supported (an extracted entry carries no value selector). Use document containment with the GIN index above for exact field equality.

Supabase and Managed Postgres: What Actually Needs Superuser

Only one thing on this page needs superuser: the ORE operator class behind _ord_ore. Everything else — equality btree/hash, _ord/text_search ordering btree, match GIN, JSON containment GIN, field-level ordering — installs and engages with a plain non-superuser role. Do not generalize the ORE warning into "encrypted columns can't be indexed on Supabase"; the default ordering path (_ord, via CLLW-OPE) binds Postgres's native bytea btree operator class and needs nothing installed.

The _ord_ore restriction, precisely: its btree ordering depends on a hand-written operator class created by the EQL installer, and CREATE OPERATOR CLASS is a superuser-gated command in stock PostgreSQL. Whether that blocks ORE is per-platform, not a blanket managed-Postgres rule: AWS RDS and Aurora fully support it (their master role can create operator classes), while cloud-hosted Supabase is the one confirmed platform that refuses it. Where the install role can't create the opclass, the installer detects this and disables the _ord_ore domains — using one raises feature_not_supported with a hint naming the alternatives.

The silent-failure mode to check for: if an _ord_ore column somehow exists without the opclass, CREATE INDEX … USING btree (eql_v3.ord_term_ore(col)) does not fail — PostgreSQL binds the generic record_ops instead. The index builds, occupies space, and never engages. Run stash eql verify first: it reads the ORE state directly and distinguishes the two healthy configurations (opclass present, or opclass skipped with every _ord_ore domain disabled) from the incoherent half-working state that makes this trap possible — and stash eql install runs the same check automatically. What verify does not tell you is which opclass an existing index bound at build time; for that, check the index itself:

SELECT i.relname, oc.opcname
  FROM pg_index x
  JOIN pg_class i    ON i.oid = x.indexrelid
  JOIN pg_opclass oc ON oc.oid = x.indclass[0]
 WHERE i.relname = 'events_at_ord_ore';
-- ore_block_256_operator_class  → ORE ordering, index engages
-- record_ops                    → opclass was skipped at install; index is inert

_ord has no such failure mode.

Making a Query Engage the Index

Functional-index engagement is structural: the planner inlines the operator into the same extractor expression the index was built on and matches the expression trees syntactically. All three of these must hold:

1. The value must carry the term the index extracts. eq_term needs hm, ord_term needs op, ord_term_ore needs ob, match_term needs bf, containment needs the ste_vec. The domain rows in the table above tell you which terms a column's values carry; a value with only a bloom term will never drive an equality index.
2. The index must be created after the data carries the term. If you change which terms a column's values carry (e.g. re-encrypt under a different domain), recreate the index — a functional index built before the term existed will not match.
3. The query operand must be typed so the encrypted operator resolves, not the native jsonb one. A typed parameter ($1) or an explicit cast to the domain works; a bare ::jsonb literal falls through to native jsonb semantics and skips the index. The Drizzle, Prisma Next, and Supabase integrations emit correctly-typed operands already — this requirement only bites hand-written SQL.

And after every index build: run ANALYZE. CREATE INDEX on an expression gathers no statistics for that expression, so until ANALYZE runs the planner has no histogram for eql_v3.eq_term(col) and can misjudge — or ignore — the index it just built.

Query-Shape Traps

The ORDER BY sort-key trap. The planner inlines operators in predicates, not sort keys: ORDER BY col adds a Sort node even when the ordering index exists and the WHERE clause is using it. To stream rows out of the index already ordered, write the sort key in extractor form:

SELECT * FROM events
  WHERE encrypted_at < $1
  ORDER BY eql_v3.ord_term(encrypted_at) DESC
  LIMIT 10;

The natural-form Top-N sort scales linearly with the rows passing WHERE; at scale that is the difference between seconds and milliseconds. The Drizzle integration's asc/desc already emit ORDER BY eql_v3.ord_term(col) for you.

The value::jsonb projection trap. SELECT col::jsonb … ORDER BY col folds the cast into the scan and sorts on (col)::jsonb — which matches no index. Project the column raw, wrap the ordered query in a subquery and cast outside the LIMIT, or sidestep it entirely with ORDER BY eql_v3.ord_term(col).

GROUP BY / DISTINCT on the extractor, not the raw column. GROUP BY col hashes the entire encrypted payload (1–2 KB per row); the estimated hash table blows past work_mem, so the planner falls back to GroupAggregate — sorting kilobyte rows and spilling to disk. Group on the term instead:

SELECT eql_v3.eq_term(encrypted_email), count(*)
  FROM users
  GROUP BY eql_v3.eq_term(encrypted_email);

The term is small and deterministic, so HashAggregate fits in work_mem with no tuning. If an ORM insists on grouping the raw column, raising work_mem is the rescue knob — but the extractor form is the design.

Pick the extractor the domain actually has: eq_term on the hm-carrying domains (types.*Eq, types.TextOrd*, types.TextSearch). The numeric/date/timestamp types.*Ord / *OrdOre domains have no eq_term — group on eql_v3.ord_term(col) (or ord_term_ore(col)); their ordering term is injective, so it is an exact grouping key, and the ordering btree covers it.