How do I identify slow queries in PostgreSQL?

Enable pg_stat_statements or configure log_min_duration_statement = 200 in postgresql.conf.

What does EXPLAIN ANALYZE show?

It executes the query and returns actual execution times, node costs, index usage, and scan types (Seq Scan vs Index Scan).

Why is my Node.js ORM causing high database CPU?

ORMs often generate N+1 query loops or select all columns without indexes, overwhelming connection pools and CPU.

PostgreSQL Slow Queries & High CPU — Performance Guide

Database bottlenecks are responsible for over 80% of application latency spikes. This guide outlines how to trace slow PostgreSQL queries and optimize query execution in production.

⚡ 3-Step Diagnostic Workflow

1. Identify Slow Queries with `pg_stat_statements`

Run the following query to surface top time-consuming database operations:

SELECT 
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

2. Analyze Query Plans with `EXPLAIN ANALYZE`

Prefix any slow query with EXPLAIN (ANALYZE, BUFFERS) to inspect how PostgreSQL executes it:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';

Key Metrics to Watch:

Seq Scan: Indicates a missing index on queried fields (e.g., user_id or status).
External sort Disk: Indicates work_mem is insufficient for in-memory sorting.

3. Add Composite Indexes for Filtered & Sorted Fields

-- Create composite index covering filter criteria
CREATE INDEX CONCURRENTLY idx_orders_user_status ON orders (user_id, status);

Tip

Always execute CREATE INDEX CONCURRENTLY in production environments to avoid blocking table writes during index creation.

🚫 Preventing N+1 Query Patterns in ORMs

ORMs like Prisma, Drizzle, and TypeORM can easily generate silent N+1 query loops if relationships are loaded sequentially.

❌ Problematic N+1 Loop

// ❌ FAILS: Executes 1 initial query + N sub-queries (100 users = 101 queries)
const users = await db.user.findMany();
for (const user of users) {
  const posts = await db.post.findMany({ where: { userId: user.id } });
}

✅ Optimized Single Query

// ✅ FIXED: Executes 1 batch query with SQL JOIN
const usersWithPosts = await db.user.findMany({
  include: { posts: true },
});

📊 Summary Checklist

Performance Area	Symptom	Action Item
Missing Index	High CPU & `Seq Scan` in `EXPLAIN`	Add targeted single or composite B-Tree indexes
N+1 ORM Loops	High query count per request	Batch relationships with `.findMany({ include })`
Memory Spills	`External sort Disk`	Tune `work_mem` in `postgresql.conf`

Facing persistent database slowdowns or locking issues? Get a performance audit or diagnose your issue.