DEV Community: 晖莫

Postgres Advisory Locks Are Not the Lock You Think

晖莫 — Mon, 14 Sep 2026 13:57:15 +0000

At 03:40 my nightly reconcile job ran twice. Two workers, same pg_advisory_lock(42), both proceeded, and the refunds went out twice. The lock was real. The code was correct. It still did nothing, because each worker held a different Postgres backend behind PgBouncer in transaction mode.

pg_advisory_lock looks like a distributed lock. It is not one. It is a mutex owned by a database session.

What the lock actually is

A session-level advisory lock lives in the backend process that ran the statement. pg_advisory_lock(key) blocks until it gets the lock. pg_try_advisory_lock(key) returns true or false right away. The lock is held until pg_advisory_unlock(key) runs or that backend disconnects. It is reentrant: two calls on the same session need two unlocks, and a stray pg_advisory_unlock on a lock you do not hold returns false and logs a warning while changing nothing.

Now the failure modes, in the order I hit them.

The pool owns the session, not you. In transaction mode each transaction can land on a different backend. My worker acquired the lock in transaction one and released it in transaction two. The unlock ran on a backend that never held it, returned false, and I had been filtering that warning out of the logs for months. The original backend kept the lock for whichever client got it next. New workers blocked, or with the try variant, skipped their turn forever.

Disconnects release the lock. A failover, a restart, or a dropped TCP connection kills the backend and the lock with it. A worker that believes it holds the lock now does not, and nothing tells it.

No fencing token. Even with one stable connection, the lock says nothing about the write that happens after it. A worker can be paused by GC, a VM freeze, or a network partition past its lock's lifetime, wake up, and write anyway. The lock was never what stopped the second writer. Only the resource can do that.

What advisory locks are good for

Short mutual exclusion inside one database, inside one transaction. That is the honest scope.

Use pg_advisory_xact_lock or pg_try_advisory_xact_lock. They release at COMMIT or ROLLBACK, so a crashed client cannot leak them, and you write no unlock code at all.

BEGIN;
-- Non-blocking: false means another transaction is already doing this work.
SELECT pg_try_advisory_xact_lock(hashtext('nightly-reconcile')) AS got_it;
-- Application: if got_it is false, COMMIT and return.
UPDATE ledger SET reconciled = true WHERE reconciled = false;
COMMIT;

That pattern is fine for guarding a migration, serializing a trigger, or stopping two cron entries from running the same statement at once. It needs direct connections, or a pooler in session mode, and a transaction measured in seconds, not minutes.

What to use when you need a lease

If the work outlives a transaction, or crosses processes you do not control, put the lease in a table. A row with an expiry and a fencing token gives you something a session lock cannot: a value the resource itself can check.

CREATE TABLE job_lease (
  job_name   text PRIMARY KEY,
  owner      text        NOT NULL,
  fencing    bigint      NOT NULL DEFAULT 0,
  expires_at timestamptz NOT NULL
);

-- Claim. One atomic statement, no session state, safe behind any pooler.
INSERT INTO job_lease (job_name, owner, fencing, expires_at)
VALUES ('nightly-reconcile', :worker_id, 1, now() + interval '5 minutes')
ON CONFLICT (job_name) DO UPDATE
  SET owner      = EXCLUDED.owner,
      fencing    = job_lease.fencing + 1,
      expires_at = EXCLUDED.expires_at
WHERE job_lease.expires_at < now()
RETURNING fencing;
-- No row returned: a live lease exists, back off.
-- Row returned: you own the lease and the new fencing token.

The token only matters if the resource enforces it. Store the last token on the row you mutate and reject anything older:

UPDATE refunds
   SET processed_at = now(), last_fencing = :fencing
 WHERE id = :refund_id
   AND :fencing > last_fencing;

Zero rows updated means a stale worker tried to write. Log it and stop. Renew the lease with the same three-way check, and treat a zero-row renewal as an immediate stop:

UPDATE job_lease
   SET expires_at = now() + interval '5 minutes'
 WHERE job_name = 'nightly-reconcile'
   AND owner = :worker_id
   AND fencing = :fencing
RETURNING expires_at;

Set the expiry from data, not vibes. Log clock_timestamp() at the start and end of the critical section in production, read the high percentile after a week, and make the lease several times longer than that while renewing at a fraction of it. If a run can occasionally take much longer than usual, chunk the work and renew between chunks.

I still use pg_advisory_xact_lock. I use it for what it is: a lock for the length of one transaction, on a connection I control. Anything longer, or anything that has to survive a reconnect, gets a row and a token.

Your Retry Logic Is a Load Amplifier

晖莫 — Mon, 14 Sep 2026 13:55:45 +0000

Our cache cluster blipped for four seconds at 09:14. It came back at 09:14:04 and immediately fell over again, and this time it stayed down for nineteen minutes. Nothing was wrong with the cache. We had DDoS'd ourselves with our own retry policy, from roughly 900 pods running the same library.

The code looked responsible. It was the pattern every blog post recommends:

import random, time

def backoff_delay(attempt, base=0.5, cap=30.0):
    # exponential backoff, no jitter
    return min(cap, base * (2 ** attempt))

Why synchrony is the bug

Here is the mechanism I missed for a long time. Every client that fails at the same wall-clock instant computes the same next-attempt time, because the delay is a pure function of the attempt number. Pods don't have independent attempt counters in the interesting case — they all fell over together when the dependency degraded, so they're all on attempt 0, then all on attempt 1, and so on.

The result is not a smooth smear of retries. It's a train of discrete spikes. At t+0.5s, all 900 pods retry. The remaining capacity absorbs maybe half of them. The other half fail, and now they all retry together at t+1.5s. Each wave is bigger than the load the dependency can take, and the waves line up exactly at the moments the dependency is weakest — because a saturated service recovers slower than it degrades.

The failure mode has a name worth knowing: a retry storm, or metastable failure. The system doesn't return to its original state when the trigger is removed, because the retries themselves are the load.

I confirmed it by logging not the retry count but the histogram of retry timestamps across the fleet. A healthy client pool produces a flat distribution. Ours had three sharp bars, 500ms apart. That histogram is the measurement you want — it's cheap, and it tells you immediately whether you have a synchrony problem.

Full jitter and decorrelated jitter

The fix is to make the delay a random variable instead of a function. AWS's architecture blog popularized the comparison, and full jitter is the one I use by default:

import random

def sleep_full_jitter(attempt, base=0.5, cap=30.0):
    # pick uniformly from [0, capped exponential]
    return random.uniform(0, min(cap, base * (2 ** attempt)))

def sleep_decorrelated(attempt, base=0.5, cap=30.0, prev=0.0):
    # sleep = min(cap, random(base, prev * 3))
    return min(cap, random.uniform(base, prev * 3))

Full jitter spreads arrivals uniformly across the window. Decorrelated jitter is stateful: each delay is drawn between base and three times the previous delay, which gives a random walk that widens over time and tends to keep the average load lower on long outages. Full jitter is simpler and has no per-attempt state to get wrong. Decorrelated wins when retries can last for minutes and you want the tail to stretch.

The important property of both: the expected rate of retries stays roughly constant rather than spiking, even if the number of clients is large. That's the whole point. You are converting a synchronizing process into a dispersing one.

Retry budgets beat per-call counts

Jitter alone wasn't enough for us, because the request amplifier was still unbounded. The real control is a retry budget: cap retries as a fraction of total requests per client, not per call.

Instead of "each call may retry 3 times," say "this client may issue retries equal to 10% of its successful requests, refilled continuously." Google's SRE book describes this as a retry budget; Finagle exposes it as RetryBudget. The property you get is proportionality: when the dependency is healthy, retries are cheap and plentiful. When it's degraded and most requests fail, the budget drains and retries stop almost entirely. It's a negative feedback loop where per-call counts are a fixed multiplier.

A budget also bounds the worst case. With 3 retries per call at 5 layers of the stack, one user request can become 4^5 = 1024 calls. I have seen this exact multiplication in a service mesh, and it is not theoretical.

Circuit breakers and idempotency

Two things have to be true or none of this works.

The circuit breaker trips on failure rate over a rolling window, and it must have a half-open state that admits a small number of probes. Without it, retries keep the load on a dead dependency while the breaker's state machine still reports closed. With it, the breaker and the budget do different jobs: the budget limits how much extra traffic you generate, the breaker stops generating traffic at all.

Idempotency is the harder one. Retrying a non-idempotent POST /payments doesn't amplify load, it duplicates money. Every retried operation needs an idempotency key that the server deduplicates on, and the client needs to send the same key on retry, not a fresh one. If you can't make an operation idempotent, don't retry it — return the error and let the caller decide.

The version of backoff_delay I run today is roughly fifteen lines, and the diff that mattered was adding random.uniform(0, ...). That one call was the difference between a four-second blip and a nineteen-minute outage.

Why the index I added made my query slower

晖莫 — Mon, 14 Sep 2026 13:50:54 +0000

I added CREATE INDEX ON events (status) on a Friday. By Monday the dashboard query that looks for pending events was slower than before I touched it. The index was not corrupt, the query was not wrong, and nothing had been deployed in between. The planner had just picked a plan that costs more on this table.

The plan changed, not the data

status has four values, and pending is most of the rows, especially right after a backlog. Postgres will still reach for an index when the predicate is not selective, because the planner decides from the row estimate in pg_statistic, which is built by sampling the table during ANALYZE. If the estimate says "few rows", it chooses a bitmap index scan or a plain index scan. Then it fetches heap pages in index order, one random read at a time, rechecks visibility on every tuple, and throws away most of what it read. A sequential scan reads the same pages in physical order and never opens the index at all.

Two more versions of the same mistake caught me earlier.

A composite index on (tenant_id, created_at) did nothing for the queries that filtered on created_at alone. Postgres cannot cheaply skip a leading column, so the index sat there costing writes and disk while the plan stayed sequential. Put the column you range-filter on first.

Stale statistics after a bulk load. I inserted a large batch and ran the app immediately. Autovacuum had not analyzed the table yet, so the estimates were from before the load and the planner was reasoning about a table that no longer existed.

Index bloat is the third one. After enough updates and deletes, index pages fill with dead entries, the index grows past what the live data warrants, and index-only scans stop being index-only because the visibility map is not set. Check pg_stat_user_indexes.idx_scan for indexes nothing reads, and pg_stat_all_tables.n_dead_tup with last_autovacuum for tables autovacuum is losing ground on.

Confirming it with EXPLAIN (ANALYZE, BUFFERS)

Guessing is how I got here. Run the actual query:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, created_at
FROM events
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

Read four things. The node type: Seq Scan turning into Bitmap Heap Scan or Index Scan is the change you are hunting. The rows= on the estimated line against the rows= on the actual line for the same node, where a wide gap means the planner was working from bad statistics. Rows Removed by Filter, where a count close to the rows returned means the index is discarding nearly everything it read. And Heap Fetches under an Index Only Scan, where a high count means the visibility map is stale, usually after heavy writes.

Buffers tell you more than wall-clock time. shared hit and shared read count the 8 kB pages the plan touched. A plan that touches more pages loses on a cold cache even when its estimated cost looks lower. Run the statement twice: the first run shows reads, the second shows hits, and neither is the same thing as the cost model.

For a real comparison, capture EXPLAIN (ANALYZE, BUFFERS) before the index exists and again after, on the same data, and compare buffer counts. Do not trust the estimated cost column on its own.

What to do instead

Make the index match the query, or drop it. A partial index is usually the answer when the hot predicate is a small slice of the table:

CREATE INDEX CONCURRENTLY events_pending_created_at_idx
ON events (created_at DESC)
WHERE status = 'pending';

That index holds only the rows the query wants, so it stays small, and it can serve the ORDER BY as well as the filter. CONCURRENTLY avoids blocking writes, at the cost of a slower build and a possible invalid index if the build fails — check pg_index.indisvalid before assuming it worked.

The rest of the toolbox: put the filtered column first in a composite index, add INCLUDE columns when you want an index-only scan, run ANALYZE yourself after a bulk load rather than waiting for autovacuum, and use CREATE STATISTICS when two columns are correlated and the single-column estimates mislead the planner. Then verify the fix the same way you found the problem. After the change, EXPLAIN (ANALYZE, BUFFERS) should show fewer shared buffers than the plan it replaced. If it does not, the index is not paying for its write cost, and DROP INDEX CONCURRENTLY is the honest fix.

Idempotency keys are the cheapest reliability feature you can ship

晖莫 — Sun, 13 Sep 2026 17:31:21 +0000

Every payment integration eventually ships the same bug. A client sends POST /charge, the request succeeds, the response times out in transit, the client retries, and the customer is billed twice. Nobody wrote incorrect logic. The bug lives in the gap between "the work happened" and "the caller learned that the work happened", and no amount of careful coding closes that gap on its own.

Idempotency keys close it. The idea is small enough to explain in one sentence: the caller attaches a unique key to a request, and the server promises that the same key will produce the same outcome exactly once, no matter how many times it arrives.

Why retries are not optional

If you run anything over a network, retries are already happening. HTTP clients retry. Load balancers retry. Your own SDK retries on connection reset. Kubernetes restarts pods mid-request and the work is delivered again by a queue redelivery you did not configure.

So the question is never "should we handle duplicates". The question is whether you handle them deliberately or discover them in a support ticket at 2am with a customer asking why their card was charged three times.

The four lines that make it work

The storage design is what people get wrong. An idempotency record needs to capture three things: the key, the request fingerprint, and the result. The result matters as much as the key, because a retry must return the original response, not just refuse to repeat the work.

create table idempotency_keys (
  key            text primary key,
  request_hash   text        not null,
  status         text        not null default 'in_progress', -- in_progress | done
  response_body  jsonb,
  response_code  int,
  created_at     timestamptz not null default now(),
  expires_at     timestamptz not null
);

create index on idempotency_keys (expires_at);

The primary key on key is the whole trick. It converts "have I seen this before?" from an application-level check into a database guarantee, which means it stays correct when two requests arrive at the same millisecond on different machines.

The part nobody warns you about

The naive flow — read the row, do the work, write the row — has a race. Two concurrent retries both read "no record", both charge the card, and both write. The primary key does not save you, because neither transaction tried to insert until after the damage was done.

The fix is to make the insert the claim:

def claim(key: str, request_hash: str) -> Claim:
    try:
        db.execute(
            "insert into idempotency_keys (key, request_hash, expires_at)"
            " values (%s, %s, now() + interval '24 hours')",
            (key, request_hash),
        )
        db.commit()
        return Claim.ACQUIRED
    except UniqueViolation:
        row = db.fetch_one("select * from idempotency_keys where key = %s", (key,))
        if row["request_hash"] != request_hash:
            # Same key, different body: the caller has a bug. Refuse loudly.
            raise Conflict("idempotency key reused with a different payload")
        if row["status"] == "done":
            return Claim.REPLAY(row["response_code"], row["response_body"])
        return Claim.IN_PROGRESS

Three outcomes, and each one deserves a different HTTP status. ACQUIRED means do the work. REPLAY means return the stored response verbatim, with the original status code — a replayed 201 must not become a 200. IN_PROGRESS means a concurrent attempt is running right now, and the correct answer is 409 Conflict with a Retry-After, not a second charge.

That request_hash comparison is the detail that separates a robust implementation from a dangerous one. Without it, a buggy client that reuses a key for a different payload gets the previous response silently, and you have created a data corruption mechanism that looks like a reliability feature.

Expiry is a product decision, not a technical one

Keys cannot live forever. The window should cover the longest plausible retry chain: client-side backoff, a queue redelivery, and a human clicking the button again after lunch. Twenty-four hours covers all of that for most products. Card networks care much less about your key than you do — the money moving twice is the problem, not the row.

Set expires_at when you insert, delete on a schedule, and index the expiry column so the cleanup job is a range scan instead of a table scan. If you skip the index, the cleanup query becomes the next incident.

What it costs

Forty lines, one table, one index, one cron job. Compare that against the cost of a duplicate-charge incident: refunds, a reconciliation script written under pressure, a support conversation you cannot automate, and a customer who now checks their statement.

Reconciliation is work you do after the fact with incomplete information. Idempotency keys are work you do before the fact with complete information. Ship the keys.