PostgreSQL Performance Tuning — The Queries That Are Killing Your Database

-- Enable the extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- Find the top 10 slowest queries by total time
SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round(max_exec_time::numeric, 2) AS max_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  left(query, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Always use ANALYZE to get actual execution times
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) 
SELECT o.id, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01'
  AND o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 20;
 
-- What to look for:
-- Seq Scan on orders  → Missing index (scanning entire table)
-- Nested Loop         → Could be fine or terrible (depends on row count)
-- Sort                → If not using an index, could be expensive
-- Hash Join           → Usually efficient for large joins
-- Rows Removed by Filter: 49998 → Scanned 50K rows to return 2 (bad selectivity)

-- The #1 performance fix: add indexes on columns you filter and join on
CREATE INDEX CONCURRENTLY idx_orders_created_status 
  ON orders (created_at DESC, status)
  WHERE status = 'completed';  -- Partial index: only index what you query
 
-- For text search patterns
CREATE INDEX CONCURRENTLY idx_customers_email 
  ON customers (lower(email));  -- Functional index for case-insensitive search
 
-- For JSON queries
CREATE INDEX CONCURRENTLY idx_orders_metadata_source
  ON orders ((metadata->>'source'));

// ✗ N+1: 1 query for orders + N queries for customers
const orders = await db.query("SELECT * FROM orders LIMIT 20");
for (const order of orders) {
  order.customer = await db.query(
    "SELECT * FROM customers WHERE id = $1", [order.customer_id]
  );
  // This executes 20 separate queries
}
 
// ✓ Single query with JOIN
const orders = await db.query(`
  SELECT o.*, c.email, c.name
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  LIMIT 20
`);
 
// ✓ Or batch load with IN clause
const orders = await db.query("SELECT * FROM orders LIMIT 20");
const customerIds = orders.map(o => o.customer_id);
const customers = await db.query(
  "SELECT * FROM customers WHERE id = ANY($1)", [customerIds]
);

// PostgreSQL default: max_connections = 100
// Each connection uses ~10MB RAM
// 100 connections = 1GB just for connections
 
// Use a connection pooler (PgBouncer or built-in pool)
const pool = new Pool({
  max: 20,              // Max connections in the pool
  idleTimeoutMillis: 30000,  // Close idle connections after 30s
  connectionTimeoutMillis: 5000, // Fail fast if pool is exhausted
});
 
// Monitor pool health
setInterval(() => {
  console.log({
    total: pool.totalCount,
    idle: pool.idleCount,
    waiting: pool.waitingCount, // If > 0, pool is too small
  });
}, 10000);

-- For a 16GB RAM, 4-core production server:
ALTER SYSTEM SET shared_buffers = '4GB';        -- 25% of RAM (default: 128MB)
ALTER SYSTEM SET effective_cache_size = '12GB';  -- 75% of RAM
ALTER SYSTEM SET work_mem = '64MB';              -- Per-operation sort memory
ALTER SYSTEM SET maintenance_work_mem = '512MB'; -- For VACUUM, CREATE INDEX
ALTER SYSTEM SET random_page_cost = 1.1;         -- SSD storage (default: 4.0 for HDD)
ALTER SYSTEM SET effective_io_concurrency = 200;  -- SSD parallel reads
 
-- Reload configuration
SELECT pg_reload_conf();

// Add query performance testing to CI
test("order listing query stays under 50ms", async () => {
  // Seed with realistic data volume
  await seedOrders(100000);
  
  const start = Date.now();
  await db.query(`
    SELECT o.id, o.total, c.email
    FROM orders o JOIN customers c ON c.id = o.customer_id
    WHERE o.created_at > NOW() - INTERVAL '30 days'
    ORDER BY o.created_at DESC LIMIT 20
  `);
  const duration = Date.now() - start;
  
  expect(duration).toBeLessThan(50);
});