Database indexes for fast Drizzle queries

The package never creates indexes for you, so each query shape it sends needs one that fits. The default case-insensitive filter needs an index on lower(column), cursor pages need one on the sort columns plus the primary key, full-text search needs a GIN index, contains needs pg_trgm, and relation loaders need indexes on foreign keys and join-table columns. A tenant column goes first in a composite index.

Which index does each query need?Copy Anchor Link

Find the call you make, then create the index next to it. Every example is PostgreSQL; the migration that holds them is yours.

Which index does a case-insensitive filter need?Copy Anchor Link

An index on lower(column). While sql.caseSensitive is false, the default, a bare string on a text column compiles to lower(column) = lower($1) in findAll, count, countBy and ?column= over HTTP, and a plain index on the column cannot serve it:

CREATE INDEX users_email_lower ON users (lower(email));

Two other ways get the same effect:

• A citext column. Declare the column as citext (with a Drizzle customType). The package only wraps text, varchar and char columns in lower(), so it sends a plain =, which citext already compares without case, and a normal index on the column works.
• sql.caseSensitive: true. Equality is then a plain = everywhere and a plain index works, but jo@x.io no longer matches Jo@x.io.

Which index makes cursor pages fast?Copy Anchor Link

One on the sort columns followed by the primary key, in the order of sortBy. Each page starts with a bound on the first sort column, so the database seeks to the cursor instead of reading every row before it:

-- findByCursor(filters, { sortBy: '-created_at' })
CREATE INDEX posts_created_id ON posts (created_at DESC, id);

-- findByCursor(filters, { sortBy: ['status', 'title'] })
CREATE INDEX posts_status_title_id ON posts (status, title, id);

The primary key always comes last and always sorts ascending. An index on (created_at, id) serves the bound of a descending page too; (created_at DESC, id) also matches its ORDER BY exactly. The same index serves offset pages with that sort, up to the OFFSET itself, which still reads every skipped row. See cursor pagination for the query.

Which index does full-text search use?Copy Anchor Link

A GIN index on exactly the vector the query builds, or on a stored tsvector column. Full-text search shows both shapes:

-- fullTextSearch(term, ['title', 'body']): the expression, column order and config included
CREATE INDEX posts_fts ON posts USING gin (
  (to_tsvector('english', coalesce(title, '')) || to_tsvector('english', coalesce(body, '')))
);

-- or a generated column, searched with fullTextSearch(term, ['search'])
ALTER TABLE posts ADD COLUMN search tsvector GENERATED ALWAYS AS (
  to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
) STORED;
CREATE INDEX posts_search ON posts USING gin (search);

How do I speed up contains and search?Copy Anchor Link

With a trigram index from the pg_trgm extension. contains, startsWith, endsWith, their i forms and options.search compile to ILIKE '%term%' (or LIKE), which a btree index cannot serve, so without one PostgreSQL reads the whole table:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX posts_title_trgm ON posts USING gin (title gin_trgm_ops);

One trigram index per searched column. A search over ['title', 'body'] is title ILIKE ... OR body ILIKE ..., which PostgreSQL answers with both indexes combined.

Which indexes do relations need?Copy Anchor Link

The keys the batched loaders look up. PostgreSQL indexes a primary key and a unique constraint, but not a foreign key, so add these yourself:

-- has-many: posts.comments loads comments WHERE post_id IN (...)
CREATE INDEX comments_post_id ON comments (post_id);

-- many-to-many: a primary key on (post_id, tag_id) covers post_id lookups;
-- tags.posts, relation filters and counts from the tag side need tag_id
CREATE INDEX post_tags_tag_id ON post_tags (tag_id);

A belongs-to (posts.author) looks up the referenced primary key, which is indexed already. A relation filter ({ comments: { some: { ... } } }) and options.counts use the same foreign key as the loader.

Should my indexes skip soft-deleted rows?Copy Anchor Link

Yes, when most reads leave the trash out. Every read adds deleted_at IS NULL while soft delete is on, so a partial index with the same predicate is smaller and still matches:

CREATE INDEX posts_live_created_id ON posts (created_at DESC, id)
  WHERE deleted_at IS NULL;

A trash view (onlyDeleted: true) then needs its own index, or reads the table.

How do I index a multi-tenant table?Copy Anchor Link

Put the tenant column first. A scoped entity adds tenant_id = $1 to every read, count and write, so each index above works best with the scope columns in front:

CREATE INDEX docs_tenant_created_id ON docs (tenant_id, created_at DESC, id)
  WHERE deleted_at IS NULL;
CREATE INDEX docs_tenant_title_lower ON docs (tenant_id, lower(title));

When should I turn off the count or use the cursor?Copy Anchor Link

On a large table, when nobody needs the total. findAll runs a COUNT(*) next to every page, and on 200,000 rows that count was 97% of the call. Two ways out:

• withCount: false (?withCount=false). total is null and hasMore says whether a next page exists. See pagination.
• findByCursor, which skips the count unless you pass count: true, and whose deep pages stay as fast as the first with the index above. Use it for infinite scroll, exports and sync jobs.

Keep the count for a table that shows page numbers and a total, and give its filters an index so the count can use it.

Common questionsCopy Anchor Link

Next stepsCopy Anchor Link