Use EXPLAIN ANALYZE, pg_stat_statements, and k6 to identify slow queries, missing indexes, and N+1 patterns in PostgreSQL-backed NestJS APIs.
Database queries are the most common source of performance problems in web applications. A query that takes 5ms when tested with 100 rows takes 8 seconds with 1 million rows — not because the code changed, but because the data grew. Performance testing without testing database query performance against realistic data volumes produces false confidence.
This lesson covers the complete database performance workflow: enable query logging, identify slow queries with pg_stat_statements, read EXPLAIN ANALYZE plans, add indexes, and verify improvement with k6.
Before you can optimize, you need visibility into query performance. PostgreSQL provides this through slow query logging and pg_stat_statements.
1-- Enable slow query logging in postgresql.conf
2-- log_min_duration_statement = 100 -- log queries taking >100ms
3-- OR: set dynamically
4
5ALTER SYSTEM SET log_min_duration_statement = 100; -- 100ms threshold
6SELECT pg_reload_conf();
7
8-- Enable pg_stat_statements extension
9CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
10
11-- Add to postgresql.conf:
12-- shared_preload_libraries = 'pg_stat_statements'
13-- pg_stat_statements.track = all1-- Find top 10 slowest queries (run after load test)
2SELECT
3 LEFT(query, 100) AS query_preview,
4 calls,
5 ROUND(total_exec_time::numeric / calls, 2) AS avg_ms,
6 ROUND(total_exec_time::numeric, 2) AS total_ms,
7 ROUND(stddev_exec_time::numeric, 2) AS stddev_ms,
8 ROUND(rows::numeric / calls, 1) AS avg_rows,
9 shared_blks_hit + shared_blks_read AS total_blocks
10FROM pg_stat_statements
11WHERE calls > 10
12ORDER BY total_exec_time DESC
13LIMIT 10;This query shows which SQL statements consume the most total database time — avg_ms * calls = total_ms identifies queries that are either slow per call or called very frequently. Both matter for overall performance.
EXPLAIN ANALYZE executes the query and shows the actual execution plan with timing.
1-- Always use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) for full detail
2EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
3SELECT
4 c.id, c.title, c.price, c.rating,
5 COUNT(l.id) AS lesson_count,
6 u.name AS instructor
7FROM courses c
8JOIN lessons l ON l.course_id = c.id
9JOIN users u ON u.id = c.instructor_id
10WHERE c.category = 'typescript'
11GROUP BY c.id, u.name
12ORDER BY c.rating DESC
13LIMIT 20;Output to understand:
1Limit (cost=2345.67..2346.17 rows=20 width=156) (actual time=2341.234..2341.456 rows=20 loops=1)
2 -> Sort (cost=2345.67..2358.45 rows=5113 width=156) (actual time=2341.232..2341.350 rows=20)
3 Sort Key: c.rating DESC
4 Sort Method: top-N heapsort Memory: 29kB
5 -> HashAggregate (cost=1987.12..2038.24 rows=5113 width=156) (actual time=2289.123..2312.456)
6 -> Hash Join (cost=45.12..1756.34 rows=46156) (actual time=1.234..2145.678)
7 -> Seq Scan on courses c (actual time=0.012..1234.567 rows=48291 loops=1)
8 Filter: ((category)::text = 'typescript')
9 Rows Removed by Filter: 52134What to look for:
Seq Scan on courses = no index on category — reading 48k rows to return 20actual time=1234.567 = 1.2 seconds just for the table scanRows Removed by Filter: 52134 = 52k rows discarded — index would eliminate thisFix: CREATE INDEX courses_category_rating_idx ON courses (category, rating DESC);
After index: actual time drops from 1234ms to 0.8ms.
N+1 queries occur when you fetch N records and then issue one additional query per record. Under load, 100 courses = 101 queries = catastrophic.
1// BAD: N+1 in NestJS — 1 query for courses + N queries for instructors
2@Get()
3async getCourses() {
4 const courses = await this.prisma.course.findMany({ take: 20 });
5 // For each course, a separate query is issued to fetch the instructor
6 return Promise.all(
7 courses.map(async (c) => ({
8 ...c,
9 instructor: await this.prisma.user.findUnique({ where: { id: c.instructorId } }),
10 }))
11 );
12 // = 1 + 20 = 21 queries per request
13}
14
15// GOOD: Single JOIN query with Prisma include
16@Get()
17async getCourses() {
18 return this.prisma.course.findMany({
19 take: 20,
20 include: {
21 instructor: { select: { id: true, name: true, avatarUrl: true } },
22 _count: { select: { lessons: true } },
23 },
24 orderBy: { rating: 'desc' },
25 });
26 // = 1 query with LEFT JOIN
27}
28
29// DETECT N+1 in development: enable Prisma query logging
30// In prisma.service.ts:
31const prisma = new PrismaClient({
32 log: [
33 { emit: 'event', level: 'query' },
34 ],
35});
36prisma.$on('query', (e) => {
37 if (e.duration > 50) {
38 console.warn(`Slow query (${e.duration}ms): ${e.query}`);
39 }
40});
41// Under k6 load: logs every query — identify bursts of identical queriesNot all indexes are equal. Index type must match the query pattern.
1-- B-tree: equality and range queries (default)
2CREATE INDEX courses_category_idx ON courses (category);
3CREATE INDEX courses_price_idx ON courses (price); -- for BETWEEN queries
4
5-- Composite: combines multiple filter/sort columns
6-- Rule: filter columns first (equality), then sort column
7CREATE INDEX courses_category_rating_idx ON courses (category, rating DESC);
8-- Covers: WHERE category = 'x' ORDER BY rating DESC
9
10-- Partial: only indexes rows matching a condition
11-- Much smaller index — only published courses need fast lookup
12CREATE INDEX courses_published_idx ON courses (category, created_at DESC)
13 WHERE status = 'published';
14
15-- GIN: full-text search
16CREATE INDEX courses_fts_idx ON courses
17 USING gin(to_tsvector('english', title || ' ' || description));
18
19-- Index on foreign key (always do this — Prisma does NOT do it automatically)
20CREATE INDEX lessons_course_id_idx ON lessons (course_id);
21CREATE INDEX enrollments_user_id_idx ON enrollments (user_id);
22CREATE INDEX enrollments_course_id_idx ON enrollments (course_id);Index management in Prisma:
1model Course {
2 id String @id @default(cuid())
3 title String
4 category String
5 rating Float
6 status String
7
8 @@index([category, rating(sort: Desc)]) // composite index
9 @@index([status, category]) // partial via filtered not supported in Prisma — use migration
10}Check index usage:
1SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
2FROM pg_stat_user_indexes
3ORDER BY idx_scan DESC; -- indexes with idx_scan=0 are never used — drop themCreate a dedicated k6 script that measures database-heavy endpoints before and after optimization.
1// tests/perf/db-performance.js — measure database query performance
2import http from 'k6/http';
3import { check, sleep } from 'k6';
4import { Trend } from 'k6/metrics';
5
6const searchDuration = new Trend('search_query_ms', true);
7const courseDuration = new Trend('course_list_ms', true);
8
9export const options = {
10 vus: 50,
11 duration: '5m',
12 thresholds: {
13 search_query_ms: ['p(95)<300', 'p(99)<800'],
14 course_list_ms: ['p(95)<200'],
15 http_req_failed: ['rate<0.001'],
16 },
17};
18
19export default function () {
20 // Test 1: full-text search (GIN index)
21 const searchStart = Date.now();
22 const searchRes = http.get('http://localhost:3004/search?q=typescript+testing');
23 searchDuration.add(Date.now() - searchStart);
24 check(searchRes, { 'search 200': (r) => r.status === 200 });
25 sleep(0.5);
26
27 // Test 2: course listing with join (composite index)
28 const listStart = Date.now();
29 const listRes = http.get('http://localhost:3004/courses?category=backend&sort=rating');
30 courseDuration.add(Date.now() - listStart);
31 check(listRes, { 'list 200': (r) => r.status === 200 });
32 sleep(0.5);
33}
34// Run BEFORE and AFTER adding indexes:
35// BEFORE: search_query_ms p95=2340ms
36// AFTER: search_query_ms p95=45ms (52x improvement)End-to-end database performance workflow: run k6 to identify a slow endpoint, use EXPLAIN ANALYZE to find the bottleneck, add the correct index, and verify with k6.
1-- Step 1: Find slow queries after k6 load test
2-- Run in psql against your PostgreSQL database
3
4SELECT
5 LEFT(query, 120) AS query_snippet,
6 calls,
7 ROUND(mean_exec_time::numeric, 1) AS avg_ms,
8 ROUND(total_exec_time::numeric / 1000, 1) AS total_sec,
9 ROUND(rows::numeric / calls, 0) AS avg_rows
10FROM pg_stat_statements
11WHERE calls > 100
12 AND mean_exec_time > 100 -- slower than 100ms on average
13ORDER BY total_exec_time DESC
14LIMIT 5;
15
16-- Step 2: Get full query from results and EXPLAIN it
17-- Suppose the slow query is the course listing:
18EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
19SELECT
20 c.id, c.title, c.slug, c.price, c.rating, c.lesson_count,
21 c.thumbnail_url, c.category, c.difficulty,
22 u.name AS instructor_name, u.avatar_url AS instructor_avatar,
23 COUNT(e.id) AS enrollment_count
24FROM courses c
25INNER JOIN users u ON u.id = c.instructor_id
26LEFT JOIN enrollments e ON e.course_id = c.id
27WHERE c.category = 'backend'
28 AND c.status = 'published'
29GROUP BY c.id, u.name, u.avatar_url
30ORDER BY c.rating DESC
31LIMIT 20 OFFSET 0;
32
33-- Likely output: Seq Scan on courses (cost=...) (actual time=1450..1452 rows=20 loops=1)
34-- Filter: ((category = 'backend') AND (status = 'published'))
35-- Rows Removed by Filter: 89432
36
37-- Step 3: Add the right indexes based on EXPLAIN output
38-- Composite index covering the WHERE clause + sort
39CREATE INDEX CONCURRENTLY courses_pub_cat_rating_idx
40 ON courses (status, category, rating DESC)
41 WHERE status = 'published';
42-- CONCURRENTLY = no lock, safe to run in production
43
44-- Index on foreign keys (prevent sequential scans during JOIN)
45CREATE INDEX CONCURRENTLY enrollments_course_id_idx ON enrollments (course_id);
46CREATE INDEX CONCURRENTLY enrollments_user_id_idx ON enrollments (user_id);
47
48-- Step 4: Re-run EXPLAIN to confirm index usage
49EXPLAIN (ANALYZE, BUFFERS)
50SELECT c.id, c.title, c.rating, u.name AS instructor_name
51FROM courses c
52JOIN users u ON u.id = c.instructor_id
53WHERE c.category = 'backend' AND c.status = 'published'
54ORDER BY c.rating DESC
55LIMIT 20;
56-- Expected: Index Scan using courses_pub_cat_rating_idx (actual time=0.042..0.312 rows=20)
57-- 4600x improvement in execution time
58
59-- Step 5: Verify with Prisma migration
60-- prisma/migrations/20260601_add_course_performance_indexes.sql
61CREATE INDEX IF NOT EXISTS courses_pub_cat_rating_idx
62 ON courses (status, category, rating DESC)
63 WHERE status = 'published';
64
65CREATE INDEX IF NOT EXISTS lessons_course_id_idx ON lessons (course_id);
66CREATE INDEX IF NOT EXISTS enrollments_course_id_idx ON enrollments (course_id);
67CREATE INDEX IF NOT EXISTS enrollments_user_id_idx ON enrollments (user_id);
68
69-- Monitor index usage after deployment:
70SELECT
71 indexrelname AS index_name,
72 idx_scan AS times_used,
73 idx_tup_read AS rows_read,
74 pg_size_pretty(pg_relation_size(indexrelid)) AS size
75FROM pg_stat_user_indexes
76JOIN pg_indexes ON indexname = indexrelname
77WHERE schemaname = 'public'
78 AND tablename IN ('courses', 'lessons', 'enrollments')
79ORDER BY idx_scan DESC;This workflow shows the complete database performance cycle. pg_stat_statements identifies the courses listing query as the highest total-time consumer (1450ms avg × 5000 calls/min = 121 seconds of DB time per minute). EXPLAIN ANALYZE confirms the cause: a sequential scan reading 89k rows to return 20, because there is no index covering (status, category, rating). The composite partial index on (status, category, rating DESC) WHERE status = 'published' eliminates the scan entirely, dropping query time from 1450ms to 0.3ms. The foreign key indexes on enrollments prevent sequential scans during the COUNT JOIN. Run the k6 script before and after to measure the actual RPS and p95 improvement.
The next lesson covers frontend bundle analysis with webpack-bundle-analyzer — identifying and eliminating large dependencies, implementing code splitting, and measuring the impact on page load times and Lighthouse scores.
Your progress is saved in this browser, on this device only.