#Leader-only background workers

Some background jobs must run on exactly one process at a time. A queue poller, a scheduler, a row-retention sweep: run two copies and they double-create, double-charge, or fight over the same rows. But you still want to deploy the service with more than one replica for availability. The job, then, is leader election: many processes running, one of them active.

This runbook covers how we do that with a PostgreSQL advisory lease, and the one mistake that quietly breaks the whole thing.

#The mechanism: a session-level advisory lock

PostgreSQL advisory locks are application-defined locks on a 64-bit key. PostgreSQL does not attach any meaning to the key; you do. To elect a leader, every replica tries to grab the same key. Exactly one wins.

SELECT pg_try_advisory_lock(1840000001);

Two properties make this the right tool:

• It is non-blocking. pg_try_advisory_lock returns true if it took the lock and false if someone else holds it. Losers do not wait; they go and stand by, retrying every few seconds.
• It is session-scoped. The lock is held for the life of the database connection, not a transaction. Use pg_advisory_lock, not the pg_advisory_xact_lock variant, when the holder needs to keep the lock while it works. When the connection closes, gracefully or not, PostgreSQL releases the lock automatically. That auto-release on disconnect is the failover mechanism: kill the leader and the next stand-by takes over within a retry interval.

Give each singleton its own stable key, and keep those keys constant across releases. During a rolling deploy, old and new binaries run at the same time; if they compute different keys for the same role, both think they are leader. Treat the key set as a deployment API.

poller     -> 1840000001
scheduler  -> 1840000002
reconciler -> 1840000003

#The trap: a transaction pooler gives you two leaders

Here is the failure mode that motivates this entire runbook.

The lock lives on a session. A transaction-mode connection pooler (PgBouncer in transaction mode, and most “serverless Postgres” front doors) does not give you a stable session. It hands your transaction a backend connection, then returns that connection to the pool when the transaction ends and may give it to a different client next time. The

What makes this dangerous is that nothing errors. pg_try_advisory_lock happily returns true on a backend that no longer “owns” the exclusion you think it does. Two replicas both get true. You have two leaders, both convinced they are the only one, and you find out from the duplicated side effects.

#Guard the connection: a session-stability probe

“Use a direct connection” is an operational instruction, and operational instructions get violated. The robust move is to make the worker refuse to lead on a connection that is not session-stable, by probing before it trusts the lock:

1. Read the backend PID with pg_backend_pid().
2. Create a temporary table and insert a row. Temp tables are session-private.
3. Read the backend PID again and read the row back.
4. If the PID changed, or the row is gone, the “session” is being multiplexed under you. Refuse leadership and log loudly.

SELECT pg_backend_pid();                       -- first PID
CREATE TEMP TABLE probe (v int) ON COMMIT PRESERVE ROWS;
INSERT INTO probe (v) VALUES (1);
SELECT pg_backend_pid();                        -- must equal the first PID
SELECT count(*) FROM probe;                     -- must be 1
DROP TABLE probe;

A pooler that reassigns the connection between those statements fails the probe. Better a worker that refuses to start than two that silently run.

#Heartbeat and failover

Advisory locks have no TTL. Once acquired, the lock is held until the connection ends, so there is nothing to renew. But the leader still needs to notice if its connection died, so it does not keep working while the lock has silently moved on. A cheap liveness probe on an interval is enough:

SELECT 1;

Run it on the lease connection every 15 seconds or so. If it fails, the leader has lost its session: stop the worker, drop back to the stand-by loop, and start competing for the lock again. Meanwhile each stand-by retries the pg_try_advisory_lock every few seconds, so when a leader dies the next process picks up the role within one retry interval.

#A two-gate safety pattern

Worth pairing with the lease: make a worker loop hard to enable by accident. Two independent gates, both required:

1. A build-time feature flag. The worker code is not even compiled into the default binary.
2. A runtime environment toggle. Even when compiled in, the loop stays off unless explicitly switched on.

Default-off on both means a fresh deploy is inert until someone deliberately turns a worker on, and a single wrong flag cannot start a loop. Combined with the lease, a misconfiguration produces “nothing runs” (safe) rather than “two things run” (a mess).

#Verify it

• One leader. Check logs across replicas: exactly one should report holding the lease; the rest report standing by.
• Clean failover. Kill the leader process. A stand-by should acquire the lease within a retry interval and there should be no gap where two hold it.
• Pooler rejection. Point the lease connection at a transaction pooler in a test environment and confirm the session-stability probe refuses to lead.

#Related

• Runner autoscaling: the scaler’s control loops (poller, scheduler, reconciler) are singletons elected this way.
• Backups and restore: the database under these leases is itself backed up by tier.