I/O Bottlenecks

Hook: Interviewers love this question because it separates 'I added an index' from 'I understand why the query was slow'.

Question: What are I/O bottlenecks in SQL, and how do indexes help reduce them?

Answer: An I/O bottleneck happens when a query spends most of its time waiting for data to be read from or written to storage, instead of using CPU. In SQL, that usually means too many table pages are being scanned, too many random row lookups are happening, or the engine is spilling work to disk for sorts and joins. Indexes help by letting the database jump to a small set of pages instead of reading the whole table.

Interview-Ready Answer: I/O bottlenecks are slowdowns caused by excessive disk or page reads and writes. In SQL, that often means a full table scan, lots of random lookups, or temp-file spills during sorting and joining. I reduce I/O by making predicates searchable with good indexes, especially selective and covering indexes, so the engine reads far fewer pages. A key detail is that indexes are not free: they speed reads but add storage and write overhead, so I choose them based on the real query shape.

What an I/O bottleneck really means

I/O means input/output: reading and writing data pages. A page is the small block size the database moves around, commonly 8 KB in PostgreSQL and 16 KB in InnoDB/MySQL. If the needed page is not already in memory, the engine must fetch it from storage, and that wait is often much slower than CPU work.

How the database gets from a query to pages on disk

1. The optimizer reads your SQL and estimates which plan will touch the fewest pages.
2. If your WHERE clause matches the left side of an index and is selective, the engine can do an index seek or index range scan instead of reading every row.
3. The index points to the matching rows or, in some engines, can satisfy the whole query itself if it is a covering index (an index that contains every column the query needs).
4. If the needed data is not in cache, the database performs physical reads from disk or SSD. Many small random reads are usually worse than fewer sequential reads.
5. If the query needs a big sort, hash join, or aggregation and memory is not enough, the engine may spill to temp storage, which is another I/O cost.

Why indexes help

An index is like a book’s table of contents. Instead of reading every page to find one chapter, the engine jumps straight to the right section. That is why indexes are so powerful for selective lookups, such as finding one customer’s orders or one user’s email.

When to use an index, and when not to

1. Use an index when the filter is selective, the query is frequent, and the access pattern is stable.
2. Do not expect an index to help when the query returns a large part of the table; a sequential scan can be cheaper because it reads pages in order.
3. Avoid non-sargable predicates (predicates the optimizer cannot search efficiently), such as wrapping the indexed column in a function like DATE(created_at) or using a leading wildcard like %abc.
4. Prefer composite indexes when filters and sorts happen together, but keep the leading column order aligned with the most common search pattern.

Performance and complexity

Very roughly, a table scan is O(N) in rows or pages touched, while an index lookup is closer to O(log N) to find the leaf, plus any extra row fetches. In real life, the constant factors matter: a full scan on a warm cache can be surprisingly fast, while an index plan that causes thousands of random reads can be slower than scanning. A good rule of thumb is that indexes shine when they let you read a tiny fraction of the table; once you need a meaningful chunk of the table, scans often win.

Important edge cases

• Cache can hide I/O pain: a query may look fine in dev because everything fits in memory, then slow down badly in production when cache misses increase.
• Write-heavy tables pay a price: every insert, update, or delete must also update indexes, which adds write I/O.
• Too many indexes hurt: they increase storage, maintenance, and checkpoint work.
• Covering is engine-specific: some engines call it a covering index, others use different terms, but the idea is the same: avoid extra table fetches.

Memory model: Read fewer pages, and the database gets faster. That is the whole game.

Real-World Story: Imagine an e-commerce checkout service that shows a customer their recent orders while they finish payment. The team adds a simple index on created_at, but the real query filters by customer_id and sorts by created_at. During a holiday sale, p95 latency jumps from 40 ms to 2 seconds, the database CPU stays oddly low, and disk reads spike hard. The logs show lots of full scans and temp-file activity, which tells you the problem is I/O, not raw CPU.

The misunderstanding causes a real user-visible incident: customers click Place Order, the page spins, and some requests time out. Support sees duplicate attempts because users refresh, which creates more load and even more reads. The fix is usually a better composite index that matches the real filter order, plus a query rewrite that avoids functions on indexed columns.

Follow-up & Tricky Questions:

• How do you tell if a query is I/O bound or CPU bound? Look at the symptoms: high disk reads, temp-file spills, and low CPU usually point to I/O; high CPU with low reads often points to expensive joins, sorts, or expressions.
• Why can an index make a query slower? If the query returns a large portion of the table, the engine may do many random lookups through the index, which can be worse than one sequential scan.
• What is a covering index? It is an index that contains all the columns needed by the query, so the database can answer the query without visiting the table again.
• What is a non-sargable predicate? It is a condition the optimizer cannot use efficiently to search an index, such as a function on the indexed column or a leading wildcard pattern.
• How does caching change the picture? A warm cache reduces physical reads, so a query that looks slow on a cold system may appear fast after repeat execution. Interviewers want you to know that cache can hide I/O costs, not remove them.
• Tricky: Should I index every column in WHERE? No. Every extra index adds write cost and storage, so index only the columns that are actually selective and frequently used.
• Tricky: Is a full table scan always bad? No. If the query needs most of the rows, a sequential scan can be the cheapest plan because it reads pages in order.
• Tricky: Will ORDER BY automatically use an index? Only if the index order matches the filter and sort pattern closely enough. If not, the engine may still need to sort.

Common Mistakes:

• Adding an index blindly: Fix the query pattern first, then add the smallest useful index.
• Forgetting write cost: More indexes mean slower inserts, updates, and deletes.
• Using functions on indexed columns: Rewrite predicates so the engine can search the raw column value.
• Assuming cache equals no I/O problem: Production traffic, cold starts, and larger tables can still expose the bottleneck.

Memory Hook: Index = table of contents, I/O = page reads. If the database can jump to the right page instead of reading the whole book, it wins.

Cheat Sheet:

• SQL I/O bottlenecks usually mean too many page reads or temp-file writes.
• Indexes help most when the filter is selective.
• Use composite indexes that match WHERE plus ORDER BY.
• Prefer sargable predicates: compare raw columns, not wrapped expressions.
• Covering indexes remove extra table lookups.
• Scans can beat indexes when many rows are needed.

Practice Tasks:

• Write one query that is I/O heavy, then rewrite it to use a range predicate.
• Design a composite index for a query that filters by one column and sorts by another.
• Take one read-heavy query and make it covering by adding only the columns it truly needs.