SQL

An index is a sorted structure, usually a B-tree, that lets the database find rows without reading the whole table. It costs storage and slows writes, because every insert and update maintains it, which is why indexing every column is not a strategy.

Composite indexes have an ordering rule that decides half of real query performance: an index on (customer_id, created_at) can serve a lookup by customer, and by customer and date together, and cannot serve a lookup by date alone. It is a phone book sorted by surname then first name. Choosing the column order is choosing which queries the index can answer.

Indexes are also easy to disable by accident. Wrapping a column in a function, comparing it to a different type, or starting a LIKE pattern with a wildcard all force a scan, because the index is sorted by the raw value and the query is asking about something else. Most sudden slowdowns after an innocent change are one of those three.

The way to know rather than guess is EXPLAIN, ideally with ANALYZE so the numbers are measured rather than estimated. What to look for is short: a sequential scan on a large table, an estimated row count far from the actual one, which means the statistics are stale, and a nested loop over many rows where a hash join would be cheaper. A covering index, one that contains every column the query needs, lets the database answer without touching the table at all, which is the largest single win available on a hot read path.

GROUP BY collapses rows into one row per group, and every selected column must either be in the group or inside an aggregate, because there is no sensible answer otherwise. WHERE filters rows before grouping and HAVING filters groups after it, which is the distinction that decides whether a query is filtering the input or the result.

Window functions do the other thing people want, which is to compute across related rows while keeping every row. A running total, a rank within each customer, the difference from the previous row, the average over a trailing seven days: each is one OVER clause. Before window functions the same results needed a self-join or application code, and both were slower and harder to read.

The three parts of a window are worth learning as a unit: PARTITION BY chooses the group, ORDER BY chooses the order within it, and the frame chooses how many rows around the current one to include. ROW_NUMBER, RANK and DENSE_RANK differ only in how they treat ties, and choosing wrongly is a quiet way to lose or duplicate rows in a top-N-per-group query.

That top-N-per-group pattern is the one to remember, because it appears constantly: number the rows within each partition by the ordering you care about, then keep those numbered at or below N. It replaces a correlated subquery that gets slower with every group, and it reads as what it does.

A transaction groups statements so they succeed or fail together. That much is familiar; what matters in practice is what other transactions can see while yours is running, which is the isolation level, and it is usually left at whatever the database defaults to without anyone choosing it.

Read committed, the common default, means you never see uncommitted data and can see different results if you run the same query twice, because other transactions commit in between. Repeatable read fixes the second query to the same snapshot. Serializable behaves as though transactions ran one after another, and pays for it in aborts under contention. The anomalies these prevent, dirty reads, non-repeatable reads, phantoms and write skew, are worth being able to name because each maps to a real bug.

The practical rule is to keep transactions short and to keep anything slow outside them. A transaction that holds a row lock while calling an external API holds it for the length of that call, and a queue of requests forms behind it. The same applies to user interaction: never hold a transaction open across a form being filled in, which is exactly what optimistic concurrency and a version column exist to replace.

Two more things that catch people. Deadlocks are normal under concurrency and are resolved by the database aborting one transaction, so application code has to be prepared to retry, which means the work must be safe to repeat. And a transaction rolling back does not undo side effects outside the database: emails sent, files written and messages published are gone regardless, which is why those belong after the commit or behind an outbox.

A schema is the one place a rule can be enforced for every writer, including the script somebody runs by hand at midnight and the service written next year by someone who never read your validation code. That is the argument for putting constraints in the database rather than only in the application: application checks hold for the paths that remember them, and constraints hold for all of them.

The ones that earn their keep are unremarkable. NOT NULL, so a column that must have a value cannot quietly hold nothing. Foreign keys, so a row cannot reference something that does not exist. Unique constraints, which are the honest way to express at most one of these and are also how you make an upsert safe under concurrency. CHECK constraints for the small invariants, such as a quantity that cannot be negative.

Nullable columns deserve more suspicion than they get, because NULL is not a value but the absence of one, and it propagates: any comparison with it is unknown rather than false, so a filter excluding one value also excludes the nulls unless you say otherwise. Every nullable column is a branch in every query that touches it, so the question is always whether absence is genuinely a state this thing can be in.

Choose types deliberately rather than by habit. A timestamp with a time zone rather than without, because the version without one stores an instant with no way of knowing what it meant. Text rather than a guessed length limit, since the constraint belongs where it can be validated with a reason. A decimal type or integer minor units for money. An enumerated type or a lookup table for a fixed set, so a typo is rejected rather than persisted.

Migrations are part of the design, not an afterthought. Every change should be reversible, applied in a version-controlled sequence rather than by hand on a console, and safe with both the old and the new application running, which is what expand and contract exists for. A schema that can only be changed during downtime is a schema that stops changing.