The Problem: A Slow Student Portal
A few months into running the UGC student portal, reports came in that the grade report page was taking 8–12 seconds to load for some students. Classic N+1 problem combined with a missing index.
Here's how I diagnosed and fixed it.
Step 1: Find Slow Queries
Enable pg_stat_statements in PostgreSQL:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
The culprit: a query joining enrollments, grades, and students with no index on enrollments.student_id.
Step 2: EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT s.name, g.score, c.title
FROM grades g
JOIN enrollments e ON e.id = g.enrollment_id
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
WHERE e.student_id = 12345;
Output showed a Sequential Scan on enrollments (180,000 rows). Adding a composite index fixed it:
CREATE INDEX CONCURRENTLY idx_enrollments_student_course
ON enrollments (student_id, course_id);
Query time dropped from 4.2s → 0.08s.
Step 3: Fix Django N+1 with select_related
# Bad - fires N queries for N grades
grades = Grade.objects.filter(enrollment__student=student)
for g in grades:
print(g.enrollment.course.title) # extra query per grade!
# Good - single JOIN query
grades = Grade.objects.filter(
enrollment__student=student
).select_related("enrollment__course")
Step 4: Connection Pooling
Django opens a new DB connection per request by default. Under load, this kills PostgreSQL. Install PgBouncer:
sudo apt install pgbouncer
Configure pgbouncer.ini in transaction mode and point Django at port 6432 instead of 5432.
After all optimisations: the 8-second page loaded in 0.3 seconds.