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.