Database interview questions for backend engineers

Almost everyone can write a join. Far fewer can say why the query is slow, what the index is doing, or what happens when two of these run at the same time. That gap is what the round is measuring.

Indexes, past the definition

The opening question is usually some form of when would you add an index. The definitional answer, that it avoids scanning the table, is table stakes. Three things separate a senior answer.

• Column order in a composite index. An index on the filter column plus the sort column only helps if the leading column is the one you filter on. Reverse them and it is close to useless for that query. This is the single most common follow-up in the whole area.
• The write cost. Every insert and update maintains every index on the table. On write-heavy tables, removing an index can be the optimisation.
• Selectivity. An index on a boolean that is true for most rows will often be ignored, because scanning is cheaper than jumping around. Interviewers like this one because it shows you know the planner makes a decision.

The strongest habit is to talk about the plan rather than about intentions. "I would look at the plan, see whether it is a scan and a sort, add the index, and confirm the plan changed" is a different level of answer from "I would add an index on the where clause".

The N+1 query problem

Universal, because every ORM makes it easy and it is invisible in development where the list has four rows.

Be able to describe both the mechanism and the detection. One query fetches the list, then accessing a related field on each row issues another query, so a hundred rows becomes a hundred and one round trips. Each is fast, the total is not, and it degrades linearly with data you do not control. The tell in production is a request whose latency scales with page size while every individual query looks healthy.

Transactions and isolation

Expect at least one. The trap is reciting the four ACID letters, which demonstrates reading rather than experience.

What earns credit is being able to describe a concrete anomaly. Two requests read the same balance, both subtract from it, and one update is lost. Or a report reads a row twice inside one transaction and gets different values because another transaction committed in between. Then say which isolation level prevents which, and why nobody runs everything at the strictest level: it costs throughput and increases the chance of deadlocks and aborts.

The practical follow-up is usually about the read-then-write pattern. Reading a value in application code and writing a computed result back is a race unless the database is enforcing it, which is why a unique constraint, a conditional update or a select for update is the actual fix rather than checking first.

Locking and deadlocks

Asked as a scenario more often than a definition: your writes intermittently fail with a deadlock error, what is happening.

Two transactions taking the same locks in different orders is the classic cause, and the practical fix is to make the order consistent, keep transactions short, and not do slow work such as an external API call while holding a lock. Mentioning that last one tends to land, because it is the version of this bug that people actually ship.

Schema and migrations

Increasingly common in senior rounds, because it is where database knowledge meets operational judgement.

Normalise until you have a reason not to, and be able to name your reason for denormalising rather than presenting it as a default. Then the migration question: how do you change a schema without downtime.

1. Add the new column or table, nullable, deploying nothing that requires it.
2. Deploy code that writes both old and new.
3. Backfill in batches, not one large statement that locks the table.
4. Deploy code that reads the new path.
5. Stop writing the old one, then drop it in a later release.

That expand-and-contract shape is the answer, and knowing that a naive index creation can lock writes for the duration on a large table is the follow-up worth having ready.

Connection pooling

Quietly one of the most useful things to understand, and one of the least discussed in preparation material.

Connections are expensive and databases cap them, so applications hold a pool. The interview-relevant part is what happens when the pool is exhausted: requests queue, latency climbs everywhere at once, and it looks exactly like the database being slow while the database is comfortable. Being able to describe that misdiagnosis is a strong signal, because most people have chased it in the wrong direction at least once.

Every one of these topics has a second layer, and the second layer is what gets asked. If you can only give the definition, that is worth knowing before the interview rather than during it, which is the audit worth running on yourself.