Complete Database Tutorial

Databases are the part of the stack where a small misunderstanding becomes an outage. An application bug affects one request; a missing index affects every request, and a transaction held open across a network call can stop the whole service. That asymmetry is why the fundamentals here repay attention more than almost anywhere else.

The good news is that the fundamentals are few, and they are the same across engines. An index is an ordered structure. A transaction is a promise about what other transactions can see. A join multiplies rows according to matching keys. Almost every practical question — why is this slow, why did the count double, why did this deadlock — is one of those three.

Why a query is slow: read the plan, not the query

The single most valuable database habit is running EXPLAIN before forming an opinion. The plan tells you what the optimiser decided, which is frequently not what the SQL suggests: a join order you did not write, an index ignored because statistics are stale, a sort that spilled to disk.

Four things in a plan explain most slow queries. A sequential scan where you expected an index seek. A large gap between estimated and actual row counts, which means statistics are wrong and every downstream decision was made on bad information. A nested loop over a large outer relation. And a sort or hash that did not fit in memory.

-- Actual timings and row counts, not just the plan shape
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- The four things to look for:
--   Seq Scan where a seek was expected        -> index missing or unusable
--   rows=1 ... actual rows=480000             -> stale statistics
--   Nested Loop with a big outer relation     -> bad join strategy
--   Sort Method: external merge  Disk: 42MB   -> work_mem too small

ANALYZE orders;        -- refresh statistics before blaming the optimiser

Normalisation, and when to stop

Normal forms have a plain-language purpose: store each fact once, so it cannot disagree with itself. Third normal form — every non-key column depends on the key, the whole key, and nothing but the key — is the practical target for transactional systems, and going further is rarely worth it.

Denormalise deliberately, not defensively. The sequence that holds up in review: normalise, measure, and only then duplicate a column or maintain a rollup — with an explicit answer for how the copy stays correct when the source changes. A denormalised column with no update path is a future bug with a date on it.

Transactions: the part everyone recites and few apply

ACID is easy to list and harder to use. The property that generates real bugs is isolation, because the default level in every mainstream database permits anomalies — and which anomalies differs by engine. PostgreSQL and SQL Server default to read committed; MySQL's InnoDB defaults to repeatable read. Code written against one and deployed on the other can behave differently with no error.

Two operational rules prevent most incidents. Keep transactions short and never hold one across a network call, because every row it touched is locked until it ends. And acquire locks in a consistent order across the codebase — deadlocks are almost always two code paths taking the same two locks in opposite orders.

Scaling: the order that actually works

Scaling decisions go wrong when they are taken out of order. The sequence that works, and that interviewers and reviewers expect: fix the queries and indexes first, because an unindexed query wastes any amount of hardware. Then add caching for read-heavy paths. Then read replicas, accepting replication lag and deciding which reads may be stale. Then partition large tables. Only then shard, because sharding changes your application's data model and removes cross-shard joins and transactions.

Each step costs more complexity than the last, and each buys more headroom. Skipping to the end is how teams acquire a distributed system to solve a missing-index problem.