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.

Building Indexes at Scale

Query performance and build performance are separate axes; on large encrypted tables the build is the one that bites.

• Raise maintenance_work_mem for the build session — the single highest-leverage knob. The 64 MB default spills a multi-million-row build to disk early:

  SET maintenance_work_mem = '2GB';
  CREATE INDEX ...;
  ANALYZE ...;
  

• Prefer btree over hash for equality at scale. Build characteristics differ sharply:

  Access method	Build	Scales past cache?	Parallel build?
  btree	sort, then sequential bulk-load	yes	yes
  GIN	batched buffer build	yes	no
  hash	random bucket fill	no	no

  A hash build scatters rows to random buckets; once the index outgrows cache it goes random-I/O-bound (a 10M-row hash build has been observed to stall after 17 hours; the btree equivalent built without drama). A btree on eql_v3.eq_term(col) serves = identically.

• The de-TOAST floor. A functional index build de-TOASTs the whole stored value once per row to evaluate the extractor — for large eql_v3_json_search documents this sets an unavoidable floor on build rate, identical across access methods. Run large builds on fast native storage (containerized Postgres on a virtualized filesystem — e.g. Docker Desktop on macOS — is the worst case).

• Diagnose a slow build from a second session:

  SELECT phase, tuples_done, tuples_total,
         round(100.0 * tuples_done / nullif(tuples_total, 0), 1) AS pct
  FROM pg_stat_progress_create_index;
  

  A steady tuples_done rate is healthy; a rate that decays over time is the cache/memory wall — raise maintenance_work_mem, and if it's a hash index, rebuild as btree.

Verifying with EXPLAIN

The first move on any slow encrypted query is EXPLAIN (COSTS OFF):

• ✓ Index Scan using <your-index> — the functional index is engaged.
• ✓ Bitmap Index Scan on <your-index> — same, for set-style predicates (@@, @>).
• ✓ Index Cond: referencing the extractor (eql_v3.eq_term(…), eql_v3.ord_term(…)) — the inlined predicate matched.
• ✗ Seq Scan — no index used; work through Troubleshooting.
• ✗ Filter: showing the raw operator (col < '…') — inlining did not happen. Usual causes: a pinned search_path on a customized extractor function, a plpgsql body where a sql one is expected, or the planner genuinely judging another plan cheaper.
• ✗ A Sort node above an Index Scan — natural-form ORDER BY; switch the sort key to the extractor form.

Once the plan shape is right, EXPLAIN ANALYZE for actual timings.

Troubleshooting

Index not being used:

1. Verify the value carries the term:

   SELECT encrypted_email::jsonb ? 'hm' AS has_hmac,
          encrypted_email::jsonb ? 'op' AS has_ope,
          encrypted_email::jsonb ? 'ob' AS has_ore_block,
          encrypted_email::jsonb ? 'bf' AS has_bloom
   FROM users LIMIT 1;
   

2. Verify the operand is typed ($1 or $1::eql_v3.query_text_eq — not $1::jsonb, and not the column domain public.eql_v3_text_eq: query payloads are term-only, and the column domains' CHECK requires the ciphertext key c that query payloads deliberately omit).

3. Recreate the index if the column's term composition changed after it was built.

4. Run ANALYZE. Also note: on very small tables a Seq Scan is the correct plan — don't chase it below a few thousand rows.

= returns zero rows: equality needs the domain's equality-serving term — hm where the domain carries it (_eq, text_ord, text_ord_ore, text_search), the injective ordering term (op / ob) on the numeric/date/timestamp _ord / _ord_ore domains. A bare storage-only domain has neither; confirm the column's domain and that the client is emitting the term.

ORE index never engages: run the pg_opclass query from the superuser section — a record_ops binding means the index is inert.

Where the Index DDL Goes

The integrations emit the query operators for you — none applies index DDL on its own. Making sure these indexes exist is always your job. This skill is the general model — recipes, engagement rules, verification. How to apply it in a specific integration lives in that integration's skill:

• Drizzle — encryptedIndexes(t) from @cipherstash/stack-drizzle derives the recommended indexes for every encrypted column in the table, or declare individual expression indexes in the schema DSL. See stash-drizzle § Indexing Encrypted Columns.
• Prisma Next — since Prisma Next 0.17, @@index(expression: "eql_v3.eq_term(email)", name: "users_email_eq", type: "btree") declares a functional index directly in schema.prisma; the accompanying ANALYZE rides a raw-SQL migration operation. See stash-prisma § Indexing encrypted columns.
• Supabase — a supabase/migrations/ file; no superuser needed (see above). See stash-supabase.
• Raw SQL / plain PostgreSQL — the recipes in this skill, in whatever migration tool owns the schema. Never ad-hoc in production. The predicates those indexes serve are in stash-postgres.

When to Create Indexes During an Encryption Rollout

• Fresh encrypted column (new table or new field): ship the CREATE INDEX in the same migration that adds the column. Every value written carries its terms from day one, so the index is correct from the first row.
• Encrypting an existing column (the stash encrypt lifecycle): create the indexes after stash encrypt backfill completes and before switching reads to the encrypted column. Building after backfill is one bulk pass instead of per-row index maintenance across the whole backfill, and the reads you cut over to engage an index from the first query. Remember ANALYZE after the build. See stash-encryption § "Rolling Encryption Out to Production" for the full lifecycle.

These indexes do not survive an EQL reinstall or upgrade. The install SQL begins with DROP SCHEMA IF EXISTS eql_v3 CASCADE, and every functional index on an extractor depends on that schema — so stash eql upgrade (or eql install --force, or re-applying the bundle by hand) cascade-drops all of them. Columns and data are untouched (the public.eql_v3_* domains deliberately don't depend on the eql_v3 schema); only the indexes vanish, and queries fall back to sequential scans without erroring. After any EQL upgrade or reinstall, recreate them — migration runners skip already-applied migrations, so add a new migration re-issuing the CREATE INDEX statements (or run the DDL directly), ANALYZE, and confirm with the EXPLAIN checklist above.

Reference

• stash-encryption — the types.* domain catalog, wire-format operators and ordering, and the staged rollout lifecycle.
• stash-cli — stash eql install, stash eql verify (is the installed operator/opclass surface complete and the ORE state coherent), stash eql validate (its "No functional index over eql_v3.…" Info finding is resolved by this skill), and stash encrypt backfill / drop.
• stash-drizzle, stash-supabase, stash-prisma — per-integration query patterns; index DDL placement per the section above.
• stash-postgres — the hand-written predicate forms these indexes serve (pg / postgres-js, no ORM).
• stash-edge — the WASM entry, for apps whose queries run on Deno / Workers / Supabase Edge Functions.