Databases Interview Questions and Answers

Sample questions

A few questions from each level with the correct answer and an explanation.

Databases · Junior

1. What is a relational database?

   Correct answer: A database that stores data in tables of rows and columns and links tables through keys

   PostgreSQL, MySQL, SQL Server, Oracle and SQLite are relational databases. You query them with SQL, and relationships between tables are expressed with primary and foreign keys.

2. What is a primary key?

   Correct answer: A column (or set of columns) that uniquely identifies each row and cannot be NULL

   A table has at most one primary key. The database automatically creates a unique index for it, so lookups by primary key are fast.

3. What is a foreign key?

   Correct answer: A column that references the primary key (or a unique key) of another table, and the database checks that the referenced row exists

   For example, orders.user_id references users.id. The database rejects an order for a non-existent user. ON DELETE CASCADE or ON DELETE SET NULL define what happens when the referenced row is deleted.

4. How do you model a many-to-many relationship, such as students and courses?

   Correct answer: With a junction table, e.g. enrollments(student_id, course_id), holding a foreign key to each side

   The junction table can also hold data about the relationship itself, like the enrollment date or a grade. A comma-separated list can't be indexed, joined or constrained properly.

Databases · Middle

1. What data structure does a default PostgreSQL index use?

   Correct answer: A B-tree

   B-trees keep keys sorted, so they support equality, ranges (<, BETWEEN), sorting and prefix LIKE 'abc%' (with the right collation or operator class). Hash, GIN, GiST and BRIN are for special cases.

2. There's an index on (last_name, first_name). Which query can use it efficiently?

   Correct answer: WHERE last_name = 'Smith'

   A composite index is sorted by the first column, then by the second within it, like a phone book. It helps queries on a leading prefix of its columns; searching only by first_name usually can't use it well.

3. There's an index on created_at. Why might WHERE date(created_at) = '2026-11-01' ignore it?

   Correct answer: A function on the column changes the value, so the index on the raw column can't be searched; use a range or an expression index

   Rewrite as created_at >= '2026-11-01' AND created_at < '2026-11-02', or create an index on the expression itself.

4. Why can't a normal B-tree index speed up WHERE name LIKE '%son'?

   Correct answer: With a leading wildcard the beginning of the value is unknown, so the sorted index can't narrow the search

   LIKE 'son%' can use a B-tree (with a suitable operator class). For substring or fuzzy search use a trigram GIN index (pg_trgm) or full-text search.

Databases · Senior

1. How does PostgreSQL decide whether a row version is visible to a transaction?

   Correct answer: Each row version stores xmin/xmax transaction IDs, which are compared with the transaction's snapshot

   xmin is the creating transaction and xmax the deleting or updating one. A snapshot lists which transactions were still in progress, so the row is visible only if its creator committed before it.

2. What is transaction ID wraparound, and why is it dangerous?

   Correct answer: Transaction IDs are 32-bit and reused; if old rows aren't frozen by VACUUM in time, PostgreSQL stops accepting writes to protect data

   Autovacuum freezes old rows long before the limit. Disabling autovacuum or holding very long transactions can push a database toward a forced shutdown.

3. What is table bloat and what usually causes it?

   Correct answer: Space taken by dead row versions that isn't reused fast enough, often due to heavy updates with autovacuum lagging or long transactions blocking cleanup

   Tune autovacuum per table (lower scale factors for hot tables), avoid long transactions, and rebuild bloated objects online with pg_repack or REINDEX CONCURRENTLY.

4. What is the purpose of the write-ahead log (WAL)?

   Correct answer: Changes are written to a sequential log and flushed before commit, so the database can recover after a crash and stream changes to replicas

   Data pages can be written to disk later in the background. WAL also powers physical replication, logical decoding and point-in-time recovery.