We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design Masterclass · Reliability Resilience
Free preview: the first 2 parts of this 5-part lesson.
Already bought the course? Sign in to read the whole lesson.
this lesson is part of
System Design Masterclass
Unlock this lesson and the 751 other paid lessons
$25 once, by PayPal
See the System Design courseread the rest of this lesson in
System Design Masterclass
Unlock this lesson and the 751 other paid lessons
$25 once, by PayPal
See the System Design coursein Reliability Resilience
This lesson is part of the full System Design Masterclass. Unlock it along with 769 other lessons for a one-time payment.
One-time payment by card or PayPal
Get Lifetime AccessOne-time payment: UPI, card or netbanking
Get Lifetime Access18 Foundation lessons are free, no payment needed. Try them now
It is easy and good
“It is really nice and good Please join the course”
A senior DBA looks at a slow query dashboard and ignores the 5-second query sitting at the top. Instead, they fix a 50 ms query buried on page three. The next morning, database CPU drops 30 percent.
Junior engineers stare at the dashboard sorted by per-call . Senior engineers sort by total time. The difference is career-defining.
Total time equals call count times per-call latency. A 5-second query run twice a day costs 10 seconds of database time. A 50 ms query run two million times a day costs 100,000 seconds. Ten thousand times more impact. Baron Schwartz's High Performance MySQL hammered this point home twenty years ago and it remains the most underused insight in database operations.
Database profiling is the discipline of finding these queries, understanding their execution plans, and fixing them before they cause outages. Every large-scale database incident report you read, from GitLab's 2017 read outage to Shopify's index-related latency spikes, traces back to queries that someone could have caught with a 30-minute profiling session.
A senior DBA looks at a slow query dashboard and ignores the 5 second monster at the top. Instead they fix a 50 ms query buried on page three. The next morning database CPU drops 30 percent. Why?
Total time equals call count times per call . A 5 second query run twice a day costs 10 seconds of database time. A 50 ms query run two million times costs 100,000 seconds, ten thousand times more impact. Baron Schwartz's High Performance MySQL hammered this point home twenty years ago and it remains the most underused insight in database operations. Pt-query-digest from Percona Toolkit ranks queries by total time precisely so engineers stop being misled by per-call latency.
The workflow is rigid: capture, rank, explain, fix, verify. The slow query log feeds pt-query-digest, the top offender by total time gets EXPLAIN ANALYZE, and the plan reveals what the database actually does instead of what the developer hoped it would do. Watch the third step where a sequential scan of eight million rows turns out to be the cause of 38 percent of database CPU.
The most common finding by far is a missing foreign key index. Rails and Django migrations create the column but not the index unless the developer remembers to add it. The query works fine on a development dataset of a thousand rows and falls over at five million. GitLab posted a 2017 retrospective where exactly this caused a two hour read outage as a previously fast query started doing full table scans after a routine data growth.
EXPLAIN ANALYZE is the ground truth. A plan shows whether the database is using an index, how many rows it expects, how many it actually got, and where time is spent. Experienced DBAs always demand a plan before proposing any index change because the planner sometimes refuses to use an obvious index due to statistics being stale or selectivity being too low.
The fix in Postgres is CREATE INDEX CONCURRENTLY, which avoids the table lock that the regular form takes. On a busy production table the difference between concurrent and non concurrent index creation is the difference between zero downtime and a 30 second outage. After creation, run ANALYZE to refresh statistics so the planner actually picks up the new index.
Verification is non negotiable. Re-run EXPLAIN ANALYZE on the same query and confirm the plan changed to an index scan. The fix is not done until the dashboard shows the regression resolved, typically within minutes for a high frequency query.