SQL Databases (RDBMS)

In a Nutshell

SQL databases (Relational Database Management Systems) store data in tables with rows and columns, enforced by a rigid schema. They give you the most powerful query language in computing (SQL), the ability to join data across tables, and ACID transactions that guarantee correctness. If you need relationships between data, correctness that cannot be compromised (money, inventory, user accounts), or query patterns that will change over time, an RDBMS is the right default. PostgreSQL and MySQL handle far more scale than most engineers assume.

How It Actually Works

Core Concepts

Concept	What It Means
Table (Relation)	A structured collection of rows, each with the same columns
Schema	The predefined structure — column names, types, constraints
Primary Key (PK)	Uniquely identifies each row
Foreign Key (FK)	A reference to a PK in another table — how relationships are expressed
Index	A data structure (usually B-tree) that speeds up lookups on a column
Join	Combining rows from two or more tables based on a related column
Transaction	A group of operations that succeed or fail as a unit (ACID)

ACID Guarantees

Property	Guarantee	What Happens Without It
Atomicity	All operations in a transaction succeed, or none do	Partial writes leave data in an inconsistent state
Consistency	The database moves from one valid state to another	Constraints are violated (negative balances, orphaned records)
Isolation	Concurrent transactions don't interfere with each other	Dirty reads, lost updates, phantom rows
Durability	Committed data survives crashes	Data loss on restart

When to Choose SQL

✅ Use SQL when:

• Data has clear relationships (users → orders → items)
• You need joins across entities
• Correctness is non-negotiable (financial transactions, inventory)
• Query patterns will evolve (SQL is flexible — you can query any way you want)
• You need transactions spanning multiple tables

❌ Don't use SQL when:

• You need to store billions of rows with simple key-based access (use key-value)
• Each record is self-contained with no relationships (consider document stores)
• Write throughput exceeds what a single primary can handle and you can't shard easily
• Data is unstructured or schema changes frequently

PostgreSQL vs MySQL

Aspect	PostgreSQL	MySQL
Standards compliance	Very strict SQL compliance	Some deviations (e.g., implicit type casting)
Advanced features	JSONB, CTEs, window functions, full-text search, PostGIS	Simpler feature set, great replication
Write performance	MVCC with no read locks	InnoDB MVCC, historically faster for simple writes
Replication	Logical + physical replication	Mature async replication, Group Replication
Ecosystem	Extensions (PostGIS, pg_vector, Citus)	Widespread hosting, Aurora MySQL
Best for	Complex queries, geospatial, analytics, general purpose	High-throughput web apps, read-heavy workloads

General guidance: PostgreSQL is the stronger default for new projects. MySQL is fine if your team knows it well or you're using AWS Aurora MySQL.

Scaling SQL

SQL databases are often dismissed as "not scalable," but this is a myth at most scales:

1. Vertical scaling — A single PostgreSQL instance on modern hardware handles millions of rows and thousands of QPS
2. Read replicas — Route reads to replicas for read-heavy workloads (most apps are 90%+ reads)
3. Connection pooling — PgBouncer or ProxySQL prevents connection exhaustion
4. Partitioning — Split a large table by range or hash (e.g., partition orders by month)
5. Sharding — Split across multiple databases by a shard key (e.g., user_id). This is the hard step — cross-shard joins and transactions become painful
6. Managed services — AWS Aurora, Google Cloud SQL, and Azure Database provide automatic replication, failover, and scaling

Seeing It in Action

Scenario: Schema design for an e-commerce platform

-- Users table
CREATE TABLE users (
    user_id     BIGSERIAL PRIMARY KEY,
    email       VARCHAR(255) UNIQUE NOT NULL,
    name        VARCHAR(100) NOT NULL,
    created_at  TIMESTAMP DEFAULT NOW()
);

-- Products table
CREATE TABLE products (
    product_id  BIGSERIAL PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    price       DECIMAL(10, 2) NOT NULL,
    stock       INTEGER NOT NULL DEFAULT 0,
    category_id BIGINT REFERENCES categories(category_id)
);

-- Orders table
CREATE TABLE orders (
    order_id    BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL REFERENCES users(user_id),
    total       DECIMAL(12, 2) NOT NULL,
    status      VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    created_at  TIMESTAMP DEFAULT NOW()
);

-- Order items (junction table — many-to-many between orders and products)
CREATE TABLE order_items (
    order_id    BIGINT REFERENCES orders(order_id),
    product_id  BIGINT REFERENCES products(product_id),
    quantity    INTEGER NOT NULL,
    unit_price  DECIMAL(10, 2) NOT NULL,  -- denormalized: price at time of purchase
    PRIMARY KEY (order_id, product_id)
);

-- Indexes for common queries
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_order_items_product ON order_items(product_id);

Why SQL is right here:

• Orders reference users and products — relationships matter
• Inventory decrement must be atomic — transactions are essential
• "Show me all orders by user X with product details" requires a join
• Business will want to query in ways we can't predict — SQL flexibility

Interview Questions

1. Q: When would you choose PostgreSQL over a NoSQL database for a new project? Hint: When data has relationships, you need joins, correctness via ACID is required, or your query patterns will evolve. PostgreSQL's JSONB also handles semi-structured data well, reducing the need for a document store in many cases.

2. Q: How would you scale a PostgreSQL database handling 50K read QPS and 5K write QPS? Hint: Read replicas absorb the 50K reads (route via PgBouncer with read/write splitting). The 5K writes hit the primary. Add caching (Redis) for hot data to reduce both read and write pressure. If writes become the bottleneck, consider partitioning or sharding by a natural key.

3. Q: Explain the trade-off between normalization and denormalization in a SQL schema. Hint: Normalization eliminates redundancy (each fact stored once) — good for write-heavy, correctness-critical systems. Denormalization duplicates data to avoid joins — good for read-heavy systems. Example: storing product_name in order_items avoids a join but means a product name change doesn't retroactively update old orders (which is actually correct behavior for historical records).

4. Q: What happens when two users try to buy the last item in stock simultaneously? Hint: Without proper handling, both see stock=1 and both succeed — overselling. Fix with: UPDATE products SET stock = stock - 1 WHERE product_id = X AND stock > 0 (atomic check-and-decrement), or SELECT FOR UPDATE (pessimistic locking), or optimistic locking with a version column. This is exactly where ACID transactions prove their value.

5. Q: Why is "SQL doesn't scale" considered a myth? Where does it actually break down? Hint: A single PostgreSQL handles millions of rows and thousands of QPS. Read replicas, partitioning, and caching extend this further. It breaks down when: write throughput exceeds one primary's capacity, data is too large for partitioning on one instance, or cross-shard joins are required. At that point, consider sharding (with application-managed routing) or NewSQL.

References

• Designing Data-Intensive Applications by Martin Kleppmann — Chapters 2 and 7 on data models and transactions
• PostgreSQL Documentation — comprehensive and well-written official docs
• Use The Index, Luke — deep dive on SQL indexing and query performance

Dive Deeper

• High Performance MySQL by Schwartz, Zaitsev & Tkachenko — the definitive MySQL performance guide
• Postgres Weekly Newsletter — stay current on PostgreSQL ecosystem
• CockroachDB Blog — Living Without Atomic Clocks — how distributed SQL databases handle time