.tip

Mar 13, 2026 · one of the small things

RETURNING can give you the old row too

Since Postgres 18, RETURNING can name both the old and the new values in one statement, so you don't need a trigger or a second query to see what changed.

UPDATE accounts SET balance = balance - 50 WHERE id = 7
RETURNING old.balance AS before, new.balance AS after;

It's handy for audit rows, and for that "what did I just overwrite?" moment in the middle of a migration.

postgresql

~/tips/today — 2026-10-19

~cat tips/today

Never build an index on a live table without CONCURRENTLY

postgresqlmigrations

A plain CREATE INDEX takes a lock that blocks every write to the table for the whole build. On a big table that means minutes of outage that look like a hung app.

CREATE INDEX CONCURRENTLY orders_customer_idx ON orders (customer_id);

It's slower and can't run inside a transaction, so you'll have to tell your migration tool. If it fails halfway, it leaves an invalid index behind, so check pg_index.indisvalid and drop it before you retry.

↵ open y yesterday l ls /tips

esc close