Relational Databases Explained: From Tables to Sharding

Part 1 — What Is a Relational Database?

A relational database stores data as a set of tables, where each table represents a particular type of entity and its attributes.

Tables can be connected to each other through shared values called keys. This relationship between tables is what gives a relational database its name.

For example, Suppose we are designing a simplified Twitter-like system.

Before creating the database schema, we first need to answer a simple question:

For our Twitter-like system, the core entities are:

These entities give us our initial set of tables — users, tweets, likes, follows.

But identifying entities is only the first step. We also need to distinguish an entity from its attributes.

Similarly, a tweet may have:

So, when reading a problem statement, a useful first pass is:

Example tables

Here is how the tables would look like in a database.

Users — users table (each row = one user):

Tweets — tweets table (each row = one post):

Here, both tables have their own primary key: id.

The id in the users table uniquely identifies each user, while user_id in the tweets table is a foreign key that references users.id.

This creates a relationship between the two tables, allowing a single user to have multiple tweets.

We will discuss the different types of keys and how to identify them in the later part of this blog.

Part 2 — How Data Is Stored

2.1 Tables, Rows, Columns

A table is a grid, think of it like a spreadsheet where each row represents one record (one user, one tweet), and each column represents one attribute of that record, with a defined data type.

CREATE TABLE users (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    username   VARCHAR(30)  NOT NULL,
    email      VARCHAR(255) NOT NULL,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

For example, the users table might contain:

2.2 Primary Key / Foreign Key

A primary key is a column (or combination of columns) that uniquely identifies each row in a table. No two rows can have the same primary-key value, and a primary-key value cannot be NULL.

For example, in the users table, id is the primary key:

SELECT * FROM users WHERE id = 10;

More on how indexes find rows quickly in the next section.

Now suppose we want to store tweets. Every tweet belongs to a user, so the tweets table needs a way to refer to the user who created it.

CREATE TABLE tweets (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    user_id    INT NOT NULL,
    content    VARCHAR(280) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Here, tweets.user_id refers to users.id.

users                         tweets

id  username                  id   user_id   content
──  ────────                  ──   ───────   ───────
42  alice                     101     42     Hello!
                              102     42     My second tweet

This is where the foreign key comes in.

A foreign key is a constraint that tells the database:

"tweets.user_id must refer to an existing users.id."

A foreign key is not the same thing as an index.

• Primary key → uniquely identifies a row and is backed by an index.
• Foreign key → enforces a relationship between tables.
• Index → helps the database find rows efficiently. We will learn more on this in later parts of the chapter.

2.3 Relationships

The relationships between entities often come naturally from the requirements of an application.

For example, in a social media application:

• A user can have many tweets.
• A tweet can be liked by many users.
• A user can have many followers.
• A user can follow many other users.

These relationships help us understand how the different entities in our system are connected.

Once we identify these relationships, we can use them to design our database schema. The type of relationship determines how we structure our tables and where we place primary keys, foreign keys, and additional tables.

With primary and foreign keys in place, three relationship shapes cover almost everything:

A many-to-many relationship always needs a junction (or "join") table in between, since neither side can hold a single foreign key pointing at "many" rows:

For example, a user can like many tweets, and a tweet can be liked by many users.

A single foreign-key column cannot represent this relationship. Instead, we create a junction table that stores each relationship:

CREATE TABLE likes (
    user_id    INT NOT NULL,
    tweet_id   INT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, tweet_id),
    FOREIGN KEY (user_id)  REFERENCES users(id),
    FOREIGN KEY (tweet_id) REFERENCES tweets(id)
);

CREATE TABLE followers (
    follower_id INT NOT NULL,
    followee_id INT NOT NULL,
    PRIMARY KEY (follower_id, followee_id),
    FOREIGN KEY (follower_id) REFERENCES users(id),
    FOREIGN KEY (followee_id) REFERENCES users(id)
);

A junction table turns a many-to-many relationship into two one-to-many relationships.

For example, in the likes table, each row represents one user liking one tweet:

This means:

• User 1 liked tweets 101 and 102.
• User 2 liked tweet 101.

The composite primary key:

PRIMARY KEY (user_id, tweet_id)

ensures that the same user cannot like the same tweet more than once.

The two foreign keys connect the relationship back to the original tables:

users                  likes                  tweets

  User 1 ────────────► (1, 101) ────────────► Tweet 101
  User 1 ────────────► (1, 102) ────────────► Tweet 102
  User 2 ────────────► (2, 101) ────────────► Tweet 101

The same pattern applies to followers. Each row represents one relationship between two users:

2.4 ER Diagram

As our database grows, we will have many tables and relationships between them. It can become difficult to understand how all these tables are connected just by looking at the table definitions.

This is where an ER (Entity-Relationship) diagram helps.

An ER diagram is a visual representation of the database. It shows:

• The different entities/tables
• The attributes/columns of each table
• The primary keys (PKs)
• The foreign keys (FKs)
• The relationships between tables

The full schema, as an entity-relationship diagram: For our Twitter-like system, an ER diagram could show something like:

If you mean color-code the ER diagram by entity type while keeping the relationships clear:

erDiagram
  USERS ||--o{ TWEETS : posts
  USERS ||--o{ LIKES : likes
  TWEETS ||--o{ LIKES : "liked by"
  USERS ||--o{ FOLLOWERS : "follows / followed by"

  USERS {
    int id PK
    string username
    string email
    datetime created_at
  }

  TWEETS {
    int id PK
    int user_id FK
    string content
    datetime created_at
  }

  LIKES {
    int user_id FK
    int tweet_id FK
    datetime created_at
  }

  FOLLOWERS {
    int follower_id FK
    int followee_id FK
  }

  style USERS fill:#111827,stroke:#F97316,stroke-width:3px,color:#FFFFFF
  style TWEETS fill:#111827,stroke:#06B6D4,stroke-width:3px,color:#FFFFFF
  style LIKES fill:#111827,stroke:#EAB308,stroke-width:3px,color:#FFFFFF
  style FOLLOWERS fill:#111827,stroke:#A855F7,stroke-width:3px,color:#FFFFFF

Part 3 — ACID Transactions

ACID stands for Atomicity, Consistency, Isolation, and Durability. These are four guarantees that make database transactions reliable. They ensure that a group of database operations either completes correctly as a whole or does not leave the database in an incorrect or partially updated state.

3.1 Why We Need This at All

Suppose posting a tweet actually requires two writes: insert the tweet, and increment a tweet_count column on the user's row. What happens if the first write succeeds and the app crashes before the second one runs? Now the tweet exists, but the user's count is wrong forever. ACID is the set of guarantees a database gives you so that a group of writes behaves as a single, indivisible unit — either all of it happens, or none of it does.

Part 4 — Database Indexes

4.1 Why Indexes Are Needed

Suppose you run this query on a tweets table with 50 million rows:

SELECT * FROM tweets WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20;

Without any help from an index, the database has to read every row in the table, check whether user_id equals 42, and keep the matching rows.

This is called a full table scan.

The larger the table, the more rows the database has to check. For example, a query that takes only a few milliseconds on a 1,000-row table can take significantly longer when the same table contains millions of rows.

Without an index — full table scan

An index is a separate, ordered data structure that lets the database jump straight to matching rows instead of checking every one.

With an index — direct row lookup

4.2 B-Tree Basics

The default index structure in almost every relational database (including PostgreSQL) is a B-Tree — a balanced, sorted tree where every lookup takes roughly the same, small number of steps regardless of table size.

B-Tree index — exact match and range query

Because the values inside each node are kept in sorted order, the database can repeatedly narrow down which branch to follow — similar to how you'd find a name in a printed phone book by jumping to roughly the right page, rather than reading every page from the start. This is also why B-Trees are good at range queries (created_at > '2026-01-01'), not just exact matches — the sorted order means a range of matching values sits together, contiguously, inside the tree.

4.3 How an Index Changes the Read Path

Creating an index is a single statement:

CREATE INDEX idx_tweets_user_id ON tweets(user_id);

From this point on, the database's query planner can choose between a full table scan and an index scan for any query filtering on user_id — and for a selective filter (one that matches a small fraction of rows), the index scan wins by orders of magnitude. You can see which one PostgreSQL actually chose with:

To check whether PostgreSQL is using a full table scan or an index scan, use EXPLAIN before your query:

EXPLAIN SELECT * FROM tweets WHERE user_id = 42;

It shows the execution plan PostgreSQL intends to use for the query.

4.4 Types of Indexes, by Query Pattern

Different query shapes call for different index types.

4.5 Real-World Examples: Twitter/X-Style Queries

Back to our running schema — let's see how different user queries map to SQL statements and indexes.

With indexing covered, the next two sections put it to work: how Postgres groups writes into transactions, and how it stops concurrent writers from stepping on each other.

Part 5 — Transactions in Postgres

5.1 BEGIN, COMMIT, ROLLBACK

Back to the tweet-plus-counter example from Part 3 — this is exactly what wrapping writes in a transaction looks like in practice:

If anything goes wrong partway through — a constraint violation, an application error, a deliberate check that fails — the transaction can be undone entirely instead of committed:

BEGIN;

INSERT INTO likes (user_id, tweet_id)
VALUES (7, 101);

UPDATE tweets
SET like_count = like_count + 1
WHERE id = 101;

-- If everything succeeds
COMMIT;

-- If something goes wrong instead
ROLLBACK;

-- COMMIT saves both changes.
-- ROLLBACK undoes both changes.

This is Atomicity, made concrete: everything between BEGIN and COMMIT succeeds together, or ROLLBACK undoes all of it together.

5.2 Isolation Levels

Atomicity tells you what happens to one transaction's own writes. Isolation levels control what a transaction can see of other, concurrent transactions' uncommitted or recently-committed changes.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- ... queries here all see one consistent snapshot ...
COMMIT;

Isolation levels answer "what can I see," but they don't by themselves stop two transactions from both trying to write to the same row at the same moment — that's what locking is for.

Part 6 — Locking

6.1 Why Locking Is Needed

Suppose two users like the same tweet at almost the same instant. Both transactions read likes_count = 10, both compute 10 + 1 = 11, and both write back 11 — the correct answer was 12, but one increment was silently lost. This is a lost update, and it's exactly the kind of concurrency bug that isolation levels alone don't fully prevent for read-then-write patterns. Locking is how the database prevents it.

6.2 Pessimistic Locking: Shared and Exclusive Locks

BEGIN;
SELECT likes_count FROM tweets WHERE id = 501 FOR UPDATE;
-- this row is now locked — other transactions trying to write to it must wait
UPDATE tweets SET likes_count = likes_count + 1 WHERE id = 501;
COMMIT;

FOR UPDATE takes an exclusive lock — only one transaction can hold it on a given row at a time, and it blocks both other readers-for-update and other writers. A shared lock is weaker: multiple transactions can hold a shared lock on the same row simultaneously (useful when several transactions just need to read a stable value without letting anyone else change it out from under them), but a shared lock still blocks any transaction trying to take an exclusive lock on that row.

-- Shared lock: many readers can hold this at once, but no writer can
-- take an exclusive lock until all shared locks are released.
SELECT likes_count FROM tweets WHERE id = 501 FOR SHARE;

6.3 SKIP LOCKED and NOWAIT

Two refinements matter in practice, especially for job-queue-style tables:

-- NOWAIT: fail immediately instead of waiting for a locked row.
SELECT * FROM tweets WHERE id = 501 FOR UPDATE NOWAIT;

-- SKIP LOCKED: silently skip rows that are already locked by another
-- transaction, and return the next available one instead.
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

SKIP LOCKED is exactly the pattern behind multiple worker processes safely pulling from the same job queue table — each worker grabs a different unlocked row instead of piling up waiting for the same one.

6.4 Optimistic Locking

-- The tweets table has a `version` column.
UPDATE tweets
SET likes_count = likes_count + 1, version = version + 1
WHERE id = 501 AND version = 7;

-- If this affects 0 rows, someone else updated the row first —
-- re-read the current version and retry.

With transactions and locking both in place, every write to our schema is now protected — both from partial failure (Atomicity) and from concurrent corruption (locking). Only now does it make sense to ask: what happens when one database server isn't enough?

Part 7 — Scaling Relational Databases

Everything up to this point — indexes, transactions, locks — assumed one database server. This section is about what to do once that one server isn't enough, and deliberately comes last: it's much easier to reason about what you're scaling and why now that you know what a transaction is protecting and what a lock is preventing.

7.1 Vertical Scaling

The simplest option: give the existing server more CPU, memory, or faster disks. No architectural change, no new failure modes — but it has a hard ceiling (the biggest machine a cloud provider offers) and doesn't help if the bottleneck is concurrent connections or geographic latency rather than raw compute.

flowchart LR
    DB1@{ shape: cyl, label: "4 CPU<br/>16 GB RAM<br/>1 TB<br/>1K QPS" }
    DB2@{ shape: cyl, label: "8 CPU<br/>32 GB RAM<br/>2 TB<br/>3K QPS" }
    DB3@{ shape: cyl, label: "32 CPU<br/>128 GB RAM<br/>8 TB<br/>10K QPS" }

    DB1 -->|Scale Up| DB2
    DB2 -->|Scale Up| DB3

    style DB1 fill:#3B82F6,stroke:#2563EB,color:#fff,stroke-width:2px
    style DB2 fill:#3B82F6,stroke:#2563EB,color:#fff,stroke-width:2px
    style DB3 fill:#3B82F6,stroke:#2563EB,color:#fff,stroke-width:2px

If your Mermaid renderer supports classDef sizing, you can additionally define separate classes for small/medium/large.

7.2 Read Replicas and Read/Write Splitting

If the workload is read-heavy (a Twitter-style app reads timelines far more often than it writes tweets), you can add read replicas — copies of the primary database that continuously receive its writes via replication, and serve read queries independently.

flowchart TB
  App[Application] -->|writes| Primary[(Primary DB)]
  Primary -->|replication| Replica1[(Read Replica 1)]
  Primary -->|replication| Replica2[(Read Replica 2)]
  App -->|reads| Replica1
  App -->|reads| Replica2

  %% Application / Client-Server
  style App fill:#6B7280,stroke:#4B5563,color:#fff

  %% Primary DB
  style Primary fill:#3B82F6,stroke:#2563EB,color:#fff

  %% Read Replicas
  style Replica1 fill:#8B5CF6,stroke:#6D28D9,color:#fff
  style Replica2 fill:#8B5CF6,stroke:#6D28D9,color:#fff

  %% Write path
  linkStyle 0 stroke:#6B7280,stroke-width:2px

  %% Replication paths
  linkStyle 1 stroke:#8B5CF6,stroke-width:2px
  linkStyle 2 stroke:#8B5CF6,stroke-width:2px

  %% Read paths
  linkStyle 3 stroke:#8B5CF6,stroke-width:2px
  linkStyle 4 stroke:#8B5CF6,stroke-width:2px

The application then does read/write splitting: all writes go to the primary, all reads go to a replica. This multiplies read capacity without touching the write path at all — but replication is typically asynchronous, so a replica can lag slightly behind the primary, meaning a read immediately after a write might not reflect it yet.

7.3 Partitioning

Partitioning splits one large table into smaller physical pieces called partitions, while keeping them on the same database server. PostgreSQL automatically routes each row to the appropriate partition based on the partition key.

A common approach for a tweets table is range partitioning by date:

CREATE TABLE tweets (
    id         INT NOT NULL,
    user_id    INT NOT NULL,
    content    VARCHAR(280),
    created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE tweets_2024
    PARTITION OF tweets
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE tweets_2025
    PARTITION OF tweets
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

CREATE TABLE tweets_2026
    PARTITION OF tweets
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

You don't need to specify the partition when inserting data. PostgreSQL automatically determines where the row belongs:

INSERT INTO tweets (id, user_id, content, created_at)
VALUES (1, 101, 'Hello', '2026-06-15');

The row is automatically stored in:

tweets_2026

Querying Partitioned Data

When a query filters by the partition key, PostgreSQL can use partition pruning to skip partitions that cannot contain the requested data.

For example:

SELECT *
FROM tweets
WHERE created_at >= '2026-01-01'
  AND created_at < '2026-02-01';

Only tweets_2026 needs to be scanned because the query only asks for data from 2026.

What If a Query Spans Multiple Partitions?

A query can span multiple partitions, and PostgreSQL simply queries each relevant partition and combines the results.

For example:

SELECT *
FROM tweets
WHERE created_at >= '2025-12-01'
  AND created_at < '2026-02-01';

This range crosses the boundary between 2025 and 2026:

tweets
│
├── tweets_2025  → Dec 2025 ──┐
│                              │
└── tweets_2026  → Jan 2026 ──┤
                               ↓
                         Results combined

PostgreSQL can prune all other partitions and scan only:

tweets_2025
tweets_2026

So partitioning does not mean a query can only access one partition. A query can access as many partitions as necessary.

Each Partition Has Its Own Index

Each partition can have its own physical indexes.

For example:

CREATE INDEX idx_tweets_2025_user_id
ON tweets_2025 (user_id);

CREATE INDEX idx_tweets_2026_user_id
ON tweets_2026 (user_id);

Conceptually:

tweets
│
├── tweets_2024
│    └── index on user_id
│
├── tweets_2025
│    └── index on user_id
│
└── tweets_2026
     └── index on user_id

You can also create the index on the partitioned parent:

CREATE INDEX idx_tweets_user_id
ON tweets (user_id);

PostgreSQL creates corresponding indexes on the individual partitions.

So physically, there is not one giant index containing all rows. Each partition maintains its own index structure.

If a query spans two partitions, PostgreSQL can use the index of each relevant partition:

Query: user_id = 101
       │
       ├──→ tweets_2025 → index_2025 → results
       │
       └──→ tweets_2026 → index_2026 → results
                                      │
                                      ↓
                                Combined results

What Happens When a New Year Arrives?

Partitions are not automatically created when a new year begins.

For example, if tweets_2027 does not exist and you try:

INSERT INTO tweets (id, user_id, content, created_at)
VALUES (2, 101, 'Hello 2027', '2027-01-05');

PostgreSQL will reject the insert because there is no partition that can store the row:

ERROR: no partition of relation "tweets" found for row

Therefore, new partitions need to be created ahead of time, either manually or through automation.

For example:

CREATE TABLE tweets_2027
    PARTITION OF tweets
    FOR VALUES FROM ('2027-01-01') TO ('2028-01-01');

In production, this can be automated using a scheduled job that creates future partitions before they are needed.

Why Use Partitioning?

Partitioning can provide several benefits:

• Partition pruning — queries can skip irrelevant partitions.
• Smaller indexes — each partition has its own index structure.
• Easier maintenance — old partitions can be archived or dropped independently.
• Better data organization — data can be separated by time, tenant, region, etc.

7.4 Sharding

Sharding goes further: splitting data across multiple separate database servers, each holding a subset of rows, chosen by a shard key.

flowchart TB
  App[Application] -->|route by user_id| Router{"Shard Router"}
  Router --> ShardA[(Shard A<br/>users 0–999)]
  Router --> ShardB[(Shard B<br/>users 1000–1999)]
  Router --> ShardC[(Shard C<br/>users 2000–2999)]

  %% Application / Client-Server
  style App fill:#6B7280,stroke:#4B5563,color:#fff

  %% Shard Router
  style Router fill:#3B82F6,stroke:#2563EB,color:#fff

  %% Shards
  style ShardA fill:#8B5CF6,stroke:#6D28D9,color:#fff
  style ShardB fill:#8B5CF6,stroke:#6D28D9,color:#fff
  style ShardC fill:#8B5CF6,stroke:#6D28D9,color:#fff

  %% Application → Router
  linkStyle 0 stroke:#6B7280,stroke-width:2px

  %% Router → Shards
  linkStyle 1 stroke:#8B5CF6,stroke-width:2px
  linkStyle 2 stroke:#8B5CF6,stroke-width:2px
  linkStyle 3 stroke:#8B5CF6,stroke-width:2px

Sharding tweets by user_id means one user's tweets always live on the same shard — a query for "this user's timeline" only ever needs to hit one server. The cost is real complexity: a query joining across users on different shards (e.g. "show tweets from everyone I follow") now has to fan out across multiple servers and merge results in the application, since the database itself can no longer do that join in one place.

7.5 When Each Approach Helps

Part 8 — Putting Everything Together: A Twitter/X-Like Example

Nothing here is new — every single piece was introduced earlier in this guide, in the order it needed to be understood.

Part 9 — Common Mistakes & Interview Questions

9.1 Common Mistakes

9.2 Interview Questions

Part 10 — FAQ