All jobs / Database administrator
Database Architecture interview questions
Database Architecture questions a Database administrator can be asked, from first job to senior. Open one to see the strongest answer and why it works.
What is a primary key actually for, and what makes a good one?
The strongest answer: It identifies exactly one row, never changes and is never null — so a stable surrogate or a truly immutable natural key, not an email address people edit
Why it works: Exactly — uniqueness, immutability and not-null are the contract; a key that can change breaks every reference to it.
- One row, one key, forever.
- Never null, never edited.
- Natural keys must be truly immutable.
- Every table needs one.
Why do database engines read and write in fixed-size pages rather than single rows?
The strongest answer: The page is the unit the engine reads, caches and writes — a fixed block holding many rows — so fetching one row pulls in its whole page and the neighbours come almost free
Why it works: Exactly — the page is the unit of I/O, of caching and of locking, which is why row width and locality change how much work a query does.
- Pages are the unit of I/O.
- One block holds many rows.
- Reading a row fetches its page.
- So row width and locality matter.
What does normalisation buy you, and when might you deliberately denormalise?
The strongest answer: It stores each fact once, so updates cannot leave contradictory copies; I denormalise deliberately for read paths where the join cost is measured and the copy is kept in sync
Why it works: Exactly — normalisation is about one source of truth; denormalisation is a measured, maintained trade.
- Store each fact once.
- Normalise to avoid contradictory copies.
- Denormalise on measurement, with sync.
- One wide table is not a shortcut.
What does ACID mean for someone running a transactional database day to day?
The strongest answer: Atomic: all or nothing; Consistent: constraints hold; Isolated: concurrent work does not see half-done changes; Durable: committed means survives a crash — which is why I care about fsync and write-ahead logs
Why it works: Exactly — and durability is where operational settings can quietly break the promise.
- Atomic, consistent, isolated, durable.
- Durability depends on configuration.
- Isolation is a dial, not a given.
- ACID is not speed or uptime.
Why wrap a debit and a credit in a single transaction rather than two statements?
The strongest answer: So the two changes commit or roll back together — if the process dies between them, money does not vanish or double
Why it works: Exactly — atomicity is the whole reason; partial application is the failure to prevent.
- Related changes commit together.
- A crash between commits loses money.
- Correctness, not speed.
- Short transactions can still be atomic.
The same query takes two seconds the first time and twenty milliseconds the second. What is going on?
The strongest answer: The first run read its pages from storage; they now sit in the buffer cache in memory, so the second run touches memory instead of disk — the query itself did not change
Why it works: Right — the buffer cache holds data pages, which is why a cold cache and a warm cache give such different timings.
- The cache holds pages, not results.
- First run reads from storage.
- Second run reads from memory.
- Report cold and warm separately.
What does “read committed” isolation protect you from, and what does it not?
The strongest answer: It stops you seeing uncommitted changes from other sessions, but the same query run twice in one transaction can return different rows
Why it works: Exactly — no dirty reads, but non-repeatable reads and phantoms are still possible.
- No dirty reads.
- Repeated reads may differ.
- Higher levels cost more contention.
- Know your engine’s default.
The application checks that a username is unique before inserting. Is a unique constraint still needed?
The strongest answer: Yes — two requests can pass the check at the same moment; only a unique index makes it impossible, and the app handles the violation error
Why it works: Exactly — check-then-insert is a race; the constraint is the actual guarantee.
- Check-then-insert races.
- Unique index is the guarantee.
- App check gives the nice error.
- Constraints beat clean-ups.
A nightly import into the invoices table has slowed down since the constraints were added.
A team wants to drop foreign keys because they “slow down inserts”. Your view?
The strongest answer: Keep them — they are the only guarantee against orphan rows; if a specific bulk load is hurting, load in order or defer checking for that job, measured
Why it works: Exactly — integrity is the database’s job; tune the specific hot path instead of removing the guarantee.
- Foreign keys prevent orphans.
- Tune the hot load, keep the rule.
- App validation is not enforcement.
- Integrity lives in the database.
When does partitioning a table actually help?
The strongest answer: When queries filter on the partition key so whole partitions are skipped, or when we need to drop or archive old data quickly; it does not make a table faster by itself
Why it works: Exactly — pruning and lifecycle management are the wins; size alone is not a reason.
- Partition for pruning.
- Partition for cheap archiving.
- Size alone is not a reason.
- Key must match the queries.
What is an extent, and why does the engine hand out space in extents rather than a page at a time?
The strongest answer: An extent is a short run of contiguous pages; allocating in runs keeps related pages together and cuts the allocation bookkeeping, so a scan reads sequentially instead of jumping around the file
Why it works: Exactly — contiguity is the point, and it is why growth increments and file layout decide whether a large scan stays sequential.
- An extent is contiguous pages.
- Allocate in runs, not singles.
- Contiguity keeps scans sequential.
- Set sensible growth increments.
Why does the engine write the change to the transaction log before the data pages, and what does that buy you?
The strongest answer: Write-ahead logging: the log record is flushed at commit and the data pages can be written later, so a crash is recovered by replaying the log — durability without waiting on scattered writes
Why it works: Exactly — one sequential write at commit plus lazy data writes is the whole trick, and it is what point-in-time recovery is built on.
- Log first, data pages later.
- Commit waits on a sequential write.
- Recovery replays the log.
- The log is not a data copy.
Some engines store a table in the order of a clustered index, others leave it as an unordered heap. What does clustering change, and when does it hurt?
The strongest answer: Clustering keeps rows in key order, so ranges on that key are sequential and every secondary index carries the key; the cost is a wide or random key inflating indexes and splitting pages
Why it works: Exactly — cheap ranges and lookups on that key, paid for by every secondary index and by insert order.
- Clustering orders the rows themselves.
- Ranges on that key get cheap.
- Every secondary index carries the key.
- Wide or random keys cost dearly.
Two customers occasionally buy the last unit of stock at the same time. Which isolation or locking approach do you recommend?
The strongest answer: Make the decrement itself atomic — an UPDATE with a `WHERE quantity > 0` check or an explicit row lock — rather than read-then-write; serializable would also work but costs retries across the workload
Why it works: Exactly — the narrowest correct tool: an atomic conditional write on the row.
- Make the decrement atomic.
- Conditional UPDATE or row lock.
- Serializable works but costs.
- Read-then-write is the race.
A team proposes storing all entity attributes in one key-value table “for flexibility”. Your review?
The strongest answer: Ask what actually varies; keep the known, queried attributes as typed columns, and allow a JSON or document column only for truly variable extras — an all-EAV design loses types, constraints and indexable queries
Why it works: Exactly — structure the known, bound the unknown.
- Type the known attributes.
- Bound the variable part.
- EAV loses constraints and indexes.
- Do not bank on later.
The team says the primary “cannot scale further” and wants to shard. Your response?
The strongest answer: Exhaust the cheaper levers first — query fixes, read offload, partitioning, bigger hardware, archiving — and shard only if writes genuinely exceed one node, choosing a key that keeps most transactions on one shard
Why it works: Exactly — sharding is a permanent complexity tax, so be certain it is the write ceiling you are hitting.
- Cheaper levers first.
- Shard for real write ceilings.
- Choose the key carefully.
- Complexity is permanent.
A team proposes rewriting the core schema from scratch because the current one has “too much debt”. Your position?
The strongest answer: Ask which concrete problems the rewrite solves and whether incremental expand-contract changes could solve them with less risk; support rewriting only the parts where the model is wrong, with a live-data migration plan
Why it works: Exactly — problems first, incremental where possible, and the data migration is the hard part.
- Name the concrete problems.
- Incremental before big bang.
- Rewrite only what is truly wrong.
- Plan the live data migration.
A table is heading towards a scale where a single partition scheme no longer fits every query. How do you decide the strategy?
The strongest answer: Rank the queries by importance, partition for the dominant access path and lifecycle, accept that secondary paths need indexes or a separate derived table, and plan the partition count and maintenance for years ahead
Why it works: Exactly — one scheme serves one dominant path; the rest is served deliberately elsewhere.
- Partition for the dominant path.
- Serve other paths deliberately.
- Plan partition count for years.
- Do not sub-partition everything.