PostgreSQL guides and tutorials
These guides are about Postgres itself. Each one starts from a problem you can hit in production, reproduces it with SQL you can paste into psql, and ends with a fix you can check. They target PostgreSQL 17, the version Relyt One runs, and most of what they cover applies to any recent Postgres. Follow the reading path below in order, or jump straight to the guide that matches your problem.
11 articles
Start here
Four guides, in the order we suggest: change a schema without locking your table, get queries onto your indexes, keep row-level security closed, then check what changes if you come from Postgres 18.
- Postgres migrations that lock your table: what each ALTER TABLE takes, and how to ship it safelyWhy a one-line ALTER TABLE can stall every query on a table, which lock each common migration takes on PostgreSQL 17, which ones rewrite the table, and the lock_timeout, NOT VALID and CONCURRENTLY patterns that keep production running.
- Why Postgres isn't using your index: seven causes, with EXPLAIN output you can reproduceYou created the index and EXPLAIN still shows a sequential scan. Seven reasons PostgreSQL ignores an index (functions, casts, wildcards, selectivity, stale statistics, column order, tiny tables), each with a reproduction and a fix.
- Five ways row-level security fails openRow-level security is the best defense against a forgotten WHERE clause, until it silently stops applying. Five ways it fails open on PostgreSQL 17, each with a reproduction, a fix, and a catalog query that catches it in CI.
- Coming from Postgres 18? What to change for Postgres 17Relyt One runs PostgreSQL 17. If your schema or your habits come from Postgres 18, here is what fails on 17, what to write instead, and how to load a dump taken on 18, with every snippet run on both versions.
More posts tagged Postgres
RSS feed
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.
What breaks behind PgBouncer transaction pooling, and how to fix each one
Why you get 'prepared statement already exists', settings that leak between requests and advisory locks that lock nothing behind a transaction pooler, reproduced on PgBouncer 1.24 and PostgreSQL 17, with the fix for each and a rule for when to use the direct connection instead.
Getting started with Relyt One managed auth
A step-by-step guide to a multi-user Next.js app on Relyt One. Enable managed auth on a branch, sign users in, verify their JWTs with @relyt/auth, and let Postgres row-level security keep every user's rows apart.
Managed auth for Relyt One, now in preview
Sign-up, sign-in, sessions and organizations for your app's users, running next to your branch and storing users in your own database. Tokens are short-lived EdDSA JWTs you verify with JWKS, and row-level security does the rest.
Introducing Relyt One: serverless Postgres that branches like code
Relyt One is serverless PostgreSQL 17 with copy-on-write branches, compute that suspends when idle, built-in vector search and managed auth. Here is how it works and how to go from sign-in to a branched database in five minutes.
