Q: An engineer backs up the data directory with a plain recursive copy while the database keeps accepting writes, with no snapshot or coordination. What is the main risk?

Correct answer: The copy can be internally inconsistent and may not restore to a valid state. A live copy reads files over a span of time while data keeps changing, so file A and file B can reflect different moments — a torn, inconsistent image that may refuse to start or contain half-applied transactions. Safe physical backups use a consistent snapshot or the engine's backup mode. The copy is neither blocked (b), guaranteed valid (a), nor encrypted (d).

Q: On a very large, high-traffic orders table, why can running a plain schema change like this in production be risky?

Correct answer: Some changes lock the table or run long, blocking writes (and, for some DDL, reads) and causing downtime. On a big, busy table an operation like building an index can hold locks and run long enough to stall queries, so teams schedule it in low-traffic windows or use online/concurrent variants. It does not corrupt data (a), it is reversible with DROP INDEX (c), and it does not require loading the table into RAM (d).

Q: You take a full backup every Sunday and a differential backup every night Monday–Saturday. To restore the database to its state on Wednesday night, which backups do you need?

Correct answer: Only Sunday's full backup together with Wednesday night's differential. A differential contains everything changed since the last full backup, so each night's differential already includes the previous days' changes — you need just the full plus the one differential for your target night. Applying every differential in the chain (a) describes an incremental strategy; a differential alone (c) has no base, and the full alone (d) misses the week's changes.

Q: A table order_items(order_id, product_id, quantity, product_name) has the composite primary key (order_id, product_id), and product_name depends only on product_id. What is the problem and its fix?

Correct answer: It violates 2NF: product_name is a partial dependency on part of the key; move it to a products table. 2NF requires every non-key column to depend on the whole composite key. product_name depends only on product_id, part of the key — a partial dependency — so it belongs in a products table keyed by product_id. The values are atomic, so it is not a 1NF issue (a), and a partial dependency is distinct from the transitive dependency that 3NF addresses (b).

Question 1

You must move a database to a server running a different major version on a different CPU architecture. Which is generally the safer choice, and why?

Accepted Answer

Correct answer: The logical SQL dump, since it is portable and can be reloaded into a different version or platform. Physical backups copy engine-internal file formats that can differ between major versions and architectures, so restoring them elsewhere is fragile. A logical dump is engine-readable SQL/data, which is exactly why it is the standard tool for cross-version and cross-platform moves. Replication (b) keeps copies in sync but is not a prerequisite for migration.

Question 2

The job has exited successfully every night for a year, but during a real outage the restore fails. What does this classic situation illustrate?

Accepted Answer

Correct answer: A green backup job proves the command ran, not that the output is restorable. A zero exit code means the dump command ran, not that the output is complete and restorable — a wrong flag, an excluded schema, or a silent truncation stays hidden until you actually restore (e.g. psql newdb < /backups/nightly.sql into a scratch database). Only a real test restore turns 'we have backups' into 'we can recover.' The other options misdiagnose the cause.

Question 3

An engineer backs up the data directory with a plain recursive copy while the database keeps accepting writes, with no snapshot or coordination. What is the main risk?

Accepted Answer

Correct answer: The copy can be internally inconsistent and may not restore to a valid state. A live copy reads files over a span of time while data keeps changing, so file A and file B can reflect different moments — a torn, inconsistent image that may refuse to start or contain half-applied transactions. Safe physical backups use a consistent snapshot or the engine's backup mode. The copy is neither blocked (b), guaranteed valid (a), nor encrypted (d).

Question 4

On a very large, high-traffic orders table, why can running a plain schema change like this in production be risky?

Accepted Answer

Correct answer: Some changes lock the table or run long, blocking writes (and, for some DDL, reads) and causing downtime. On a big, busy table an operation like building an index can hold locks and run long enough to stall queries, so teams schedule it in low-traffic windows or use online/concurrent variants. It does not corrupt data (a), it is reversible with DROP INDEX (c), and it does not require loading the table into RAM (d).

Question 5

You take a full backup every Sunday and a differential backup every night Monday–Saturday. To restore the database to its state on Wednesday night, which backups do you need?

Accepted Answer

Correct answer: Only Sunday's full backup together with Wednesday night's differential. A differential contains everything changed since the last full backup, so each night's differential already includes the previous days' changes — you need just the full plus the one differential for your target night. Applying every differential in the chain (a) describes an incremental strategy; a differential alone (c) has no base, and the full alone (d) misses the week's changes.

Question 6

A table order_items(order_id, product_id, quantity, product_name) has the composite primary key (order_id, product_id), and product_name depends only on product_id. What is the problem and its fix?

Accepted Answer

Correct answer: It violates 2NF: product_name is a partial dependency on part of the key; move it to a products table. 2NF requires every non-key column to depend on the whole composite key. product_name depends only on product_id, part of the key — a partial dependency — so it belongs in a products table keyed by product_id. The values are atomic, so it is not a 1NF issue (a), and a partial dependency is distinct from the transitive dependency that 3NF addresses (b).

Database Senior Interview Questions

Sample questions

Topics

By specialization

By seniority

Other fields