.tip

Nov 14, 2025 · one of the small things

Give the app role a statement_timeout

One query missing an index can hold a connection for minutes while the pool drains behind it. A timeout on the role turns that into an error the app can deal with.

ALTER ROLE app SET statement_timeout = '5s';
ALTER ROLE reporting SET statement_timeout = '5min';

Use separate roles for the app and for reports, each with the limit its work needs. A query that really does need longer can set its own timeout for that one statement.

postgresqloperations

~/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