Skip to content

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.

3 min read

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:

sql
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.

sql
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:

sql
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.

sql
SELECT pg_advisory_xact_lock(hashtext('krypton-moderation-schema'));  -- schema creation
SELECT pg_advisory_xact_lock(hashtext($1));  -- $1 = 'reminders:<user id>'; released at commit

5. 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.

Illustrated avatar of Kumar Manas

Written by Kumar Manas

Full-stack developer in India. I build Melodius and Krypton.

Related projectKrypton: discord security bot