Why `FOR UPDATE SKIP LOCKED` Quietly Took Down Our Database (And `EXPLAIN ANALYZE` Lied to Us the Whole Time)

A story about a 0.143ms query that somehow pinned our Postgres CPU at 98%, and the one formula from queueing theory that finally explained why.

---

The Symptom

We run a small, boring-sounding microservice: it hands out unique IDs to callers. One endpoint, one job. GET /id?uniqueKey=... → here's your ID, go away.

It should be one of the fastest endpoints in our stack. Grab an available ID from a pool, mark it as taken, return it. Milliseconds, easily.

Except in production, at a perfectly reasonable ~50 requests per second, this is what we were actually seeing:

• Average response time: 8–12 seconds.
• Worst case: 67 seconds.
• Postgres CPU: pinned at ~98%.

For an endpoint that does one SELECT, a tiny bit of logic, and one UPDATE. Something was very wrong, and the database was clearly the one sweating.

The Query Looked Innocent

Here's roughly what the claim logic looked like under the hood:

SELECT * FROM available_ids
WHERE assigned = 'N'
FOR UPDATE SKIP LOCKED
LIMIT 1;

This is a completely standard pattern for building a work queue on top of Postgres: grab one unclaimed row, lock it so nobody else can grab it too, and if you happen to land on a row someone else already locked, just skip it and keep looking. It's in every "how to build a job queue in Postgres" blog post. It's not exotic. It's not wrong.

Our first suspicion, reasonably, was a missing index. The WHERE assigned = 'N' filter had nothing backing it, so we assumed Postgres was doing a full sequential scan on every single request. Added a partial index:

CREATE INDEX CONCURRENTLY idx_available_unassigned
ON available_ids (id)
WHERE assigned = 'N';

Index in place. Ran EXPLAIN ANALYZE to confirm the fix.

The Plot Twist: The Query Plan Looked Perfect

Limit (cost=0.42..0.58 rows=1 width=100) (actual time=0.073..0.074 rows=1 loops=1)
  ->  LockRows (cost=0.42..72259.34 rows=449333 width=100) (actual time=0.071..0.071 rows=1 loops=1)
        ->  Index Scan using idx_available_unassigned (actual time=0.058..0.058 rows=1 loops=1)
Execution Time: 0.143 ms

The index was being used. The query ran in 0.143 milliseconds. Buffer hits: a handful. This is, by every normal measure, a great query plan.

And the database was still on fire.

This is the moment that made the whole investigation interesting, instead of just "add an index and go home." A query that is objectively fast when you test it by hand was somehow bringing a production database to its knees when real traffic hit it. Those two facts should not both be true. One of them had to be lying and it wasn't the database.

The Math We Were Missing

Here's the reasoning trap, and I suspect it's a very common one: at 50 requests per second, it feels natural to assume "at most 50 of these are happening at once." If each request is quick, that's basically true. But that assumption quietly smuggles in a second assumption that each request is quick and that's exactly the thing we hadn't actually verified.

The real relationship, straight out of queueing theory, is Little's Law:

L = λ × W

Where L is the number of things concurrently "in the system," λ is the arrival rate, and W is how long each one stays in the system. Arrival rate alone tells you nothing about concurrency — you also need to know how long each request sticks around.

And our requests were sticking around for a long time remember, average response time was 8–12 seconds. Plug that in:

L ≈ 50 req/s × 10s ≈ 500

Not 50 concurrently-locked rows. Up to 500. Every new SKIP LOCKED scan wasn't competing with 50 other transactions, it could be competing with hundreds, all holding locks, all forcing it to skip past them one by one before finding something free. And every skip isn't free, it's real CPU work checking lock state, pinning buffers, walking the index.

Worse, this is a feedback loop, not a one-time cost. More locked rows means more skipping, which means each transaction takes even longer, which means even more rows are locked by the time the next request shows up. Once the system's processing time creeps past the natural gap between arrivals, it doesn't degrade gracefully, it spirals.

The Real Culprit: A Lock Inside a Lock

So why was each request taking 8–12 seconds in the first place, when the query itself takes 0.143 milliseconds? That's the actual bug, and it was sitting in the application code, not the database:

synchronized (lockForKey(uniqueKey)) {
    Row row = jdbc.queryForObject(
        "SELECT * FROM available_ids WHERE assigned = 'N' FOR UPDATE SKIP LOCKED LIMIT 1");

    // build the response object, run some business logic...

    jdbc.update("UPDATE available_ids SET assigned = 'Y', ... WHERE id = ?", row.id());
}

The Postgres row lock from FOR UPDATE doesn't get released until the transaction commits and the transaction doesn't commit until that entire synchronized block finishes. Which means the database lock was being held across:

• The full round-trip back to the application,
• Whatever business logic ran in between,
• A second, separate round-trip for the UPDATE,
• And however long the thread spent waiting for an in-process Java lock shared with other requests.

The query was fast. The transaction was slow. And Postgres doesn't care how fast your SELECT was — it only cares how long you keep the row checked out.

The Fix: Stop Making the Database Referee Every Single Request

The tempting fix is "just make the critical section shorter" combine the SELECT and UPDATE into one atomic statement so the lock is held for less time. That genuinely helps. But it doesn't remove the underlying pattern: you're still asking dozens or hundreds of concurrent transactions to race each other for the same handful of rows, every single time a caller wants an ID.

The fix that actually worked was to stop doing that entirely:

1. Buffer a batch of available ids in memory, fetched with one lightweight (unlocked) query: SELECT id FROM available_ids WHERE assigned = 'N' ORDER BY id LIMIT 10000.
2. Serve requests out of that in-memory pool — no database round trip at all for the common case.
3. Claim an id with a direct point-update: UPDATE available_ids SET assigned = 'Y' WHERE id = :id AND assigned = 'N'. This targets one row by primary key. There's no scanning, no skip-locking, no contention with anything. Postgres doesn't even need an index scan to find a candidate, because the application already knows exactly which row it wants.
4. Refill the pool asynchronously in the background once it drops below a threshold, so requests almost never have to wait on a refill.

This turns "one query-pair per HTTP request" into "one bulk query per ten thousand requests." The thundering-herd problem doesn't get mitigated — it gets deleted, because there's no longer a race to delete.

(One fair question: does this still work safely with multiple instances of the service running? Yes, for correctness — the guarded WHERE assigned = 'N' update means two instances racing for the same buffered id just results in one succeeding and the other retrying with its next buffered id. It's a little wasteful if that happens often, but it can never double-assign an id.)

Did It Actually Work?

We shipped it to a staging environment and compared it directly against the old code still running in production, pulling the exact same metrics from both at the same time:

Metric	Old code	New code
Threads blocked on lock contention	59	0
Average response time	~9.5 seconds	~13.7 milliseconds
Worst-case response time	40 seconds	~17 milliseconds

Roughly a 500–900x improvement, and the blocked-thread count going to exactly zero was the real tell — not just "faster," but the actual contention pattern gone.

The Takeaway

If I had to compress this whole investigation into one sentence, it'd be: EXPLAIN ANALYZE tells you the truth about a query running alone, and almost nothing about what happens when it has to share. A fast query executed by hundreds of competing transactions isn't a fast system, the thing that matters isn't execution time, it's how long you hold what you're competing over.

A few things I'd tell anyone building a FOR UPDATE SKIP LOCKED queue on Postgres:

• Measure lock hold time, not just query time. They are not the same number, and the gap between them is exactly where concurrency problems hide.
• Never let application logic run while holding a database row lock. Combine your select-and-update into one statement, or better, don't lock a row you haven't already decided how to update.
• Remember Little's Law whenever "but our TPS is low" comes up as a defense. Low throughput and high concurrency are completely compatible, as long as service time is high enough and it's service time, not request rate, that determines how much your database is actually fighting with itself.
• If a queue-style claim pattern is on your hot path, consider not hitting the database on every single request at all. Batching and in-memory buffering isn't just a performance optimization. Here, it's the thing that removes the entire failure mode.

Our database wasn't slow. We were just asking it to referee a fight we'd accidentally started.