Database query optimization

// pg-config.ts
import { Pool, PoolConfig } from 'pg';

export const poolConfig: PoolConfig = {
  host: process.env.DB_HOST,
  port: Number(process.env.DB_PORT) || 5432,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max: 20, // Connection pool size
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
};

// Enable query logging in development
if (process.env.NODE_ENV === 'development') {
  poolConfig.logQuery = true;
}

-- postgresql.conf
log_min_duration_statement = 100; -- Log queries > 100ms
log_statement = 'none';
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ';
auto_explain.log_min_duration = 100;
auto_explain.log_analyze = true;
auto_explain.log_buffers = true;

// migration-001-create-indexes.sql
-- Leftmost prefix rule: composite indexes must match query WHERE order
CREATE INDEX idx_users_email_status ON users(email, status) WHERE status = 'active';
-- Covering index for frequent read paths
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at) INCLUDE (total, currency);
-- Partial index for high-cardinality filtered data
CREATE INDEX idx_audit_logs_error ON audit_logs(created_at) WHERE level = 'ERROR';

// user-repository.ts
import { pool } from './pg-config';

export async function getActiveUsersByEmail(emailPrefix: string) {
  // Query matches leftmost prefix of idx_users_email_status
  const query = `
    SELECT id, email, status, created_at
    FROM users
    WHERE email >= $1 AND email < $2 AND status = 'active'
    ORDER BY email ASC
    LIMIT 50;
  `;
  // Range scan instead of LIKE '%...' to preserve index usag

e const start = emailPrefix; const end = emailPrefix.replace(/.$/, c => String.fromCharCode(c.charCodeAt(0) + 1));

return pool.query(query, [start, end]); }

### Step 3: Query Rewriting Patterns
Replace anti-patterns with execution-plan-friendly constructs.

```ts
// anti-pattern: SELECT * + application-side filtering
// optimized: column projection + database-side filtering
export async function getRecentOrders(userId: string, days: number) {
  const query = `
    SELECT o.id, o.total, o.currency, o.created_at
    FROM orders o
    WHERE o.user_id = $1 
      AND o.created_at >= NOW() - INTERVAL '${days} days'
    ORDER BY o.created_at DESC
    LIMIT 20;
  `;
  return pool.query(query, [userId]);
}

// anti-pattern: IN clause with subquery causing nested loop
// optimized: EXISTS with correlated subquery for early termination
export async function getActiveUsersWithRecentOrders() {
  const query = `
    SELECT u.id, u.email
    FROM users u
    WHERE u.status = 'active'
      AND EXISTS (
        SELECT 1 FROM orders o
        WHERE o.user_id = u.id 
          AND o.created_at >= NOW() - INTERVAL '30 days'
      );
  `;
  return pool.query(query);
}

Step 4: Architecture Decisions & Rationale

• Raw SQL for Critical Paths: ORMs introduce query generation overhead and obscure execution plans. Use parameterized raw queries for high-traffic endpoints to guarantee predictable plan caching.
• Connection Pooling: Database connections are expensive. A pool of 15–25 connections typically saturates a single PostgreSQL instance on modern hardware. Exceeding this causes context switching overhead and lock contention.
• Read Replicas & Cache-Aside: Offload analytical and read-heavy queries to replicas. Implement a cache-aside pattern for idempotent lookups with TTL-based invalidation, not write-through caching which increases write latency.
• Materialized Views for Aggregations: Complex joins and window functions should be precomputed. Refresh schedules must align with business SLAs, not real-time expectations.

// cache-aside implementation with Redis
import { createClient } from 'redis';
import { pool } from './pg-config';

const redis = createClient({ url: process.env.REDIS_URL });

export async function getOrderWithCache(orderId: string) {
  const cacheKey = `order:${orderId}`;
  const cached = await redis.get(cacheKey);
  if (cached) return JSON.parse(cached);

  const result = await pool.query(
    `SELECT id, user_id, total, currency, created_at FROM orders WHERE id = $1`,
    [orderId]
  );
  
  if (result.rows[0]) {
    await redis.set(cacheKey, JSON.stringify(result.rows[0]), { EX: 300 });
  }
  return result.rows[0] || null;
}

Pitfall Guide

1. Over-Indexing

Adding indexes to every filtered column increases write amplification, slows INSERT/UPDATE operations, and bloats storage. Each index requires maintenance during DML operations. Best practice: index only columns appearing in WHERE, JOIN, or ORDER BY clauses for high-frequency queries. Validate with EXPLAIN ANALYZE before deployment.

2. Ignoring Execution Plans

Guessing index placement or query structure without reviewing the planner's output guarantees suboptimal performance. The cost-based optimizer provides exact row estimates, join strategies, and I/O costs. Best practice: run EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) on critical queries. Treat the plan as a contract, not a suggestion.

3. N+1 Query Pattern

ORM convenience methods that fetch related entities in loops generate one query per iteration. This pattern destroys connection pool capacity and multiplies latency linearly. Best practice: use JOIN, IN clauses with batched IDs, or ORM eager-loading features with explicit select constraints. Never iterate database calls in application code.

4. Composite Index Misordering

Composite indexes follow the leftmost prefix rule. A query filtering on the second or third column without the first will bypass the index entirely. Best practice: order composite indexes by cardinality and query frequency. Place highest-selectivity columns first. Test with pg_stat_user_indexes to verify usage.

5. Caching Without Invalidation Strategy

Caching unbounded or stale data creates consistency violations and cache stampedes during TTL expiration. Best practice: implement versioned cache keys, event-driven invalidation on writes, and jittered TTLs to prevent thundering herds. Never cache mutable data without a defined invalidation path.

6. Assuming Database Magic

Modern query planners are sophisticated but not omniscient. They rely on accurate statistics. Stale pg_statistic data or skewed distributions cause plan regressions. Best practice: run ANALYZE after bulk operations, monitor plan stability with pg_stat_statements, and use query hints or CTE materialization when the planner consistently chooses suboptimal paths.

7. Neglecting Connection Limits

Unbounded connection creation under load causes lock contention, memory exhaustion, and TCP backlog drops. Best practice: cap pool size at 2x CPU cores, implement queueing with timeout thresholds, and use connection draining during deployments. Monitor pg_stat_activity for idle-in-transaction states.

Production Bundle

Action Checklist

• Enable slow query logging and auto_explain to capture execution plans for queries exceeding 100ms
• Run EXPLAIN (ANALYZE, BUFFERS) on top 10 latency-critical queries and document baseline metrics
• Audit existing indexes against pg_stat_user_indexes to identify unused or redundant structures
• Replace SELECT * with explicit column projection on all high-traffic endpoints
• Implement leftmost-prefix composite indexes matching actual WHERE/JOIN/ORDER BY patterns
• Configure connection pool max at 15–25 with idle timeout and queueing strategy
• Deploy cache-aside pattern with event-driven invalidation for idempotent read paths
• Schedule weekly ANALYZE runs and monitor pg_stat_statements for plan regression

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
High-frequency point lookups by primary key	B-tree index + connection pooling	O(log n) lookup, eliminates full scans	Reduces compute spend by 60–80%
Complex aggregations across joined tables	Materialized view with scheduled refresh	Precomputes expensive joins, decouples read/write load	Lowers IOPS provisioning, stabilizes latency
Real-time search with partial matches	Full-text search index (tsvector)	Avoids LIKE '%...' table scans, leverages inverted index	Replaces expensive application-side filtering
Burst traffic on read-heavy endpoints	Read replica + cache-aside with jittered TTL	Distributes load, prevents cache stampedes	Scales horizontally without vertical upgrades
Bulk data ingestion	COPY command + deferred index creation	Minimizes WAL generation, batches index maintenance	Reduces write amplification by 40–70%

Configuration Template

// db-optimization-config.ts
import { Pool, types } from 'pg';
import { createClient } from 'redis';

// Parse numeric types correctly
types.setTypeParser(20, (val) => parseInt(val, 10));

export const optimizedPool = new Pool({
  host: process.env.DB_HOST,
  port: Number(process.env.DB_PORT) || 5432,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
  statement_timeout: 5000,
  query_timeout: 5000,
});

optimizedPool.on('error', (err) => {
  console.error('Unexpected pool error:', err);
  process.exit(1);
});

export const redisClient = createClient({
  url: process.env.REDIS_URL,
  socket: { reconnectStrategy: (retries) => Math.min(retries * 50, 2000) },
});

redisClient.on('error', (err) => console.error('Redis client error:', err));

// Query monitoring wrapper
export async function queryWithMetrics(sql: string, params?: any[]) {
  const start = performance.now();
  try {
    const result = await optimizedPool.query(sql, params);
    const duration = performance.now() - start;
    if (duration > 100) {
      console.warn(`[SLOW QUERY] ${duration.toFixed(2)}ms | ${sql.substring(0, 100)}`);
    }
    return result;
  } catch (err) {
    console.error(`[QUERY ERROR] ${err.message} | ${sql.substring(0, 100)}`);
    throw err;
  }
}

Quick Start Guide

1. Install dependencies: npm install pg redis @types/pg @types/redis
2. Configure environment variables: DB_HOST, DB_PORT, DB_NAME, DB_USER, DB_PASSWORD, REDIS_URL
3. Enable PostgreSQL logging: Add log_min_duration_statement = 100 and auto_explain.log_min_duration = 100 to postgresql.conf, then restart the service
4. Deploy the configuration template and replace direct pool.query() calls with queryWithMetrics() to establish baseline telemetry
5. Run EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) on your top 3 latency-critical queries, apply leftmost-prefix indexes, and verify plan changes using pg_stat_statements