Databases, from the ground up#

This is the guide I wish someone had handed me before my first “design the schema” interview question. It goes from “what even is a database” to why your production Postgres instance falls over at 3am, and it tries not to skip the boring-but-important parts in between.

Each topic below is its own page. Use the menu on the left, or browse by section here.

Start here#

What’s a database, really

Strip away the buzzwords and a database is just an organized pile of information kept somewhere it won’t…

what-is-a-database
What a DBMS is actually doing all day

A DBMS earns its keep by doing a handful of unglamorous jobs, constantly, without you noticing. When you…

what-is-a-dbms
Why not just use files?

You genuinely can build a system on flat files, CSVs, JSON blobs, whatever. Plenty of small tools do…

files-vs-dbms

Data models#

The five shapes data can take

Picking a data model is picking which questions will be easy to ask later and which will be painful…

data-models
“SQL vs. NoSQL” isn’t really the question

This gets framed as a rivalry, but it’s closer to comparing a screwdriver to a whole toolbox labeled “not”…

data-models

Under the hood#

ACID, unpacked

ACID gets recited like a magic word in interviews, but each letter is answering a specific, concrete fear…

under-the-hood
Normal forms, without the textbook language

Normalization is really just “don’t write the same fact in two places.” If a customer’s address changes,…

under-the-hood
Indexes: the whole reason queries aren’t slow

Without an index, finding a row means the database checks every single one, a full table scan. On a table…

under-the-hood
Transactions and what happens when two writes collide

A transaction is a group of operations that either all succeed or all fail together, you saw the “A” in…

under-the-hood
The CAP theorem, in one paragraph

Once a database is spread across more than one machine, the network between those machines will eventually…

under-the-hood

At scale#

Replication: copies for safety and speed

A replica is a second copy of your database that stays in sync with the original, usually by streaming the…

at-scale
Sharding: when one machine isn’t enough, period

Replication copies the same data everywhere. Sharding does the opposite, it splits the data itself across…

at-scale

Reference#

Popular systems, and where they actually shine

None of these “win” outright, each was built with a specific set of trade-offs in mind, and the honest…

reference
Glossary

Key terms used throughout this guide.

reference

Operating in production#

Connection pooling#

Why you need a pool at all

Every connection to a database costs memory and a bit of CPU on the server side, even when it's sitting idle doing nothing. Most databases…

connection-pooling-why-you-need-a-pool-at-all
The three pooling modes

If you're using PgBouncer (or anything similar) you'll pick one of these:

connection-pooling-pooling-modes
Sizing a pool

A bigger pool is not automatically better. Past a certain point more connections just means more contention on the same CPU and disk, and…

connection-pooling-pool-sizing
Serverless makes this worse

Traditional app servers hold a small, stable number of long-lived processes. Serverless spins up and tears down instances constantly, and…

connection-pooling-pooling-serverless
Idle connections and timeouts

A connection sitting idle for an hour is still holding a slot in the pool that a busy request can't use. Set an idle timeout so connections…

connection-pooling-idle-timeouts
One pool per service, or one shared pool

If several services talk to the same database, each with its own pool, the database still sees the sum of all of them. A shared pooler in…

connection-pooling-pool-per-service

Backups and restore#

Logical vs physical backups

Type

backups-restore-logical-vs-physical-backups
Point in time recovery

A daily snapshot only gets you back to last night. Point in time recovery (PITR) replays the write-ahead log on top of a base backup, so…

backups-restore-pitr
The restore drill

Backups fail silently more often than they fail loudly. A backup job can run green for months while producing a file that can't actually be…

backups-restore-restore-drill
How much history to keep

There's no universal answer, but a common pattern:

backups-restore-backup-retention
Full, incremental, and differential

Type

backups-restore-incremental-backups
Storing backups outside the region

A backup stored in the same region, or worse the same data center, as the database doesn't help if that whole region goes down.…

backups-restore-cross-region-backups

Zero downtime migrations#

The problem is locks, not the change itself

Most schema changes are quick. The dangerous ones are the ones that lock the whole table while they run, which on a big table can mean…

zero-downtime-migrations-the-problem-is-locks-not-the-change-itself
The expand and contract pattern

The trick for anything risky is to split one change into several small, individually safe steps, deploying code in between.

zero-downtime-migrations-expand-contract
Adding NOT NULL without locking the table

Never run a migration and a deploy at the exact same moment, give the schema change time to finish first

zero-downtime-migrations-not-null-workaround
A few rules that save you at 3am

Never run a migration and a deploy at the exact same moment, give the schema change time to finish first

zero-downtime-migrations-migration-tips
Renaming a column safely

A rename is instant on the database side, but breaks any code still using the old name the moment it runs. Do it in steps instead:

zero-downtime-migrations-renaming-columns
Dropping a column safely

Dropping is instant too, and just as risky if anything still reads it. Deploy the code change that stops using the column first, wait a…

zero-downtime-migrations-dropping-columns

Monitoring and alerting#

The metrics that actually matter

Metric

monitoring-alerting-the-metrics-that-actually-matter
Turning on the slow query log

Most databases can log any query over a threshold automatically.

monitoring-alerting-slow-query-log
What good monitoring actually looks like

Dashboard showing connections, replication lag, and disk space, checked without needing to dig

monitoring-alerting-what-good-monitoring-looks-like
Setting up dashboards people actually open

A dashboard nobody looks at until an incident is already happening isn't doing its job. Keep the main one small, the handful of metrics…

monitoring-alerting-dashboards
Alert fatigue

An alert that fires every day and gets ignored trains people to ignore alerts. If something pages a human, it should mean a human needs to…

monitoring-alerting-alert-fatigue
On-call runbooks

For each alert that can page someone, write down what it means and the first two or three things to check, before an incident, not during…

monitoring-alerting-runbooks

Common production incidents#

Deadlocks

Two transactions each hold a lock the other one needs, and both wait forever. The database eventually notices and kills one of them for you.

common-incidents-deadlocks
Lock contention

Not a deadlock, just a lot of transactions queuing up for the same row or table. Feels like the database "randomly" got slow.

common-incidents-lock-contention
The N+1 query problem

Not a database problem exactly, but it shows up as one. Your app loads a list of 50 orders, then makes a separate query for each order's…

common-incidents-n-plus-one
A migration that locks the table

Covered in more depth on the migrations page, but the incident pattern is always the same: a migration that seemed fine in staging on a…

common-incidents-runaway-migration
Running out of disk

Databases handle running out of disk very badly, writes fail, and depending on the system, it can be difficult to even run the DELETE you…

common-incidents-out-of-disk
Replica lag suddenly spikes

Usually caused by a large write or batch job hitting the primary, the replica falls behind trying to keep up. If your app reads from…

common-incidents-replica-lag-spike

Caching strategies#

The main patterns

Pattern

caching-strategies-the-main-patterns
Cache aside in practice

The expire=300 matters as much as the caching itself. Without it, a cache never forgets, and you'll be debugging "why is this data six…

caching-strategies-cache-aside-example
Cache invalidation

The famously hard part. Two honest approaches:

caching-strategies-invalidation
What not to cache

Anything where a stale read causes real harm, account balances, inventory counts right before checkout

caching-strategies-what-not-to-cache
Read through caching

Similar to cache aside, but the cache itself, not the app, is responsible for fetching from the database on a miss. The app only ever talks…

caching-strategies-read-through
Cache stampede

A popular cache entry expires, and a burst of simultaneous requests all miss at once and all hit the database at the same time trying to…

caching-strategies-cache-stampede

Security and access#

Authentication and least privilege#

Why the app user shouldn't be an admin

It's tempting to connect as the same superuser you use to run migrations, one set of credentials, one less thing to manage. The problem…

auth-least-privilege-why-the-app-user-shouldnt-be-an-admin
Splitting roles by job

A reasonable minimum split for most production systems:

auth-least-privilege-role-separation
Setting this up

Granting privileges on today's tables doesn't cover tables created tomorrow. It's easy to grant access carefully at setup, then six months…

auth-least-privilege-grant-example
Default privileges catch you later

Granting privileges on today's tables doesn't cover tables created tomorrow. It's easy to grant access carefully at setup, then six months…

auth-least-privilege-defaults-matter
Row level security

Beyond table-level grants, some databases can restrict access to specific rows based on who's asking, not just what table they're touching.

auth-least-privilege-row-level-security
Service accounts vs human accounts

An app's database credentials and a person's personal login should never be the same thing. Service accounts should be scoped tightly to…

auth-least-privilege-service-vs-human

Encryption#

The three layers

Layer

encryption-the-three-layers
Encryption at rest

Almost every managed cloud database offers this as a checkbox, it encrypts the actual disk. It's close to free to turn on and protects…

encryption-at-rest
Encryption in transit

This means requiring SSL/TLS on the connection between your app and the database, not just supporting it.

encryption-in-transit
Column level encryption

For fields that are sensitive enough that even a database admin, or an attacker with valid read access, shouldn't be able to just read them.

encryption-column-level
Key management basics

Encryption is only as strong as how well the key is protected. A dedicated key management service, separate from the database and the app,…

encryption-key-management
Rotating encryption keys

Like credentials, keys shouldn't live forever. Rotation for encryption keys is trickier than for passwords though, data encrypted with an…

encryption-key-rotation

Secrets and credential management#

The default bad habit

Database credentials often end up hardcoded in a config file, committed to git at some point in a project's history, and never rotated…

secrets-management-the-default-bad-habit
Where secrets should actually live

Option

secrets-management-where-secrets-go
Rotating credentials

Rotation only works if it's boring and routine, not a special event that happens once after an incident.

secrets-management-rotation
Who gets access to secrets

Production credentials accessible to the deploy pipeline, not to every developer's laptop

secrets-management-least-access
Secrets in CI/CD pipelines

Build and deploy pipelines often need database credentials too, to run migrations, for example. Store them in the CI platform's own secrets…

secrets-management-secrets-cicd
Secrets in containers and Kubernetes

A raw Kubernetes Secret is only base64 encoded, not encrypted, by default. For anything production facing, pair it with encryption at rest…

secrets-management-secrets-kubernetes

Scaling in practice#

Read/write splitting in practice#

Why split at all

A single database server can only take so many queries before it runs out of CPU, memory, or disk I/O. Most apps read far more than they…

read-write-splitting-why-split-at-all
The basic shape

The primary is the only place writes are allowed. Replicas get a copy of every write through replication, and only serve reads.

read-write-splitting-basic-shape
Where the routing decision gets made

Layer

read-write-splitting-routing-layer
The staleness problem

Replication isn't instant. A write to the primary takes a moment to reach a replica, so a read right after a write can miss it entirely.

read-write-splitting-staleness-problem
Read your own writes

A few common fixes for the staleness problem above:

read-write-splitting-read-your-writes
Picking which replica

With more than one replica, you need a way to choose. Round robin is simplest and usually fine. Some setups route by least connections, or…

read-write-splitting-picking-replica

Consistency trade offs at scale#

Strong vs eventual, the short version

Model

consistency-tradeoffs-at-scale-strong-vs-eventual-the-short-version
Why you can't have both, really

Once data lives on more than one machine, a network hiccup between them is inevitable eventually. When that happens, the system has to…

consistency-tradeoffs-at-scale-why-cant-have-both
Read your own writes, at least

A lot of real systems don't need full strong consistency everywhere, they need something narrower: a user should always see their own…

consistency-tradeoffs-at-scale-read-your-writes-consistency
Per system, what you actually get

It doesn't mean "sometimes wrong forever." It means there's a window, usually milliseconds, occasionally longer under load, where different…

consistency-tradeoffs-at-scale-per-system-consistency
What "eventual" actually means in practice

It doesn't mean "sometimes wrong forever." It means there's a window, usually milliseconds, occasionally longer under load, where different…

consistency-tradeoffs-at-scale-what-eventual-means
Picking a level per piece of data

Not every table needs the same consistency level. An account balance probably wants strong consistency. A "like count" on a post can…

consistency-tradeoffs-at-scale-picking-level-per-data

Multi-region considerations#

Why go multi-region at all

Two usual reasons: latency, putting data physically closer to users so requests don't cross an ocean, and resilience, surviving an entire…

multi-region-considerations-why-go-multi-region-at-all
The one fact that drives everything else here

Network round trips between distant regions take real, unavoidable time, tens to hundreds of milliseconds. No amount of good engineering…

multi-region-considerations-one-fact-drives-everything
Active-passive vs active-active

Setup

multi-region-considerations-active-active-vs-passive
Where the primary lives

In an active-passive setup, picking the primary region matters more than it seems. It should usually be wherever your heaviest write…

multi-region-considerations-where-primary-lives
Replication across regions

Almost always asynchronous across regions, synchronous cross-region replication would mean every write waits on the slowest region's round…

multi-region-considerations-cross-region-replication
Conflict handling in active-active

If two regions both accept a write to the same record before syncing, you get a conflict. Same techniques as any distributed consistency…

multi-region-considerations-conflict-handling-active-active

Database deep dives#

PostgreSQL#

What it is

PostgreSQL is an open source, object-relational database that's been under active development since the 1980s (as POSTGRES, then…

postgresql-what-it-is
How it works under the hood

Postgres uses a process-per-connection model: every client connection gets its own backend process, not a thread. It stores data in on-disk…

postgresql-pg-how-it-works
Setting it up

JSONB for schema-flexible document data, indexable

postgresql-pg-setup
Your first queries

JSONB for schema-flexible document data, indexable

postgresql-pg-first-queries
Functionality that sets it apart

JSONB for schema-flexible document data, indexable

postgresql-pg-functionality
Indexing options

Index type

postgresql-pg-indexes

MySQL#

What it is

MySQL is an open source relational database, owned by Oracle since 2010, with MariaDB as its most popular community fork. It's the M in the…

mysql-what-it-is
How it works under the hood

MySQL's defining trait is pluggable storage engines: the SQL layer on top is the same, but the actual storage and locking behavior…

mysql-mysql-how-it-works
Setting it up

Classic binlog-based async replication

mysql-mysql-setup
Your first queries

Classic binlog-based async replication

mysql-mysql-first-queries
Notable functionality

Classic binlog-based async replication

mysql-mysql-functionality
Indexing and the clustered key

InnoDB tables are clustered on the primary key, meaning row data is physically stored in primary key order. A poorly chosen primary key…

mysql-mysql-indexes

MongoDB#

What it is

MongoDB is a document database: instead of rows in tables, it stores JSON-like documents (BSON, a binary superset of JSON) in collections.…

mongodb-what-it-is
How it works under the hood

Documents live in collections, collections live in databases. Internally, MongoDB stores BSON on disk via the WiredTiger storage engine,…

mongodb-mongo-how-it-works
Setting it up

Multi-stage data processing, similar in spirit to SQL's GROUP BY but far more composable

mongodb-mongo-setup
Your first queries

Multi-stage data processing, similar in spirit to SQL's GROUP BY but far more composable

mongodb-mongo-first-queries
Notable functionality

Multi-stage data processing, similar in spirit to SQL's GROUP BY but far more composable

mongodb-mongo-functionality
Tunable consistency

Read/write concern

mongodb-mongo-consistency

Redis#

What it is

Redis is an in-memory key-value store. It's often called a database, and it can be configured to act like one, but its default behavior is…

redis-what-it-is
How it works under the hood

Redis is single-threaded for command execution (newer versions parallelize I/O, but command execution is still serial), which is exactly…

redis-redis-how-it-works
Setting it up

Strings, hashes, lists, sets, sorted sets

redis-redis-setup
Your first commands

Strings, hashes, lists, sets, sorted sets

redis-redis-first-commands
Notable functionality

Strings, hashes, lists, sets, sorted sets

redis-redis-functionality
Persistence modes

Mode

redis-redis-persistence

SQLite#

What it is

SQLite is a relational database that runs inside your application process as a library, not a separate server. The entire database is one…

sqlite-what-it-is
How it works under the hood

Because SQLite is embedded, "connecting" to it just means opening a file. Concurrency is handled with file-level locking: historically a…

sqlite-sqlite-how-it-works
Setting it up

There's no server to install for most languages, SQLite ships as a library.

sqlite-sqlite-setup
Your first queries

Columns have "type affinity" rather than strict types

sqlite-sqlite-first-queries
Notable functionality

Columns have "type affinity" rather than strict types

sqlite-sqlite-functionality
WAL mode

Turn this on for almost every real application. It allows readers and a writer to work concurrently instead of blocking each other, and…

sqlite-sqlite-wal-mode

Cassandra#

What it is

Apache Cassandra is a distributed wide-column database designed from the ground up for horizontal scale across many nodes, including across…

cassandra-what-it-is
How it works under the hood

Data is partitioned across the cluster by a partition key, using consistent hashing so adding or removing nodes only reshuffles a fraction…

cassandra-cassandra-how-it-works
Setting it up

CQL looks like SQL but isn't. You can't join tables, and you generally can't query by anything other than the partition key without a…

cassandra-cassandra-setup
Your first queries (CQL)

CQL looks like SQL but isn't. You can't join tables, and you generally can't query by anything other than the partition key without a…

cassandra-cassandra-first-queries
Modeling data query-first

In a relational database you model entities, then write queries against them. In Cassandra you design the table around the specific query…

cassandra-cassandra-modeling
Tunable consistency

Consistency level

cassandra-cassandra-consistency