Databases & SQL
The layer where almost every serious performance problem eventually turns up: how data is modelled, how indexes and query plans decide whether a query takes a millisecond or a minute, what a transaction really promises under concurrency, and where a non-relational store genuinely fits.
Assumes you know: Enough SQL to write a join and a GROUP BY without looking it up, A rough idea of how a disk and a page cache differ in speed
Overview
What this area actually covers
Databases is the study of how data is stored so that it can be found again quickly and changed safely while other people are changing it too. Those two halves — retrieval and concurrency — generate nearly every question you will be asked.
In practice the area splits into two skills that are usually conflated. Using a database well means modelling a schema that constrains what can be wrong, writing queries that let the engine do its job, indexing deliberately, and knowing what a transaction gives you. Operating one means backups you have restored, replication and failover, upgrades without downtime, capacity planning, and being the person on the call when disk fills at three in the morning. Most engineers are hired for the first and quietly judged on how much of the second they understand.
Two things get bundled in wrongly. Analytics and warehousing overlap in language but differ in mechanics — a columnar warehouse optimised for scanning billions of rows behaves nothing like a row store optimised for fetching one, and advice from one context is often actively harmful in the other. Object-relational mappers are a separate topic: knowing an ORM's API is not database knowledge, and the most expensive database bugs come from engineers who only ever spoke to the database through one.
There is a third boundary that decides how deep an interview goes. Knowing SQL and knowing databases are different competencies. SQL is a language for expressing what you want; database knowledge is understanding what the engine will do about it. A candidate can write an elegant seven-table query with three window functions and have no idea why it takes ninety seconds, and another can write plainer SQL that runs in four milliseconds because they knew which access path the engine would pick. Both are useful. Only the second is what senior interviews are looking for.
Where to go next
Now practise it
28 interview questions in Databases & SQL, each with the rubric the interviewer is scoring against.
- You are building a user-profile service. Key-value store or document database: what decides it?
- You have to pick a database in the first five minutes of a design round. Which three questions decide it?
- You need a work queue with priorities and you are told to build it in Postgres, not Kafka. What does the dequeue statement have to do, and what happens when ten workers run it at once?
- A table has forty million rows, thirty-five million of them soft-deleted, and every query for active rows has got slower. Would a partial index help, and what would you have to be careful about?