Database administrator interview questions

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 is the difference between an INNER JOIN and a LEFT JOIN?

The strongest answer: INNER returns only rows that match on both sides; LEFT returns every row from the left table and fills the right side with NULLs where there is no match — so LEFT is how you keep the customers with no orders

Why it works: Right. The question to ask is: do I want rows with no match to survive?

• INNER: matches on both sides only.
• LEFT: all left rows, NULLs for missing right.
• Use LEFT to keep the ones without a match.
• A WHERE on the right table can silently turn LEFT into INNER.

In plain words, how does an index make a query faster?

The strongest answer: It is a separate, ordered structure — usually a B-tree — that lets the engine jump to matching rows instead of reading the whole table

Why it works: Exactly — a lookup structure kept beside the data, at the cost of extra writes and space.

• An index is a separate ordered structure.
• It replaces a scan with a seek.
• It costs writes and space.
• It is not a result cache.

Why does WHERE status != 'closed' not return rows where status is NULL?

The strongest answer: Because NULL is unknown, and any comparison with NULL is neither true nor false — the row is filtered out; use IS NULL or COALESCE to include them deliberately

Why it works: Exactly. Three-valued logic: comparisons with NULL are unknown and WHERE keeps only true.

• NULL means unknown, not empty.
• Comparisons with NULL are unknown, so WHERE drops them.
• IS NULL / IS NOT NULL to test.
• COALESCE to give NULL a stand-in value.

A pull request adds eleven indexes to the orders table in one migration.

A developer suggests indexing every column “just in case”. Your response?

The strongest answer: Every index slows inserts and updates and takes space, so we index for the queries we actually run and drop the ones that never get used

Why it works: Exactly — indexes are paid for on every write, so they must earn their place with real queries.

• Index for real queries.
• Every index taxes every write.
• Drop indexes nobody uses.
• “Just in case” is not a design.

When do you use HAVING instead of WHERE?

The strongest answer: HAVING filters after grouping, on aggregates — customers with more than five orders; WHERE filters rows before they are grouped

Why it works: Right. WHERE first on rows, GROUP BY, then HAVING on the groups.

• WHERE: rows, before grouping.
• HAVING: groups, after aggregation.
• COUNT, SUM, AVG live in HAVING.
• Order: WHERE → GROUP BY → HAVING.

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 is a read replica for, and what is it not?

The strongest answer: It offloads read traffic and gives you a warm copy for failover; it is not a backup, because a bad DELETE replicates in seconds

Why it works: Exactly — replicas are for reads and availability, never a substitute for backups.

• Replicas serve reads and failover.
• A replica is not a backup.
• Mistakes replicate too.
• Writes still go to one place.

What is a primary key, and what is a foreign key?

The strongest answer: A primary key uniquely identifies each row and cannot be NULL; a foreign key is a column that references another table's primary key, and the database enforces that the referenced row exists

Why it works: Exactly. Identity on one side, an enforced reference on the other.

• Primary key: unique, not NULL, identifies the row.
• Foreign key: references a primary key elsewhere.
• Referential integrity is enforced by the database.
• Keys are constraints; indexes are structures.

What is the difference between a database backup and a storage snapshot?

The strongest answer: A snapshot is a point-in-time image of the volume, fast but tied to that storage; a backup is a consistent copy you can move elsewhere and restore independently — I want both, tested

Why it works: Exactly — snapshots are convenient and quick; backups are portable and independent of the storage that failed.

• Snapshots are fast but local.
• Backups are portable and independent.
• Use both; test both.
• Same server is no protection.

What does an index do, and what does it cost?

The strongest answer: It lets the database find rows by a column without scanning the whole table, like a book's index; it costs storage and makes every insert, update and delete on that column slower because the index must be maintained

Why it works: Right. Faster reads on that column, a tax on every write.

• Index: fast lookup on a column.
• Costs storage and write time.
• Index what you filter and join on.
• Every index is maintained on every write.

What do you need in place to recover a database to a point in time, say 14:32 yesterday?

The strongest answer: A base backup taken before that moment plus an unbroken chain of transaction or archive logs up to it, stored off the server, with the restore procedure rehearsed

Why it works: Exactly — base plus continuous log chain, and one missing log file breaks everything after it.

• Base backup + continuous logs.
• One gap breaks the chain.
• Store logs off the server.
• Rehearse the restore.

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.

Explain RPO and RTO and how they shape a backup design.

The strongest answer: RPO is how much data we can afford to lose, which sets log-shipping frequency; RTO is how long we can be down, which sets restore speed and whether we need a warm standby

Why it works: Exactly — the two numbers translate directly into architecture and cost.

• RPO = acceptable data loss.
• RTO = acceptable downtime.
• Both drive design and cost.
• Agree them, then build to them.

You have to load tens of millions of rows into a table tonight. How?

The strongest answer: Use the engine’s bulk-load path, load in committed batches, consider dropping and recreating non-critical indexes, and refresh statistics after — while watching log and disk space

Why it works: Exactly — bulk path, batches, index strategy, statistics and space are the checklist.

• Use the bulk-load path.
• Commit in batches.
• Handle indexes deliberately.
• Refresh statistics after.

Synchronous versus asynchronous replication — how do you choose?

The strongest answer: Synchronous when losing a committed write is unacceptable and the standby is close enough that the extra commit latency is tolerable; asynchronous when latency or distance matters more than the last few transactions

Why it works: Exactly — it is a trade between durability guarantee and write latency, decided per system.

• Sync: no lost commits, higher latency.
• Async: fast, may lose recent writes.
• A slow sync standby slows the primary.
• Decide per system, not by fashion.

An events table holds six years of data and only the last three months are queried. Options?

The strongest answer: Agree a retention rule with the owners, move older rows to an archive table or cheaper storage in batches, and partition by time so future archiving is a partition drop

Why it works: Exactly — retention is a business decision; batches and partitions make it routine.

• Agree retention with owners.
• Archive in batches.
• Partition by time for the future.
• One giant delete is an outage.

Finance says the end-of-day report, which reads from a replica, is missing the last few minutes of orders.

How do you respond?

The strongest answer: Measure the lag and its cause — long transactions, heavy reporting queries, or network — then either route that report to the primary, add lag-aware routing, or fix the workload that is holding replay back

Why it works: Exactly — lag has a cause; find it and decide where that particular read belongs.

• Measure lag and find the cause.
• Route lag-sensitive reads deliberately.
• Fix the workload blocking replay.
• Restarts are not diagnosis.

Security asks for “the database to be encrypted”. What do you actually put in place?

The strongest answer: Encryption in transit with enforced TLS, encryption at rest for data files and backups with managed keys, and a clear answer on what column-level encryption is for — each protects against a different attacker

Why it works: Exactly — transit, rest and backups are separate controls against separate threats.

• TLS in transit, enforced.
• Encrypt data files and backups.
• Column encryption is a targeted tool.
• Each control matches a threat.

The checkout page has been timing out for five minutes and the on-call is asking for help.

Dozens of sessions are waiting and the app has slowed to a crawl. How do you find the culprit?

The strongest answer: Query the lock and session views to find the head of the blocking chain, see what that session is doing and who owns it, then decide whether to wait, contact them or kill it

Why it works: Exactly — find the root blocker, understand it, then act with a decision rather than a reflex.

• Find the head of the chain.
• Understand before you kill.
• Waiters are victims.
• Restarts are not lock management.

HR asks who changed a salary value last Tuesday. Can you answer, and what would you set up?

The strongest answer: Only if auditing was already on — so I enable auditing for sensitive tables, with named accounts, and ship the audit trail off the server where the auditor cannot alter it

Why it works: Exactly — you cannot audit retrospectively; set it up for sensitive data and protect the trail.

• Auditing must be on beforehand.
• Named accounts make it meaningful.
• Store the trail off the server.
• Transaction logs are not an audit.

Both code paths update the `accounts` and `ledger` tables; the errors cluster around payroll runs.

The application logs show deadlocks a few times a day. What do you do?

The strongest answer: Capture the deadlock graph, find the two code paths that lock the same rows in opposite orders, and fix the ordering or shorten the transactions; the app should also retry the victim

Why it works: Exactly — deadlocks are a design symptom; the graph names the culprits.

• Read the deadlock graph.
• Fix lock ordering.
• Shorten transactions.
• Retry is hygiene, not the cure.

A query is slow. What is the first thing you run, and what are you looking for?

The strongest answer: EXPLAIN (with ANALYZE where safe) to see the plan: a sequential scan on a big table, an estimated row count far from the actual, a nested loop over many rows, or a sort spilling to disk

Why it works: Right. The plan tells you where the time goes before you change anything.

• EXPLAIN first, change second.
• Scans, bad estimates, nested loops, disk sorts.
• ANALYZE shows real times and counts.
• Fix the biggest node.

A product needs high availability for its main relational database. How do you pick the topology?

The strongest answer: Start from RPO, RTO and the failure modes we must survive — node, zone, region — then choose the simplest topology that meets them, often a synchronous standby with automated failover, and rehearse it

Why it works: Exactly — requirements first, then the least complex design that satisfies them, proven by drills.

• Targets and failure modes first.
• Simplest topology that meets them.
• Automate and rehearse failover.
• Multi-primary is not a default.

The request arrives as a ticket asking for “a fresh prod dump in staging by Monday”.

Developers want a copy of the production database in staging. Conditions?

The strongest answer: Only with personal data masked or synthesised on the way in, the same access controls as production on the copy, and a retention limit — or, better, a generated dataset with production’s shape

Why it works: Exactly — a copy of personal data is still personal data.

• Mask or synthesise PII.
• Copies inherit the rules.
• Limit retention.
• Prefer generated data.

What can go wrong with automatic failover, and how do you guard against it?

The strongest answer: Split brain when the old primary keeps taking writes, flapping on network blips, and data loss if the standby was behind — so I need quorum or a witness, fencing, and sensible detection thresholds

Why it works: Exactly — split brain, flapping and lag are the three, and fencing plus quorum are the defence.

• Fence the old primary.
• Quorum or a witness decides.
• Tune detection against flapping.
• Know the standby’s lag at failover.

How do you rotate the application’s database password without downtime?

The strongest answer: Create a second credential or role, deploy the application with the new one while both are valid, verify no connections use the old one, then revoke it — and automate this via a secrets manager

Why it works: Exactly — overlap, verify, revoke, automate.

• Two valid credentials overlap.
• Verify before revoking.
• Automate with a secrets manager.
• Service accounts rotate too.

How do you rename a heavily used column without downtime?

The strongest answer: Expand and contract: add the new column, dual-write from the application, backfill in batches, switch reads, then drop the old column in a later release once nothing references it

Why it works: Exactly — several small, reversible steps instead of one coordinated leap.

• Add, dual-write, backfill, switch, drop.
• Each step is reversible.
• Old and new code coexist.
• Drop only when nothing references it.

Backups are written to a second volume on the same server. Adequate?

The strongest answer: No — one server or one account failure loses both; copy backups to separate storage in another location, encrypted, with restore tested from that copy

Why it works: Exactly — separate the copy from every failure the primary shares.

• Separate location.
• Separate account or credentials.
• Encrypt the copy.
• Test restore from the remote copy.

A backfill must touch every row of a large live table. How do you run it?

The strongest answer: In small keyed batches with short transactions, pausing between them, throttled on replication lag and lock waits, resumable from where it stopped, and run outside the peak

Why it works: Exactly — small, resumable, throttled on the signals that matter.

• Small keyed batches.
• Throttle on lag and locks.
• Resumable from a checkpoint.
• Off-peak, from a server.

Two requests read a balance of 100, each subtracts 30, and the balance ends at 70. What happened and what fixes it?

The strongest answer: A lost update: both read then wrote; fix with a single UPDATE that does the arithmetic in the database (SET balance = balance - 30 WHERE balance >= 30), optimistic locking with a version column, or SELECT ... FOR UPDATE

Why it works: Right. Let the database do the arithmetic, or lock or version the row.

• Read-modify-write races.
• Atomic UPDATE with the arithmetic.
• Version column for optimistic locking.
• SELECT FOR UPDATE for pessimistic.

Design a disaster-recovery drill for the main database.

The strongest answer: Pick a scenario, restore into the DR environment from real backups without touching production, point a copy of the application at it, measure against RTO and RPO, and write down every surprise

Why it works: Exactly — a real restore, measured against the targets, with findings that change the plan.

• Restore from real backups.
• Measure against RTO and RPO.
• Test the application against it.
• Record every surprise.

Optimistic or pessimistic locking for editing a document that several people open?

The strongest answer: Optimistic — a version number checked on save — because conflicts are rare and locks held across a human's editing time would block everyone; pessimistic locking suits short, contended, automated updates

Why it works: Right. Humans hold edits for minutes; a version check on save handles the rare collision.

• Optimistic: version check on save.
• Pessimistic: lock while editing.
• Human edits: optimistic.
• Short automated updates: locks are fine.

You are specifying storage for a busy transactional database. How do you lay out the volumes, and why?

The strongest answer: Separate the transaction log from the data files — the log is the small sequential write every commit waits on — keep backups on different storage, and size for the I/O pattern, not capacity

Why it works: Right — the log is the commit latency path, and backups on the same device disappear with it.

• Log writes are commit latency.
• Separate log from data files.
• Backups on different storage.
• Size for IOPS, not only capacity.

The largest tenant’s dashboard times out every morning while every other tenant loads instantly.

The same parameterised query is fast for most customers and terribly slow for one large customer. What is going on?

The strongest answer: The cached plan was built for a typical parameter value and is wrong for the skewed one — parameter sniffing; I can recompile that query, optimise for a representative value, or split the code path

Why it works: Exactly — skew plus plan reuse; fix it at the query, not the whole cache.

• Skewed data, reused plan.
• Compare the two plans.
• Fix the one query.
• Do not flush the world.

Checksum failures are climbing on the primary of the order system; some reads return errors and the standby may have replicated the damage.

You are the incident commander. What are your first moves?

The strongest answer: Stop the spread: fence writes, preserve the corrupted files and logs, check whether the standby shares the corruption, choose between a clean standby and a point-in-time restore against the RPO, and announce a status cadence

Why it works: Exactly — contain, preserve, assess the standby, choose the recovery path deliberately, communicate.

• Contain writes first.
• Preserve evidence.
• Verify the standby is clean.
• Choose the path against RPO; communicate.

The team is choosing between a cloud vendor’s proprietary database and an open engine on the same cloud. How do you frame the decision?

The strongest answer: Name what the proprietary option gives us that we will use, what leaving would cost in years, and how much of the application touches vendor-specific features — then decide with eyes open, not by principle

Why it works: Exactly — lock-in is a cost to be priced, not a sin to be avoided at all costs.

• Name the real benefit.
• Price the exit.
• Contain vendor-specific features.
• Decide with eyes open.

Leadership wants the database to survive a full region outage. How do you approach it?

The strongest answer: Clarify the RPO and RTO for a regional loss, then design a cross-region standby with matching replication — usually asynchronous, with the accepted loss stated — plus fenced promotion, application cut-over and a drill

Why it works: Exactly — the honest conversation about loss and latency comes before the topology.

• Regional RPO and RTO first.
• State the accepted loss.
• Fenced promotion and cut-over.
• Drill it.

During a long database outage, executives keep asking for updates. How do you communicate?

The strongest answer: A fixed cadence of short updates — what is broken, what we know, what we are doing, the next decision point, when the next update comes — on one channel, no speculation about fix time

Why it works: Exactly — predictable, honest, and separated from the technical work.

• Fixed cadence.
• Known, doing, next decision.
• No speculative fix times.
• One channel, one voice.

How do you upgrade a major database version across a large fleet with near-zero downtime?

The strongest answer: Build a repeatable pipeline: compatibility checks, a logical-replication or blue-green cut-over per database, canary a low-risk system first, automate the switch with a rollback, and schedule the fleet in waves with a stop rule

Why it works: Exactly — canary, automate, wave, and know when to stop.

• Canary, then waves.
• Automate the cut-over and rollback.
• A stop rule for the wave.
• Repeatable beats heroic.

Product wants dashboards over the transactional database. When do you say no and build a separate analytical store?

The strongest answer: When the analytical queries scan large fractions of tables, need historical snapshots or joins across services, and start competing with the product for the working set; then a warehouse fed by replication or CDC with its own model is the honest answer

Why it works: Right. Row stores serve transactions; scans and history want a columnar, separately modelled store.

• OLTP: many small transactions.
• OLAP: scans, history, cross-source joins.
• Replica for light reporting.
• Warehouse when they compete.

A major engine version upgrade is planned for the platform a dozen applications sit on.

How do you test compatibility before the date?

The strongest answer: Restore a production copy onto the new version, replay a captured workload, compare plans and timings, check deprecated syntax and driver support — and keep the old optimiser behaviour at first

Why it works: Exactly — a real workload replayed against a real copy is the only compatibility test that counts, and separating the optimiser change keeps the rollback simple.

• Restore a production copy.
• Replay a real workload.
• Compare plans and timings.
• Separate the optimiser change.

A change freeze is in force and a team needs an urgent index to stop a customer-facing slowdown. How do you decide?

The strongest answer: Weigh doing it against not doing it: an online index build with a rollback is low risk, so it goes through the emergency change path with a second reviewer and a record

Why it works: Exactly — the emergency path exists for exactly this, with review and a trail.

• Compare risk of doing vs not.
• Use the emergency path.
• Second reviewer and a record.
• Urgent is not a criterion.

You are designing the vote layout for a cluster spread across two data centres.

How do you set up quorum and a witness, and why does the vote count matter?

The strongest answer: An odd number of votes so one side can always hold a majority, with the witness in a third location that fails independently — otherwise whichever site holds it wins every partition

Why it works: Exactly — the vote count decides who may continue, and putting the witness inside one site quietly hands that site every decision.

• Keep the vote count odd.
• A majority continues; a minority stops.
• Put the witness in a third failure domain.
• Forcing quorum is an incident action.

How do you model a warehouse so analysts can answer new questions without you?

The strongest answer: Facts and dimensions — a star schema: a fact table of measurable events keyed to conformed dimensions like date, customer and product — so any question is a join of the fact to dimensions the analysts already know

Why it works: Right. Conformed dimensions are what make questions composable.

• Fact table: events with measures.
• Dimensions: date, customer, product.
• Conformed dimensions across facts.
• Grain declared per fact table.

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 service writes an order and publishes an event, and sometimes the event never arrives. What is the fix?

The strongest answer: The transactional outbox: write the event into an outbox table in the same transaction as the order, and a relay publishes from the outbox and marks rows sent — no dual write, no lost events, at-least-once delivery the consumers already handle

Why it works: Right. One transaction, one source of truth, a relay for the rest.

• Dual writes lose events.
• Outbox row in the same transaction.
• Relay publishes and marks sent.
• Consumers stay idempotent.