SQL and Databases

A relational database stores data in tables and uses constraints to keep relationships valid. SQL describes the result you want; the database chooses an execution plan.

Start with the data model

Choose a primary key for each table. A foreign key relates rows to another table. Unique, not-null and check constraints express rules the database can enforce even when several applications write to it.

Keep each fact in one appropriate place. Repeating it in several tables means every update has to keep those copies consistent. Derived values can be stored deliberately, but they need a clear update rule.

Reading a query

WHERE filters rows before grouping; HAVING filters groups. ORDER BY specifies result order. Without it, a convenient order from one run is not a guarantee.

An inner join keeps matches. A left join also retains unmatched rows from its left side. A filter on the right-hand table in WHERE can remove those unmatched rows, changing the intended result.

Joining two one-to-many relationships can multiply rows. Check the cardinality before adding an aggregate or using DISTINCT to hide duplication. See the SELECT documentation.

Nulls

NULL represents an absent or unknown value. Use IS NULL to test for it. Comparisons involving nulls do not behave like comparisons between ordinary values; a filter only retains rows where its condition is true.

Decide whether absence is valid in the data model rather than filling every missing value with an arbitrary default.

Transactions

A transaction groups changes into a commit or rollback. Isolation controls what concurrent transactions can observe. Some operations need locking or a retry when another transaction conflicts.

Keep transactions short and handle failure around the whole operation. See PostgreSQL's transaction guide.

Indexes and plans

Indexes can speed up suitable lookups while adding storage and write cost. Their column order matters for composite indexes. An index is useful only if the workload and planner can use it.

Read an execution plan before guessing. EXPLAIN ANALYZE executes the statement, so take care with statements that change data. Check row-count estimates, repeated work and where time is spent. See the index documentation.

Application boundaries

Use parameterised queries for values. Do not construct SQL by joining untrusted text into the statement. Keep schema changes in migrations and plan how older application versions will behave during a rollout.