---
name: stash-indexing
description: Create and verify PostgreSQL indexes on EQL v3 encrypted columns — functional-index recipes over the term extractors (eql_v3.eq_term, ord_term, ord_term_ore, match_term, to_ste_vec_query) mapped to the types.* domains, what works without superuser on Supabase and managed Postgres versus the ORE opclass restriction, which domains have no index option, the ORDER BY / GROUP BY query shapes that engage an index, building indexes on large tables, and the EXPLAIN verification checklist. Use when creating or reviewing a schema migration that adds an encrypted column, adding an index to an encrypted column, diagnosing a slow encrypted query or an EXPLAIN plan showing Seq Scan, or answering whether encrypted columns can be indexed on Supabase or managed PostgreSQL.
---

# 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](#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`, …).

| Schema factory | Postgres domain | Terms carried | Index recipes |
|---|---|---|---|
| `types.NEq`, `types.TextEq` | `public.eql_v3_<t>_eq` | `hm` | equality (`eq_term`) |
| `types.NOrd` | `public.eql_v3_<t>_ord` | `op` | **one** ordering index (`ord_term`) — serves equality, range, and `ORDER BY` |
| `types.NOrdOre` | `public.eql_v3_<t>_ord_ore` | `ob` | **one** ORE ordering index (`ord_term_ore`) — equality + range; superuser installs only |
| `types.TextOrd` | `public.eql_v3_text_ord` | `hm`, `op` | equality (`eq_term`) **+** ordering (`ord_term`) — two indexes |
| `types.TextOrdOre` | `public.eql_v3_text_ord_ore` | `hm`, `ob` | equality (`eq_term`) **+** ORE ordering (`ord_term_ore`; superuser) |
| `types.TextMatch` | `public.eql_v3_text_match` | `bf` | free-text match (`match_term`) |
| `types.TextSearch` | `public.eql_v3_text_search` | `hm`, `op`, `bf` | equality + ordering/range + match (three indexes) |
| `types.Json` | `public.eql_v3_json_search` | `sv` (ste_vec) | containment GIN + field-level ordering |
| bare `types.N` / `types.Text`, `types.Boolean` | `public.eql_v3_<t>` | none | **none — storage-only by design** |

**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](#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.)

```sql
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](#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`.

```sql
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](#query-shape-traps).)

### ORE Ordering — `eql_v3.ord_term_ore` (superuser installs only)

For the `_ord_ore` domains only:

```sql
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](#supabase-and-managed-postgres-what-actually-needs-superuser) 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:

```sql
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 (`@>`):

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

  ```sql
  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:

  ```sql
  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](#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:**

   ```sql
   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](#supabase-and-managed-postgres-what-actually-needs-superuser) — 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.