Skip to content
DocsconceptsDocumentation

Indexes

Declare B-tree indexes on tables and query them with withIndex — every filtered read should hit one.

Last updated:

An index is a sorted, B-tree-backed lookup over one or more columns. Declare it on a table, then read through it with .withIndex() so the query seeks the rows it needs instead of scanning the whole table and filtering in memory.

Declaring an index

.index(name, [fields], { unique? }) adds a secondary index. List the columns in the order you'll constrain them. The index is ordered left to right, so the leftmost field must be the one you equality-match first.

// lunora/schema.ts
import { defineSchema, defineTable, v } from "lunorash/server";

export default defineSchema({
    messages: defineTable({
        channelId: v.id("channels"),
        userId: v.id("users"),
        text: v.string(),
    })
        .index("by_channel", ["channelId", "_creationTime"])
        .index("by_user", ["userId"]),

    users: defineTable({
        email: v.string(),
        name: v.string(),
    }).index("by_email", ["email"], { unique: true }),
});

{ unique: true } additionally enforces that no two rows share the indexed value.

Fields are top-level columns

An index names columns of the row, not paths inside them — "user.settings.theme" is not accepted. A v.object() column is stored as one JSON value, so indexing inside it would mean an expression index over json_extract(...) on every engine plus a where DSL that can route a dotted key to it. Half of that is worse than none: a predicate that missed the route would read as a silent full table scan rather than an error, which is the exact problem the index was declared to avoid.

Denormalise the field you query onto the row and keep the copy current in the mutation that writes the nested value:

// lunora/schema.ts
users: defineTable({
    settings: v.object({ theme: v.string() }),
    // Written alongside `settings.theme` on every update; this is the indexed one.
    theme: v.string(),
}).index("by_theme", ["theme"]),

Querying with withIndex

q.withIndex(name, q => …) selects the index and builds a range over its leading columns. The range builder offers .eq, .gt, .gte, .lt, .lte: equality-match the leading fields, then optionally bound the next one:

import { query, v } from "@/lunora/_generated/server";

export const recent = query.input({ channelId: v.id("channels"), since: v.number() }).query(async ({ ctx, args: { channelId, since } }) => {
    return ctx.db
        .query("messages")
        .withIndex("by_channel", (q) => q.eq("channelId", channelId).gt("_creationTime", since))
        .order("desc")
        .take(50);
});

.withIndex() composes with .order(), .filter(), and every terminal (.collect(), .first(), .take(n), .paginate(), .unique()).

Why every filtered read should hit an index

ctx.db.query(table).filter(...) with no .withIndex() first loads every row and filters in memory. That's fine for a handful of rows, but a problem as the table grows. A .withIndex() read seeks straight to the matching range. As a rule, any read that constrains a column should constrain it through an index, and any foreign-key column you read by should have an index leading with it.

How the Advisors catch unindexed reads

The Advisors lint your schema and discovered queries at build time and surface these in the Studio:

  • filter_without_index (warn): a query(...).filter(...) with no .withIndex() first; it loads every row and filters in memory.
  • unindexed_foreign_key (info): a foreign-key column with no index leading with it, so reads on it full-scan. (It honours SQLite's leftmost-prefix rule; a foreign-key column named _id is exempt.)
  • empty_index (warn) and duplicate_index (info) catch indexes that narrow nothing or are a redundant prefix of another.

At runtime, index_utilization flags a dead index (zero recorded reads) and a hot unindexed scan (a table full-scanned many times with no index).

Checking a read with explain

ctx.db.<table>.explain(args) takes the same arguments as findMany and reports how the database would run it, without running it:

const { sql, params, plan } = await ctx.db.messages.explain({ where: { room: "lobby" } });
// plan: ["SEARCH messages USING INDEX by_room (room=?)"]

An index serves the read when the plan says SEARCH … USING INDEX; SCAN means every row is read. sql and params are the exact statement and bound values. explain honors RLS and soft delete like the read does, so the plan is the one your caller would get. Explaining does not subscribe a live query to the table. A relation filter in where is resolved first with an ordinary read of the related table, which does subscribe to that table, because the ids it matched end up in params. limit: n shows as LIMIT n + 1, the over-fetch findMany uses to learn whether another page exists.

params includes values your RLS policies injected (a user id, a tenant), and a relation filter inlines the ids it matched, so keep explain output on the server rather than returning it to a client. A filter on a masked column is refused, as it is for findMany.

Plan lines are the database's own text: SQLite's EXPLAIN QUERY PLAN for shard tables and D1, and EXPLAIN on a Postgres or MySQL .global() backend, whose lines differ. Only SQLite and D1 are run against the real engine in the test suite; the Postgres and MySQL path is checked against canned plan rows.

Aggregate indexes

Beyond ordinary secondary indexes, a table can declare an aggregate index via .aggregateIndex(name, { by, op, field, where }). The runtime keeps a counter row per by group (maintained by triggers on every write), so reads whose where keys all participate in by are answered in O(1) from the counter table without scanning rows. Use count (field-less), or sum/min/max/avg (which take a field).

messages: defineTable({
    channelId: v.id("channels"),
    userId: v.id("users"),
    text: v.string(),
})
    .index("by_channel", ["channelId", "_creationTime"])
    .aggregateIndex("count_by_channel", { by: ["channelId"] });