PostgreSQL Fundamentals

Foreign keys, unique constraints, and check constraints are enforced by the database itself, not by application code that can be bypassed or buggy.

SQL lets you ask questions you didn't anticipate when you designed the schema — joins across tables, aggregations, window functions — without writing new code.

Multiple writes across multiple tables either all happen or none happen, even under concurrent load or a mid-transaction crash.

Decades of operational experience: replication, backup tooling, monitoring extensions, and a huge base of engineers who already know it.

A named collection of rows sharing the same columns and types. Physically, a table is a sequence of fixed-size pages on disk (see Chapter 3).

A single record. Internally called a tuple — the term you'll see throughout Postgres' MVCC machinery.

A column (or set of columns) that uniquely identifies each row. Postgres automatically creates a unique B-Tree index to enforce it.

A column that must match a primary key in another table. Enforced at write time — you cannot insert an order for a customer_id that doesn't exist.

Best for equality and range queries (=, <, BETWEEN, ORDER BY). The default and by far the most common index type.

Equality-only lookups. Rarely used in practice — B-Tree covers the equality case too, with more flexibility.

Used for full-text search, array containment, and geospatial queries (PostGIS) — structures that don't fit a simple ordered tree.

Tokenizes the SQL text, checks grammar, and confirms every referenced table/column exists. Produces a parse tree.

Given the parse tree, evaluates candidate execution strategies (sequential scan, index scan, various join orders and algorithms) and picks the one with the lowest estimated cost — a unitless number combining I/O and CPU estimates.

Runs the chosen plan node-by-node, pulling rows up through the plan tree (joins, filters, sorts) until the final result set is produced.

Every statement in a transaction commits, or none do. A crash mid-transaction rolls everything back.

Constraints (foreign keys, checks, uniqueness) are never violated, even transiently, from the outside observer's point of view.

Concurrent transactions don't see each other's uncommitted changes — the exact degree is configurable (see Chapter 8).

Once a transaction commits, the change survives a crash — guaranteed by the Write-Ahead Log (WAL), which is fsynced to disk before commit returns.

Taken automatically by UPDATE/DELETE/SELECT FOR UPDATE on the specific rows touched. Two transactions updating different rows never block each other.

Occurs when two transactions each hold a lock the other wants, forming a cycle. Postgres' background detector finds the cycle and aborts one transaction.

The default. Each statement sees a fresh snapshot — you can get non-repeatable reads within one transaction.

One snapshot for the whole transaction. Prevents non-repeatable and phantom reads, but if you try to update a row that a concurrent transaction has changed since your snapshot, Postgres raises a serialization error and the transaction must be retried.

The strongest level — guarantees the outcome is equivalent to running transactions one at a time. Implemented by detecting and aborting conflicting transactions, so the application must retry.