Posts about sql
Articles, guides and engineering notes about sql from the Relyt One team.
3 articles
Why Postgres IDs skip numbers, and how to fix "duplicate key" errors after an import
Where gaps in PostgreSQL serial and identity columns come from (rollbacks, ON CONFLICT, crashes, CACHE), why inserts fail with duplicate key value violates unique constraint after a data import, one query that resets every sequence, running out of integer IDs, and gapless invoice numbers. Every statement reproduced on Postgres 17.11.
Postgres as a job queue: FOR UPDATE SKIP LOCKED, done right
How to build a background job queue in PostgreSQL 17 with SELECT ... FOR UPDATE SKIP LOCKED: why the naive SELECT-then-UPDATE claims the same job twice, why FOR UPDATE alone makes workers wait, leases and retries, LISTEN/NOTIFY, vacuum, and what polling workers do to a serverless compute. Every statement reproduced on Postgres 17.11.
INSERT ... ON CONFLICT vs MERGE in Postgres: which upsert to use, and the errors each one throws
When to upsert with INSERT ... ON CONFLICT and when to use MERGE in PostgreSQL 17, why MERGE can fail with a duplicate key error under concurrency, and how to fix 'cannot affect row a second time', empty RETURNING and 'no unique or exclusion constraint matching the ON CONFLICT specification'. Every statement reproduced on Postgres 17.11.
