A query that ran in 50 ms yesterday now takes 20 seconds. Nothing in the code changed. How do you investigate?

What the interviewer wants to hear

• Check if it is blocked by locks or the server is overloaded
• Compare the current execution plan with the old one
• Look for stale or changed statistics and run ANALYZE
• Check data growth, bloat or a dropped index
• Consider parameter-sensitive plans

Model answer

First I check whether the query itself is slow or just waiting. I look at pg_stat_activity or SHOW PROCESSLIST for lock waits, and at CPU and disk IO in monitoring. If it is not blocked, I run EXPLAIN ANALYZE and compare with the old plan if we store plans, for example with auto_explain. A sudden change usually means the planner picked a different plan. Common reasons are stale statistics after a big data load, so I run ANALYZE; an index that was dropped or became invalid; table bloat; or data that grew past a point where the planner switches to a sequential scan or a different join type. I also check for parameter-sensitive plans, where a generic prepared statement plan is bad for some values. Then I fix the root cause and add monitoring for it.

More medium Database Administrator questions

• Explain the SQL isolation levels and the anomalies each one allows.
• How do you set up and monitor replication, and what do you do when replica lag grows?
• How would you add a column and an index to a 500 million row table without downtime?
• A busy PostgreSQL table keeps getting bloated. How do you tune autovacuum?
• Walk me through how you read an EXPLAIN ANALYZE plan to find why a query is slow.
• How would you tune the main PostgreSQL memory settings on a new server?
• Which metrics and alerts would you set up for a production PostgreSQL or MySQL database?
• What are the main operational differences between running MySQL (InnoDB) and PostgreSQL?
• Developers send you schema changes and new queries to review. What do you look for?