The database section is where senior backend interviews get decided, because it is the hardest area to fake. You either have read a query plan or you have not, and the follow-ups find out quickly.
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.
An endpoint got slower as data grew. How would you find out why?
Junior answer: I would check the slow query log and add indexes to the columns being filtered on, and probably add caching if it is read heavy.
Senior answer: First I would look at whether one query is slow or many queries are being issued, because those have completely different fixes and the second one hides from a slow query log. If the count scales with the number of rows returned, it is N+1 and the fix is eager loading, not an index. If it is one slow query, then the plan tells me whether it is a scan, a sort or a bad join order. Caching I would consider last, since caching a query I have not understood usually just moves the problem and adds an invalidation bug.
Distinguishing one-slow-query from many-fast-queries is the whole diagnosis, and it is where most candidates skip straight to a fix.
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.
- Add the new column or table, nullable, deploying nothing that requires it.
- Deploy code that writes both old and new.
- Backfill in batches, not one large statement that locks the table.
- Deploy code that reads the new path.
- 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.
The habit that carries this whole area
Talk about evidence, not intentions. Reading a plan, comparing query counts, checking pool saturation. Candidates who say what they would look at outscore candidates who say what they would change.
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.
Common questions
How much SQL should I be able to write live?
Joins, aggregation with grouping, and usually a window function or a subquery. Perfect syntax is rarely the point; being able to explain what the query makes the database do is.
Do I need to know a specific database?
Know one properly and you can reason about the others. Interviewers care much more about depth in one than familiarity with four.
Is NoSQL knowledge expected?
Enough to say when you would choose it and what you give up. The strongest answer starts from access patterns, not from the label on the technology.
Database rounds are decided by the follow-up, not the first answer. Run a database-focused interview and see how far yours goes. Practise a database interview
Top comments (1)
A Practical Way to Prepare for Backend Database Interviews
This is a great breakdown because senior database interviews are rarely about memorizing definitions. The follow-ups are where the real depth shows.
I’d add one more principle: always connect the database behavior to the application-level symptom.
A query being slow could mean a bad execution plan, but it could also be N+1 queries, connection-pool exhaustion, lock contention, or an inefficient transaction boundary. The ability to distinguish those cases is much more valuable than simply knowing which index to add.
A few questions I’d use to test myself:
Can I read an EXPLAIN/query plan and identify the expensive operation?
Can I explain why a composite index works or doesn't based on column order?
Can I diagnose N+1 from query counts rather than assuming the SQL itself is slow?
Can I explain a lost update with two concurrent transactions?
Can I design a zero-downtime schema migration?
Can I explain what happens when the connection pool is exhausted?
Can I identify the cause of a deadlock and describe how I would prevent it?
Can I explain the trade-offs of normalization, denormalization, and NoSQL based on access patterns?
The strongest database interview answers usually follow the same pattern:
Observe → measure → form a hypothesis → change one thing → verify with evidence.
That mindset is useful far beyond interviews it is how you troubleshoot production systems without guessing.
For backend engineers preparing for senior-level interviews, I’d spend less time memorizing database terminology and more time practicing the follow-up questions.
If you'd like to discuss backend/database interview preparation or engineering opportunities, you can reach me on TGcoolsoftDev.