postgres / queries

updated November 13, 2026

I follow these rules for a schema and its queries. Each rule comes from how Postgres works. Several came from Hazel Bachrach's What I Wish Someone Told Me About Postgres. Each of my repos states the same rules in its AGENTS.md, in the same order, so an agent reads one version of each. postgres / safe migrations covers DDL.

Column types

I give a column the type of its data:

CREATE TABLE notes (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  company_id bigint NOT NULL REFERENCES companies (id),
  body text NOT NULL,
  pinned boolean NOT NULL DEFAULT false,
  happened_at timestamptz NOT NULL,
  extra jsonb NOT NULL DEFAULT '{}'
);

I use text with no length. varchar(n) adds a check and no speed. timestamptz stores an instant. timestamp stores a clock reading with no time zone. jsonb parses the value once, at the write, and an index can read it. json keeps the text and parses it on each read. The Postgres wiki page Don't Do This lists more.

One app stores a time as RFC 3339 text on purpose. The text sorts in time order, and Go scans every time into one type. An app that I ported from SQLite has TEXT times and INTEGER flags. I do not copy them into a new column. Each new TEXT time makes the later change to timestamptz larger.

One place for each fact

I store a fact in one table and refer to it by key. A copy of a fact is a cache. I name the job that rebuilds it from the source. A search table that a job fills again every 15 minutes is a cache of this kind.

jsonb

Postgres keeps statistics on each column. The planner reads them to guess how many rows a filter matches. It keeps no statistics on the keys in a jsonb value, so it uses a fixed guess. A field that a query filters, joins, or sorts on gets its own column. I keep jsonb for a payload that code reads whole, such as the arguments of a job.

A filter on a jsonb key compares text or jsonb, not a bare value:

SELECT id FROM jobs WHERE args ->> 'company_id' = '42';
SELECT id FROM jobs WHERE args @> '{"company_id": 42}';

NULL

NULL means unknown. A comparison with NULL is NULL, and WHERE keeps only the rows where its condition is true. So on a nullable column, <> drops the NULL rows:

-- Leaves out each user whose title is NULL.
SELECT id FROM users WHERE title <> 'manager';

-- Keeps them.
SELECT id FROM users WHERE title IS DISTINCT FROM 'manager';

NOT IN (...) and <> ALL (...) drop them too. coalesce is the other fix.

NOT IN over a subquery is worse. One NULL from the subquery makes the NOT IN match no rows:

-- Matches nothing when one data_reviews.company_id is NULL.
SELECT id FROM companies
WHERE id NOT IN (SELECT company_id FROM data_reviews);

SELECT id FROM companies
WHERE NOT EXISTS (
  SELECT 1 FROM data_reviews
  WHERE data_reviews.company_id = companies.id
);

I write NOT EXISTS. A static check in CI fails on a NOT IN (SELECT ...) in a query file.

LIKE

In LIKE, % matches any run of characters, and _ matches any one character. A name with an underscore in it becomes a pattern:

-- Also matches 'dailyreport'.
SELECT id FROM checks WHERE name LIKE 'daily_%';

SELECT id FROM checks WHERE starts_with(name, 'daily_');

I match a prefix with starts_with, which has no wildcards. In one repo, a check fails on a LIKE pattern that is a plain prefix or that holds an unescaped _.

Column positions

GROUP BY 1 and ORDER BY 2 name a column by its position in the SELECT list. An edit to that list changes what they mean, with no error. I use positions in psql, and I write the column names in a query file.

Indexes

Postgres decides from its statistics whether to read an index. On a table of a few hundred rows, it reads the whole table, because that is faster. So EXPLAIN on a test database tells me nothing about an index. I read the plan on a restored copy of production.

A btree index on (a, b) sorts by a, then by b inside each a. It serves a filter on a, or on a and b. It does not serve a filter on b alone. A PRIMARY KEY or UNIQUE constraint builds a btree index too. So an index on (a) beside a key on (a, b) answers nothing new, and each write updates both. In one repo, a check fails on an index whose columns start a key or another index.

Transactions

A transaction keeps each lock it takes until it ends. A row lock from an UPDATE makes each other writer of that row wait. A read inside the transaction holds a table lock, and DDL on that table waits for it. So I run only SQL between Begin and Commit. An HTTP call, a git command, or a file write goes before or after the transaction. A change to many rows runs in batches, each in its own transaction.

One server sets idle_in_transaction_session_timeout to one minute on each session. A goroutine that stops between two statements then loses its session, and its locks, a minute later. A job that holds a transaction for its whole run sets a longer limit.

A lock that must last longer than a transaction is a session advisory lock. I do not set idle_session_timeout on a session that holds one. Postgres would end the idle session and release the lock with no error.

postgres / safe migrations shows how one slow read and one fast ALTER stop every query on a table.

← All articles