---
title: Optimization
description: Indexes, query plans, the N+1 problem, pooling, caching and batches — what to reach for and when.
type: how-to
product: database
summary: Making a query fast, and knowing which one needs it.
updated: 2026-09-28
order: 3
---

A database is a full Postgres, so the tuning you know applies unchanged. This
page is the short path: measure, add the index, fix the shape of the query.

## Create indexes

An index is what turns a scan of the whole table into a lookup. Declare them
in the schema so migrations create them:

```ts
import { pgTable, text, timestamp, uuid, boolean, index, uniqueIndex } from 'drizzle-orm/pg-core'

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull().unique(), // Automatic unique index
  name: text('name'),
  status: text('status').default('active'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
  // Single column index
  statusIdx: index('users_status_idx').on(table.status),

  // Composite index (order matters!)
  statusCreatedIdx: index('users_status_created_idx')
    .on(table.status, table.createdAt),

  // Unique index
  emailIdx: uniqueIndex('users_email_idx').on(table.email),
}))

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  title: text('title').notNull(),
  slug: text('slug').notNull(),
  authorId: uuid('author_id').references(() => users.id),
  published: boolean('published').default(false),
  publishedAt: timestamp('published_at'),
}, (table) => ({
  // Index for foreign key lookups
  authorIdx: index('posts_author_idx').on(table.authorId),

  // Composite for common query pattern
  publishedDateIdx: index('posts_published_date_idx')
    .on(table.published, table.publishedAt),

  // Unique slug per author
  authorSlugIdx: uniqueIndex('posts_author_slug_idx')
    .on(table.authorId, table.slug),
}))
```

<Callout tone="note" title="Column order in a composite index">
A composite index can be used when a query filters on a prefix of its
columns, so put the column you always filter on first. `(status,
created_at)` serves `where status = ?` and `where status = ? order by
created_at`, and does not serve `where created_at > ?` on its own.
</Callout>

Large tables want `CREATE INDEX CONCURRENTLY` instead, which does not block
writes — [Migrations](/docs/database/migrations) has the caveats.

## Read a query plan

`EXPLAIN ANALYZE` runs the query and reports what Postgres did:

```ts
import { db } from '@/lib/db'
import { sql } from 'drizzle-orm'

// Analyze a query's execution plan
const plan = await db.execute(sql`
  EXPLAIN ANALYZE
  SELECT * FROM users
  WHERE status = 'active'
  ORDER BY created_at DESC
  LIMIT 20
`)

console.log(plan.rows)
// Output shows:
// - Seq Scan vs Index Scan (Index is usually better)
// - Estimated vs Actual rows
// - Execution time
// - Sort method (in-memory vs disk)
```

```ts
// GOOD: Index Scan
Index Scan using users_status_created_idx on users
  Index Cond: (status = 'active')
  Rows Removed by Filter: 0
  Actual time: 0.015..0.025 ms

// BAD: Sequential Scan (full table scan)
Seq Scan on users
  Filter: (status = 'active')
  Rows Removed by Filter: 9500
  Actual time: 5.123..12.456 ms

// Watch for these red flags:
// - Seq Scan on large tables
// - High "Rows Removed by Filter"
// - Sort using disk instead of memory
// - Nested loops with many iterations
```

The number to trust is the *actual* row count against the *estimated* one. A
planner that expected 10 rows and got 100,000 chose a plan for the wrong
problem — usually because statistics are stale, which `ANALYZE` fixes.

<Callout tone="note" title="When a sequential scan is right">
A small table, or a query that returns most of a large one, is cheaper to
scan than to follow an index row by row. A seq scan is only a finding when
the query returns a small fraction of a big table.
</Callout>

## Avoid the N+1 problem

A loop that queries per row costs one round trip per row:

```ts
// This causes N+1 queries!
const posts = await db.select().from(posts).limit(20)

// 1 query for posts + 20 queries for authors = 21 queries!
for (const post of posts) {
  const author = await db.select()
    .from(users)
    .where(eq(users.id, post.authorId))
  post.author = author[0]
}
```

Join instead, or let the relation do it:

```ts
import { db } from '@/lib/db'
import { posts, users } from '@/lib/db/schema'
import { eq } from 'drizzle-orm'

// Single query with join
const postsWithAuthors = await db.select({
  post: posts,
  author: {
    id: users.id,
    name: users.name,
    avatarUrl: users.avatarUrl,
  },
}).from(posts)
  .leftJoin(users, eq(posts.authorId, users.id))
  .limit(20)
```

```ts
import { db } from '@/lib/db'

// Using Drizzle's query API
const postsWithAuthors = await db.query.posts.findMany({
  limit: 20,
  with: {
    author: {
      columns: {
        id: true,
        name: true,
        avatarUrl: true,
      },
    },
  },
})
```

And when the two things are not a join, one batched read for all of them:

```ts
import { db } from '@/lib/db'
import { users } from '@/lib/db/schema'
import { inArray } from 'drizzle-orm'

// Fetch posts first
const posts = await db.select().from(posts).limit(20)

// Batch load all authors in one query
const authorIds = [...new Set(posts.map(p => p.authorId).filter(Boolean))]
const authors = await db.select()
  .from(users)
  .where(inArray(users.id, authorIds))

// Create lookup map
const authorMap = new Map(authors.map(a => [a.id, a]))

// Attach authors to posts
const postsWithAuthors = posts.map(post => ({
  ...post,
  author: post.authorId ? authorMap.get(post.authorId) : null,
}))
```

## Connection pooling

Every database has a pooler in front of it, and the `uri` that `connect`
returns already goes through it, in transaction mode. So a serverless
function that opens a connection per request does not open a connection per
request: the pooler hands back one that is already up.

Nothing has to be configured for that. What is worth doing is keeping a
long-running process from opening more connections than it needs:

```ts
import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'
import * as schema from './schema'

// Through the pooler: many short-lived connections are fine
const sql = postgres(process.env.DATABASE_URL!)
export const db = drizzle(sql, { schema })

// For a long-running process that would rather hold a few of its own
const pool = postgres(process.env.DATABASE_URL!, { max: 10 })
export const dbPool = drizzle(pool, { schema })
```

<Callout tone="warning" title="Do not hold a connection across a slow call">
A transaction holds its connection until it commits, and a request that
awaits a third-party API inside a transaction holds it for the whole wait.
Do the external call first, or after the commit — never inside.
</Callout>

## Cache what does not change

Anything read far more often than it is written does not need to be read from
Postgres every time. The platform has a KV store for exactly this:

```ts
import { Sylphx } from '@sylphx/sdk'

const sylphx = new Sylphx()

async function cachedQuery<T>(
  key: string,
  queryFn: () => Promise<T>,
  ttlSeconds: number,
): Promise<T> {
  const { value } = await sylphx.data.kv.get({ namespaceId: 'ratelimit', key })
  if (value?.value) {
    return JSON.parse(Buffer.from(value.value, 'base64').toString()) as T
  }

  const data = await queryFn()
  await sylphx.data.kv.put({
    namespaceId: 'ratelimit',
    key,
    value: Buffer.from(JSON.stringify(data)).toString('base64'),
    ttlSeconds: String(ttlSeconds),
  })

  return data
}

// Invalidate cache when data changes
async function createPost(data: NewPost) {
  const post = await db.insert(posts).values(data).returning()

  // Invalidate related caches
  await sylphx.data.kv.delete({ namespaceId: 'ratelimit', key: 'popular-posts' })
  await sylphx.data.kv.delete({
    namespaceId: 'ratelimit',
    key: `user-posts:${data.authorId}`,
  })

  return post
}
```

Values are bytes, carried base64, and `ttl_seconds` is a string because it is
a 64-bit integer; `0` means no expiry. The [KV collection](/docs/api/kv) has
the whole API, including the conditional writes that make a cache stampede
safe.

## Batch large reads and writes

One statement that writes a thousand rows beats a thousand statements:

```ts
import { db } from '@/lib/db'
import { users } from '@/lib/db/schema'
import { inArray, sql } from 'drizzle-orm'

// Batch insert
const newUsers = Array.from({ length: 1000 }, (_, i) => ({
  email: `user${i}@example.com`,
  name: `User ${i}`,
}))

// Insert in chunks to avoid memory issues
const chunkSize = 100
for (let i = 0; i < newUsers.length; i += chunkSize) {
  const chunk = newUsers.slice(i, i + chunkSize)
  await db.insert(users).values(chunk)
}

// Batch update with CASE expression
const updates = [
  { id: 'id1', name: 'New Name 1' },
  { id: 'id2', name: 'New Name 2' },
  { id: 'id3', name: 'New Name 3' },
]

await db.execute(sql`
  UPDATE users
  SET name = CASE id
    ${sql.join(
      updates.map(u => sql`WHEN ${u.id} THEN ${u.name}`),
      sql` `
    )}
  END
  WHERE id IN (${sql.join(updates.map(u => sql`${u.id}`), sql`, `)})
`)

// Batch delete
const idsToDelete = ['id1', 'id2', 'id3']
await db.delete(users).where(inArray(users.id, idsToDelete))
```

```ts
// Stream results for very large datasets
import { db } from '@/lib/db'
import { users } from '@/lib/db/schema'

// Cursor-based pagination for processing all records
async function processAllUsers(batchSize = 1000) {
  let cursor: string | null = null
  let processed = 0

  while (true) {
    const query = db.select().from(users)
      .orderBy(users.id)
      .limit(batchSize)

    if (cursor) {
      query.where(gt(users.id, cursor))
    }

    const batch = await query

    if (batch.length === 0) break

    // Process this batch
    for (const user of batch) {
      await processUser(user)
      processed++
    }

    cursor = batch[batch.length - 1].id
    console.log(`Processed ${processed} users...`)
  }

  return processed
}
```

## Checklist

- Index the columns you filter and sort on, and always index a foreign key you
  join through.
- Select the columns you need. `SELECT *` reads what you will not use, and it
  cannot be served by an index alone.
- Paginate. Never fetch a whole table to show a page of it.
- Join, use a relation, or batch — never a query per row.
- Read the plan before and after a change. `EXPLAIN ANALYZE` is the only
  evidence that a change helped.
- Cache reads that outnumber their writes, and invalidate on write.

<RelatedDocs
	links={[
		{
			href: '/docs/database/query-guide',
			label: 'Query guide',
			description: 'The queries these indexes exist for.',
		},
		{
			href: '/docs/database/transactions',
			label: 'Transactions',
			description: 'Isolation levels, and how long a transaction holds its locks.',
		},
		{
			href: '/docs/api/kv',
			label: 'The KV collection',
			description: 'The cache used above, as a service of its own.',
		},
	]}
/>