indexes saved our admin dashboard

uniz manages 1,500+ student records for campus admins. the dashboard was fine until exam season — everyone logged in at once and the attendance view took eight seconds. admins don't wait eight seconds. they refresh angrily and file bugs that say "slow" with no other detail.

first instinct: add redis. second instinct: run explain analyze. second instinct was right.

the slow query

filtering students by batch, status, and date range — on columns with no indexes. joining tables prisma made easy to write but postgres had to scan. the orm hid the cost until the query plan didn't.

sql

SELECT s.*, b.name
FROM students s
JOIN batches b ON b.id = s.batch_id
WHERE s.batch_id = $1
  AND s.status = $2
  AND s.updated_at >= $3;

the plan looked fine in dev with 200 rows. it did not look fine with 1,500 during peak hours.

what we changed

composite index on (batch_id, status) — filter path stopped scanning the full table
required batch_id — nullable foreign keys had snuck in, creating orphan rows and messier joins
normalized duplicate batch references — one way to refer to a batch, not three

sql

CREATE INDEX idx_students_batch_status
  ON students (batch_id, status);

indexes aren't free — writes get slightly heavier, disk grows. but that one index turned a sequential scan into an index scan. query latency dropped enough that nobody mentioned speed again. that's the metric that matters.

schema cleanup nobody wanted

we also normalized relations that had grown organically — duplicate ways to refer to the same student batch, nullable keys that should've been required. prisma migrations made it less scary. zero-downtime wasn't perfect, but we planned for off-peak.

before / after

p95 dashboard load: ~8s → under 1s
seq scans on students: every request → none on hot path
"slow" tickets: weekly → zero for two months

orms help you ship. they don't replace understanding what the database actually does. run explain. index the columns you filter and sort by constantly. denormalize only when you can prove you need to.

if your admin panel is slow, resist the microservices pitch. start with the query plan. boring wins.

the slow query

sql

SELECT s.*, b.name
FROM students s
JOIN batches b ON b.id = s.batch_id
WHERE s.batch_id = $1
  AND s.status = $2
  AND s.updated_at >= $3;

the plan looked fine in dev with 200 rows. it did not look fine with 1,500 during peak hours.

what we changed

composite index on (batch_id, status) — filter path stopped scanning the full table

required batch_id — nullable foreign keys had snuck in, creating orphan rows and messier joins

normalized duplicate batch references — one way to refer to a batch, not three

sql

CREATE INDEX idx_students_batch_status
  ON students (batch_id, status);

schema cleanup nobody wanted

before / after

p95 dashboard load: ~8s → under 1s

seq scans on students: every request → none on hot path

"slow" tickets: weekly → zero for two months

if your admin panel is slow, resist the microservices pitch. start with the query plan. boring wins.