Q: What is the N+1 query problem in database access?

Loading N child records requires 1 query to fetch parents + N separate queries for each parent's children — solved by eager loading or JOINs. Fetching 100 users then querying each user's orders separately = 101 queries. Fix: use JOIN or ORM eager loading (e.g., .includes() in Rails, .prefetch_related() in Django). Huge performance impact.

Q: What is the difference between optimistic and pessimistic locking?

Optimistic assumes no conflict — checks at commit time and rolls back if conflict detected; pessimistic locks the row immediately to prevent concurrent modifications. Optimistic: add a version column; increment on update; fail if version changed (good for low-conflict scenarios). Pessimistic: SELECT FOR UPDATE locks the row (good for high-conflict, e.g., seat booking).

Q: What is a composite index and what is the 'leading column' rule?

An index on multiple columns — queries must filter on the leading (leftmost) column(s) to benefit from the index. Index on (last_name, first_name): WHERE last_name = 'Smith' uses the index; WHERE first_name = 'John' does not. Column order matters — put the most selective, most frequently queried column first.

Q: What is database connection pooling and why is it important?

Reusing a pool of pre-established database connections instead of creating and destroying one per request — dramatically reduces connection overhead. Creating a DB connection is expensive (TCP handshake, auth). A pool maintains N open connections and lends them out. Libraries: HikariCP (Java), pgBouncer (PostgreSQL), SQLAlchemy pooling (Python).

Question 1

What is a B-tree index and why do databases commonly use it?

Accepted Answer

A self-balancing tree keeping data sorted, supporting O(log n) search, insert, and delete — ideal for range queries and ordered scans. B-trees keep data sorted on disk. They support both exact-match and range queries (BETWEEN, >, <). Most relational databases (PostgreSQL, MySQL InnoDB) use B+ trees where all data is in leaf nodes linked for efficient scans.

Question 2

What is the N+1 query problem in database access?

Accepted Answer

Loading N child records requires 1 query to fetch parents + N separate queries for each parent's children — solved by eager loading or JOINs. Fetching 100 users then querying each user's orders separately = 101 queries. Fix: use JOIN or ORM eager loading (e.g., .includes() in Rails, .prefetch_related() in Django). Huge performance impact.

Question 3

What is the difference between optimistic and pessimistic locking?

Accepted Answer

Optimistic assumes no conflict — checks at commit time and rolls back if conflict detected; pessimistic locks the row immediately to prevent concurrent modifications. Optimistic: add a version column; increment on update; fail if version changed (good for low-conflict scenarios). Pessimistic: SELECT FOR UPDATE locks the row (good for high-conflict, e.g., seat booking).

Question 4

What is a composite index and what is the 'leading column' rule?

Accepted Answer

An index on multiple columns — queries must filter on the leading (leftmost) column(s) to benefit from the index. Index on (last_name, first_name): WHERE last_name = 'Smith' uses the index; WHERE first_name = 'John' does not. Column order matters — put the most selective, most frequently queried column first.

Question 5

What is database connection pooling and why is it important?

Accepted Answer

Reusing a pool of pre-established database connections instead of creating and destroying one per request — dramatically reduces connection overhead. Creating a DB connection is expensive (TCP handshake, auth). A pool maintains N open connections and lends them out. Libraries: HikariCP (Java), pgBouncer (PostgreSQL), SQLAlchemy pooling (Python).

DBMS & SQL — Junior Dev Interview Questions

More Junior Dev Topics

DBMS & SQL at Other Levels