.tip

Jun 4, 2026 · one of the small things

Cursor pagination, not OFFSET, past the first few pages

OFFSET 100000 reads and throws away a hundred thousand rows to return ten. And a row inserted between two page loads shifts everything, so the client sees a duplicate or a gap.

SELECT * FROM events WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC LIMIT 50;

The cursor is the last row's (created_at, id), encoded. Every page costs the same, and the list holds still when new rows arrive.

httpapi-designpostgresql

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