Query optimization techniques

const enriched = await Promise.all( orders.rows.map(async (order) => { const items = await pool.query( SELECT * FROM order_items WHERE order_id = $1, [order.id] ); return { ...order, items: items.rows }; }) ); return enriched; }

**After (Single query with JOIN + covering columns + explicit typing):**
```typescript
import { Pool, QueryResult } from 'pg';

const pool = new Pool({ 
  connectionString: process.env.DATABASE_URL,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

interface OrderRow {
  id: string;
  user_id: string;
  total: number;
  item_id: string;
  sku: string;
  quantity: number;
  price: number;
}

async function getUserOrdersOptimized(userId: string) {
  const res: QueryResult<OrderRow> = await pool.query(
    `SELECT 
       o.id, o.user_id, o.total,
       oi.id AS item_id, oi.sku, oi.quantity, oi.price
     FROM orders o
     LEFT JOIN order_items oi ON oi.order_id = o.id
     WHERE o.user_id = $1
     ORDER BY o.created_at DESC`,
    [userId]
  );

  // Group in application layer (cheaper than repeated round-trips)
  const grouped = res.rows.reduce((acc, row) => {
    if (!acc[row.id]) {
      acc[row.id] = {
        id: row.id,
        user_id: row.user_id,
        total: row.total,
        items: []
      };
    }
    if (row.item_id) {
      acc[row.id].items.push({
        id: row.item_id,
        sku: row.sku,
        quantity: row.quantity,
        price: row.price
      });
    }
    return acc;
  }, {} as Record<string, any>);

  return Object.values(grouped);
}

Step 5: Architecture Decisions & Rationale

Rationale: Query optimization alone hits a ceiling when data volume exceeds memory capacity. Partitioning and materialized views shift execution cost from runtime to maintenance windows. Read replicas isolate analytical I/O from transactional throughput. Connection pooling eliminates TCP handshake overhead and prevents connection exhaustion during traffic bursts.

Pitfall Guide

1. Indexing low-cardinality columns: Adding indexes to columns with few distinct values (e.g., status, is_active) bloats storage and slows writes without improving read performance. The planner often ignores them anyway.
2. Composite index misordering: Placing range or sort columns before equality filters breaks index usage. The planner can only use leading columns for index scans.
3. Ignoring planner statistics decay: VACUUM reclaims space; ANALYZE updates planner statistics. Running VACUUM without ANALYZE leaves the planner guessing, causing suboptimal join strategies.
4. Blind work_mem tuning: Increasing work_mem allows larger in-memory sorts and hash tables, but unbounded increases trigger OOM kills when multiple complex queries run concurrently. Set conservatively and monitor temp_files in logs.
5. Caching without invalidation: Redis or application-level caching accelerates reads but introduces consistency violations when underlying data changes. Cache-aside patterns without write-through invalidation or TTL alignment cause stale reads.
6. ORM lazy loading in batch contexts: ORMs optimize for single-entity retrieval. Bulk operations require explicit JOIN, IN, or batch fetch strategies. Lazy loading in loops creates exponential query growth.
7. Not testing with production-like data: Query plans change at scale. A query using an index scan on 10K rows may switch to a sequential scan on 10M rows because the planner calculates full scan as cheaper than random I/O.

Production Best Practices:

• Run pg_stat_statements continuously; audit top 20 queries weekly.
• Use EXPLAIN (ANALYZE, BUFFERS) in CI/CD pipelines for schema migrations.
• Schedule pg_cron or external jobs for VACUUM ANALYZE on high-churn tables.
• Monitor blks_read vs blks_hit in pg_statio_user_tables to track cache efficiency.
• Enforce explicit column selection in linting rules (SELECT * bans).

Production Bundle

Action Checklist

• Enable pg_stat_statements and slow query logging (log_min_duration_statement = 200)
• Audit existing indexes using pg_stat_user_indexes and drop unused ones (idx_scan = 0)
• Rewrite top 5 slow queries using EXPLAIN (ANALYZE, BUFFERS) and align with planner cost model
• Configure connection pooling (PgBouncer or native pool) with max limits matching CPU cores × 2
• Implement materialized views for dashboard/analytical queries with scheduled refresh
• Schedule automated VACUUM ANALYZE for high-churn tables via pg_cron or cron
• Add query linting to CI pipeline to block SELECT * and unindexed WHERE clauses
• Establish baseline latency and I/O metrics before and after each optimization cycle

Decision Matrix

Configuration Template

postgresql.conf (optimization baseline)

shared_buffers = 25% of RAM
effective_cache_size = 75% of RAM
work_mem = 64MB
maintenance_work_mem = 512MB
random_page_cost = 1.1
effective_io_concurrency = 200
wal_level = replica
max_wal_senders = 3
log_min_duration_statement = 200
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0

PgBouncer.ini

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 200
default_pool_size = 25
reserve_pool_size = 5
server_idle_timeout = 30
server_lifetime = 3600

TypeScript Connection Pool Config

import { Pool } from 'pg';

export const db = new Pool({
  host: process.env.DB_HOST || '127.0.0.1',
  port: Number(process.env.DB_PORT) || 6432,
  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,
});

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

Quick Start Guide

1. Install monitoring extensions: Run CREATE EXTENSION IF NOT EXISTS pg_stat_statements; and set log_min_duration_statement = 200 in postgresql.conf. Restart PostgreSQL.
2. Identify bottlenecks: Query pg_stat_statements to extract the top 3 queries by total_exec_time. Copy one for analysis.
3. Generate execution plan: Run EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <query>;. Note Seq Scan, Sort, and Rows Removed by Filter lines.
4. Apply targeted index: Create a composite index matching equality → range → sort order. Verify usage with EXPLAIN.
5. Validate improvement: Re-run the query. Confirm Seq Scan → Index Scan, reduced actual rows vs estimated rows, and lower Execution Time. Commit schema change and update application query if necessary.

Query optimization is deterministic when treated as a contract between application logic and storage execution. Measure first, rewrite deliberately, and validate against production data distribution. The cost of inaction compounds; the ROI of systematic optimization scales linearly with data growth.