SQL Basics Every System Designer Needs

Tables and keys

Let us model a simple blog. This SQL creates three tables:

CREATE TABLE users (  id          BIGSERIAL PRIMARY KEY,  email       TEXT NOT NULL UNIQUE,  name        TEXT NOT NULL,  created_at  TIMESTAMPTZ NOT NULL DEFAULT now());CREATE TABLE posts (  id          BIGSERIAL PRIMARY KEY,  author_id   BIGINT NOT NULL REFERENCES users(id),  title       TEXT NOT NULL,  body        TEXT,  created_at  TIMESTAMPTZ NOT NULL DEFAULT now());CREATE TABLE comments (  id          BIGSERIAL PRIMARY KEY,  post_id     BIGINT NOT NULL REFERENCES posts(id),  user_id     BIGINT NOT NULL REFERENCES users(id),  text        TEXT NOT NULL,  created_at  TIMESTAMPTZ NOT NULL DEFAULT now());

• Primary key (id). A column (or columns) whose value identifies each row, and no two rows can have the same value.
• Foreign key (REFERENCES). A column that points to a row in another table. Here it says "this author_id must be a real user". The database refuses bad data.
• UNIQUE. No two rows can have the same value in this column. No two users can share an email, even if two sign-up requests arrive at the same moment. The database is your last line of defence against race conditions (bugs where timing changes the result, from lesson 4).
• NOT NULL. This column cannot be empty (null means "no value").

Auto-increment IDs vs UUIDs:

• Auto-increment IDs (1, 2, 3…) are numbers that the database counts up for you. They are small and fast to index. But they reveal how many records you have, and they are hard to generate across many servers.
• UUIDs (Universally Unique IDs, random 128-bit numbers) can be created anywhere without asking another server. But they are bigger, and random values spread writes all over the index.
• Newer time-ordered formats like UUIDv7, or Twitter-style Snowflake IDs, are a middle way. We come back to this in the URL-shortener case study.

The queries you'll use constantly

-- Read with filter, sort, limitSELECT id, title, created_atFROM postsWHERE author_id = 42ORDER BY created_at DESCLIMIT 10;-- InsertINSERT INTO users (email, name) VALUES ('asha@example.com', 'Asha');-- UpdateUPDATE posts SET title = 'New title' WHERE id = 7;-- DeleteDELETE FROM comments WHERE id = 99;-- Upsert: insert, or update if it already exists (PostgreSQL)INSERT INTO users (email, name) VALUES ('asha@example.com', 'Asha K')ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

Always use a WHERE in UPDATE and DELETE. WHERE says which rows to change. If you forget it, the command changes every row. This is one of the most famous mistakes on live systems.

Joins: combining tables

A join combines rows from two tables by matching a column, such as author_id with id. This query lists the posts of the last 7 days with the author's name:

SELECT p.title, u.name AS authorFROM posts pJOIN users u ON u.id = p.author_idWHERE p.created_at > now() - interval '7 days';

Aggregates and GROUP BY

An aggregate is a function that turns many rows into one value, such as COUNT or SUM. GROUP BY puts rows into groups, and the aggregate runs once per group. This query answers "the 10 most-commented posts this week, with author names":

SELECT p.id, p.title, u.name AS author, COUNT(c.id) AS comment_countFROM posts pJOIN users u     ON u.id = p.author_idLEFT JOIN comments c ON c.post_id = p.idWHERE p.created_at > now() - interval '7 days'GROUP BY p.id, p.title, u.nameHAVING COUNT(c.id) > 0ORDER BY comment_count DESCLIMIT 10;

• WHERE filters rows before grouping.
• HAVING filters groups after grouping.

Indexes: the most important performance tool

Without an index, WHERE author_id = 42 makes the database read every row in posts. This is called a sequential scan (or full table scan). With 100 rows, you will not notice. With 50 million rows, it takes seconds.

CREATE INDEX idx_posts_author ON posts (author_id);

Now the database keeps a sorted structure (usually a B-tree, a tree of sorted pages that we explore in Part 4) that jumps straight to author 42's posts.

Composite indexes and the "left-prefix" rule. A composite index is an index on more than one column. Our earlier query filters by author_id and sorts by created_at. One index can serve both:

CREATE INDEX idx_posts_author_created ON posts (author_id, created_at DESC);

Think of a phone book sorted by last name, then first name:

• It helps you find "Sharma" and "Sharma, Rahul".
• It does not help you find everyone named "Rahul".

In the same way, an index on (author_id, created_at) helps queries on author_id, or on author_id + created_at. It usually does not help queries on created_at alone. (PostgreSQL 18 can sometimes "skip" the first column when it has only a few different values, but you should not count on it.)

The cost of indexes:

• Every INSERT, UPDATE and DELETE must also update the indexes on that table. More indexes mean slower writes.
• Indexes take disk space and memory.
• So index the columns you actually filter, join and sort by in frequent queries, not every column.

EXPLAIN: ask the database how it runs your query

EXPLAIN shows the plan the database will use for a query. EXPLAIN ANALYZE also runs the query and shows the real time each step took.

EXPLAIN ANALYZESELECT * FROM posts WHERE author_id = 42 ORDER BY created_at DESC LIMIT 10;

Before the index:

Limit  (actual time=812.4 ms)
  -> Sort
    -> Seq Scan on posts  (rows=5,000,000)  Filter: (author_id = 42)

After the index:

Limit  (actual time=0.09 ms)
  -> Index Scan using idx_posts_author_created on posts

Here the query went from 812 ms to 0.09 ms, thousands of times faster. (These numbers are an example, and your results will differ.) Seq Scan on a big table in a frequent query is the first thing to look for.

The N+1 query problem

An ORM (Object-Relational Mapper) is a library that lets you use database rows as objects in your code, so you do not write SQL by hand. ORMs (Prisma, Sequelize, Django ORM, Hibernate, ActiveRecord) make it easy to write code like this by accident:

const posts = await Post.findAll({ limit: 20 });   // 1 queryfor (const post of posts) {  post.author = await User.findByPk(post.authorId); // 20 more queries!}

This is 1 + N queries (21 here). Each one is a network round trip (a trip to the database and back). With 100 posts and a 2 ms round trip, that is 200+ ms wasted.

Fix: fetch everything in one or two queries, with a join or with eager loading (loading the related rows together with the main rows):

const posts = await Post.findAll({ limit: 20, include: [User] }); // 1 query with a JOIN

N+1 is one of the most common reasons why people say "the database is slow" when really the app asks too many times.

Transactions: all or nothing (a first look)

A transaction is a group of database steps that run as one unit: all of them succeed, or none of them do. Transferring money needs two updates. Both must succeed, or neither:

BEGIN;UPDATE accounts SET balance = balance - 500 WHERE id = 1 AND balance >= 500;UPDATE accounts SET balance = balance + 500 WHERE id = 2;COMMIT;   -- or ROLLBACK if anything failed

If the server crashes between the two updates, the database undoes the first one. This is the "A" (Atomicity) in ACID. Atomicity means "all or nothing". ACID is the set of four promises that a transaction database makes. Part 4 covers the full ACID story and isolation levels.

Connections: the hidden limit

Every app server talks to the database over connections. A connection is an open channel between the app and the database. Each connection uses real memory on the database server. In PostgreSQL, each connection is a separate process that uses several MB. Databases have a maximum number of connections (PostgreSQL's default is 100, and many setups use a few hundred).

Here is the classic trap:

Adding more app servers can take down the database, even though the number of queries per user did not change. Serverless functions (small pieces of code that the cloud starts on demand) make this worse, because every function instance may open its own connection.

Fixes:

• Connection pooling in the app. A connection pool keeps a few connections open and reuses them. Use small pools (HikariCP for Java, or the built-in pools in most ORMs).
• A pooler in front of the database (for example, PgBouncer for PostgreSQL). Thousands of app connections share a few dozen real database connections.
• Remember that a bigger pool is not always faster. The database only has so many CPU cores. After some point, more queries at the same time just wait for each other.

OLTP vs OLAP

OLTP (Online Transaction Processing) means many small, quick reads and writes, such as orders and logins. OLAP (Online Analytical Processing) means a few big queries that scan a lot of data to make reports.

Running heavy analytics on your main (OLTP) database can slow down every user's checkout. That is why companies copy data into a separate analytics warehouse (a database built for reports), or at least run reports on a read replica (a copy of the database that only answers reads).