Databases Interview Questions

SQL: structured tables, ACID transactions, strong consistency (Postgres, MySQL). NoSQL: flexible schemas, horizontal scaling (MongoDB, DynamoDB).

Reduce redundancy via normal forms (1NF, 2NF, 3NF). Use denormalization selectively for read performance.

B-tree indexes speed lookups and sorts. Trade-offs: extra storage and slower writes.

-- Postgres
CREATE INDEX users_email_idx ON users(email);

ACID: Atomicity, Consistency, Isolation, Durability. Isolation levels: Read Committed, Repeatable Read, Serializable.

INNER, LEFT, RIGHT, FULL. Use indexes on join keys to improve performance.

SELECT u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id;

Master-replica for read scaling and HA. Use health checks and automatic failover (e.g., Patroni).

Split data across shards by key (user_id). Beware hot shards and cross-shard queries.

ORMs speed development; raw SQL offers control and performance. Mix both pragmatically.

ACID ensures reliable transactions; BASE favors availability and eventual consistency in distributed systems. Choose per workload and consistency needs.

B-tree (default), Hash (equality), GIN/GiST (arrays/full-text/geospatial). Pick indexes based on query patterns.

Limit concurrent DB connections via pools; reuse connections to reduce overhead. Tune pool size per workload and DB limits.

Use snapshots and WAL archiving; verify backups, test restores regularly, and define RPO/RTO.

Document (MongoDB), key-value (Redis), column-family (Cassandra), graph (Neo4j). Design schemas around access patterns.

Read Uncommitted, Read Committed, Repeatable Read, Serializable; anomalies and trade-offs per level.

In network partitions, systems choose between consistency and availability. Many DBs prefer AP with eventual consistency.

Ensure durability by recording changes to a log before applying to pages; enables crash recovery and replication.

Use EXPLAIN to inspect access paths and costs; add indexes, rewrite queries, and tune statistics.

Coordinators ensure atomicity across participants; can block under failures. Modern systems prefer idempotent operations and Sagas.