/stash-indexing

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.)

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