Show HN: Pg-interleave (Postgres proxy for reproducible race-condition tests)

Deterministic concurrency tests for PostgreSQL. A wire-protocol proxy that sits between your code and Postgres and lets a test freeze a specific statement, hold its response, tear a connection right after a commit, or inject a 40P01 deadlock, on command and in a fixed order. No changes to the application code. Status: alpha. Rust server plus TypeScript SDK. CI runs against PostgreSQL 14, 15 and 16. APIs may change. Plaintext connections only (see Limitations). test code ──HTTP :9090──▶ control plane ─┐ ▼ pg / any client ──:5433──▶ proxy ──────▶ PostgreSQL :5432 Why • Install • Quickstart • Capabilities • SDK • Schedule Fuzzer • Record & Replay • pg-boss case study • HTTP API • Alternatives • Limitations Results at a glance The race is real in both runs. Without the fuzzer, queries finish in well under a millisecond, so the transactions almost never overlap. The fuzzer moves them into overlapping orders. Method and commands to reproduce are in Empirical detection rate. Most concurrency tests either run two transactions with Promise.all and hope, or sequence them by hand with await. The second is already deterministic, as long as you control every statement. pg-interleave is for the case where you don't: a function in your application opens a transaction and runs several statements, and you want to freeze it between two of them, run something else, then let it continue. The test only says "stop the connection that is about to run UPDATE accounts ...". If your scenario can be written as plain sequential awaits on two clients, you don't need this tool. PostgreSQL's own isolationtester is also worth knowing: it explores permutations of steps across sessions and lives inside the Postgres source tree. Why not just Promise.all or sleep(50)? Uncoordinated tests pass by accident. Racing transactions rarely overlap on a local connection. The bug is there, but the plain test almost never sees it (see the benchmark in the Schedule fuzzer section). sleep() is slow and brittle. Arbitrary delays lengthen CI and still fail under CPU throttling or a busy runner. Barriers pin the order. pg-interleave pauses statements at the PostgreSQL wire-protocol boundary, so you choose which statement goes first, every time. Docker images, release binaries and the npm package are published with each GitHub release (latest: v0.7.1). 1. Run the proxy Docker. Pass all three flags: docker run arguments replace the image defaults. 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 Standalone binary. Download the archive for your platform from Releases: The archive contains a single executable named pg-interleave (pg-interleave.exe on Windows). It is the same program as the pg-interleave-server crate. tar xzf pg-interleave-linux-x86_64.tar.gz ./pg-interleave --listen-addr 127.0.0.1:5433 --target-addr 127.0.0.1:5432 2. Install the SDK npm install -D pg-interleave Requires Node 20+. From source You need a running PostgreSQL, Rust 1.88+ (edition 2024, the code uses let-chains) and Node 20+. # start the proxy cargo run --release -p pg-interleave-server -- \ --listen-addr 127.0.0.1:5433 \ --target-addr 127.0.0.1:5432 \ --control-addr 127.0.0.1:9090 # run the SDK tests cd packages/sdk-ts npm ci # also builds dist/ via the prepare script npm test Point your client at port 5433 instead of 5432. The control plane is on 9090. Defaults: listen 127.0.0.1:5433, target 127.0.0.1:5432, control 127.0.0.1:9090, barrier TTL 5000 ms (--barrier-ttl-ms). The target address can be a hostname. 10-second example Freeze an UPDATE inside the proxy, look at the table from another connection, then let it through. import { Client } from "pg"; import { PgInterleaveClient } from "pg-interleave"; const chaos = new PgInterleaveClient("http://127.0.0.1:9090"); const url = "postgres://postgres:postgres@127.0.0.1:5433/postgres"; await chaos.reset(); const observer = new Client({ connectionString: url }); const a = new Client({ connectionString: url }); await observer.connect(); await a.connect(); await observer.query( "CREATE TABLE IF NOT EXISTS accounts (id int PRIMARY KEY, balance int NOT NULL)", ); await observer.query( "INSERT INTO accounts VALUES (1, 100) ON CONFLICT (id) DO UPDATE SET balance = 100", ); // Arm a barrier for connection `a` const barrier = await chaos.intercept({ client: a, onQueryMatch: /UPDATE accounts/, }); const update = a.query( "UPDATE accounts SET balance = balance - 50 WHERE id = 1", ); await barrier.wait(); // the statement is held in the proxy; Postgres has not seen it console.log((await observer.query("SELECT balance FROM accounts")).rows[0]); // { balance: 100 } await barrier.resume(); // let it through await update; console.log((await observer.query("SELECT balance FROM accounts")).rows[0]); // { balance: 50 } What it can do Behavior you can rely on (each is covered by a test): FIFO release. resume-next releases pending barriers in creation order. One-shot by default. A rule fires once (times: 1). Pass times to change it. Bad patterns fail loudly. An invalid regex is rejected with HTTP 400 at registration. Timeouts are errors, not silence. If a barrier is not released within the TTL, the client receives SQLSTATE 57014 and GET /events reports had_timeouts: true, so a test can fail on it. No silent misses. await rule.assertFired(n) checks that the rule matched exactly n times and throws otherwise. If an ORM or library changes its SQL formatting and your pattern stops matching, the test fails instead of passing for the wrong reason. Per-connection targeting. Rules can be scoped to a backend PID (the SDK reads it with pg_backend_pid()) or to a SQL comment tag. Extended protocol. Prepared statements are matched when executed, including repeated executions, and synthetic deadlocks cleanly recover the protocol stream with standard ErrorResponse frames. Parameter inspection. Match parameterized queries ($1, $2) using exact values or "*" wildcards via withParams. Example: a real lost update Transaction A reads a balance, then writes a value computed from it. We freeze A just before its UPDATE, let B complete a whole transaction, then release A. const barrier = await chaos.intercept({ client: a, onQueryMatch: /UPDATE bank_accounts SET balance = 80/, }); await a.query("BEGIN"); await a.query("SELECT balance FROM bank_accounts WHERE id = 1"); // sees 100 const txA = (async () => { await a.query("UPDATE bank_accounts SET balance = 80 WHERE id = 1"); await a.query("COMMIT"); })(); await barrier.wait(); // A is frozen before its UPDATE // B does a full read-modify-write while A is frozen await b.query("BEGIN"); const { rows } = await b.query( "SELECT balance FROM bank_accounts WHERE id = 1", ); await b.query( `UPDATE bank_accounts SET balance = ${rows[0].balance - 10} WHERE id = 1`, ); await b.query("COMMIT"); await barrier.resume(); await txA; // final balance is 80, not 70: B's update was lost The same scenario with SELECT ... FOR UPDATE makes B block on A's row lock, and the final balance is 70. Both versions are in packages/sdk-ts/src/lost-update.test.ts. Example: a retry loop that double-charges examples/retry-anomaly shows a common mistake rather than a bug in any library. A function inserts a payment inside a transaction and is wrapped in a generic retry helper (p-retry here). pg-interleave drops the connection after Postgres committed but before the client saw the result. The helper sees a network error, retries, and the order is inserted a second time: billing_orders ends up with two rows ($100 charged instead of $50). The retry was not wrong to fire; the transaction was not safe to repeat. The usual fix is an idempotency key with a unique constraint (INSERT ... ON CONFLICT (idempotency_key) DO NOTHING). This kind of failure is almost impossible to reproduce with mocks, because the commit really happened on the server. Real-world case study: pg-boss We ran pg-interleave against pg-boss v12.38.1, a PostgreSQL job queue, without modifying the library. The suite is in examples/pg-boss-exploration (8 tests). What it checks and what it found: None of these produced a bug in pg-boss. They are listed because "the fault really happened and the library coped" is the result the tool is meant to give you. What it found in pg-interleave itself. The first version of the deadlock test passed without the fault ever being injected: pg-boss writes its fetch statement over several lines, and a single-line regex never matched. Nothing failed, because a rule that does not fire is silent by default. That is why assertFired exists and why a JavaScript RegExp is now compiled with dotAll and keeps its i flag (see Query matching semantics). const chaos = new PgInterleaveClient("http://127.0.0.1:9090"); await chaos.reset(); // clear all rules, release all barriers const barrier = await chaos.intercept({ client, // scopes the rule to this client's backend PID onQueryMatch: /UPDATE accounts SET balance/, withParams: ["*", 100], // optional: match $1, $2 arguments action: "pause_before_query", // or "pause_after_response" times: 1, // optional, default 1 }); await barrier.wait(); // resolves when the statement is frozen in the proxy await barrier.resume(); // releases the statement to PostgreSQL await barrier.assertFired(1); // throws unless the rule fired exactly once // Network fault injection const tear = await chaos.intercept({ client, onQueryMatch: /COMMIT/i, action: "drop_connection_on_commit", }); await tear.assertFired(1); // Synthetic deadlock const deadlock = await chaos.injectDeadlock({ onQueryMatch: /UPDATE accounts/, times: 1, }); await deadlock.assertFired(1); await chaos.hasTimeouts(); // true if any barrier exceeded the TTL Query matching semantics Multiline and dotAll. SQL from ORMs and drivers is often multi-line. A JavaScript RegExp is always compiled with the Rust s flag, so . matches \n. The i and m flags are preserved. This differs from plain JavaScript, where . does not cross newlines. Plain strings. A string pattern gets (?s) prepended. If it already starts with Rust inline flags such as (?is), it is used as-is. Check that it fired. Patterns match against the exact SQL text the driver sends. When in doubt, record a run with --record and read the statements, then call assertFired() in the test. Connection pool compatibility (SQL comment routing) With connection pools (pg.Pool, Prisma, Drizzle), physical connections are shared dynamically. Route rules with SQL comment tags instead of backend PIDs: import { Pool } from "pg"; import { PgInterleaveClient } from "pg-interleave"; const pool = new Pool({ connectionString: "postgres://postgres:postgres@127.0.0.1:5433/postgres", }); const chaos = new PgInterleaveClient("http://127.0.0.1:9090"); // 1. Intercept only queries marked with sessionTag "workerA" const barrier = await chaos.intercept({ sessionTag: "workerA", onQueryMatch: /UPDATE inventory SET stock = stock - 1/, }); // 2. Tag the query with the SDK helper const taggedSql = PgInterleaveClient.tag( "UPDATE inventory SET stock = stock - 1 WHERE id = 1", "workerA", ); await pool.query(taggedSql); // 3. Resume when ready await barrier.resume(); Prepared statement parameter matching (withParams) When an ORM uses parameterized queries ($1, $2), scope barriers to specific argument values, or use "*" as a wildcard: // Intercept only when deducting stock for item ID 2 await chaos.intercept({ client, onQueryMatch: /UPDATE params_inventory SET stock = stock - \$1 WHERE id = \$2/, withParams: [1, 2], // matches $1 = 1, $2 = 2 }); // Wildcard: any quantity, but strictly item ID 1 await chaos.intercept({ client, onQueryMatch: /UPDATE params_inventory SET stock = stock - \$1 WHERE id = \$2/, withParams: ["*", 1], }); Structural AST selectors Match queries structurally (parsed with sqlparser-rs) instead of with brittle regexes: // Freezes the matching operation on the matching table, whatever the formatting const barrier = await chaos.intercept({ client, ast: { operation: "UPDATE", table: "accounts", }, }); // Matches regardless of quotes, newlines, or schema prefixes: await client.query(` UPDATE "public"."accounts" SET balance = balance - 20 WHERE id = 1 `); await barrier.wait(); await barrier.resume(); Supported operations: SELECT, INSERT, UPDATE, DELETE. A simple-query batch with several statements matches if any statement in it matches. Data-modifying CTEs are not detected, see Limitations. Schedule fuzzer (Automated Race Condition Discovery) Barriers let you test an interleaving you already thought of. The schedule fuzzer is for the ones you did not: it adds pseudo-random delays before queries (including BEGIN and COMMIT) are forwarded to PostgreSQL, so concurrent transactions reach the database in different orders from run to run. Run your existing concurrent tests under it and see whether anything breaks, for example a lost update. # start the proxy with fuzzing enabled cargo run --release -p pg-interleave-server -- \ --fuzz \ --fuzz-max-delay-ms 30 The proxy logs the seed it picked, so a failure can be replayed: INFO pg_interleave_server: Schedule fuzzer ENABLED. Seed: 133742 (use --seed 133742 to reproduce) If a test fails in CI under fuzzing, start the proxy locally with the logged seed and rerun the same test: cargo run --release -p pg-interleave-server -- \ --fuzz --seed 133742 --fuzz-max-delay-ms 30 GET /events reports fuzzer_enabled, so a test or benchmark can confirm which mode it is running in. Capabilities & Guarantees Per-connection reproducibility. Each client connection gets an independent PRNG stream derived with SplitMix64 from (base_seed, conn_id). With the same --seed, every connection receives the same sequence of delays, without cross-connection coupling. Empirical detection rate. Scenario: an uncoordinated read-modify-write on one row, two transactions, no sleeps in the code, the second transaction starting 5 ms or 15 ms after the first, 100 runs per cell (examples/lost_update_benchmark.ts). Without the fuzzer (TCP_NODELAY enabled on both sockets): 0–5 / 100 lost updates at 5 ms and 0–2 / 100 at 15 ms (baseline scheduling noise varies by machine and OS scheduler). With the fuzzer (--fuzz --seed 42 --fuzz-max-delay-ms 30): 85–98 / 100 (e.g. 91–98 / 100 on Windows 11, 85–90 / 100 on Linux). The same seed reproduces the same delay sequence per connection. Detection rates across repeated runs are close but not identical, because client-side timing still varies (see Limitations). To reproduce: cd examples && npm install # 1. Baseline run (without fuzzer) cargo run --release -p pg-interleave-server -- --listen-addr 127.0.0.1:5433 npx tsx lost_update_benchmark.ts # prints "Schedule Fuzzer: DISABLED", then roughly 0-5/100 (machine-dependent) # 2. Fuzzer run with a fixed seed cargo run --release -p pg-interleave-server -- --fuzz --seed 42 --fuzz-max-delay-ms 30 npx tsx lost_update_benchmark.ts # prints "Schedule Fuzzer: ENABLED", then roughly 85-98/100 The script reads DATABASE_URL (default: the local proxy on port 5433) and cleans up its table. Latency cost. Every delayed query costs up to --fuzz-max-delay-ms. Keep the bound small (20–30 ms) in CI. It finds, it does not prove. Fuzzing samples schedules. A green run means these schedules passed, not that the code is race free. Run it with several seeds. Trace record & replay Record the schedule of a run, then replay it in a regression test. 1. Record Start the proxy with --record: cargo run --release -p pg-interleave-server -- --record trace.json During the test, write the in-memory trace to disk through the SDK (or POST /flush-trace): await chaos.flushTrace(); The trace is also the quickest way to see the exact SQL a library sends, which helps when writing a pattern. 2. Replay cargo run --release -p pg-interleave-server -- --replay trace.json The proxy holds outgoing statements until their turn in the recorded schedule, so the captured statement order across connections is reproduced even if the clients start in a different order. This is exercised end to end in packages/sdk-ts/src/replay.test.ts: the test sends the second connection's statements first and asserts that the recorded order still wins. HTTP control plane The control plane has no authentication. Keep it on 127.0.0.1. Comparison with alternatives Toxiproxy is the right tool for latency, bandwidth and connection-level faults on any TCP service. pg-interleave only speaks PostgreSQL, which is what lets it target a single statement. Limitations & Protocol Edge Cases pg-interleave is an L7 stateful proxy over the PostgreSQL Frontend/Backend Protocol v3.0. Keep these constraints in mind: TLS / SSL. The proxy inspects unencrypted wire-protocol frames. It answers 'N' to every SSLRequest and GSSENCRequest during startup, so clients must allow plaintext (sslmode=disable or sslmode=prefer). sslmode=require and sslmode=verify-full abort the handshake. Extended protocol with Flush ('H'). The proxy buffers messages and evaluates barriers when it receives Sync ('S') or a simple Query ('Q'). Drivers that pipeline and rely on explicit Flush frames to read intermediate results before sending Sync can deadlock the socket. Data-modifying CTEs. The AST selector inspects the root statement. For WITH moved AS (UPDATE inventory ...) SELECT * FROM moved the statement is classified as SELECT. Use a regex (onQueryMatch) for these. Connection poolers in transaction mode (PgBouncer, RDS Proxy, Supavisor). Server connections and backend PIDs change between transactions, so target rules with sessionTag comments rather than client. Do not hold a barrier longer than the pooler's query_wait_timeout. Global in-memory state. Rules, barrier queues and connection mappings are shared across the proxy process. Run test files serially (vitest --fileParallelism=false), call await chaos.reset() between tests, or use distinct sessionTag values. Out-of-band cancellation. A statement held by pause_before_query has not reached PostgreSQL. A CancelRequest (80877102) from another connection therefore has no effect on the held barrier until it is resumed or exceeds --barrier-ttl-ms. Fuzzer determinism boundaries. --seed fixes the sequence of delays the proxy injects. Client-side scheduling (Node.js event loop latency, OS preemption) still varies by milliseconds, so benchmark figures are an empirical distribution, not a lockstep replay. Development cargo fmt --check cargo clippy --all-targets -- -D warnings cargo test # decoder unit and property tests # integration tests need Postgres and the proxy running on 5433/9090 cd packages/sdk-ts && npm ci && npm test # pg-boss suite cd examples/pg-boss-exploration && npm install && npm test CI runs the test suite on a matrix of Node 18, 20, 22 and PostgreSQL 14, 15, 16. See CONTRIBUTING.md before opening a pull request, and SECURITY.md to report a vulnerability.