The endpoint that took 2.5 seconds

I spent most of July 19 making UniZ faster by guessing. Every fix was real. None of them was the worst problem. The worst problem showed up in the first run of the last thing I built that day: a benchmark.

UniZ is a campus platform I built as a side project for my college, RGUKT Ongole. Students across all four years, 1,500+ of them, use it to check grades, attendance and registrations. Admins upload all of that data. Everything runs on one 4 vCPU VPS with a K3s cluster behind Cloudflare, so there is no budget for throwing hardware at a slow page.

A day of guesses

There was no single obvious slow thing, so I went through the backend in phases and fixed whatever looked suspicious. Two of those fixes are worth describing.

Case-insensitive lookups. Student IDs and OTP lookups used Prisma's mode: "insensitive". It looks harmless, but on Postgres it compares with ILIKE, and a plain B-tree index cannot serve that, so the database falls back to scanning the table. The IDs were always stored uppercase, so the fix was to uppercase the input and match exactly:

// before: forces a sequential scan
studentId: { equals: targetStudentId, mode: 'insensitive' },

// after: exact match, uses the index
semesterId: (semesterId as string)?.toUpperCase(),

Per-row calls on upload. Uploading grades sent a request to another service for every row. It now sends one per student, at the end.

Then I measured

The last PR of the day (#119) added a benchmark harness that hits every busy endpoint with a webmaster token against production, plus slow-query logging. The first production run pointed straight at one endpoint:

GET /academics/registrations averaged 2486 ms, with a p95 of 3505 ms.

It loaded every matching row from the 20,324-row Registration table with a full include: { subject, semester }. The admin tables only render six fields. The semester join was never read at all.

The fix

#120 did four things:

• Narrow select. Only id, studentId, batch, status, createdAt and subject { code, name }. I checked both admin components that consume it, so the shape they see did not change.
• Exact filters. Branch and batch codes (CSE, ECE, O21 and so on) are stored uppercase, so the same mode: "insensitive" trap was removed here too.
• A 20-second Redis cache keyed by semester, branch and batch. Admins re-fetch this list on every filter change, and 20 seconds of staleness is fine for an overview.
• A composite index for the per-semester count of registered students:

-- CONCURRENTLY cannot run inside Prisma's migration transaction, so this is a
-- plain CREATE INDEX; the table is small enough that the brief lock is a non-issue.
CREATE INDEX IF NOT EXISTS "Registration_status_semesterId_idx"
  ON "Registration" ("status", "semesterId");

Measuring also told me what not to do. Postgres was already tuned sensibly, connection counts were well under the cap, so PgBouncer was not worth adding, and one small table did not need search indexes because a sequential scan over it was already sub-millisecond. An index there would only have slowed writes.

The number I don't have

In #120 I wrote that I would rerun the benchmark after deploying and post the difference. I didn't. The PR title still reads "2.5s→" with nothing after the arrow. I won't make a number up.

Takeaways

• Build the benchmark first. A day of guessing found real issues, and one measured run found the worst one in minutes.
• mode: "insensitive" in Prisma can quietly turn an indexed lookup into a table scan. If your data has a canonical case, normalize the input instead.
• Select what the page renders, not what the model has.
• Measurement is also how you decide what to skip.
• Record the after number in the same PR. A fix without one is a claim, not a result.