Relational databases have been the default choice for persistent storage for half a century. Not because engineers lack imagination — the industry has tried alternatives several times per decade — but because relational databases solve the hardest problem in data management (correctness under concurrency) better than any of those alternatives. The model is simple: data lives in tables of rows and columns; relationships between tables are expressed through foreign keys; queries are written in a declarative language called SQL.

What makes them trustworthy is not the table model. It is the four-letter promise: ACID. Atomicity, consistency, isolation, and durability are the guarantees that let you charge a credit card, transfer money, or book a seat without the system silently corrupting itself under load. Newer database categories trade pieces of ACID for scale or flexibility — sometimes worth it, often not.

Underneath the SQL, a relational database is a careful dance between memory and disk. Data lives in fixed-size pages (typically 8 KB in PostgreSQL, 16 KB in MySQL/InnoDB). Pages are loaded into a shared buffer pool; queries operate on those in-memory pages. The trick that makes durability work is the write-ahead log — every change is appended to a sequential log file before the modified page is written back to its data file. On crash, replay the log; the database is consistent again. This is also the foundation of replication: streaming the WAL is how replicas stay in sync.

Indexes are the difference between a query that runs in 1 ms and one that runs in 30 seconds. The default in every mainstream database is the B-Tree — a balanced tree where every leaf is at the same depth, giving O(log n) lookups and efficient range scans. Hash indexes exist, are O(1) for equality, and are nearly useless for anything else.

Two more index concepts pay outsize dividends in production. Composite indexes cover multiple columns in one structure, with the leftmost-prefix rule: an index on (tenant_id, created_at) can serve queries on tenant_id alone or on both columns, but not on created_at alone. Covering indexes include all columns the query needs, so the engine never has to visit the table itself — index-only scans are dramatically faster than index-then-heap lookups.

Every query goes through three phases: parsed into a syntax tree, planned by the optimiser (which considers many possible execution paths and picks one based on collected statistics), and executed. The planner is the part that surprises engineers. Two queries that look identical can have wildly different plans depending on data distribution — a small table gets a sequential scan, a large one gets an index scan. EXPLAIN shows you the chosen plan; EXPLAIN ANALYZE runs the query and shows actual timings. Read these. Profile slow queries with them. They tell you the truth.

The most expensive technical decision I see junior architects make is choosing “something else” over a relational database for vague reasons. “It won't scale.” “Schema is too rigid.” “Mongo is more modern.” Most of these reasons evaporate on contact with actual numbers. PostgreSQL on a single beefy server handles tens of thousands of QPS for years before you need to think about anything more exotic. The cases where you genuinely need to move away from relational are real, but they are specific and measurable.

Database connections are not free. Each PostgreSQL connection consumes around 5 MB of server memory and a backend process. A web app with 200 concurrent requests and a naive “one connection per request” pattern will exhaust your database before it exhausts anything else. Connection pooling — reusing a fixed set of connections across many requests — is non-negotiable in production.

The pool size formula that most teams get wrong: pool_size ≈ cpu_cores × 2 on the database server, not on the app server. A pool of 200 connections does not give you 200× throughput; it gives you contention. The database can only do so much real work in parallel; the pool should match that capacity. Use PgBouncer (PostgreSQL) or HikariCP (Java) and size the pool to the database, not to your app.

You ask the ORM for 100 users. Then for each user, you access their orders. The ORM dutifully runs 1 + 100 = 101 queries instead of one JOIN. Multiplied by every endpoint and every developer, you have a database doing tens of thousands of queries when it should be doing hundreds. The fix is eager loading (JOIN, preload, includes, depending on the ORM). The detection is your slow query log — or better, an APM tool that shows query counts per request.

When the primary is bottlenecked on reads, the standard answer is to add replicas: the primary handles all writes; replicas serve reads. This works because most production workloads are read-heavy (5:1 to 100:1). The catch: replication lag. Replicas are eventually consistent — usually milliseconds behind, occasionally seconds, rarely minutes during heavy load. The classic bug is the read-your-own-writes problem: a user creates a post, immediately reads from a replica, and sees nothing.

The standard fix: route reads to the primary for a short window (5–30 seconds) after any write from the same user, then go back to replicas. Some systems use replication slots and committed-LSN tracking to be more precise, but the simple time-based heuristic catches the vast majority of cases.

The single most dangerous thing you can do to a production relational database is run a destructive migration in one step. Dropping a column while the old code still reads it crashes your service; adding a NOT NULL column without a default crashes inserts during deploy; renaming a table breaks every replica. Zero-downtime migrations follow a discipline: every change is split into stages where the schema and the code are simultaneously correct.

Most production systems use soft deletes: a deleted_at timestamp marks a row as deleted instead of physically removing it. This gives you audit trails, recovery, and foreign-key integrity. The cost: every query must filter WHERE deleted_at IS NULL, and forgetting that filter shows deleted data to users. The non-obvious requirement: a partial index on WHERE deleted_at IS NULL — otherwise the planner scans deleted rows on every query and your indexes get bigger and slower over time.