Database Engineering

This path is about engineering databases: modelling a domain correctly, choosing physical design deliberately, writing queries the planner can execute well, and changing a schema while the application keeps writing. It is not about operating them. Pooling, replication, failover and backups are operations, and they have their own homes - see the final section for where.

It is written around PostgreSQL because it is the engine this site covers in most depth, and because its documentation is unusually honest about internals. The concepts transfer; the syntax and the defaults do not, so do not carry a Postgres default into MariaDB or SQL Server without checking it.

Each stage states what you must already know, what you will be able to do afterwards, what to read, and how to tell you are finished rather than merely done reading.

Stage 1 - Data modelling and schema design

Prerequisites. A command line, a text editor, and the ability to install software locally. No prior database experience.

Outcome. Given a description of a domain in prose, you can produce a normalised schema with the right primary keys, foreign keys and NOT NULL constraints, and write joins and aggregates against it that return the correct rows, including when nulls are involved.

What to read and do. The work here is deciding what the tables are, before you write any SQL against them. Start with the entities and relationships in the domain, choose keys, then apply the normal forms - the site’s normalisation, schema, primary key and foreign key entries are the short version; the relational model chapters of the PostgreSQL tutorial are the long one.

A concrete failure to recognise: an orders table carrying customer_city. That is a transitive dependency - the city depends on the customer, not the order - and it means the same fact is stored in many rows, so a customer moving cities requires updating every one of them and any missed row is silent corruption. Third normal form is the line where each fact lives in exactly one place.

Then write constraints deliberately. A CHECK constraint, a UNIQUE index and a foreign key are cheaper and more reliable than the application code that would otherwise have to enforce the same rule - they keep holding when a second service starts writing to the same table. Learn what NULL actually does: it is neither equal nor unequal to anything, which is why WHERE and HAVING behave differently and why a LEFT JOIN plus a WHERE clause on the right-hand table silently becomes an inner join.

Denormalisation is not a failure; it is a trade. Storing a computed or copied value buys read speed at the price of maintaining the copy forever, and the copy will drift the day someone writes to one place and not the other. Make it consciously, write down what maintains it, and it is legitimate.

Ready to move on when. You can point at a schema and say which normal form each table satisfies, which constraint would have prevented a given class of bad data, and which denormalised columns exist and what keeps them true.

Stage 2 - How the engine stores your data

Prerequisites. Stage 1.

Outcome. You can explain, in terms of pages and row versions, what happens when a row is inserted, updated and deleted, why an updated table grows without new data arriving, and what the write-ahead log is for.

What to read and do. This is the stage that makes every later stage make sense. Read the MVCC, WAL and autovacuum material in the PostgreSQL reference, and the site’s concept entries for MVCC, vacuum and bloat, transaction ID wraparound and write amplification.

The key model: Postgres never updates a row in place. An UPDATE writes a new row version and marks the old one dead, which is why a heavily updated table grows, why autovacuum exists, and why a long-running transaction on a reporting replica can cause bloat on the primary. Once this clicks, the operational symptoms stop being mysteries - the table is not “leaking”, it is accumulating dead versions faster than vacuum removes them.

Not every engine makes the same choice. InnoDB organises the table itself as a B-tree on the primary key (an index-organised table), so the primary key lookup is the table scan, and secondary indexes store the primary key rather than a physical address. PostgreSQL keeps an unordered heap plus indexes that point into it. Neither is wrong; they optimise for different access patterns, and knowing which yours uses changes how you should pick primary keys.

Ready to move on when. Given a workload described as “hot rows updated constantly”, you can predict which structures grow, which background process matters, and which tunable is the actual lever - before touching configuration.

Stage 3 - Index design

Prerequisites. Stage 2. Indexing decisions make no sense without the storage model underneath.

Outcome. Given a real query, you can design the index that serves it - correct composite column order, covering columns where a heap fetch is the bottleneck, a partial predicate where only a slice of the table is queried - and state what that index costs on every insert and update.

What to read and do. Read Use The Index, Luke end to end; it is a thorough treatment of how a B-tree index is actually laid out and why column order in a composite index is a design decision, not a detail. The site’s index concept is the summary.

The rules that matter:

• Leftmost prefix. An index on (a, b, c) serves WHERE a = ?, WHERE a = ? AND b = ?, and WHERE a = ? AND b = ? AND c = ? - but not WHERE b = ? alone. Order columns by what queries actually constrain.
• Equality first, then range. In a composite index, columns tested with = should precede the column tested with <, > or BETWEEN, because the range ends the ordered traversal.
• Covering indexes. INCLUDE adds columns the index carries but does not sort, enabling an index-only scan - but only while the visibility map says the heap pages are all-visible, which a write-heavy table undermines.
• Partial indexes. WHERE on the index itself indexes only the rows that match - the right tool when queries only ever touch status = 'open' on a table that is mostly closed.

Every index is paid for on every write, so the job includes removal: pg_stat_user_indexes shows which indexes are never scanned. An index nobody reads is pure write amplification.

Ready to move on when. Given a slow query and the existing indexes, you can say whether the fix is a new index, a reordered index, a rewritten query - or no index at all, because the planner is right that a sequential scan is cheaper.

Stage 4 - The query planner

Prerequisites. Stage 3.

Outcome. You can read an EXPLAIN (ANALYZE, BUFFERS) plan, identify why the planner chose what it chose, and state a specific cause and a specific fix for a regressed query - including the fix’s cost, because every index is paid for on every write.

What to read and do. The planner is a cost estimator, not a rule engine. It picks the cheapest plan given what it believes about the data - and what it believes comes from statistics, not the data itself. Read the performance tips chapter, then look at pg_stats for a table you know: the most-common-values list, the histogram bounds, and n_distinct are the entire basis for its row estimates.

Estimation errors compound. A filter the planner thinks keeps 1% of rows but actually keeps 30% produces a wrong plan, and that wrong estimate feeds the join above it, which estimates wrongly in turn. Learn to compare estimated rows against actual rows at each node of a plan; the first node where they diverge sharply is where the problem lives.

Know the three join algorithms and when each wins:

And learn what makes a predicate sargable: wrapping the column in a function (WHERE lower(email) = ?, WHERE created_at::date = ?) makes the existing index invisible, because the index is sorted on the raw value, not the transformed one. The fix is either rewriting the predicate or indexing the expression.

Ready to move on when. You can take a query that regressed in production, obtain a plan, state whether the cause is stale statistics, low selectivity or an unusable index - and say what the fix costs.

Stage 5 - Transactions, isolation and concurrency

Prerequisites. Stages 1-4, in particular the MVCC model from stage 2.

Outcome. You can name the anomalies each isolation level permits, reproduce a lost update and a write skew in a local database with two psql sessions, and choose between SERIALIZABLE, SELECT ... FOR UPDATE and an application-level lock for a given piece of business logic.

What to read and do. Read the transaction isolation chapter and the site’s isolation levels and transaction entries, then reproduce each anomaly yourself with two terminals. Reading about write skew does not stick; watching two concurrent transactions each check a constraint, each see it satisfied, and each commit a change that violates it, does.

Note the defaults, because they differ and this is a common source of ported bugs: PostgreSQL defaults to READ COMMITTED, and its REPEATABLE READ is snapshot isolation that aborts conflicting transactions rather than blocking. Consult the MariaDB and SQL Server references here before assuming behaviour carries over.

Also learn the lock landscape, because stage 6 depends on it: which DDL statements take an ACCESS EXCLUSIVE lock, how a lock queue forms behind one, and how to read pg_locks and pg_stat_activity to see it.

Ready to move on when. You can look at a deadlock in the log and explain the lock ordering that caused it, and for any piece of concurrent business logic you can say which isolation level or lock makes it correct - and why a weaker one does not.

Stage 6 - Schema evolution

Prerequisites. Stage 5, specifically the lock material.

Outcome. You can write a migration for a busy table and state which locks each statement takes, for how long, and what queues behind it - and you can reshape a table without the application ever seeing a half-changed schema.

What to read and do. ALTER TABLE takes an ACCESS EXCLUSIVE lock by default - the lock that queues behind everything and blocks everything, including plain SELECT. On a table under traffic, the naive migration is an outage; the discipline of this stage is making change without one.

The patterns that make it safe:

• CREATE INDEX CONCURRENTLY builds without blocking writes, at the cost of two table scans and a rule of its own: it cannot run inside a transaction, and a failed build leaves an INVALID index behind that you must drop and rebuild.
• CHECK ... NOT VALID then VALIDATE CONSTRAINT adds a constraint in two steps - the NOT VALID form enforces the rule for new rows without scanning the old ones, then validation scans under a weaker lock that does not block reads or writes.
• SET NOT NULL on an existing column scans the table to prove no NULLs exist; on PostgreSQL 15 and later it can skip the scan when a validated CHECK constraint already proves it - which is why the safe sequence is add-check-not-valid, validate, then set-not-null.
• ADD COLUMN with a constant DEFAULT has been metadata-only since PostgreSQL 11 - the default is stored once, not written into every row. A volatile default still rewrites the table.

For anything larger - a column rename, a type change, moving data between tables - the expand-contract pattern is the general answer: add the new structure alongside the old, migrate writes to dual-write or backfill, move reads, then drop the old structure. Each step ships and is safe on its own; no step requires old and new code to agree.

Ready to move on when. You can take a migration an ORM generated, identify which statements will take ACCESS EXCLUSIVE on which tables, and rewrite it into a sequence that runs under traffic - with a stated reason each step is safe.

Stage 7 - Choosing the model, not just the engine

Prerequisites. Stages 1-6. This stage is last on purpose. Choosing a datastore before you can reason about the relational model is how teams end up with three stores they cannot run.

Outcome. Given a workload, you can argue for or against a document store, a wide-column store or a key-value cache, and identify when a relational engine with jsonb is the better answer than a separate database.

What to read and do. Read the MongoDB reference for the document model, sharding and its replica-set semantics, and the database comparison for the cross-engine view. Pay attention to what a sharded system gives up: cross-shard transactions, cheap joins, and the ability to change your mind about the shard key later. The choosing a relational database decision page covers the relational side of the same question.

The honest default is that a single well-indexed PostgreSQL instance handles far more load than most teams assume - and stages 3 and 4 are what make “well-indexed” a skill rather than a hope. The operational cost of a second datastore is permanent. Choose one when you can name the specific property the relational engine cannot provide.

Ready to move on when. You can write a short decision note for a real workload that names the constraint driving the choice, the property you are giving up, and the migration path if the choice turns out to be wrong.

What this path deliberately leaves out

Operating a database - pooling connections, replicating it, failing it over, backing it up - is a different discipline from engineering the data inside it. That material is real, it just does not belong here:

• Connection pooling and failover topology - Pooling and failover topology, plus the PgBouncer and Pgpool-II references.
• Replication and high availability - PostgreSQL high availability and the middleware references for Patroni, keepalived and HAProxy.
• Running the platform underneath - the Production Infrastructure path covers Linux, networking, failover and observability.

Coverage gaps

These belong in the path but do not yet have reference pages on this site. Use the primary sources.