Indexes

Indexes are a data structure that allow you to speed up your database queries by telling Convex how to organize your documents. Indexes also allow you to change the order of documents in query results.

If you are new to indexes, start with Introduction to Indexes and Query Performance.

Defining Indexes

Indexes are defined as part of your Convex schema. Each index consists of:

1. A name
   • Must be unique per table.
2. A list of fields to index, in order
   • These can either be fields directly on your documents (like name) or dot-separated paths referencing fields on nested documents (like properties.name).

To add an index onto a table use the index method on your table's schema:

convex/schema.ts

import { defineSchema, defineTable, s } from "convex/schema";

// Define a messages table with an index on (channel, _creationTime).
export default defineSchema({
  messages: defineTable({
    channel: s.id("channels"),
    body: s.string(),
    user: s.id("users"),
  }).index("by_channel_and_creation_time", ["channel", "_creationTime"]),
});

This index is ordered first by the channel field defined in the schema, and then by the system-generated _creationTime field.

Every time you run npx convex push, the indexes in your deployment will be updated to match your schema.

You may notice that the first push that defines an index is a bit slower than normal. This is because Convex needs to backfill your index. The more data in your table, the longer it will take Convex to organize it in index order. If this is problematic for your workflow, contact us.

You can feel free to query an index in the same push that defines it. Convex will ensure that the index is backfilled before the new query and mutation functions are registered.

Be careful when removing indexes

In addition to adding new indexes, npx convex push will delete indexes that are no longer present in your schema. Make sure that your indexes are completely unused before removing them from your schema!

Querying Using Indexes

A query for "messages in channel created 1-2 minutes ago" over the by_channel_and_creation_time index would look like:

const messages = await db
  .table("messages")
  .index("by_channel_and_creation_time")
  .range(q =>
    q
      .eq("channel", channel)
      .gt("_creationTime", Date.now() - 2 * 60000)
      .lt("_creationTime", Date.now() - 60000)
  )
  .collect();

The .index method defines which index to query. The choice of index both affects how you can filter results in .range and what order the results are returned in.

The .range method defines how Convex will use your index to select documents. An index range is a description of which documents Convex should consider when running the query.

An index range is always a chained list of:

1. 0 or more equality expressions defined with .eq.
2. [Optionally] A lower bound expression defined with .gt or .gte.
3. [Optionally] An upper bound expression defined with .lt or .lte.

You must step through fields in index order.

Each equality expression must compare a different index field, starting from the beginning and in order. The upper and lower bounds must follow the equality expressions and compare the next field.

For example, it is not possible to write a query like:

// DOES NOT COMPILE!
const messages = await db
  .table("messages")
  .index("by_channel_and_creation_time")
  .range(q =>
    q
      .gt("_creationTime", Date.now() - 2 * 60000)
      .lt("_creationTime", Date.now() - 60000)
  )
  .collect();

This query is invalid because the by_channel_and_creation_time index is ordered by (channel, _creationTime) and this query range has a comparison on _creationTime without first restricting the range to a single channel. Because the index is sorted first by channel and then by _creationTime, it isn't a useful index for finding messages in all channels created 1-2 minutes ago. The TypeScript types within range will guide you through this.

To better understand what queries can be run over which indexes, see Introduction to Indexes and Query Performance.

The performance of your query is based on the specificity of the range.

For example, if the query is

const messages = await db
  .table("messages")
  .index("by_channel_and_creation_time")
  .range(q =>
    q
      .eq("channel", channel)
      .gt("_creationTime", Date.now() - 2 * 60000)
      .lt("_creationTime", Date.now() - 60000)
  )
  .collect();

then query's performance would be based on the number of messages in channel created 1-2 minutes ago.

Picking a good index range

For performance, define index ranges that are as specific as possible! If you are querying a large table and you're unable to add any equality conditions with .eq, you should consider defining a new index.

.range is designed to only allow you to specify ranges that Convex can efficiently use your index to find. For all other filtering you can use the .filter method.

For example to query for "messages in channel created by me" you could do:

const messages = await db
  .table("messages")
  .index("by_channel_and_creation_time")
  .range(q => q.eq("channel", channel))
  .filter(q => q.eq(q.field("user"), myUserId))
  .collect();

In this case the performance of this query will be based on how many messages are in the channel. Convex will consider each message in the channel and only return the messages where the user field matches myUserId.

Limits

Convex supports indexes containing up to 16 fields. You can define 32 indexes on each table. Indexes can't contain duplicate fields.

The _id field is automatically added to the end of every index to ensure a stable ordering. Because indexes can't include duplicate fields, you cannot include _id as a field in your indexes.

The by_id and by_creation_time indexes are reserved.