Build leader election with PostgreSQL locks

Sometimes the smallest reliable leader-election service is the database connection the workers already share.

PostgreSQL advisory locks let an application attach meaning to a lock key. A dedicated worker session can try to acquire one well-known key, run the singleton duty while the session owns it, and let another candidate take over when that session ends.

The current PostgreSQL advisory-lock documentation says application code is responsible for correct use, distinguishes session- and transaction-level locks, and notes that session locks survive transaction rollback but are released when the session ends. Those details decide the implementation.

I would keep one dedicated connection, never return it to a pool while leading, tie all work to an abort signal, expose database ownership as readiness, and state clearly that a lock alone does not fence delayed external operations.

This pattern is useful when the duty is bounded and PostgreSQL availability is already a prerequisite. It is not a universal consensus substitute.

Choose a proportionate boundary

Use this pattern when one PostgreSQL cluster is already required, temporary leader loss is acceptable, and the duty can stop or reconcile after database disconnection.

I would pressure-test that decision with four questions:

• Is PostgreSQL already a hard dependency?
• Can work pause during failover?
• Is one cluster the right scope?
• Does the task need consensus elsewhere?

The failure mode here is adding an advisory lock to a duty whose external availability and safety exceed the database boundary. In small services and worker fleets that already depend on PostgreSQL and need one active scheduler, poller, reconciler, cache refresher, or coordinator without adopting a separate consensus system for a bounded leadership task, that can hide the exact boundary a reviewer or teammate needs to understand. My working artifact would be a leader-election suitability note. I want it close enough to the implementation that it can change the work, not created afterward to decorate the story.

The result I would look for is a documented reason the simple coordinator matches consequence. That is a narrower claim than saying the whole system improved, but it is also one I can verify and defend.

In practice, I would put a leader-election suitability note beside the question “Is PostgreSQL already a hard dependency?” before the first implementation review. The next pass would use “Can work pause during failover?” to test the boundary, then “Is one cluster the right scope?” to expose the state most likely to be missed. I would keep “Does the task need consensus elsewhere?” for the release check because it asks whether the decision still holds outside the ideal path. The work is ready to move when the artifact can explain the choice and the observed result supports a documented reason the simple coordinator matches consequence.

Derive a stable namespaced key

The 64-bit key or two 32-bit keys should be deterministic, collision-reviewed, environment-scoped, versioned, and shared by every candidate implementation.

The practical review starts here:

• Which duty does the key name?
• Can two features collide?
• Does staging share production?
• How will key versions migrate?

Those questions keep hashing a label ad hoc in each service from becoming the default. I would capture the decision in an advisory-key registry, then use it while the work is still cheap to change. For single-active worker duties coordinated by the database they already require, the artifact should make ownership, constraint, and next action visible without requiring a private explanation.

Success would look like all candidates competing on exactly one intended key with collision tests. If I cannot point to that evidence, I have a direction, not a finished decision.

The implementation move is to make an advisory-key registry part of the working surface. I would use it to answer “Which duty does the key name?” while scope is still flexible, and “Can two features collide?” before code or content becomes expensive to unwind. During QA, “Does staging share production?” and “How will key versions migrate?” become concrete checks rather than discussion prompts. That sequence turns single-active worker duties coordinated by the database they already require into something the team can operate and gives me a specific outcome to report: all candidates competing on exactly one intended key with collision tests.

Own a dedicated connection

Session-level authority belongs to one physical database session that must not return to the pool, be transparently replaced, or share ambiguous lifecycle with unrelated queries.

Before implementation, I would answer:

• Which backend owns the lock?
• Can the pool recycle it?
• How is connection loss observed?
• Who closes it?

The artifact is a dedicated leader-connection wrapper. Its job is to expose the tradeoff early enough that design, engineering, support, or product can disagree with something concrete. The common trap is acquiring on one pooled session and running work after that session is released; it moves uncertainty downstream and makes the final interface carry a problem the system never resolved.

For me, the useful receipt is lock ownership and process leadership ending together under disconnect. That connects a dedicated session-level advisory lock with stable key derivation, connection ownership, acquisition backoff, readiness, cancellation, bounded work, database truth, observability, and an explicit boundary for external side effects to an observable result instead of a process claim.

I would test this with one typical case and one boundary case. The typical case should make “Which backend owns the lock?” easy to answer. The boundary should force a decision about “Can the pool recycle it?” and “How is connection loss observed?.” I would record both in a dedicated leader-connection wrapper, including the part that stayed unresolved after the first pass. The final check, “Who closes it?,” is where the artifact earns its place: it either supports lock ownership and process leadership ending together under disconnect, or it shows exactly why another iteration is needed.

Acquire without a stampede

Nonblocking try-lock calls, jittered delay, cancellation, database backoff, and candidate visibility allow prompt takeover without creating query pressure.

I would use these prompts during the working review:

• How often do followers try?
• Is delay randomized?
• Can shutdown cancel waiting?
• What happens during database outage?

If the team slips into polling the lock continuously from every replica, the product can still look complete while its operating rule stays ambiguous. I would make an acquisition backoff policy the shared reference and keep it small enough to update as evidence changes.

The standard is bounded database load and measured failover latency at fleet scale. That tells me whether the decision helped the product, not merely whether the document was completed.

The working sequence is small: draft an acquisition backoff policy, review it against “How often do followers try?,” implement the narrowest useful path, and then return with evidence for “Is delay randomized?.” I would use “Can shutdown cancel waiting?” to inspect product consequence and “What happens during database outage?” to decide whether the result is stable enough to ship. This keeps polling the lock continuously from every replica visible as a known risk and makes bounded database load and measured failover latency at fleet scale the release receipt rather than a hopeful conclusion.

Make authority a runtime state

Leader, follower, acquiring, yielding, and uncertain states should control readiness, scheduling, metrics, logs, and operator messages through one owner object.

I would pressure-test that decision with four questions:

• What does readiness mean?
• Can follower serve other traffic?
• Which state permits new duty work?
• How is owner inspected?

The failure mode here is setting a global boolean after lock acquisition and never invalidating dependents. In small services and worker fleets that already depend on PostgreSQL and need one active scheduler, poller, reconciler, cache refresher, or coordinator without adopting a separate consensus system for a bounded leadership task, that can hide the exact boundary a reviewer or teammate needs to understand. My working artifact would be a leadership state machine. I want it close enough to the implementation that it can change the work, not created afterward to decorate the story.

The result I would look for is every singleton action gated by current session authority. That is a narrower claim than saying the whole system improved, but it is also one I can verify and defend.

In practice, I would put a leadership state machine beside the question “What does readiness mean?” before the first implementation review. The next pass would use “Can follower serve other traffic?” to test the boundary, then “Which state permits new duty work?” to expose the state most likely to be missed. I would keep “How is owner inspected?” for the release check because it asks whether the decision still holds outside the ideal path. The work is ready to move when the artifact can explain the choice and the observed result supports every singleton action gated by current session authority.

Propagate loss as cancellation

Connection error, server shutdown, manual yield, process signal, and failed ownership check should abort scheduling and child work before cleanup or reacquisition.

The practical review starts here:

• Which signal stops the loop?
• Do child queries receive it?
• How long can cleanup run?
• Can new work start while yielding?

Those questions keep logging connection loss while an interval continues to enqueue work from becoming the default. I would capture the decision in a leadership abort tree, then use it while the work is still cheap to change. For single-active worker duties coordinated by the database they already require, the artifact should make ownership, constraint, and next action visible without requiring a private explanation.

Success would look like no new leader-only work beginning after authority becomes uncertain. If I cannot point to that evidence, I have a direction, not a finished decision.

The implementation move is to make a leadership abort tree part of the working surface. I would use it to answer “Which signal stops the loop?” while scope is still flexible, and “Do child queries receive it?” before code or content becomes expensive to unwind. During QA, “How long can cleanup run?” and “Can new work start while yielding?” become concrete checks rather than discussion prompts. That sequence turns single-active worker duties coordinated by the database they already require into something the team can operate and gives me a specific outcome to report: no new leader-only work beginning after authority becomes uncertain.

Bound and reconcile each duty

Each run needs its own idempotency key, database claim, cursor, deadline, and terminal receipt so takeover can distinguish complete, partial, and abandoned work.

Before implementation, I would answer:

• What identifies one run?
• Where is progress durable?
• Can a successor continue?
• Which partial effect needs repair?

The artifact is a leader-duty run ledger. Its job is to expose the tradeoff early enough that design, engineering, support, or product can disagree with something concrete. The common trap is assuming singleton execution makes every task exactly once; it moves uncertainty downstream and makes the final interface carry a problem the system never resolved.

For me, the useful receipt is successor takeover converging without skipped or duplicate logical work. That connects a dedicated session-level advisory lock with stable key derivation, connection ownership, acquisition backoff, readiness, cancellation, bounded work, database truth, observability, and an explicit boundary for external side effects to an observable result instead of a process claim.

I would test this with one typical case and one boundary case. The typical case should make “What identifies one run?” easy to answer. The boundary should force a decision about “Where is progress durable?” and “Can a successor continue?.” I would record both in a leader-duty run ledger, including the part that stayed unresolved after the first pass. The final check, “Which partial effect needs repair?,” is where the artifact earns its place: it either supports successor takeover converging without skipped or duplicate logical work, or it shows exactly why another iteration is needed.

Fence consequential external effects

Advisory ownership cannot make a delayed network request disappear, so external writes should enforce idempotency, version preconditions, a database state transition, or a monotonically increasing generation.

I would use these prompts during the working review:

• Can old work arrive late?
• Does the target validate authority?
• Which generation is current?
• Can the effect be reconciled?

If the team slips into calling a third party because the process believed it still held the lock, the product can still look complete while its operating rule stays ambiguous. I would make an external-effect safety boundary the shared reference and keep it small enough to update as evidence changes.

The standard is stale work rejected or converging on one domain outcome after failover. That tells me whether the decision helped the product, not merely whether the document was completed.

The working sequence is small: draft an external-effect safety boundary, review it against “Can old work arrive late?,” implement the narrowest useful path, and then return with evidence for “Does the target validate authority?.” I would use “Which generation is current?” to inspect product consequence and “Can the effect be reconciled?” to decide whether the result is stable enough to ship. This keeps calling a third party because the process believed it still held the lock visible as a known risk and makes stale work rejected or converging on one domain outcome after failover the release receipt rather than a hopeful conclusion.

Expose database truth

pg_locks, backend PID, application_name, acquisition time, duty generation, last completed cursor, candidate count, and takeover latency should connect coordination to operations.

I would pressure-test that decision with four questions:

• Who owns the lock now?
• How long have followers waited?
• Is the leader making progress?
• Can operators yield it safely?

The failure mode here is using a heartbeat row as a second ambiguous source of authority. In small services and worker fleets that already depend on PostgreSQL and need one active scheduler, poller, reconciler, cache refresher, or coordinator without adopting a separate consensus system for a bounded leadership task, that can hide the exact boundary a reviewer or teammate needs to understand. My working artifact would be a leader observability dashboard and query. I want it close enough to the implementation that it can change the work, not created afterward to decorate the story.

The result I would look for is operators diagnosing stuck work without guessing which replica leads. That is a narrower claim than saying the whole system improved, but it is also one I can verify and defend.

In practice, I would put a leader observability dashboard and query beside the question “Who owns the lock now?” before the first implementation review. The next pass would use “How long have followers waited?” to test the boundary, then “Is the leader making progress?” to expose the state most likely to be missed. I would keep “Can operators yield it safely?” for the release check because it asks whether the decision still holds outside the ideal path. The work is ready to move when the artifact can explain the choice and the observed result supports operators diagnosing stuck work without guessing which replica leads.

Test session failure

QA should kill the leader connection, terminate backend, pause the process, exhaust the pool, restart PostgreSQL, delay external calls, deploy all candidates, and force graceful shutdown.

The practical review starts here:

• When is the lock released?
• How quickly does work abort?
• Can two duties overlap?
• Does the successor reconcile first?

Those questions keep testing only orderly unlock in one process from becoming the default. I would capture the decision in a leader failover exercise, then use it while the work is still cheap to change. For single-active worker duties coordinated by the database they already require, the artifact should make ownership, constraint, and next action visible without requiring a private explanation.

Success would look like one database owner plus bounded product effect through real failure modes. If I cannot point to that evidence, I have a direction, not a finished decision.

The implementation move is to make a leader failover exercise part of the working surface. I would use it to answer “When is the lock released?” while scope is still flexible, and “How quickly does work abort?” before code or content becomes expensive to unwind. During QA, “Can two duties overlap?” and “Does the successor reconcile first?” become concrete checks rather than discussion prompts. That sequence turns single-active worker duties coordinated by the database they already require into something the team can operate and gives me a specific outcome to report: one database owner plus bounded product effect through real failure modes.

What I would show in the work

The public version needs evidence from the work itself. For this topic, the first five artifacts I would reach for are:

• a leader-election suitability note
• an advisory-key registry
• a dedicated leader-connection wrapper
• an acquisition backoff policy
• a leadership state machine

I would not publish all five at equal weight. One should orient the reader, one should reveal the hardest tradeoff, and one should prove the result. The others can live in a downloadable note or appear as supporting frames. That edit matters because a dedicated session-level advisory lock with stable key derivation, connection ownership, acquisition backoff, readiness, cancellation, bounded work, database truth, observability, and an explicit boundary for external side effects becomes harder to understand when every process detail is treated as equally important.

I would also show one rejected direction. The useful version is specific: which option looked attractive, which constraint made it wrong, and what evidence supported the narrower choice. That gives an engineering manager something real to question and keeps the case study from reading like the final answer was obvious from the beginning.

# key
namespace jobs / duty invoice-reconcile / 742118
Key derivation version 1, database cluster prod-us, candidate worker-17, dedicated backend pid 8241.
# lease
lock acquired 20:01:03Z / generation 98
Readiness leader=true, loop signal active, heartbeat metadata updated for observability only, not authority.
# handoff
connection dropped / abort 34ms / successor 1.7s
Old external operation used idempotency key, successor reconciled cursor, pg_locks shows one owner, duplicate effects zero.

Resource path

The practical follow-up I would build is a PostgreSQL advisory-lock leader kit with namespaced key derivation, dedicated connection wrapper, try-lock loop, jitter, ownership state, abort propagation, lock inspection query, liveness and readiness semantics, graceful release, connection-loss fixture, failover timing, external-effect safeguards, metrics, alerts, and runbook. I am treating that as a resource backlog item, not pretending the adjacent downloads below are the same artifact. The related cards cover useful pieces of the workflow today; this specific file should only be published when its examples, fields, and instructions are complete.

The first version should stay concise: context, constraint, decision, evidence, owner, and follow-up. Its value would come from helping someone repeat this exact review, not from adding another generic PDF to the site.

Review checklist

The article-specific review questions are:

• Is PostgreSQL already a hard dependency?
• Which duty does the key name?
• Which backend owns the lock?
• How often do followers try?
• What does readiness mean?
• Which signal stops the loop?
• What identifies one run?
• Can old work arrive late?
• Who owns the lock now?
• When is the lock released?

I would add two editorial checks before publishing: can a recruiter find the point in the first minute, and can an engineer trace at least one claim to an implementation or production receipt? If either answer is no, the article needs another edit.

Implementation notes

For single-active worker duties coordinated by the database they already require, I would write the implementation note before polish. It would name the changed surface, source of truth, owner, failure boundary, and verification path. Those details prevent the principle from floating above the actual code or operational workflow.

The proof signals I care about are specific to this article:

• no new leader-only work beginning after authority becomes uncertain
• successor takeover converging without skipped or duplicate logical work
• stale work rejected or converging on one domain outcome after failover
• operators diagnosing stuck work without guessing which replica leads
• one database owner plus bounded product effect through real failure modes

I would choose two or three of those signals for the first release rather than instrumenting everything. The strongest pair usually combines one direct behavior check with one operating check: a route and a data query, a keyboard path and a support state, a handler replay and a reconciliation result, or a migration count and a rendered screen.

The follow-up belongs in the note before shipping. It should say what remains temporary, what evidence would trigger another pass, and who owns that decision. That is how the first version stays intentionally narrow without making the boundary invisible.

Case-study packaging

I would structure the case-study version around the four visual lessons already established:

• One database session grants one bounded leadership duty.
• Leadership changes are database-session events.
• Lock scope should match work scope.
• The leader trace makes ownership inspectable.

The opening frame explains the product pressure. The middle two show the decision moving through the system. The last frame is the receipt: what was checked, what held, and what remained unresolved. That order lets the reader move from product judgment into implementation detail without reconstructing the whole project first.

I would include one caveat tied to small services and worker fleets that already depend on PostgreSQL and need one active scheduler, poller, reconciler, cache refresher, or coordinator without adopting a separate consensus system for a bounded leadership task: a data limit, rollout boundary, unsupported state, external dependency, or result that is still directional. A precise caveat makes the evidence easier to trust because it shows where the claim stops.

The final test is whether the page creates a better conversation. If the artifact helps someone ask a sharper question about product judgment, implementation detail, or release proof in a live interview, it belongs in the story.

Interview angle

In an interview, I would explain this through a dedicated session-level advisory lock with stable key derivation, connection ownership, acquisition backoff, readiness, cancellation, bounded work, database truth, observability, and an explicit boundary for external side effects. The story should start with the product pressure, then move into the system constraint, the artifact, and the proof. That order keeps the answer grounded. It also gives the interviewer several places to go deeper: data, frontend architecture, design systems, support, migration, accessibility, or release process.

The strongest version of the answer includes a tradeoff. I want to be able to say what I chose, what I left alone, and how I knew the work helped. That is more credible than presenting every project as a clean win.

The hiring signal

A PostgreSQL advisory-lock leader is a hiring signal because it shows I can choose proportionate coordination, understand session semantics, separate leadership from fencing, and verify failure behavior in production-shaped conditions.

That is the level I want this site to communicate. The work should show taste, but it should also show operating judgment. It should make me look like someone who can enter a real product system, understand the messy middle, ship the useful version, and leave enough proof for the next person to trust it.