Database engineer technical fundamentals interview questions

As asked

Explain each of the four ACID properties using a bank transfer of $100 from account A to account B as your example, and describe what specific failure each property guards against.

Sample answer outline

Atomicity: both the debit and credit happen or neither does, preventing a state where A is debited but B is not credited. Consistency: the database constraints (balance >= 0) are never violated mid-transaction. Isolation: concurrent transactions reading balances see consistent snapshots, not intermediate states. Durability: once the transfer commits and returns success to the user, a server crash cannot undo it because the commit was written to durable storage (WAL). The candidate should name the specific failure each guards against rather than just defining the term.

Expect these follow-ups

• Which ACID property is most commonly relaxed in high-availability distributed systems and why?

As asked

Explain what Third Normal Form means, describe a real situation where a table in 3NF would be a bad choice, and what you would do instead.

Sample answer outline

3NF removes transitive dependencies so every non-key column depends only on the primary key. It is bad for analytical queries that join many tables: a report that joins 8 normalized tables will be slower and harder to maintain than a denormalized fact table. The candidate should distinguish OLTP (normalization reduces redundancy and write anomalies) from OLAP (denormalization improves read performance). A star schema in a data warehouse is a canonical example of deliberate 3NF violation.

Expect these follow-ups

• What is the difference between a snowflake schema and a star schema in a data warehouse?

As asked

Define index selectivity, explain how low selectivity makes an index nearly useless for point lookups, and give an example of a column where a B-tree index would be counterproductive.

Sample answer outline

Selectivity is the fraction of rows eliminated by a predicate. High selectivity means few rows match and the index is valuable. Low selectivity (e.g., a boolean column with 95% true) means the index returns most of the table and a sequential scan is cheaper because index lookups involve random I/O per row. A B-tree on a status column with only 3 distinct values would rarely be used by the planner for anything other than covering index purposes.

Expect these follow-ups

• What index type would you use for a low-selectivity column that is frequently queried with containment operators?

As asked

Explain the CAP theorem, describe which two properties each of PostgreSQL, Cassandra, and DynamoDB prioritizes, and give an example of a business requirement that would drive you toward each.

Sample answer outline

CAP states that a distributed system can guarantee at most two of Consistency, Availability, and Partition tolerance during a network partition. PostgreSQL (with synchronous replication) is CP: it sacrifices availability by refusing writes when the primary cannot confirm the replica. Cassandra is AP: it accepts writes even during a partition and resolves conflicts with eventual consistency. DynamoDB defaults to AP but offers strongly consistent reads at extra cost, making it tunable. The candidate should mention that partition tolerance is unavoidable in practice, making the real choice between CP and AP.

Expect these follow-ups

• What is the difference between strong consistency and eventual consistency in terms of what a client observes?

As asked

Explain how PostgreSQL collects and stores column statistics, how it uses them to estimate row counts for a predicate, and why estimates can be wildly wrong for correlated columns.

Sample answer outline

ANALYZE samples rows and stores the most common values and their frequencies, plus a histogram for the distribution. For a predicate, the planner estimates the fraction of rows using the histogram. For correlated columns (e.g., city and state), the planner applies each predicate independently and multiplies the selectivities, assuming independence, which underestimates rows severely when the columns are correlated. Extended statistics (CREATE STATISTICS ... ON (col1, col2)) teach the planner about multi-column correlations.

Expect these follow-ups

• What does pg_stats.correlation mean and how does it affect the planner's choice between an index scan and a sequential scan?

As asked

Explain the write-ahead logging protocol: why must the log record be durably written before the data page is written to disk, and what would go wrong if that order were reversed?

Sample answer outline

If the data page were written first and then the server crashed before the WAL record, the change would be in the data file with no log record to confirm or deny it. On recovery, there would be no way to know if the transaction committed, violating durability. Writing WAL first ensures that on crash recovery, replaying the WAL from the last checkpoint brings the data to a consistent committed state. The WAL is sequential and cheap to fsync; data pages are random I/O and expensive, which is why the WAL-first approach also improves I/O performance.

Expect these follow-ups

• What is group commit and how does it amortize the fsync cost of the WAL across multiple transactions?

As asked

A legacy MySQL application uses MyISAM tables. Your team is migrating to InnoDB. What are the architectural differences between the two engines that make InnoDB far better for OLTP?

Sample answer outline

InnoDB supports row-level locking; MyISAM uses table-level locking, which serializes all writes. InnoDB supports transactions with ACID guarantees; MyISAM has no transaction support. InnoDB has crash recovery via WAL and the InnoDB doublewrite buffer; MyISAM tables can be corrupted on crash and require CHECK TABLE/REPAIR TABLE. InnoDB supports foreign keys; MyISAM does not. InnoDB's MVCC allows concurrent reads and writes without blocking; MyISAM readers block writers on full-text searches.

Expect these follow-ups

• What is the doublewrite buffer in InnoDB and what failure mode does it protect against?

As asked

Explain the difference between a database connection and a session in PostgreSQL, what resources each consumes, and why maintaining many idle connections is harmful.

Sample answer outline

A connection is the network socket and OS-level resources between the client and the PostgreSQL postmaster. A session is the server-side process (in PostgreSQL's process-per-connection model, one backend process per connection) that maintains state like SET options, prepared statements, and temporary tables. Each connection allocates shared memory for the lock table entry, process memory for the backend, and a file descriptor. Idle connections still consume these resources; a system with 1,000 idle connections wastes memory and may exhaust the connection limit, blocking new legitimate connections.

Expect these follow-ups

• How does PostgreSQL's process-per-connection model compare to MySQL's thread-per-connection model?

As asked

A developer is implementing search over a product descriptions column with LIKE '%keyword%'. When should they switch to PostgreSQL's full-text search with tsvector/tsquery, and what are the trade-offs?

Sample answer outline

LIKE '%keyword%' cannot use a B-tree index and requires a full sequential scan, scaling as O(N) in data size. PostgreSQL's full-text search creates a tsvector (lexeme list) that can be indexed with a GIN or GiST index, enabling fast keyword lookups with ranking. Full-text search also handles stemming (runs matches run/running) and stop words. Trade-offs: tsvector does not support substring matching inside words (use pg_trgm GIN for that), and the index is larger. For fuzzy or substring search, pg_trgm with a GIN index is better than either.

Expect these follow-ups

• How does pg_trgm's similarity search work and what are its false-positive characteristics?

As asked

Hash indexes and B-tree indexes are both supported by PostgreSQL. Under what conditions does a hash index outperform a B-tree, and what are its limitations?

Sample answer outline

Hash indexes are O(1) for equality lookups versus O(log N) for B-tree, so for large tables with point-lookup-only access patterns (no range scans, no ORDER BY), a hash index can be faster and smaller. Limitations: hash indexes do not support range queries, LIKE patterns, or ORDER BY; in PostgreSQL before version 10 they were not WAL-logged and did not survive crashes; and they offer no benefit for low-cardinality columns. In practice, the B-tree advantage in versatility usually wins unless the access pattern is strictly equality.

Expect these follow-ups

• How does the hash index page structure differ from a B-tree in terms of overflow buckets?