Start

Agents

App store purchases

Auth

Builds

Credits

Database

Flags

Generated Assets

Hosting

Jobs and cron

Localization

Monitoring

Network

Notifications

Organizations

Queues

Realtime

Runners

Sandboxes

Storage

Webhooks

Workflows

Getting Started

Installation

Authentication

AI Services

KV Store

Overview

Webhooks

Deploy & Infrastructure

Reference

On this page

Create indexes
Read a query plan
Avoid the N+1 problem
Connection pooling
Cache what does not change
Batch large reads and writes
Checklist

How-to

Optimization

Making a query fast, and knowing which one needs it.

Checked 2026-09-28

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:

TypeScript

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),
}))

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.

Large tables want CREATE INDEX CONCURRENTLY instead, which does not block writes — Migrations has the caveats.

#Read a query plan

EXPLAIN ANALYZE runs the query and reports what Postgres did:

TypeScript

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)

TypeScript

// 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.

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.

#Avoid the N+1 problem

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

TypeScript

// 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:

TypeScript

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)

TypeScript

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:

TypeScript

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:

TypeScript

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 })

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.

#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:

TypeScript

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 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:

TypeScript

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))

TypeScript

// 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.

Was this page helpful?

Tell us what is missing, wrong, or unclear — this opens a real inbox.

Send feedback

Platform links