Cutting chat query CPU from 80% to 15%

Chat is unforgiving. Users type, wait, and judge the product in those two seconds. On Educollab, our LMS at Tatras Data, the chat query path was doing worse than feeling slow — it was driving server CPU into the 80% range in spikes, which meant everything else on the box paid for it too.

The instinct on a team that's already running Redis is to cache the problem away. We didn't start there.

Measure the actual query, not the symptom

CPU spikes are a lagging indicator. The useful artifact was the slow query log plus EXPLAIN ANALYZE on the chat thread fetch: messages, authors, read state, and a couple of joins that had grown "organically" as features landed.

Two patterns showed up immediately:

• A filter on a timestamp plus a foreign key with no composite index, so Postgres was scanning more of the table than anyone expected once volume grew.
• A chat list query that sorted in the application after pulling a wider set than the UI needed.

Neither is exotic. Both are common once an LMS graduates from a demo dataset.

Index for the query you actually run

We added strategic indexes that matched the WHERE + ORDER BY of the hot paths — not a scattershot of single-column indexes that look good in a schema review and do nothing at runtime.

After that, we tightened the SELECT list and pushed pagination to the database. The 70% query performance lift wasn't a new engine or a rewrite. It was making the existing engine do less work, more often, on a plan that used an index.

CPU spikes dropped from around 80% to 15%. That's the number I put on the resume because it's the one operators felt: headroom came back, and chat stopped being the noisy neighbor.

Cache last

Redis still has a role — unread counts, presence, short-lived thread metadata. We used it after the query was cheap. Caching a bad query just hides the bill until the cache misses, which is when users hit send.

If a chat path is expensive, fix the shape of the query and the indexes that serve it. Then decide what is worth remembering.

That's the unglamorous half of shipping GenAI products too. Models get the demos. Indexes keep the product up.