Indexes

Btree, GIN, GiST, BRIN, HNSW — when to use what, plus partial + expression + composite indexes.

Indexes accelerate reads at the cost of write throughput + storage. Voltro's DSL lets you declare every Postgres index type with explicit intent.

Single-column index

Indexes are declared at the table level — there is no column-level .index() modifier. The single surface is .index(...):

const notes = table('notes', {
  id:       id(),
  authorId: text(),
  tenantId: text(),
})
  .index(['authorId'])        // auto-named → "notes_authorId_idx"
  .index(['tenantId'])

Each generates CREATE INDEX notes_<col>_idx ON notes (<col>); — btree, default opclass.

Named composite index

For multi-column lookups + ORDER BY pagination:

import { table, id, text, timestamp } from '@voltro/database'

const notes = table('notes', {
  id:        id(),
  tenantId:  text(),
  createdAt: timestamp(),
})
  // Chained `.index(name, [cols], options?)` on the table — leftmost column
  // is the most-selective filter. Index fields are plain column names
  // (or `{ expr: '…' }` for an expression).
  .index('notes_tenant_created', ['tenantId', 'createdAt'])

Column order matters — leftmost is the most-selective filter. The framework warns when an index's leftmost column is also covered by a single-column index (redundancy).

Indexing mixin-contributed columns

.index([...]) / .unique([...]) keys accept the table's own columns plus the columns each directly applied mixin contributes — tenant() → tenantId, audit() → createdAt / updatedAt / createdBy / updatedBy, softDelete() → deletedAt / deletedBy. Order matters: declare the index or unique constraint after .with(...), so the mixin columns are in scope. Calling .index([...]) before .with(...) sees only the own columns (the mixin hasn't been applied yet).

export const memberships = table('memberships', {
  id:     id(),
  userId: reference(() => users),
})
  .with(audit(), tenant())            // applies tenantId + audit columns
  .unique(['tenantId', 'userId'])     // ✅ mixin column tenantId is addressable
  .index(['createdAt'])               // ✅ audit column, applied above

Typos are still caught — the key is the real merged column set, not an "accept any string" escape hatch. tenant() already ships its OWN tenantId index, so a plain per-tenant lookup needs nothing extra; this only matters for the additional composite keys you author yourself.

Transitive caveat. Only directly applied mixin columns enter the key union. .with(tenant()) adds tenantId but NOT audit()'s columns — even though tenant() requires audit() and those columns exist at runtime. To index a transitively-required mixin's column, apply that mixin explicitly: .with(audit(), tenant()).

Index types

Every index kind is selected with the { kind } option on the table-level .index(name, [cols], { kind }) (or .expressionIndex(...)). The default is btree.

Btree (default)

Equality + range + ordering. The right choice 90% of the time. Leave kind off to get it:

table('notes', { id: id(), authorId: text() })
  .index('notes_author', ['authorId'])         // btree, default opclass

GIN

For arrays, JSONB containment, and full-text search:

import { table, id, json } from '@voltro/database'

table('docs', { id: id(), tags: json<string[]>() })
  .index('docs_tags', ['tags'], { kind: 'gin' })

On postgres this emits USING GIN. GIN is postgres-only — on mysql / mariadb a btree can't serve containment so the migrator skips the index + warns (denormalise the path you query into a generated column and index THAT instead); on mssql / sqlite it falls back to a plain btree + a warning.

GiST

For range types, geometric types (PostGIS), and full-text search where you need ranking:

table('venues', { id: id(), location: text() })   // geography(...) in real PostGIS code
  .expressionIndex('venues_loc_gist', [{ expr: '"location"' }], { kind: 'gist' })

Postgres-only; the migrator warns + falls back to btree on other dialects. See PostGIS for the spatial story.

BRIN

Block-range index — extremely cheap for very large append-mostly tables sorted by an indexed column (time-series, logs):

table('events', { id: id(), occurredAt: timestamp() })
  .index('events_occurred', ['occurredAt'], { kind: 'brin' })

On postgres this emits USING BRIN — tiny (~1% of the table size) + fast for range scans on append-ordered data, useless for random-access lookups. Other dialects have no block-range method; the migrator falls back to a plain btree + a warning (the range scan still works, just without BRIN's size win).

HNSW (pgvector)

For vector similarity search. The index must cover exactly one vector() column — declaring kind: 'hnsw' on anything else throws at declaration time:

table('embeddings', { id: id(), embedding: vector(1536) })
  .index('emb_hnsw', ['embedding'], { kind: 'hnsw' })

Defaults to m=16, ef_construction=64, opclass vector_cosine_ops. Override the tuning knobs via kindOptions.hnsw:

table('embeddings', { id: id(), embedding: vector(1536) })
  .index('emb_hnsw', ['embedding'], {
    kind: 'hnsw',
    kindOptions: { hnsw: { m: 24, efConstruction: 128 } },
  })

Postgres + pgvector only; on every other dialect a btree on a vector is meaningless, so the migrator emits no index + warns (ANN queries fall back to a sequential scan). The distance metric → opclass mapping (vector_cosine_ops / vector_l2_ops / vector_ip_ops) is covered on the Vector columns page.

Per-dialect support matrix

warn+btree = the migrator emits a plain btree index + a [voltro:migrate] warning (the intended method was unavailable but a btree still helps). skip+warn = no index is emitted + a warning (a btree on that column would be useless).

`kind`	postgres	mysql	mariadb	mssql	sqlite
`btree` (default)	`USING btree` (implicit)	implicit	implicit	implicit	implicit
`gist`	`USING GIST`	warn+btree	warn+btree	warn+btree	warn+btree
`gin`	`USING GIN`	skip+warn	skip+warn	warn+btree	warn+btree
`brin`	`USING BRIN`	warn+btree	warn+btree	warn+btree	warn+btree
`hnsw`	`USING hnsw (col <opclass>) WITH (m=…, ef_construction=…)`	skip+warn	skip+warn	skip+warn	skip+warn

A "jsonb index" is not a separate kind — it's a json() column (already JSONB on postgres) plus { kind: 'gin' }, i.e. the gin row above.

Partial indexes

Index only rows matching a predicate — drastically smaller + faster when most rows wouldn't match. Pass { where }:

table('notes', { id: id(), updatedAt: timestamp(), archived: boolean() })
  .index('notes_unarchived', ['updatedAt'], { where: `"archived" = false` })

Useful for soft-delete tables (WHERE deletedAt IS NULL) and status-filtered queries. See Partial indexes (WHERE clause) below for the full cross-dialect story.

Expression indexes

Index a computed value, not a column — use .expressionIndex(...) with an { expr } entry:

table('users', { id: id(), email: text() })
  .expressionIndex('users_email_lower', [{ expr: 'lower("email")' }])

Then WHERE lower("email") = ? uses the index. The query builder doesn't auto-rewrite WHERE email ILIKE 'foo' to use this — you call out the expression explicitly.

Unique indexes

text().unique()                                 // single-column, on the column

Multi-column uniqueness is declared at the table level with .unique(name, [cols]) — see Composite UNIQUE constraints below.

Unique among ACTIVE rows — `.uniqueActive([...])`

Enforce uniqueness only among the rows that aren't soft-deleted — "one active roadmap per (project, year)", where a soft-deleted roadmap frees the key for a new one:

table('project_roadmaps', {
  id: id(), projectId: text(), year: integer(), deletedAt: timestamp(),
})
  .softDelete()
  .uniqueActive(['projectId', 'year'])            // partial UNIQUE among deletedAt IS NULL

Emits a CREATE UNIQUE INDEX … WHERE "deletedAt" IS NULL, so a soft-deleted row leaves the active set and a NEW row with the same key inserts cleanly — no hand-written generatedAs("CASE WHEN …") column, and no resurrection bug where a re-imported soft-deleted key collides. The predicate defaults to the softDelete() active set; override it for a custom one:

.uniqueActive('byActiveSlug', ['orgId', 'slug'], { where: `"status" = 'open'` })

Cross-dialect:

Dialect	Support
postgres / sqlite / mssql	native partial `CREATE UNIQUE INDEX … WHERE`
mysql / mariadb	lowered automatically to a generated STORED column per key column (NULL when soft-deleted) + a UNIQUE over them — NULL-distinct gives the same resurrection-safe semantics. Nothing to hand-write.

mysql / mariadb — how the emulation works. Those engines have no partial index, so .uniqueActive(['projectId', 'year']) lowers to one CASE WHEN <predicate> THEN CAST(<col> AS CHAR(255)) ELSE NULL END STORED column per key column plus a UNIQUE over them. A soft-deleted row's generated columns are all NULL, and mysql/mariadb treat NULLs as DISTINCT in a unique index, so it never collides — re-creating the key just works, exactly like the partial index elsewhere. This round-trips through the declarative differ (the generated columns are part of the declared snapshot on those dialects, so voltro dev never re-plans them). You write the same .uniqueActive([...]) on every dialect.

The predicate is emitted verbatim (ANSI double-quoted identifiers; on mysql/mariadb they are re-quoted with backticks inside the generated column).

When NOT to index

Tables with <10k rows on a fast disk — the cost of maintaining the index outweighs the seq-scan cost.
Columns with very low selectivity (boolean flags, status enums where one value dominates). Use a partial index instead.
Write-heavy hot paths. Every index is a synchronous write on every insert/update.

Partial indexes (WHERE clause)

table('orders', {
  id:       id(),
  orgId:    text(),
  status:   text(),
  createdAt: timestamp(),
})
  .index('byOpenStatus', ['orgId', 'createdAt'], {
    where: `"status" IN ('pending', 'approved')`,
  })

The DDL emits CREATE INDEX ... WHERE ... — only rows matching the predicate participate in the index. Use cases:

Index only non-soft-deleted rows: where: \"deletedAt" IS NULL``
Index only active users: where: \"banned" = false``
Index only open tickets: where: \"status" IN ('open', 'pending')``

The result: a much smaller B-tree (faster reads, smaller cache footprint) at the cost of one extra WHERE clause the query planner has to match against.

Cross-dialect:

Dialect	Support
postgres / sqlite	native `CREATE INDEX ... WHERE ...`
mssql	native "filtered index"
mysql / mariadb	NOT supported → drops the WHERE + warns at migrate

The where clause is emitted verbatim — caller is responsible for quoting identifiers per the target dialect.

Expression indexes (functions on columns)

For predicates that compute on the column rather than match exact values, use .expressionIndex():

table('users', { id: id(), email: text() })
  .expressionIndex('byEmailCi', [{ expr: 'lower("email")' }])

Now WHERE lower("email") = ? uses the index. Without it, case-insensitive email lookup is a sequential scan.

// Mix columns + expressions
.expressionIndex('byOrgCreatedMonth', [
  'orgId',
  { expr: \`date_trunc('month', "createdAt")\` },
])

// Combine with partial-where
.expressionIndex('byActiveEmailCi',
  [{ expr: 'lower("email")' }],
  { where: \`"active" = true\` },
)

Why a separate method from .index([...]):

.index([cols]) validates the column names against the row type at compile time. Typos fail at tsc --noEmit.
.expressionIndex(name, [...]) accepts arbitrary expression strings — by definition the framework can't type-check them. Keeping the two methods separate preserves the compile-time safety of the regular form.

Index names are unique per SCHEMA, not per table

Every dialect keys index names per schema (postgres pg_class, mysql / mariadb information_schema, mssql sys.indexes, sqlite sqlite_master) — not per table. So a hand-picked name reused on two tables collides:

table('ab_tests',    { /* … */ }).index('byStatusStart', ['status', 'startAt'])
table('tournaments', { /* … */ }).index('byStatusStart', ['status', 'startAt'])
// ❌ throws — 'byStatusStart' would exist twice in one schema

The DB creates only the first; every later CREATE INDEX … IF NOT EXISTS <name> is a silent no-op, so db plan re-emits the un-created ones forever and never reaches "up to date". The framework catches this when the full schema is snapshotted (boot / db plan) and fails loud, naming both tables + a suggested fix. Auto-named indexes (.index([col]) → <table>_<col>_idx) are table-prefixed and can't collide — only explicit names can. Give each a distinct, table-scoped name (abTestsByStatusStart, tournamentsByStatusStart).

Composite UNIQUE constraints

Multi-column uniqueness — (orgId, slug) must be unique so two orgs can both have a /dashboard slug but neither can have two of their own:

table('org_slugs', {
  id:    id(),
  orgId: reference(() => orgs),
  slug:  text(),
})
  .unique(['orgId', 'slug'])                         // auto-named
  .unique('byOrgSlug', ['orgId', 'slug'])            // explicit name
  .unique('byOrgSlug', ['orgId', 'slug'], { dedup: 'suffix-counter' })  // with backfill policy

Distinct from the column-level .unique() modifier (single-column only, lives on the column). Composite UNIQUE MUST be declared at the table level.

The dedup policy tells the migration planner what to do when the constraint is added to a populated table with duplicates:

'fail' (default) — refuse with the conflicting rows surfaced
'suffix-counter' — UPDATE conflicts to <value>-2, <value>-3, ...
sql\...`` — custom SQL fragment

Emits CONSTRAINT <name> UNIQUE (col1, col2, ...) inline in CREATE TABLE on every dialect. Standard SQL.

This is what backs ctx.store.upsert(..., { conflictColumns: ['a', 'b'] }) — see Bulk operations.

GiST indexes (PostGIS spatial)

{ kind: 'gist' } on .expressionIndex() emits USING GIST:

table('venues', {
  id:       id(),
  location: geography('Point', 4326),   // from @voltro/plugin-postgis
}).expressionIndex(
  'venues_loc_gist',
  [{ expr: '"location"' }],
  { kind: 'gist' },
)

GiST is the access method PostGIS needs for spatial predicates (ST_DWithin, ST_Contains, etc.) — a regular B-tree index can't serve them. Postgres-only; the migrator warns + falls back to B-tree on other dialects.

See PostGIS for the full spatial story.

Full-text indexes

table('posts', { id: id(), title: text(), body: text() })
  .fullTextIndex('postSearch', ['title', 'body'], {
    config:  'english',
    weights: { title: 'A', body: 'B' },
  })

Higher-level abstraction over expression indexes — see Full-text search for the full shape.

Inspecting

# The migrations introspection endpoint carries the full live schema —
# every table's columns + indexes as the runtime sees them.
curl http://localhost:5191/_voltro/inspect/migrations

Or via the dashboard's Database tab.

For Postgres-side inspection: pg_stat_user_indexes shows scans + tuples read per index. Indexes with idx_scan = 0 after weeks of traffic are deadweight — drop them.

Indexes

#Single-column index

#Named composite index

#Indexing mixin-contributed columns

#Index types

#Btree (default)

#GIN

#GiST

#BRIN

#HNSW (pgvector)

#Per-dialect support matrix

#Partial indexes

#Expression indexes

#Unique indexes

#Unique among ACTIVE rows — .uniqueActive([...])

#When NOT to index

#Partial indexes (WHERE clause)

#Expression indexes (functions on columns)

#Index names are unique per SCHEMA, not per table

#Composite UNIQUE constraints

#GiST indexes (PostGIS spatial)

#Full-text indexes

#Inspecting