Setting up indexes

Encrypted columns need PostgreSQL indexes for fast queries. Without an index, the database performs a sequential scan: correct but slow at scale.

Index syntax differs between deployment types. Self-hosted PostgreSQL with full EQL installed supports custom operator classes and can use B-tree indexes directly on eql_v2_encrypted columns. Managed databases like Supabase cannot install operator families (they require superuser), so indexes must use extraction functions instead.

Deployment matrix

Equality

Equality indexes speed up WHERE col = $1 queries and IN lists.

Self-hosted (B-tree with operator class):

CREATE INDEX ON users USING btree (email);

This works because the full EQL install registers a B-tree operator class for eql_v2_encrypted that compares HMAC terms.

Self-hosted or Supabase (hash on extraction function):

CREATE INDEX ON users USING hash (eql_v2.hmac_256(email));

This form works on both deployment types. Use it when you want one index that works everywhere, or when you are on Supabase.

See queries: Equality queries

Match

Match indexes speed up WHERE col LIKE $1 and ILIKE queries. They use a GIN index on the Bloom filter extracted from each encrypted value.

CREATE INDEX ON users USING gin (eql_v2.bloom_filter(name));

This form is identical for self-hosted and Supabase.

See queries: Match queries

Range and order

Range indexes support >, >=, <, <=, BETWEEN, and ORDER BY on encrypted columns.

Self-hosted (B-tree with operator class):

CREATE INDEX ON users USING btree (age);

Requires the EQL operator family (CREATE OPERATOR FAMILY) to be installed. The full EQL install includes this. The --exclude-operator-family install flag omits it.

Supabase:

Functional range indexes for Supabase are not yet available. Range filters work without an index (sequential scan). ORDER BY on encrypted columns is not supported on Supabase.

See queries: Range queries

JSONB

JSONB indexes support path existence and containment queries on encrypted JSON columns.

CREATE INDEX ON documents USING gin (eql_v2.ste_vec(metadata));

This form is identical for self-hosted and Supabase.

See queries: JSONB queries

Supabase query forms

This is the most common source of silent performance problems with encrypted columns on Supabase.

A functional index on eql_v2.hmac_256(email) is only engaged when the query uses the same extraction function. A bare WHERE email = $1 query does not use the index, even if the index exists. The database falls back to a sequential scan: your query returns correct results, but it scans every row.

Wrong (does not use functional index):

SELECT * FROM users WHERE email = $1::eql_v2_encrypted;

Right (engages the functional index):

SELECT * FROM users WHERE eql_v2.hmac_256(email) = eql_v2.hmac_256($1::eql_v2_encrypted);

The same principle applies to eql_v2.bloom_filter and eql_v2.ste_vec indexes: the extraction function must appear in both the index definition and the query predicate.

Complete example

-- Equality index (Supabase-compatible form)
CREATE INDEX users_email_eq_idx ON users USING hash (eql_v2.hmac_256(email));

-- Match index
CREATE INDEX users_name_match_idx ON users USING gin (eql_v2.bloom_filter(name));

-- JSONB index
CREATE INDEX documents_metadata_ste_idx ON documents USING gin (eql_v2.ste_vec(metadata));

-- Range index (self-hosted only — requires operator family)
CREATE INDEX users_age_range_idx ON users USING btree (age);

Related

• Searchable encryption queries: Query patterns for each index type
• Searchable encryption overview: How searchable indexes work
• Supabase integration: Supabase-specific setup and limitations
• EQL guide: Full reference for EQL types and functions