On-callHardoc-g241

Subject Slow queryLevel Senior–Staff~40 minCommon in Databases & SQL · Algorithms & data structures interviewsIndustries Technology, Software development

Question

Postgres. A frequently-updated `sessions` table (every request bumps `last_seen`) has a btree index on `last_seen`. Lookups on that index have slowly degraded over months from 1 ms to 60 ms, and the index is now larger than the table itself. `pg_stat_user_indexes` shows heavy usage; autovacuum runs. Table row count is stable. CPU/IO fine. The column updated each request is the *indexed* one. Triage why the index keeps bloating and give the durable fix.

What a strong answer looks like

Stop the bleeding first (mitigate), then form hypotheses from real signals. Separate root cause from symptom, communicate status as you go, and close with what prevents a repeat.

Learn the concepts

Diagram & narrate the incident

Loading whiteboard…

Run or narrate your approach, then ask the coach.