# Database Query Optimization

# Database Query Optimization: A Modern Developer's Guide

## Metadata

**SEO Title:** Database Query Optimization: TypeScript Best Practices 2026

**Meta Description:** Master database query optimization with modern TypeScript solutions. Learn to identify bottlenecks, implement efficient patterns, and avoid common pitfalls in production systems.

**Keywords:** database query optimization, TypeScript database performance, SQL optimization, query performance tuning, database indexing, N+1 query problem, connection pooling, ORM optimization

**Tags:** database-optimization, typescript, performance, sql, backend-development, orm, postgresql

---

## The Problem: Why Query Optimization Matters in 2026

Database query optimization remains one of the most critical yet overlooked aspects of application development. Despite advances in hardware and database technology, poorly optimized queries continue to plague production systems, causing cascading failures, degraded user experiences, and unnecessary infrastructure costs.

The modern development landscape has introduced new complexities. Microservices architectures distribute data across multiple databases, serverless functions impose strict timeout constraints, and real-time applications demand sub-100ms response times. Meanwhile, ORMs and query builders—while improving developer productivity—often obscure the actual SQL being executed, making it easier to introduce performance anti-patterns.

Consider a typical scenario: your application works perfectly in development with a few hundred records, but once deployed to production with millions of rows, response times balloon from milliseconds to seconds. Users complain, monitoring alerts fire, and you're left scrambling to identify which of the hundreds of queries in your codebase is the culprit.

The stakes are high. A single unoptimized query can:
- Lock database tables, blocking other operations
- Exhaust connection pools, causing cascading failures
- Trigger autoscaling that increases cloud costs exponentially
- Degrade user experience, leading to abandoned transactions
- Create technical debt that compounds over time

Common optimization challenges in 2026 include:

**The N+1 Query Problem**: Still the most prevalent issue, where fetching a list of items triggers individual queries for related data. With GraphQL's popularity, this problem has actually intensified, as resolvers can inadvertently create deeply nested N+1 scenarios.

**Missing or Incorrect Indexes**: Developers often rely on ORM defaults without understanding index strategies, leading to full table scans on production datasets.

**Over-fetching Data**: Selecting all columns when only a few are needed wastes bandwidth and memory, particularly problematic in serverless environments with memory constraints.

**Inefficient Joins**: Complex multi-table joins without proper indexing or query planning can bring databases to their knees.

**Connection Pool Exhaustion**: Modern applications with high concurrency can quickly exhaust database connections, especially when queries hold connections longer than necessary.

## Modern TypeScript Solutions

Let's explore practical, production-ready solutions using TypeScript with popular tools like Prisma, TypeORM, and raw SQL with type safety.

### 1. Implementing Query Monitoring and Profiling

Before optimizing, you need visibility. Here's a TypeScript middleware pattern for tracking query performance:

```typescript
import { performance } from 'perf_hooks';

interface QueryMetrics {
  query: string;
  duration: number;
  timestamp: Date;
  stackTrace?: string;
}

class QueryMonitor {
  private metrics: QueryMetrics[] = [];
  private readonly slowQueryThreshold = 100; // ms

  async trackQuery<T>(
    query: string,
    executor: () => Promise<T>
  ): Promise<T> {
    const start = performance.now();
    const stackTrace = new Error().stack;

    try {
      const result = await executor();
      const duration = performance.now() - start;

      this.metrics.push({
        query,
        duration,
        timestamp: new Date(),
        stackTrace: duration > this.slowQueryThreshold ? stackTrace : undefined
      });

      if (duration > this.slowQueryThreshold) {
        console.warn(`Slow query detected (${duration.toFixed(2)}ms):`, query);
      }

      return result;
    } catch (error) {
      const duration = performance.now() - start;
      console.error(`Query failed after ${duration.toFixed(2)}ms:`, query);
      throw error;
    }
  }

  getSlowQueries(): QueryMetrics[] {
    return this.metrics.filter(m => m.duration > this.slowQueryThreshold);
  }
}

export const queryMonitor = new QueryMonitor();
```

### 2. Solving the N+1 Problem with DataLoader

DataLoader batches and caches requests within a single execution context:

```typescript
import DataLoader from 'dataloader';
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

// Create a DataLoader for batching user queries
const userLoader = new DataLoader<number, User>(
  async (userIds: readonly number[]) => {
    const users = await prisma.user.findMany({
      where: { id: { in: [...userIds] } }
    });

    // Return users in the same order as requested IDs
    const userMap = new Map(users.map(u => [u.id, u]));
    return userIds.map(id => userMap.get(id)!);
  }
);

// Usage in resolvers or services
async function getPostsWithAuthors(postIds: number[]) {
  const posts = await prisma.post.findMany({
    where: { id: { in: postIds } }
  });

  // This batches all user requests into a single query
  const postsWithAuthors = await Promise.all(
    posts.map(async post => ({
      ...post,
      author: await userLoader.load(post.authorId)
    }))
  );

  return postsWithAuthors;
}
```

### 3. Strategic Indexing with Type Safety

Define indexes explicitly in your schema and validate them:

```typescript
// Prisma schema with strategic indexes
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  username  String
  createdAt DateTime @default(now())
  posts     Post[]

  @@index([username, createdAt]) // Composite index for common queries
  @@index([createdAt(sort: Desc)]) // Optimized for recent users
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String
  published Boolean  @default(false)
  authorId  Int
  createdAt DateTime @default(now())
  
  author    User     @relation(fields: [authorId], references: [id])

  @@index([authorId, published, createdAt]) // Covering index
  @@index([published, createdAt(sort: Desc)]) // For published posts feed
}
```

### 4. Efficient Pagination with Cursor-Based Approach

Avoid OFFSET-based pagination for large datasets:

```typescript
interface PaginationArgs {
  cursor?: number;
  limit: number;
}

interface PaginatedResult<T> {
  items: T[];
  nextCursor?: number;
  hasMore: boolean;
}

async function getPaginatedPosts(
  args: PaginationArgs
): Promise<PaginatedResult<Post>> {
  const { cursor, limit } = args;

  const posts = await prisma.post.findMany({
    take: limit + 1, // Fetch one extra to determine if there are more
    ...(cursor && {
      cursor: { id: cursor },
      skip: 1 // Skip the cursor itself
    }),
    where: { published: true },
    orderBy: { createdAt: 'desc' },
    select: {
      id: true,
      title: true,
      createdAt: true,
      author: {
        select: { id: true, username: true }
      }
    }
  });

  const hasMore = posts.length > limit;
  const items = hasMore ? posts.slice(0, -1) : posts;
  const nextCursor = hasMore ? items[items.length - 1].id : undefined;

  return { items, nextCursor, hasMore };
}
```

### 5. Connection Pool Management

Properly configure and manage database connections:

```typescript
import { Pool } from 'pg';

const pool = new Pool({
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max: 20, // Maximum pool size
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// Wrapper for automatic connection release
async function withTransaction<T>(
  callback: (client: PoolClient) => Promise<T>
): Promise<T> {
  const client = await pool.connect();
  
  try {
    await client.query('BEGIN');
    const result = await callback(client);
    await client.query('COMMIT');
    return result;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}
```

## Common Pitfalls to Avoid

**1. Premature Optimization**: Don't optimize queries without measuring first. Use query monitoring to identify actual bottlenecks rather than optimizing based on assumptions.

**2. Over-Indexing**: Every index adds overhead to write operations. Create indexes based on actual query patterns, not hypothetical scenarios.

**3. Ignoring Query Plans**: Always use `EXPLAIN ANALYZE` to understand how your database executes queries. ORMs can generate unexpected SQL.

**4. Selecting Unnecessary Data**: Avoid `SELECT *` in production code. Explicitly select only required fields to reduce memory usage and network transfer.

**5. Forgetting About Transactions**: Long-running transactions lock resources. Keep transactions short and focused.

**6. Not Using Prepared Statements**: Prepared statements prevent SQL injection and improve performance through query plan caching.

**7. Ignoring Database-Specific Features**: Modern databases offer powerful features like partial indexes, materialized views, and full-text search. Don't reinvent these in application code.

## Best Practices

1. **Monitor in Production**: Implement query performance monitoring from day one. Tools like pg_stat_statements for PostgreSQL provide invaluable insights.

2. **Use Connection Pooling**: Always use connection pools in production. Configure pool sizes based on your database's max_connections and application concurrency.

3. **Implement Caching Strategically**: Cache expensive query results using Redis or in-memory caches, but ensure proper cache invalidation strategies.

4. **Batch Operations**: When inserting or updating multiple records, use batch operations instead of individual queries.

5. **Test with Production-Like Data**: Performance issues often only appear at scale. Use realistic data volumes in staging environments.

6. **Set Query Timeouts**: Implement statement timeouts to prevent runaway queries from consuming resources indefinitely.

7. **Regular Index Maintenance**: Periodically analyze and vacuum tables (PostgreSQL) or optimize tables (MySQL) to maintain index efficiency.

## Frequently Asked Questions

**Q: When should I use raw SQL versus an ORM?**

A: Use ORMs for standard CRUD operations and simple queries where type safety and productivity matter most. Switch to raw SQL for complex queries, bulk operations, or when you need fine-grained control over query execution. Modern tools like Prisma's `$queryRaw` offer the best of both worlds with type-safe raw SQL.

**Q: How do I identify which queries need optimization?**

A: Implement query logging with execution times, enable slow query logs in your database, and use APM tools like DataDog or New Relic. Focus on queries that are either very slow (>100ms) or executed very frequently (>1000 times/minute).

**Q: What's the ideal connection pool size?**

A: Start with `(core_count * 2) + effective_spindle_count` as a baseline. For cloud databases, consider 10-20 connections per application instance. Monitor pool exhaustion metrics and adjust based on actual usage patterns.

**Q: Should I optimize for reads or writes?**

A: It depends on your application's read/write ratio. Most applications are read-heavy (90%+ reads), so optimize for reads first. Use read replicas for scaling reads and consider write-optimized strategies like write-behind caching only when writes become a bottleneck.

**Q: How do I handle database optimization in a microservices architecture?**

A: Each service should own its database and optimize independently. Use event-driven patterns to denormalize data across services when needed. Implement distributed tracing to identify cross-service query patterns that might benefit from data co-location.

**Q: What's the best way to handle full-text search?**

A: For simple cases, use database-native full-text search (PostgreSQL's tsvector, MySQL's FULLTEXT). For complex requirements, use dedicated search engines like Elasticsearch or Typesense. Don't implement text search with LIKE queries—it doesn't scale.

**Q: How often should I rebuild indexes?**

A: Most modern databases handle index maintenance automatically. For PostgreSQL, regular VACUUM and ANALYZE operations (often automated) are sufficient. Only manually rebuild indexes if you notice significant bloat or after bulk data modifications.

---

Database query optimization is an ongoing process, not a one-time task. By implementing proper monitoring, understanding your query patterns, and applying these modern TypeScript solutions, you'll build applications that scale efficiently and provide excellent user experiences even under heavy load.