Managed PostgreSQL's connection pool found a race condition my local tests kept missing

Question

I had a read-then-write bug in an app: read a balance, do the arithmetic in the application, write it back, log the previous value. Classic lost update. I knew it was there in theory. Every time I reproduced it locally I got a different answer though, somewhere between four and six lost writes out of ten, and that’s exactly the kind of flaky result that makes you shrug and move on.

Running it against a DigitalOcean Managed PostgreSQL cluster is what pinned it down. On the direct connection it behaved much as it did locally. Through the PgBouncer connection pool it became consistent: same shape every run, and the failure was obvious rather than intermittent.

Below are the numbers from all three environments, why the pool turns this particular bug from flaky to reproducible, and the Postgres 18 syntax that fixed it in a single statement. Everything came off a real cluster I created for the test and destroyed afterwards.

KFSys · Accepted Answer

The setup One account, balance 100. Ten workers, each withdrawing 10 concurrently.Correct outcome is a balance of 0 and an audit log that steps down 100 to90, 90 to 80, and so on, ten distinct rows. Each worker did the thing almost every codebase does somewhere: BEGIN; SELECT balance FROM accounts WHERE id = 1; -- app reads 100 UPDATE accounts SET balance = 90 WHERE id = 1; INSERT INTO audit_log (old_balance, new_balance) VALUES (100, 90); COMMIT; No artificial delay anywhere. I ran it in three places. The numbers final balance distinct old_balance (want 0) values logged (want 10) local Docker, postgres:18 50 / 40 / 60 5 / 6 / 4 Managed PG, direct :25060 20 / 20 / 50 8 / 8 / 5 Managed PG, pool :25061 80 / 70 / 70 2 / 3 / 3 Three runs each. Look at the local row first. The result moves every time,between four and six writes lost. That’s a real bug, and it’s also the kind you can talk yourself out of,because running it again gives you a number that looks less alarming. The pooled row does not wobble. Seven or eight writes lost, every run, andan audit log holding two or three distinct values for ten separate writes. Why the pool made it consistent The pool was created in transaction mode with size 5: doctl databases pool create racepool \ --mode transaction --size 5 --db defaultdb --user doadmin Ten clients, five backend connections. PgBouncer lets five transactionsthrough at a time. Those five read the balance at more or less the sameinstant, work out the same new value, and write it. Then the pool frees upand the next five do the same thing one step down. That’s why the distinct-value count lands on two or three. You’re gettingone value per batch rather than one per worker. The important part is what this means. The pool did not introduce the race.The application code was always wrong, on every environment, including thelaptop. What the pool did was group the concurrent transactions into tidybatches, which turned an intermittent failure into a deterministic one. For debugging that’s a gift. A bug that fails the same way every run is abug you can fix. It’s the version that quietly loses four writes now andthen that reaches production. The fix is one statement Do the arithmetic in the database so the read and the write cannot beseparated: UPDATE accounts SET balance = balance - 10 WHERE id = 1; That works on any Postgres version, and it’s the bit that stops the lostupdate. What Postgres 18 adds is that the same statement can alsotell you what it replaced: UPDATE accounts SET balance = balance - 10 WHERE id = 1 RETURNING old.balance AS was, new.balance AS now; was | now -----+----- 100 | 90 So the audit row comes from the operation that made the change, rather thanfrom an application repeating a number it read a moment earlier: WITH moved AS ( UPDATE accounts SET balance = balance - 10 WHERE id = 1 RETURNING old.balance AS was, new.balance AS now ) INSERT INTO audit_log (account_id, old_balance, new_balance) SELECT 1, was, now FROM moved; Same ten workers, through the same pool that had been losing eight of them: final balance 0 audit rows 10 distinct old_balance values 10 old_balance | new_balance -------------+------------- 100 | 90 90 | 80 80 | 70 70 | 60 60 | 50 50 | 40 40 | 30 30 | 20 20 | 10 10 | 0 Ten out of ten, in all three environments, direct and pooled. The staircasesurvives. Notes specific to Managed Databases You may already be on 18. Standard Edition covers 14 up to 18. Advancedcovers 16 up to 18, and new Advanced clusters now default to 18, so thismay already be sitting there unused. Check with: SHOW server_version; My test cluster reported 18.6. Older clusters can be upgraded in placefrom the control panel. RETURNING old/new works through the pooler. No special handling andno session state involved, which makes sense for a single statement. If youare on a pooled connection string you lose nothing here. On 17 and earlier the error is not obvious. You don’t get a messagenaming the version, just: ERROR: missing FROM-clause entry for table "old" The upsert trick works too. On an INSERT there is no old row, so oldis null, which finally gives a clean way to tell an insert from an update: INSERT INTO accounts VALUES (3, 10) ON CONFLICT (id) DO UPDATE SET balance = EXCLUDED.balance RETURNING old.id IS NULL AS was_inserted; That replaces the xmax::text::bigint <> 0 trick, which works but requiresknowing what xmax is. Caveats on the numbers Three runs per environment is a demonstration, not a benchmark. Pool size 5 is what shapes the batching. Change the size and thedistinct-value count changes with it. Treat it as the mechanism, not afixed number. My managed runs spawned a container per worker. The local ones reused asingle container. That means different startup jitter, so the local andmanaged columns aren’t a clean like-for-like and I wouldn’t read much intothat particular gap. Pooled versus direct is the comparison that holds up.Those two ran identically. The only thing that differed was the port. The takeaway I would keep: if you have a read-then-write pattern anywhere,a pooled connection is a good place to go looking for it, because the poolwill show you the failure consistently instead of once in a while.

alexdo · Answer

Heya, It is worth checking your cluster’s actual version either way, since if it’s Advanced Edition and was created recently it may already have defaulted to 18 per DigitalOcean’s docs, in which case the RETURNING old/new version above is the better option. Regards

Bobby · Answer

Hi there, Useful writeup and the observation that PgBouncer makes timing bugs consistent rather than flaky is worth remembering. The reason this happens makes sense once you think about it: PgBouncer in transaction mode multiplexes many client connections onto a smaller pool of actual Postgres connections. That changes the scheduling characteristics enough that a race window which only occasionally aligns locally, tends to align much more predictably through the pool because connection reuse and query queuing introduce a more regular timing pattern. For anyone hitting this in production, the standard fix is SELECT ... FOR UPDATE to lock the row during the read, or the newer approach in Postgres 15+ using INSERT ... ON CONFLICT DO UPDATE for atomic upserts where applicable. Curious what the Postgres 18 syntax you landed on was, sounds like it might be something newer worth knowing about.

Become a contributor for community

DigitalOcean Documentation

Resources for startups and AI-native businesses

The developer cloud

Start building today

Report this

Managed PostgreSQL's connection pool found a race condition my local tests kept missing

Related questions

What is the recommended DigitalOcean setup for a DEV PostgreSQL database alongside PROD?

What is the recommended DigitalOcean setup for a DEV PostgreSQL database alongside PROD?

What is the recommended DigitalOcean setup for a DEV PostgreSQL database alongside PROD?

Migrating an existing PostgreSQL database to a Digital Ocean Managed PostgreSQL cluster

Is CA_CERT available via the connection pool variable syntax in App Platform?

Django App & PostgreSQL Database Connectivity Between Two Droplets

Managed PostgresQL in ATL1 Timeline

Assigning pg_monitor role to users in managed PostgreSQL database

How to create a schema in a Dev Database?

When can we expect Postgres 15.14 Managed Database support to be available?

DO Managed Postgresql doadmin password automatically changing after manually changed.

Postgresql: terminating connection due to administrator command

Report this

Managed PostgreSQL's connection pool found a race condition my local tests kept missing

Related questions

What is the recommended DigitalOcean setup for a DEV PostgreSQL database alongside PROD?

What is the recommended DigitalOcean setup for a DEV PostgreSQL database alongside PROD?

What is the recommended DigitalOcean setup for a DEV PostgreSQL database alongside PROD?

Migrating an existing PostgreSQL database to a Digital Ocean Managed PostgreSQL cluster

Is CA_CERT available via the connection pool variable syntax in App Platform?

Django App & PostgreSQL Database Connectivity Between Two Droplets

Managed PostgresQL in ATL1 Timeline

Assigning pg_monitor role to users in managed PostgreSQL database

How to create a schema in a Dev Database?

When can we expect Postgres 15.14 Managed Database support to be available?

DO Managed Postgresql doadmin password automatically changing after manually changed.

Postgresql: terminating connection due to administrator command