Skip to main content
Indexes improve query performance by creating fast lookup structures on table columns. Drizzle supports standard and advanced index types across all databases.

​
Basic indexes

​
Single column index

Create an index on a single column: 
import { pgTable, serial, varchar, index } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull(),
  username: varchar('username', { length: 50 }),
}, (table) => [
  index('email_idx').on(table.email),
  index('username_idx').on(table.username),
]);

​
Composite index

Index multiple columns together for queries filtering on those columns: 
import { pgTable, serial, varchar, integer, timestamp, index } from 'drizzle-orm/pg-core';

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  authorId: integer('author_id').notNull(),
  status: varchar('status', { length: 20 }),
  createdAt: timestamp('created_at').notNull(),
}, (table) => [
  index('author_status_idx').on(table.authorId, table.status),
  index('author_created_idx').on(table.authorId, table.createdAt),
]);
Column order matters in composite indexes. Place the most selective columns first.

​
Auto-generated index names

Omit the name to let Drizzle generate one: 
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }),
}, (table) => [
  index().on(table.email), // generates name like "users_email_idx"
]);

​
Unique indexes

Create a unique index to enforce uniqueness and improve lookup performance: 
import { pgTable, serial, varchar, uniqueIndex } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull(),
  username: varchar('username', { length: 50 }),
}, (table) => [
  uniqueIndex('email_unique_idx').on(table.email),
  uniqueIndex('username_unique_idx').on(table.username),
]);
Unique indexes are different from unique constraints. Use unique() for constraints and uniqueIndex() for indexes.

​
PostgreSQL-specific features

​
Index methods

PostgreSQL supports multiple index methods: 
import { pgTable, serial, varchar, text, jsonb, index } from 'drizzle-orm/pg-core';

export const documents = pgTable('documents', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }),
  content: text('content'),
  metadata: jsonb('metadata'),
}, (table) => [
  // B-tree (default) - good for equality and range queries
  index('title_idx').using('btree', table.title),
  
  // Hash - only for equality comparisons
  index('title_hash_idx').using('hash', table.title),
  
  // GIN - for full-text search and JSONB
  index('metadata_idx').using('gin', table.metadata),
  
  // BRIN - for very large tables with natural ordering
  index('id_brin_idx').using('brin', table.id),
]);
Available methods:
  • 'btree' - Default, good for most use cases
  • 'hash' - Equality comparisons only
  • 'gin' - JSON, arrays, full-text search
  • 'gist' - Geometric data, full-text search
  • 'spgist' - Space-partitioned data
  • 'brin' - Block range indexes for large tables

​
Partial indexes

Index only rows matching a condition: 
import { pgTable, serial, varchar, boolean, timestamp, index, sql } from 'drizzle-orm/pg-core';

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }),
  published: boolean('published').default(false),
  deletedAt: timestamp('deleted_at'),
}, (table) => [
  // Index only published posts
  index('published_posts_idx')
    .on(table.title)
    .where(sql`${table.published} = true`),
  
  // Index only non-deleted posts
  index('active_posts_idx')
    .on(table.title)
    .where(sql`${table.deletedAt} IS NULL`),
]);

​
Concurrent index creation

Create indexes without blocking writes: 
import { pgTable, serial, varchar, index } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }),
}, (table) => [
  index('email_idx').on(table.email).concurrently(),
]);
Concurrent index creation takes longer but doesn’t lock the table.

​
Index with storage parameters

Specify storage parameters for fine-tuning: 
import { pgTable, serial, text, index } from 'drizzle-orm/pg-core';

export const logs = pgTable('logs', {
  id: serial('id').primaryKey(),
  message: text('message'),
}, (table) => [
  index('message_idx')
    .on(table.message)
    .with({ fillfactor: 70 }),
]);

​
Expression indexes

Index computed values: 
import { pgTable, serial, varchar, index, sql } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }),
  firstName: varchar('first_name', { length: 100 }),
  lastName: varchar('last_name', { length: 100 }),
}, (table) => [
  // Index lowercase email for case-insensitive searches
  index('email_lower_idx').on(sql`lower(${table.email})`),
  
  // Index full name
  index('full_name_idx').on(sql`${table.firstName} || ' ' || ${table.lastName}`),
]);

​
pgvector indexes

For vector similarity search with the pgvector extension: 
import { pgTable, serial, vector, index } from 'drizzle-orm/pg-core';

export const embeddings = pgTable('embeddings', {
  id: serial('id').primaryKey(),
  embedding: vector('embedding', { dimensions: 1536 }),
}, (table) => [
  // HNSW index for fast approximate search
  index('embedding_idx')
    .using('hnsw', table.embedding.op('vector_cosine_ops'))
    .with({ m: 16, ef_construction: 64 }),
  
  // IVFFlat index
  index('embedding_ivfflat_idx')
    .using('ivfflat', table.embedding.op('vector_l2_ops'))
    .with({ lists: 100 }),
]);

​
Operator classes

Specify how columns should be indexed: 
import { pgTable, serial, text, index } from 'drizzle-orm/pg-core';

export const documents = pgTable('documents', {
  id: serial('id').primaryKey(),
  title: text('title'),
}, (table) => [
  // Use text_pattern_ops for LIKE queries
  index('title_pattern_idx').on(table.title.op('text_pattern_ops')),
]);

​
MySQL-specific features

​
Index types

import { mysqlTable, serial, varchar, text, index } from 'drizzle-orm/mysql-core';

export const articles = mysqlTable('articles', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }),
  content: text('content'),
}, (table) => [
  // BTREE (default)
  index('title_idx').on(table.title).using('btree'),
  
  // HASH
  index('title_hash_idx').on(table.title).using('hash'),
  
  // FULLTEXT for full-text search
  index('content_fulltext_idx').on(table.content).using('fulltext'),
]);

​
Index length prefix

For long text columns, index only a prefix: 
import { mysqlTable, serial, text, index } from 'drizzle-orm/mysql-core';

export const posts = mysqlTable('posts', {
  id: serial('id').primaryKey(),
  content: text('content'),
}, (table) => [
  // Index first 100 characters
  index('content_prefix_idx').on(table.content.op('100')),
]);

​
SQLite-specific features

​
Partial indexes

import { sqliteTable, integer, text, index, sql } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey(),
  email: text('email'),
  deletedAt: integer('deleted_at', { mode: 'timestamp' }),
}, (table) => [
  index('active_users_idx')
    .on(table.email)
    .where(sql`${table.deletedAt} IS NULL`),
]);

​
Expression indexes

import { sqliteTable, integer, text, index, sql } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey(),
  email: text('email'),
}, (table) => [
  index('email_lower_idx').on(sql`lower(${table.email})`),
]);

​
Performance tips

1

Index columns used in WHERE clauses

Create indexes on columns frequently used for filtering:
index('status_idx').on(table.status)
2

Use composite indexes wisely

Put the most selective column first in composite indexes:
// Good: userId is more selective than status
index().on(table.userId, table.status)
3

Consider partial indexes

Index only the data you query:
index().on(table.email).where(sql`${table.active} = true`)
4

Don't over-index

Each index adds overhead to INSERT/UPDATE operations. Only create indexes you need.
5

Monitor index usage

Use database tools to identify unused indexes and remove them.

​
Complete example

import {
  pgTable,
  serial,
  varchar,
  text,
  integer,
  timestamp,
  boolean,
  jsonb,
  index,
  uniqueIndex,
  sql,
} from 'drizzle-orm/pg-core';

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  slug: varchar('slug', { length: 255 }).notNull(),
  content: text('content'),
  authorId: integer('author_id').notNull(),
  status: varchar('status', { length: 20 }).notNull(),
  tags: jsonb('tags').$type<string[]>(),
  published: boolean('published').default(false),
  publishedAt: timestamp('published_at'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => [
  // Unique index on slug
  uniqueIndex('slug_unique_idx').on(table.slug),
  
  // Composite index for common query pattern
  index('author_status_idx').on(table.authorId, table.status),
  
  // Partial index for published posts
  index('published_posts_idx')
    .on(table.publishedAt)
    .where(sql`${table.published} = true`),
  
  // GIN index for JSONB tags
  index('tags_idx').using('gin', table.tags),
  
  // Expression index for case-insensitive title search
  index('title_lower_idx').on(sql`lower(${table.title})`),
  
  // Concurrent index creation for production
  index('created_at_idx').on(table.createdAt).concurrently(),
]);

Build docs developers (and LLMs) love

Get started for freeTalk to us