High availability by design: let your database decide who runs what

One place for state, any number of servers

A high-availability database is only half of a highly available application. If an application server keeps sessions in memory, a scheduler runs on one particular machine, or a job queue lives in a separate broker, then losing that server or that broker still takes your application down, however well the database fails over.

The pattern this article describes removes those single points, and it has three parts:

• API servers keep nothing that matters. Any of them can answer any request, so you can run as many as you like and lose any of them.
• Task workers keep no work only in memory. What needs doing, who is doing it and how far they got are rows in the database.
• The database is the only state, the only queue and the only record of who runs what. It decides which worker takes which job and which server runs a task that must run once.

On a high-availability plan your database already survives the loss of a server: the standby takes over in about 30 seconds and, with synchronous replication, no committed transaction is lost (see the high-availability guide). When the database holds all of your state, the rest of your application inherits that. We run our own control plane this way, and the last section shows how.

Stateless API servers

A server is stateless when you can stop it at any moment and the next request, answered by another server, gives the same result. In practice that means:

• Sessions are rows in the database, or signed tokens the client sends back, never a map in the server's memory.
• Uploaded files go to object storage, not to the server's disk.
• Caches are only caches: a server that starts with an empty one is slower for a moment, not wrong.
• Every decision is written before the answer is sent. If the server dies after the commit, the change is kept; if it dies before, the client sees an error and can retry.

That last point needs care, because a retry can repeat a change that did go through: the commit succeeded, but the connection dropped before the answer arrived. An idempotency key makes a retry safe. The client sends a unique key with each request that changes something, and the server records it in the same transaction as the change:

create table idempotency_keys (
  key        text primary key,
  response   jsonb not null,
  created_at timestamptz not null default now()
);

-- in the same transaction as the change itself:
insert into idempotency_keys (key, response) values ($1, $2)
on conflict (key) do nothing
returning key;
-- no row returned: this request was already done; roll back and
-- answer with the stored response instead

With that, you can deploy by replacing servers one at a time, scale by adding them, and lose any of them, and your clients see at most a retried request.

The database as the job queue

Background work (sending an email, generating an invoice, calling a partner's API) needs a queue. A separate message broker is a second record of the truth next to your database, and every failure between the two has to be handled: the order saved but the message never sent, the message sent but the order rolled back, the message delivered twice. A job table in the same database avoids the first two, because the change and the job that follows from it are written in one transaction:

create table jobs (
  id           bigserial primary key,
  kind         text not null,
  payload      jsonb not null,
  run_at       timestamptz not null default now(),
  attempts     int not null default 0,
  locked_by    text,
  locked_until timestamptz,
  done_at      timestamptz
);
create index jobs_due on jobs (run_at) where done_at is null;

begin;
insert into orders (...) values (...);
insert into jobs (kind, payload) values ('order_confirmation', '{"order": 1042}');
commit;

Any number of workers take jobs from that table. Each one claims a due job with FOR UPDATE SKIP LOCKED, so two workers never take the same row and none waits for another, and takes a lease on it: the job is theirs until locked_until.

update jobs
set locked_by = $1,                          -- this worker's name
    locked_until = now() + interval '5 minutes',
    attempts = attempts + 1
where id = (
  select id from jobs
  where done_at is null
    and run_at <= now()
    and (locked_until is null or locked_until < now())
  order by run_at
  for update skip locked
  limit 1
)
returning id, kind, payload, attempts;

The claim commits at once. The worker then does the work outside any transaction and marks the job done, but only if it still holds the lease:

update jobs set done_at = now(), locked_by = null
where id = $1 and locked_by = $2;   -- 0 rows: the lease was lost

Do not hold one transaction open for the whole job instead. It would keep row locks for as long as the job takes, hold back vacuum, and on a failover lose everything it had done. A lease does not: if the worker crashes, the lease expires and another worker picks the job up. If a step fails, put the job back with a delay that doubles each time, and stop after a number of attempts so a broken job raises an alert instead of retrying forever:

update jobs
set run_at = now() + least(interval '30 seconds' * power(2, attempts), interval '30 minutes'),
    locked_by = null, locked_until = null
where id = $1 and locked_by = $2;

Because a job can run more than once (a worker can finish the work and crash before marking it done), make each job idempotent: check whether the email was already sent or the invoice already exists before doing it again, or pass the job's id as the idempotency key to the API you call. That also covers the third failure a broker has, the message delivered twice.

Who runs what: leases for work that must run once

Some work must run on exactly one server at a time: the nightly billing run, a scheduler that enqueues jobs every minute, a consumer that must process a feed in order. Starting it on "server 1" makes server 1 a single point of failure. Instead, every server is ready to run it, and the database decides which one does.

Advisory locks: the short version

PostgreSQL's advisory locks are the simplest way. Each server tries the lock on a connection it keeps open; the one that gets it runs the task, the others try again later:

select pg_try_advisory_lock(hashtext('nightly-billing'));   -- true: run it

The lock belongs to the connection. When the server stops, its connection ends and the lock is released, so the next server to try takes over. A failover closes every connection, and the new primary has no locks at all, so after a failover the first server to reconnect takes the lock again. Two things to watch:

• The server must notice when its connection is gone and stop the task, otherwise it keeps working while another server has taken the lock. Run a cheap query on the lock's connection before each unit of work.
• Session locks need a real session. Connections to ElectraDB go straight to PostgreSQL, but a connection pool in transaction mode in front of it (PgBouncer, for example) hands your session to other clients between transactions, and the lock with it.

Lease rows with a fencing token: the safe version

When running a task twice at the same moment would do damage, use a lease row instead. It survives failovers like any other committed row, and it carries a token that goes up every time the lease changes hands:

create table leases (
  name       text primary key,
  holder     text,
  token      bigint not null default 0,
  expires_at timestamptz not null default '-infinity'
);
insert into leases (name) values ('nightly-billing') on conflict do nothing;

-- take or renew; no row back means someone else holds it
update leases
set token = case when holder = $2 then token else token + 1 end,
    holder = $2,
    expires_at = now() + interval '30 seconds'
where name = $1 and (holder = $2 or expires_at < now())
returning token;

The holder renews every 10 seconds and stops at once if a renewal returns no row. All times come from the database's now(), never from the servers' clocks, so there is one clock to agree on.

Renewing is not enough on its own. A server can pause for longer than the lease (a long garbage collection, a stalled virtual machine) and wake up still believing it holds it. The token stops it doing harm: every transaction the task commits first checks that its token is still the current one, and locks the lease row so that it cannot change hands until the commit. A late write from the old holder finds a newer token and stops, instead of overwriting the new holder's work.

begin;
select 1 from leases
where name = 'nightly-billing' and token = $1   -- $1: our token
for share;
-- no row: we lost the lease; roll back and stop
update billing_runs set finished_at = now() where id = $2;
commit;

Spreading work over servers

The same lease table also divides work that is split into parts, such as one consumer per tenant or per partition of a feed. Make one lease per part (partition:1, partition:2, and so on) and let each server hold its share: the number of parts divided by the number of live servers, rounded up. Each server records a heartbeat in a workers table, and the live servers are those seen in the last 30 seconds. When a server dies, its leases expire and the others pick them up; when one joins, the others hold fewer and release the rest at their next renewal.

What your application sees during a failover

When the primary's server fails, the standby is promoted in about 30 seconds. For an application built this way, that means:

• Open connections are closed, and transactions that had not committed are rolled back. Your code retries them on a new connection, and idempotency keys make the retries safe.
• Committed claims, leases and jobs are still there on the new primary. With synchronous replication, the default, nothing committed is lost. With asynchronous replication, the last commits can be lost, and a job may run again: idempotent jobs make that harmless.
• Leases that expired during the outage are taken over as usual. Advisory locks are gone and are taken again by the first server to reconnect.
• Use a connect timeout of 5 to 10 seconds, so that a server that stopped answering is given up on quickly (see Timeouts and failover in the quickstart).

Claim jobs and take leases on the primary, never through the read-only endpoint: the standby can be slightly behind and refuses writes.

How we run ElectraDB this way

ElectraDB's own control plane, the part that creates, changes and repairs databases, follows this pattern. Its API servers keep nothing of their own. Its task servers keep their progress in our control database, which is the only state and the only queue: we do not run a message broker. Each database is a row with its desired state; an orchestrator claims a database that needs work with FOR UPDATE SKIP LOCKED, holds a five-minute lease on it and commits after every step, and periodic workers each take an advisory lock so that exactly one copy runs.

Because of that, we deploy the orchestrator in the middle of creates, plan changes and restores, and they carry on from their last committed step. A standby control server needs only the code and its configuration to take over. Our tests make every call to an outside system fail once, in turn, and check that each flow still ends exactly like a clean run. Inside ElectraDB describes the parts, and What happens when a server fails what they do when one goes down.

A checklist

• No session, file or decision lives only in one server's memory or disk.
• Requests that change something carry an idempotency key.
• Jobs are rows written in the same transaction as the change that needs them.
• Workers claim with FOR UPDATE SKIP LOCKED and a lease, commit the claim, then work.
• Every job can run twice without harm.
• Work that must run once is decided by a lease in the database, with a token on its writes.
• All lease times come from the database's clock.
• Connections have a connect timeout, and transactions are retried on a new connection.