Database Questions — Cheat Sheet

Tags: database, sql, nosql, indexing, transactions, acid, normalization, postgresql

Level: intermediate

TL;DR: Core database concepts for technical interviews covering relational design, SQL indexing, ACID guarantees, transaction isolation, and SQL vs NoSQL trade-offs.

Key concepts

Relational databases enforce schema rigidity, referential integrity via foreign keys, and ACID properties.

B-Tree indexes provide logarithmic time complexity for equality and range queries on sorted data.

Clustered indexes dictate the physical row storage order on disk, limited to one per table.

Non-clustered indexes maintain secondary search trees pointing to physical table row addresses or primary keys.

Normalization minimizes data redundancy and anomalies through structured relational forms (1NF, 2NF, 3NF).

Denormalization intentionally introduces redundancy to optimize read-heavy workloads and eliminate costly table joins.

ACID guarantees Atomicity (all-or-nothing), Consistency (rules preserved), Isolation (concurrent safety), and Durability (persisted commits).

Transaction isolation levels trade concurrency performance against anomalies like dirty reads, non-repeatable reads, and phantom reads.

CAP theorem states a distributed data store can guarantee at most two of Consistency, Availability, and Partition tolerance.

Horizontal scaling distributes records across nodes (sharding), whereas vertical scaling enhances existing machine compute and storage.

Typical questions and ready answers

Q: How do you determine whether a query is utilizing an index effectively?R: I run EXPLAIN ANALYZE to review the execution plan, checking whether the engine uses an Index Scan rather than a Sequential Scan.

Q: What is the difference between WHERE and HAVING clauses?R: I use WHERE to filter individual rows before aggregation, whereas HAVING filters aggregated groups produced by GROUP BY.

Q: What is the difference between optimistic and pessimistic locking?R: I use pessimistic locking with row locks when contention is frequent, and optimistic locking via version columns when conflict probability is low.

Q: How do you resolve a database deadlock?R: I ensure transactions access shared resources in a globally consistent order and keep transaction execution times as brief as possible.

Q: When should you pick PostgreSQL over MongoDB?R: I choose PostgreSQL when the domain requires strict relational consistency and complex joins, reserving MongoDB for dynamic polymorphic documents without strict schema constraints.

Q: What causes the N+1 query problem and how do you prevent it?R: An ORM executing separate queries for each related record causes it; I prevent it using eager loading via joins or prefetching.

Pitfalls and common mistakes

Applying functions or wildcard prefixes on indexed columns, which forces full table sequential scans.

Over-indexing tables, which accelerates read queries while severely degrading insert, update, and delete performance.

Choosing SERIALIZABLE isolation globally without evaluating retry mechanisms required for serialization failure rollbacks.

Storing unindexed foreign keys, causing table-level lock escalation or slow cascading deletions during parent record modifications.

Relying on application-level logic instead of database-level unique constraints to enforce business uniqueness rules.

Snippets and commandsSQL

```-- Execution plan inspection

EXPLAIN ANALYZE

SELECT customer_id, COUNT(order_id) AS total_orders

FROM orders

WHERE created_at >= NOW() - INTERVAL '30 days'

GROUP BY customer_id

HAVING COUNT(order_id) > 5;

-- Composite index for equality and range filtering

CREATE INDEX idx_orders_status_date ON orders (status, created_at DESC);

-- Explicit row-level locking pattern

BEGIN;

SELECT id, balance FROM accounts WHERE id = 42 FOR UPDATE;

UPDATE accounts SET balance = balance - 100 WHERE id = 42;

COMMIT;

```

To review

[ ] Internal mechanics of write-ahead logging (WAL) and checkpoint flushing

[ ] Differences between Repeatable Read and Serializable anomaly prevention in PostgreSQL

[x] Inner, left, right, and full outer join behaviors

[x] Composite indexing column ordering rules based on selectivity

References

Designing Data-Intensive Applications by Martin Kleppmann

PostgreSQL Documentation: Indexes and Concurrency Control

Use The Index, Luke by Markus Winand