Postgres race conditions live in a window of a few milliseconds

I wrote a lost-update test, ran it, and found something I had not expected. The race was not hiding. It had a narrow window, and my tests either landed inside it or missed it completely depending on a few milliseconds of timing.

Here is the experiment. Two transactions read the same balance, subtract 10, and write the result back. No locks, so the correct final balance is 80 and a lost update leaves 90. The second transaction starts a fixed number of milliseconds after the first. I ran each setting 100 times against a local PostgreSQL 16 with Node 22, on two machines: a Windows 11 laptop and a Linux box.

Start offset of tx 2	Windows: plain run	Windows: fuzzer	Linux: plain run	Linux: fuzzer
0 ms	100 / 100	96–98 / 100	95 / 100	88 / 100
1 ms	96 / 100	92–96 / 100	81 / 100	88 / 100
2 ms	72 / 100	92–95 / 100	21 / 100	87–89 / 100
5 ms	5 / 100	93–94 / 100	0 / 100	85–86 / 100
15 ms	1 / 100	91 / 100	0 / 100	78–90 / 100

Lost updates out of 100 runs. The fuzzer columns show the range over two seeds (42 and 7) with a maximum injected delay of 30 ms.

If both transactions start together, as they do in a Promise.all test, the plain run finds the bug nearly every time on both machines. A few milliseconds later the picture changes. At a 5 ms offset the plain run found it 5 times out of 100 on one machine and never on the other, and at 15 ms it found it once or never. The width of the window depends on the machine: at a 2 ms offset the plain run found the bug 72 times on the Windows laptop and 21 times on the Linux box. On the Linux box a query through the proxy takes about 0.2 ms, so a four-statement transaction is over in roughly a millisecond.

Production code does not obviously start its transactions together. A connection pool hands out a client, a request handler does some work, a function awaits something before BEGIN. Gaps of a few milliseconds are plausible, though I have not measured them in a real service, and they put the race outside the window your test can see. The fuzzer columns are the tool I will describe below. It adds random delays before queries, so the two transactions overlap even when they start far apart. At 0 ms it does slightly worse than the plain run, because its random delays sometimes pull two transactions apart. It is built for the offsets where the plain run is blind.

This is a toy scenario, one row and no locking, so almost any widening of the window finds the bug. The point is not that the number is impressive. The point is that a green test does not tell you the race is absent, only that the test did not reach it.

What I built

pg-interleave is a proxy that speaks the PostgreSQL wire protocol (v3.0). It sits between your code and Postgres, and a test controls it over a small HTTP API. The server is written in Rust on tokio and the client is a TypeScript SDK. It is alpha software, MIT licensed, currently v0.7.1.

test code ──HTTP :9090──▶ control plane ─┐
                                          ▼
pg / any client ──:5433──▶ proxy ──────▶ PostgreSQL :5432

Because it understands the protocol, it can hold one specific statement while other connections keep running. Nothing in the application changes: you point the database URL at port 5433 instead of 5432.

Here is the same lost update, but deterministic. Client A reads 100 and plans to write 80. We freeze A's UPDATE inside the proxy, let B run a full transaction, then release A.

import { Client } from "pg";
import { PgInterleaveClient } from "pg-interleave";

const url = "postgres://postgres:postgres@127.0.0.1:5433/postgres";
const chaos = new PgInterleaveClient("http://127.0.0.1:9090");
const clientA = new Client({ connectionString: url });
const clientB = new Client({ connectionString: url });
await clientA.connect();
await clientB.connect();

// Hold A's UPDATE in the proxy. Postgres has not seen it yet.
const barrier = await chaos.intercept({
  client: clientA,
  onQueryMatch: /UPDATE accounts SET balance = \$1/i,
  action: "pause_before_query",
  times: 1,
});

await clientA.query("BEGIN");
const readA = await clientA.query("SELECT balance FROM accounts WHERE id = 1");
const balanceA = readA.rows[0].balance - 20; // 80

const txA = (async () => {
  await clientA.query("UPDATE accounts SET balance = $1 WHERE id = 1", [balanceA]);
  await clientA.query("COMMIT");
})();

await barrier.wait(); // A's UPDATE is now frozen in the proxy

// B runs a complete transaction while A is frozen
await clientB.query("BEGIN");
const readB = await clientB.query("SELECT balance FROM accounts WHERE id = 1");
await clientB.query("UPDATE accounts SET balance = $1 WHERE id = 1", [readB.rows[0].balance - 30]); // 70
await clientB.query("COMMIT");

await barrier.resume(); // A's stale write lands on top of B's commit
await txA;

const res = await clientB.query("SELECT balance FROM accounts WHERE id = 1");
console.log(res.rows[0].balance); // 80, but the correct total is 50

No sleep, no timing luck: the order is the same on every run. The repository also has the SELECT ... FOR UPDATE version of this scenario (packages/sdk-ts/src/lost-update.test.ts), where B waits on A's row lock and the result is correct.

Torn commits and retry loops

Reordering statements is half of it. The other half is what happens when a connection dies at the worst moment: after Postgres has committed, before the client hears about it.

The proxy can do exactly that. With drop_connection_on_commit it lets the commit finish on the server and then closes the client's socket. The client sees a network error for a transaction that actually succeeded.

Here is a function that inserts a $50 order, wrapped in a generic retry helper:

import pRetry from "p-retry";
import { Client } from "pg";
import { PgInterleaveClient } from "pg-interleave";

const url = "postgres://postgres:postgres@127.0.0.1:5433/postgres";
const chaos = new PgInterleaveClient("http://127.0.0.1:9090");
let attempts = 0;

const createOrder = async () => {
  attempts++;
  const client = new Client({ connectionString: url });
  client.on("error", () => {}); // the dropped socket emits an 'error' event
  await client.connect();

  if (attempts === 1) {
    await chaos.intercept({
      client,
      onQueryMatch: /COMMIT/i,
      action: "drop_connection_on_commit",
      times: 1,
    });
  }

  try {
    await client.query("BEGIN");
    await client.query("INSERT INTO billing_orders (account_id, amount) VALUES (101, 50)");
    await client.query("COMMIT");
  } finally {
    await client.end().catch(() => {});
  }
};

await pRetry(createOrder, { retries: 2, minTimeout: 50 });
// SELECT * FROM billing_orders WHERE account_id = 101  ->  2 rows, $100 charged

The first attempt commits and the socket is cut. p-retry sees an error, assumes nothing happened, and tries again. The table now has two rows.

The retry helper did what it was told. The transaction was not safe to repeat, because it had no idempotency key. The usual fix is a unique constraint plus INSERT ... ON CONFLICT (idempotency_key) DO NOTHING. A mock cannot show you this, because a mocked connection drop does not leave a real committed row behind.

The schedule fuzzer

Barriers are for interleavings you already thought of. The fuzzer is for the ones you did not. Start the proxy with --fuzz and it delays queries (including BEGIN and COMMIT) by a random amount up to --fuzz-max-delay-ms, so concurrent transactions reach the database in different orders from run to run.

pg-interleave --fuzz --seed 42 --fuzz-max-delay-ms 30

If you omit --seed, the proxy picks one and logs it. Each connection gets its own random stream derived from the seed and the connection id (SplitMix64), so the same seed gives every connection the same sequence of delays. Client-side timing still varies by a few milliseconds, so rerunning with a seed gives a close result rather than an identical one. That is visible in the Linux column above: seeds 42 and 7 differed by up to 12 runs out of 100 at the 15 ms offset.

Fuzzing samples schedules. A green run means those schedules passed, not that the code is free of races.

The 44 ms trap

My first benchmark was wrong, and the way it was wrong is worth knowing about. An early version of my README claimed that the fuzzer uncovers 100% of lost updates. That number was an artifact. The proxy was adding about 44 ms to every query, which is consistent with Nagle's algorithm interacting with delayed ACKs on the proxy's sockets. With queries that slow, each transaction lasted long enough that the two overlapped whether or not the fuzzer was on, so the comparison meant nothing.

Setting TCP_NODELAY on both the client-facing and the upstream socket dropped the added latency to about 0.2 ms per query, and the plain baseline at a 5 ms offset fell to between 0 and 5 out of 100, depending on the machine. If you build a proxy, measure what the proxy itself costs before you trust any timing-dependent result.

Testing it on pg-boss, and the bug in my own tool

To see how it behaves against software I did not write, I built a test suite for pg-boss v12.38.1, a job queue on top of Postgres. It has eight tests, and none of them found a bug in pg-boss:

• A synthetic 40P01 deadlock injected into the fetch statement: the rule fired once, the worker kept polling and processed the job.
• A torn commit on job completion: the job ended up completed and the handler ran exactly once.
• The completion statement held in the proxy: the job stays active in the database while it is held and becomes completed after release.
• A handler that sleeps past the expiry timeout: the job is picked up again (retry_count 1) and runs twice, which is the documented at-least-once behavior.
• Ten concurrent send() calls with the same singleton key: one row.
• Twenty and fifty concurrent jobs: each ran exactly once.

The more interesting result was about my own tool. My first version of the deadlock test passed, and it should not have. The deadlock was never injected.

pg-boss writes its fetch statement across several lines. Abridged from its source:

UPDATE pgboss.job_common j SET
  state = 'active',
  started_on = ...

My pattern was a single-line regex, UPDATE ... SET state = 'active', and in a regex . does not cross a newline. The rule never matched, the proxy forwarded the query untouched, the worker behaved normally, and the test went green. A rule that silently does not fire looks exactly like a system that handled the fault. I only learned about it when a reviewer ran my pattern against the real multi-line statement. A second problem sat next to it: the SDK passed RegExp.source to the proxy, which dropped flags such as /i.

Two changes came out of that:

1. The SDK now compiles a JavaScript RegExp with the dotAll flag and keeps i and m, so .* crosses newlines.
2. Every rule handle has assertFired(n), which throws if the rule did not fire exactly n times.

const deadlock = await chaos.injectDeadlock({
  onQueryMatch: /UPDATE pgboss\.job_common.*SET.*state\s*=\s*'active'/i,
  times: 1,
});

// ... run the scenario ...

await deadlock.assertFired(1); // this test fails if the fault never happened

Now an ORM that reformats its SQL breaks the test loudly instead of making it pass for the wrong reason.

Limitations

• Plaintext only. The proxy answers N to SSLRequest, so sslmode=require does not work. disable and prefer do.
• Flush pipelines. Drivers that rely on explicit Flush frames before sending Sync can stall the proxy.
• Data-modifying CTEs. The AST selector looks at the top-level statement, so WITH u AS (UPDATE ...) SELECT ... is classified as SELECT. Use a regex for those.
• PgBouncer in transaction mode. Backend PIDs change between transactions, so route rules with SQL comment tags instead.
• Shared state. Rules and barriers live in the proxy process. Run test files serially and call reset() between tests.
• No authentication on the control plane. Keep it on 127.0.0.1.
• One toy benchmark, results vary by machine. The table above is a single scenario, and the race window was a different width on my two machines. I have not measured how the fuzzer behaves on a large real application.
• Alpha. CI runs on PostgreSQL 14, 15 and 16. APIs may change.

When not to use it

If you can write your scenario as plain sequential awaits on two clients, you do not need a proxy. PostgreSQL's own isolationtester explores step permutations across sessions and is a better fit when you are testing the database engine itself. Toxiproxy works at the TCP level, which is the right layer for latency and dropped connections but cannot tell a SELECT from a COMMIT. pg-interleave is for the case in between: application code you do not want to modify, where you want to stop one specific statement.

Try it

npm install -D pg-interleave

Run the proxy, either from a release binary:

./pg-interleave --listen-addr 127.0.0.1:5433 --target-addr 127.0.0.1:5432

or with Docker (the arguments replace the image defaults, so pass all three):

docker run -d --name pg-interleave \
  --add-host=host.docker.internal:host-gateway \
  -p 127.0.0.1:5433:5433 -p 127.0.0.1:9090:9090 \
  ghcr.io/alt-f6/pg-interleave:latest \
  --listen-addr 0.0.0.0:5433 \
  --control-addr 0.0.0.0:9090 \
  --target-addr host.docker.internal:5432

Point your test database URL at port 5433. To reproduce the table:

cd examples && npm install
# plain run
OFFSETS=0,1,2,5,15 npx tsx lost_update_benchmark.ts
# with the fuzzer (start the proxy with: --fuzz --seed 42 --fuzz-max-delay-ms 30)
OFFSETS=0,1,2,5,15 npx tsx lost_update_benchmark.ts

The code is at github.com/alt-f6/pg-interleave. If you try it and something breaks, please open an issue. I would especially like to hear about drivers or ORMs where the proxy misbehaves.