PostgreSQL patterns for multi-process Discord bots
Five PostgreSQL patterns from Krypton for bots that run as several processes: atomic claims, unique-row dedup, FOR UPDATE SKIP LOCKED queues, advisory locks, and LISTEN/NOTIFY cache invalidation.
Once a bot runs as several processes, as Krypton does (see sharding discord.py across processes), every "do this once" decision needs a referee outside Python. PostgreSQL is already there, and it has exactly the tools needed. These five patterns cover almost everything.
Why the database
An in-memory lock protects one process. Redis can coordinate, but the data being coordinated, cases, reminders, jail records, already lives in PostgreSQL, and doing the coordination in the same transaction as the write removes a whole class of "locked but not saved" bugs.
1. Atomic claims
To make exactly one process act on a row, flip a column only if nobody has:
UPDATE reminders SET claimed_at = now()
WHERE id = ANY($1::bigint[]) AND claimed_at IS NULL
RETURNING id;Zero rows back means someone else won. Krypton's reminders use this so neither two clusters nor an old and a new process during a deploy can send the same reminder, detailed in reminders that fire exactly once.
2. Dedup with a unique row
When the thing to deduplicate is an event, not a stored row, insert a row keyed by it and let a unique constraint pick the winner. The upsert below claims a punishment per actor, and only re-claims once the cooldown has passed: if no row comes back, another process already punished them. Anti-nuke punishments are claimed this way, and a test with five concurrent claims against a real database confirms exactly one wins. AutoMod claims each case by message id the same way, which also stops Krypton and its premium edition from both acting in one server.
INSERT INTO antinuke_dedup (guild_id, actor_id, punished_at)
VALUES ($1, $2, now())
ON CONFLICT (guild_id, actor_id) DO UPDATE
SET punished_at = now()
WHERE antinuke_dedup.punished_at < now() - ($3 * INTERVAL '1 second')
RETURNING 1 AS claimed;3. Queues with SKIP LOCKED
The dashboard asks the bot to jail or release members by inserting requests. Any process can pick them up without two grabbing the same one:
UPDATE mod_requests SET status = 'running', processed_at = now()
WHERE id IN (
SELECT id FROM mod_requests
WHERE bot = $1 AND status = 'pending' AND guild_id = ANY($2::bigint[])
ORDER BY id LIMIT 10
FOR UPDATE SKIP LOCKED
)
RETURNING *;SKIP LOCKED makes each worker see only rows nobody else has locked, so workers never block each other, and the outer update marks the batch as running in the same statement. Each process only asks for servers on its own shards. For one-winner redemptions, like backup owner codes, plain FOR UPDATE plus a conditional update is enough.
4. Advisory locks
Some critical sections are not about one row. Krypton creates its moderation schema under an advisory lock, so every cluster can start at once without racing to create tables, and enforces "25 pending reminders per user" under a per-user advisory lock, so two commands cannot both see 24.
SELECT pg_advisory_xact_lock(hashtext('krypton-moderation-schema')); -- schema creation
SELECT pg_advisory_xact_lock(hashtext($1)); -- $1 = 'reminders:<user id>'; released at commit5. LISTEN/NOTIFY for caches
Settings are cached per server for speed. When the dashboard changes settings, it calls pg_notify('krypton_settings', ...) with the server and module. Each cluster keeps one LISTEN connection and drops the affected caches immediately instead of waiting for them to expire. Commands invalidate the same way, so a setting changed in Discord or on the web applies at once.
None of these needs extra infrastructure, and each turns a race into a single SQL statement the database already knows how to make safe.
