Query: "I want to..."

Examples use Mangrove Market's tables: sales (one row per till line), site (a store or distribution centre), article (a product or SKU) and app_order. Money is stored as integer cents, so totals never pick up rounding errors (Everyday SQL).

Joins in one table

Most wrong totals come from a join that multiplies rows. Before you sum, check the row count of the join against the row count of the table you meant to count (Everyday SQL, Data modeling).

Window functions

A window function adds a value to every row without collapsing rows the way GROUP BY does. PARTITION BY says which rows belong together, ORDER BY says their order inside the window (Everyday SQL).

The order SQL runs in

You write SELECT first, but the database runs FROM and joins, then WHERE, GROUP BY, HAVING, window functions, SELECT, DISTINCT, ORDER BY and finally LIMIT. That is why a column alias from SELECT cannot be used in WHERE, and why you filter a window function's result in an outer query or CTE (Everyday SQL).

Modeling rules of thumb

Model the business first, then the screens (Data modeling).

• Every table gets a primary key. Use bigint GENERATED ALWAYS AS IDENTITY or a UUID; keep natural keys such as (site_id, local_txn_id) as a UNIQUE constraint.
• Store money as bigint cents (or numeric), never float or real.
• Store times as timestamptz. Postgres stores it in UTC and converts on display.
• One fact in one place. A many-to-many link (orders and articles) gets its own table, order_line.
• Put rules in the database where you can: NOT NULL, CHECK (qty > 0), foreign keys. Every app and script then obeys them.
• Index the foreign key columns you join or delete by; Postgres does not create those indexes for you.
• Denormalize on purpose, for reports, and write down who keeps the copy in step.

Indexes: which one

An index speeds reads and costs a little on every write. Add one for a query you can name, then prove it with EXPLAIN ANALYZE (Indexes).

Reading a query plan

Run EXPLAIN (ANALYZE, BUFFERS) before the query. ANALYZE really executes it, so wrap writes in BEGIN; ... ROLLBACK;. Read the plan from the innermost node outwards (Query plans).

Transactions and locks

Postgres runs at Read Committed unless you ask for more. Keep transactions short, never wait for a user or an HTTP call inside one (Transactions).

Safe migrations on a busy table

Change a live schema in small steps: expand, move data and code, contract. Each step can ship and roll back on its own (Safe migrations).

DBA: "something is wrong right now"

Look before you kill. These read from the system views and are safe to run on a primary (Slow queries, Postgres internals).

-- What is running, and for how long
SELECT pid, now() - query_start AS runtime, state, wait_event_type, left(query, 80)
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY runtime DESC;

-- Who is blocking whom
SELECT pid, pg_blocking_pids(pid) AS blocked_by, left(query, 80)
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- Stop a query politely, then end its session if it will not stop
SELECT pg_cancel_backend(12345);
SELECT pg_terminate_backend(12345);

Backups, restores and failover

A backup you have never restored is a hope, not a backup. Schedule a restore test and time it (Restored backups, Backups).

Access and security

Give each app and person a role with the least it needs, and log in with those roles, never as the superuser (DB security).

CREATE ROLE reporting NOLOGIN;
GRANT USAGE ON SCHEMA public TO reporting;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reporting;
CREATE ROLE tomas LOGIN IN ROLE reporting;   -- set the password with \password, not in a script

-- Row-level security: store managers see only their store
ALTER TABLE sales ENABLE ROW LEVEL SECURITY;
CREATE POLICY own_store ON sales
  USING (site_id = current_setting('app.site_id'));

Always pass user input as parameters ($1), never by gluing strings into SQL. Require TLS in pg_hba.conf (hostssl) and use scram-sha-256 passwords (DB security, Security).

psql shortcuts

Meta-commands start with a backslash and need no semicolon (Postgres internals).

Numbers worth remembering

Rough figures for planning, not promises (Estimation).

Traps that catch everyone

• NOT IN (subquery) returns no rows if the subquery has a single NULL. Use NOT EXISTS.
• COUNT(col) skips NULLs; COUNT(*) counts rows.
• AVG of an integer column in Postgres returns numeric, but 5 / 2 is 2. Multiply by 1.0 before dividing.
• BETWEEN '2026-10-01' AND '2026-10-31' on a timestamp misses most of the 31st. Use >= '2026-10-01' AND < '2026-11-01'.
• A LEFT JOIN becomes an inner join when WHERE filters on the right table. Put that filter in the ON clause.
• UPDATE or DELETE without WHERE changes every row. Run it as a SELECT first, inside BEGIN.
• Wrapping an indexed column in a function (WHERE date(sold_at) = ...) stops the index being used. Use a range or an expression index.

Glossary

Plain definitions, with the lesson that explains each one.

Aggregate

A function that turns many rows into one value, such as SUM, COUNT or AVG. Everyday SQL

Autovacuum

The background process that cleans dead row versions and refreshes statistics. Postgres ops

Cardinality

How many distinct values a column has, or how many rows a step returns; the planner's estimates depend on it. Query plans

CTE (common table expression)

A named subquery written with WITH, so a long query reads in steps. Everyday SQL

Constraint

A rule the database enforces on every write: primary key, foreign key, unique, check, not null. Data modeling

Covering index

An index that holds every column a query needs, so the table itself is not read. Indexes

Dead tuple

An old row version left behind by an update or delete, waiting for vacuum. Postgres internals

Deadlock

Two transactions each waiting for a lock the other holds; Postgres cancels one of them. Transactions

Failover

Promoting a replica to primary when the primary is lost. Failover

Foreign key

A column that must match a key in another table, so lines cannot point to orders that do not exist. Data modeling

Isolation level

How much concurrent transactions see of each other: Read Committed, Repeatable Read, Serializable. Transactions

Lock timeout

How long a statement waits for a lock before giving up, which stops a migration from queueing everyone behind it. Safe migrations

MVCC

Multi-version concurrency control: readers see a snapshot, so they do not block writers. Postgres internals

Normalization

Storing each fact once and referring to it by key. Data modeling

Partitioning

Splitting one table into child tables by a key such as month. Partitioning

PITR

Point-in-time recovery: restore a base backup, then replay WAL up to a chosen moment. Restored backups

Primary key

The column or columns that identify each row uniquely. Data modeling

Query planner

The part of the database that picks how to run a query, using table statistics. Query plans

Replica

A copy of the primary kept up to date from its WAL, used for reads and failover. Replication

Row-level security (RLS)

Policies that filter which rows each role may see or change. DB security

RPO and RTO

How much data you may lose, and how long you may be down. Restored backups

Schema migration

A versioned script that changes tables, run the same way in every environment. Safe migrations

Sharding

Splitting data across several database servers by a key. Sharding

Transaction

A group of statements that succeed or fail together. Transactions

WAL

Write-ahead log: every change written to a log before the data files, used for crash recovery, replicas and PITR. Postgres internals

Window function

A function computed over related rows while keeping every row, such as a running total. Everyday SQL

Courses on Kolom

• SQL & Data Modeling: tables, everyday queries, indexes, plans, transactions and migrations.
• Databases at Scale: partitioning, replication, sharding, backups and running Postgres.
• Database Administration with PostgreSQL: internals, restores, failover, slow queries, security and upgrades.
• Data Analysis with Excel, Python & AI: turning query results into answers.

What to read next

• The PostgreSQL documentation at postgresql.org/docs. Read the chapters on queries, indexes, concurrency control and routine maintenance.
• Use The Index, Luke by Markus Winand, free at use-the-index-luke.com. How indexes work, query by query.
• Modern SQL by Markus Winand at modern-sql.com. Window functions, CTEs and other newer features, with which databases support them.
• The Art of PostgreSQL by Dimitri Fontaine. SQL as a programming language, with real datasets.
• Designing Data-Intensive Applications by Martin Kleppmann. Replication, partitioning and transactions explained from first principles.