Relational Databases & ACID

Why Relational Databases Still Dominate

Relational databases have been the backbone of software systems for over 50 years. Despite the NoSQL wave, the majority of production transactional systems — banking, e-commerce, SaaS — still rely on PostgreSQL, MySQL, or SQL Server. Understanding why requires understanding what relational databases guarantee: the ACID properties.

ACID Properties

ACID stands for Atomicity, Consistency, Isolation, and Durability. These four guarantees define what a *transaction* means in a relational database.

Isolation Levels

Full serializable isolation is expensive. SQL databases offer a spectrum of isolation levels that trade correctness for performance. Understanding these is crucial for interviews.

Indexing Strategies

Indexes are the single biggest lever for query performance in relational databases. Without an index, every query performs a full table scan — O(n) cost. With the right index, lookups become O(log n) via a B-tree.

B-Tree Indexes

The default index type in PostgreSQL and MySQL. B-trees store data in sorted order, enabling range queries (`WHERE created_at > '2024-01-01'`), equality lookups, and ORDER BY operations without an extra sort step. The tree typically has 3-4 levels even for tables with hundreds of millions of rows.

Hash Indexes

Hash indexes provide O(1) equality lookups but cannot support range queries. Use them only for exact-match workloads. PostgreSQL supports hash indexes natively; in MySQL, hash indexes exist only in the MEMORY storage engine.

Composite and Covering Indexes

A composite index covers multiple columns. The order matters: an index on `(user_id, created_at)` supports queries filtering on `user_id` alone or `(user_id, created_at)`, but not on `created_at` alone. A covering index includes all columns a query needs, so the database never touches the main table — sometimes called an index-only scan.

-- Composite index: supports WHERE user_id = ? AND created_at > ?
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Covering index: query can be answered entirely from the index
CREATE INDEX idx_orders_covering
ON orders(user_id, created_at)
INCLUDE (total_amount, status);

-- EXPLAIN to verify index usage
EXPLAIN ANALYZE
SELECT total_amount, status
FROM orders
WHERE user_id = 42 AND created_at > '2024-01-01';

Normalization

Normalization eliminates redundancy by decomposing tables into smaller ones and using foreign keys to join them. The standard forms are 1NF, 2NF, 3NF, and BCNF. Most production schemas aim for 3NF: each non-key column depends on the whole primary key, and nothing else.

When to Choose a Relational Database

• Complex relationships: Multiple entity types with foreign key constraints and joins (users → orders → products → inventory).
• ACID transactions required: Financial operations, inventory management, any case where partial writes are catastrophic.
• Flexible ad-hoc queries: You don't know all your access patterns upfront and need SQL's expressiveness.
• Regulatory compliance: Auditability, constraints, and referential integrity are enforced by the database engine.
• Moderate scale: PostgreSQL handles hundreds of thousands of QPS with proper indexing and connection pooling.

Query Optimization Basics

The query optimizer generates an execution plan — the sequence of operations to execute a query. Use `EXPLAIN ANALYZE` to inspect the plan. Key things to look for: Seq Scan (red flag on large tables), Index Scan (good), Hash Join vs Nested Loop (depends on table size), and sort operations that could be eliminated by an index.