Modelling the Data

A schema is a bet on which queries will be common, and the two default shapes bet in opposite directions. A normalized schema stores each fact once — a users table, a links table with a user_id foreign key — and reconstructs a full view with a join; it keeps writes cheap and avoids the update-in-three-places bug, but every read that needs a link and its owner's email now costs a join. A denormalized schema copies data where it will be read — storing the owner's display name directly on the link row — which makes that read a single lookup at the cost of updating every copy when the name changes. Neither is "more correct"; the right choice follows directly from the ratio you estimated earlier. A read-heavy redirect service denormalizes aggressively; a ledger that must never show two different balances for the same account normalizes and pays the join.

An index trades write cost for read speed by keeping a separate, sorted structure that points back to the table's rows, so a lookup by an indexed column no longer has to scan every row. PostgreSQL's own documentation frames the trade-off plainly: without an index the system has to scan the entire table row by row to find matching entries, which is fine for a small table and expensive once the table has millions of rows. That speed is not free — every INSERT or UPDATE on an indexed column has to update the index too, so an index earns its place only when a real, named query pattern reads by that column often enough to justify slower writes. code on the links table is an obvious index — every redirect reads by it. created_at might not be, unless a real query actually filters or sorts by creation time.