When LIMIT 10 Means 500

A queue pickup that ignored its limit, a plan cached while the table slept, and a consumer that deadlocked on its own success.

The System, Briefly

A staged pipeline processes events; each stage has a consumer composed of a fetcher goroutine and N = 10 workers per replica, two replicas per stage. Tasks live in a Postgres table, and a task’s stage is a column. A consumer picks up tasks by leasing them:

UPDATE tasks
SET picked_at = now() + $window, attempts = attempts + 1
WHERE id IN (
  SELECT id FROM tasks
  WHERE state = $state AND deferred = 'no' AND attempts <= $max
    AND (picked_at IS NULL OR picked_at <= now())
  FOR UPDATE SKIP LOCKED
  LIMIT $n)
RETURNING *;

After the UPDATE, the consumer takes a Redis lock per task, commits, publishes a notification per task on the event bus, and starts one goroutine per task that renews the lease and the lock every $window/3 until the task is released. Handlers advance the task to the next stage. If a consumer dies, leases lapse and another replica picks the task up with attempts + 1. This is a textbook Postgres queue, and it had run for months. Until it didn’t.

The Page: Nothing Is Wrong

An operator noticed 460 tasks in flight in one stage. Dashboards were fine, alarms were quiet. Everything was – as far as telemetry could tell – working as intended:

• The stage’s consumers were healthy and reported no errors;
• The database was healthy, with no long transactions and a connection pool that was nowhere near its limit;
• Tasks were not failing and not exhausted.

However, a backlog query told the real story. By splitting tasks into groups of waiting (eligible now), leased (a consumer holds them) and exhausted, I could observe the following scenario:

What we’re looking at is unexpected: all 460 were leased, with attempt 1 – and, beyond what any dashboard showed, their leases expired within half a second of each other. Two replicas with ten workers can hold about twenty tasks, not 460, and Redis agreed: 460 lock keys, one per task, with TTLs inside the refresh cycle – something alive was renewing all of them.

The go_goroutines metric named the process. One replica had 463 goroutines, while the other had 83; every other service in the namespace sat between 23 and 43 (the baseline). Each held task costs one refresh goroutine, so a replica with 420 extra goroutines was holding about 420 tasks that no worker was handling.

The Goroutine Dump

Although this wasn’t production, the pods already ran with a strict, hardened security posture – no shell, no user, nothing – and pprof wasn’t enabled. The last resort was to SIGQUIT the misbehaving process and read its goroutine dump. The cost was that the signal would also restart the pod, and everything would go back to “normal” until the same case was hit again, but it was the best available at the moment. It was telling:

The second replica’s dump had the same shape, with 40 refreshers. For completeness’ sake, here’s an ultra-simplified version of the protocol they follow:

// Worker
for {
    tasksWanted <- struct{}{}   // token: "I am idle". Capacity N. This is line
                                // 530: all ten workers were blocked here.
    t := <-tasksReceived        // Wait for a task. Capacity N.
    handle(t)
}

// Fetcher
for {
    wanted := takeTokens(tasksWanted) // up to N.
    tasks  := pickup(wanted)          // This runs the UPDATE … LIMIT $n
                                      // mentioned above.
    for _, t := range tasks { go refresh(t) }
    for _, t := range tasks {
      tasksReceived <- t              // This is line 418: the fetcher
                                      // blocked here.
    }
}

Every task the fetcher delivers is backed by a token it took, so with at most wanted tasks per pickup neither channel can fill. Hand the fetcher a pickup of 440, though, and the invariant is gone:

1. The fetcher takes ten tokens and receives 440 tasks. Ten go straight to the idle workers and ten more fill the tasksReceived buffer; the fetcher then blocks on the next send, holding the rest.
2. Each worker that finishes sends a token for its next task and picks up a buffered one. The fetcher is stuck in its delivery loop and takes no tokens, so the token channel fills after ten.
3. When that second wave finishes, the first worker to send a token blocks – the channel is full – and soon all ten do.
4. With no worker receiving, tasksReceived fills again and the fetcher stays blocked. The undelivered tasks have live refreshers and no handler: their leases and locks renew forever, the attempt stays at 1, and no timeout exists to notice the condition.

The protocol left a fingerprint: N tasks in the first wave plus N sitting in the buffer, so exactly 2N = 20 handled… then silence. Logs showed exactly that on both replicas, 20 starts after the burst, and nothing after. A small simulation of the channel logic reproduced it numerically: a first pickup of 440 gives 20 handled and 420 leaked refreshers; one of 60 gives 20 handled and 40 stuck. Those are the two replicas’ numbers.

This leaves us in a position where the deadlock was real and well-understood, and it needed a precondition that the design said was impossible: a pickup returning more than LIMIT.

Chasing the Impossible

The lock counters gave the first hard evidence: between two given timestamps, one replica acquired 440 locks, and the other 60. Two pickups, 500 tasks in total, which matches exactly the size of the batch that was inserted into the database moments before. Each replica’s first pickup after the 500-task burst had taken everything it could see. One more oddity: the first task started about two seconds after the insert committed. I’ll come back to that.

My hypotheses, with what happened to them:

• Connection pool exhaustion: an alert had fired (pool waits), and it is the first thing everyone looks at. It was a red herring: the metric counts acquires that had to open a connection, and the pool had ten workers against 36 connections, none in use.
• A leaked transaction – no: every path rolls back on exit, and the database showed only sub-millisecond transactions.
• Clock skew between the service that inserted the tasks and the consumers, to explain the two-second delay between the commit and the first start. I measured it: the pods agreed within 24ms.
• Slow Redis locks during a 440-task pickup. Measured: each acquire took under a millisecond.
• Slow publishing after the commit. Measured: each notification takes about 0.5ms.
• The query: I read it, ran its EXPLAIN on the environment where the application was, and tested it locally on the same Postgres version: stale statistics, fresh statistics, generic plans, eight concurrent pickers, 8,000 pickups, a pickup in the same shape as the real transaction. The largest batch was exactly 10 every time; another refuted hypothesis.

However, that last verdict was wrong, and the way it was wrong is the interesting part of this story. The tests were sound, but they sampled the wrong space: they only exercised the plans a healthy table produces, and the failure needed a particular plan.

The Query

Here is what EXPLAIN ANALYZE showed on the environment’s engine, on a scratch table in the state the real one was in, with the planner nudged away from its hash, sort, and materialise strategies:

Update on tasks (actual rows=500.00 loops=1)
  -> Nested Loop Semi Join (actual rows=500.00 loops=1)
       -> Seq Scan on tasks (actual rows=502.00 loops=1)
       -> Subquery Scan on "ANY_subquery" (actual rows=1.04 loops=502)
            -> Limit (actual rows=1.04 loops=502)
                 -> LockRows (actual rows=1.04 loops=502)
                      -> Index Scan ... on tasks tasks_1

actual rows=500 for LIMIT 10? The subquery is the inner side of a nested loop and runs loops=502 times. The same script on community Postgres 18.6 gave the identical plan, the same cost numbers and the same 500 rows, so this could not be an AWS Aurora – used by the application on its environment – defect. The issue was what the executor did with that plan.

The mechanism is in ExecLockRows: a row the current command has already updated comes back from the lock attempt as TM_SelfModified, and the executor skips it – the code comment cites the Halloween problem. Each re-execution of the locking subquery therefore skips every row this statement has already updated. Outer and inner scan the table in the same order, so the first surviving row is always the current outer row: the semi join matches on it and stops, and LIMIT 10 never binds. That is the rows=1.04 per loop above and, in the repro at the end, rows=250.50 loops=500 on the inner scan: on average 249.5 skipped rows plus the one returned. Every outer row therefore finds itself in a fresh set, and the statement updates all of them. With two statements running at once, each also skips the rows the other has locked, which then splits the table between them. Two sessions racing under that plan, 250 and 250 locally. In production, 440 and 60: the replicas started 463ms apart.

The documentation is candid about SKIP LOCKED:

Skipping locked rows provides an inconsistent view of the data, so this is not suitable for general purpose work, but can be used to avoid lock contention with multiple consumers accessing a queue-like table.

It also says that:

If a LIMIT is used, locking stops once enough rows have been returned to satisfy the limit (but note that rows skipped over by OFFSET will get locked).

Read carefully: that is a guarantee about one evaluation of the query. Nothing promises that a LIMITed locking subquery inside IN (…) is evaluated once, and as we know, the planner may choose any join strategy. The documentation is explicit that, for a locking clause inside a sub-SELECT, conditions from the outer query may be used to optimise the sub-query’s execution. For this specific query, some of those strategies are catastrophic.

Why That Night

Forcing a plan with planner flags proves a mechanism, not a cause. I still had to explain how Aurora came to run that plan with default settings.

Three facts combined:

1. The driver caches prepared statements per connection: pgx’s default mode prepares a statement server-side and reuses it. For the first five executions the server plans with the actual parameters (custom plans). After that, plan_cache_mode = auto may switch to a generic plan, planned once without parameter values, and cache it on the connection.
2. The fetchers polled the table back to back: the loop, I found, had no sleep after an empty pickup, so each connection ran this statement thousands of times an hour against a table that could, for long stretches, be idle and empty. The generic plan was built from the statistics of an empty table: one estimated row.
3. For a one-row estimate the planner sees no reason to materialise the subquery: Materialize appears when the outer side is estimated at two rows or more, which is why a table with two live rows was safe in every test. At one row, the nested loop with the bare subquery is the cheapest plan.

A fresh custom plan, planned after the burst, would have scaled its estimate by the table’s current size and been safe. Only a cached plan carries the empty table’s estimate forward, and it is invalidated only by something that refreshes those statistics. Then 500 rows arrived in one transaction, and until Aurora’s autovacuum analysed the table, every pickup ran through a plan built for an empty table. With no live tuples recorded, the scale factor contributes nothing, so the trigger is the base threshold (autovacuum_analyze_threshold, 50 rows by default), which 500 inserts crossed at the next autovacuum_naptime tick – five seconds here (the community default is a minute); in my lab runs the analysis landed within a few seconds, about three in one of them. ANALYZE invalidates the cached plan, and the next execution was safe once again.

That explains why the bug needed a burst after an idle period to show itself. It also needed the burst to exceed N: with ten rows or fewer, the bad plan is indistinguishable from a good one, so a single task enqueued five minutes earlier and picked up one millisecond after its commit proves nothing either way.

I could reproduce it with no planner settings, through the real driver. A prepared statement was executed eight times against an empty table, then 500 rows were inserted with no ANALYZE:

The Fixes

1. The Query

Pick the rows in a CTE that is evaluated exactly once, and update from it. The documentation notes that a locking clause on the outer query does not reach into a WITH query, and that to lock within one, one must put the clause inside it, which is what this does:

WITH picked AS MATERIALIZED (
  SELECT id FROM tasks
  WHERE state = $state AND deferred = 'no' AND attempts <= $max
    AND (picked_at IS NULL OR picked_at <= now())
  FOR UPDATE SKIP LOCKED
  LIMIT $n)
UPDATE tasks
SET picked_at = now() + $window, attempts = tasks.attempts + 1
FROM picked
WHERE tasks.id = picked.id
RETURNING tasks.*;

Under the same forced plan that made the old statement update 500 rows, this one updates 10. The plan reads CTE Scan on picked with the work done once under CTE picked, so the join can iterate however it likes without side-effecting anything else.

Two alternatives are worth naming. = ANY(ARRAY(SELECT …)) has the same effect, since an uncorrelated ARRAY() subquery is evaluated once; I prefer the CTE because it states the intent. Setting plan_cache_mode = force_custom_plan removes this particular trigger, but leaves a statement whose correctness depends on which plan it gets, and I would rather fix the statement. I keep MATERIALIZED: by default a side-effect-free WITH query used once may be folded into the outer query, and I would rather not depend on whether a locking SELECT counts as side-effect-free.

2. The Consumer

A fetcher that deadlocks when a producer breaks its contract is brittle, even if the contract is “obviously” kept. The surplus tasks must now be delivered one per fresh token, so the fetcher keeps taking tokens while it has tasks to hand out:

for i, t := range tasks {
    if i >= wanted {
        select {
        case <-tasksWanted:
        case <-ctx.Done():
            releaseRest(tasks[i:])
            return
        }
    }
    select {
    case tasksReceived <- t:
    case <-ctx.Done():
        releaseRest(tasks[i:])
        return
    }
}

I chose to drain an oversized pickup instead of rolling it back: a rollback would have stalled a replica for as long as the database kept over-returning rows, and I did not yet know why it had. The code also logs an error with the row count, the distinct ids and the milliseconds spent in the query, the locks and the commit, which is the evidence this incident lacked. Shutdown now releases every undelivered task, where before only the one being delivered was released.

Draining has a cost: the replica that wins the race holds the whole surplus, which is exactly the 440/60 split of this incident. The alternative is to keep wanted rows and release the rest before committing (picked_at = NULL, attempts - 1), which bounds the damage without stalling a replica. It trades a second write path for a smaller blast radius, and I did not carry it in the same change.

3. The Tests

The most useful is the one that changes nothing about the planner:

func TestPickupHonoursItsLimitAfterIdlePolling(t *testing.T) {
    // analysed, empty table; autovacuum disabled so nothing re-analyses it
    for range 8 {
        require.Empty(t, pick()) // same connection: the plan is cached per connection
    }
    insertBurst(500)
    require.Equal(t, 10, len(pick()))
}

On the old query it fails with expected: 10, actual: 500; on the fix it passes. Next to it sits a test that forces a 440-task pickup through the fetcher and checks that all 440 are handled. With the old delivery, it fails with “20 of 440 tasks handled”: the production fingerprint.

What I Couldn’t Prove

1. The incident’s plan: the cluster keeps no plan history (the engine’s plan capture is off, there is no slow-statement log, and pg_stat_statements was preloaded but never created in the database). I had a faithful reproduction on the same engine, not a recording.
2. The two seconds: the first task started about two seconds after the insert committed. Clock skew, lock acquisition and publish cost were each too small, and they stay small when summed: for the 440-task pickup, 440 locks (under 440ms in total), 440 publishes at about 0.5ms each (about 220ms) and the 47ms statement come to under a second at worst, about 0.7s. The other replica is further off still: 60 tasks add up to roughly 0.15s, against an observed 2.5s. The two delays barely differ despite a sevenfold difference in pickup size, which points to a roughly fixed cost rather than a per-task one. I don’t know what it is.

Lessons Learned

1. IN (SELECT … LIMIT n FOR UPDATE SKIP LOCKED) is a trap: it is correct under most plans, and the plans that break it are exactly the ones a young table, a stale statistic or a cached plan produce. Materialise the locked set, or use = ANY(ARRAY(SELECT …)). Then test with the planner against you.
2. Prepared statements, idle tables and bursts are a hazard class: a plan’s lifetime is not the statistics’ lifetime. A generic plan built during a quiet hour can serve a burst for as long as nothing invalidates it. Hot tables that go empty deserve a deliberate plan-cache strategy.
3. Idle polling without backoff has side effects beyond load: it guarantees the switch to a generic plan within minutes, and the connection then keeps that plan for as long as it lives. Backoff alone would not have prevented it – any sane interval reaches five executions within the hour – but a loop that never sleeps is a bug in a trenchcoat, and no spec ever covered the empty queue.
4. A consumer should survive a producer that exceeds its contract. Bound every blocking operation, and keep the invariant between your channels local and obvious. The application’s protocol was correct if and only if a database query kept a promise – one that was treated as an invariant and never verified.
5. A liveness failure that looks like work is the worst kind. Tasks that are leased and not progressing look busy: healthy probes, growing metrics, no errors. Alert on liveness (goroutine counts, handled rate against leased count), not only on backlog age.

Procedures Pinned

Process lessons, separate from the technical ones:

1. Alerts name candidates, not causes. The pool-waits alert pointed at the database first, and the fastest way past it was to read what the metric actually counted.
2. Write hypotheses down together with their refutations, and be ready to retract them. I retracted my own conclusions, including “the query is safe”, when a better experiment arrived. The record made that cheap.
3. Build the reproduction on scratch objects, in transactions you roll back, with a snapshot behind you. The experiments that cracked this ran on tables created for the purpose, on the same engine as the environment, and were safe because nothing they did was permanent.
4. Measure what you would otherwise infer. Clock skew, lock latency and publish cost each had a plausible story and each was ruled out with a number.

Reproducing the Issue

The following self-contained script reproduces it on community PostgreSQL 18.6 (the actual rows=500.00 decimals are the version 18 output format); the same sequence on tables shaped like mine gave the same result on Aurora PostgreSQL 18.6. It updates 500 rows for a limit of 10. Plan caching and MATERIALIZED CTEs date from 12, but the one-row estimate for an analysed empty table depends on the reltuples = -1 convention introduced in 14, so I’d expect 14 and newer to be affected. I have not tested earlier versions:

CREATE TABLE tasks (
  id        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  state     text NOT NULL,
  picked_at timestamptz,
  attempts  int NOT NULL DEFAULT 0
);
CREATE INDEX ON tasks (state, attempts, picked_at);
ALTER TABLE tasks SET (autovacuum_enabled = false); -- holds the statistics still,
                                                    -- as in the first seconds
                                                    -- of the incident

ANALYZE tasks; -- an idle, empty table. This matters: a table never vacuumed
               -- or analysed (reltuples = -1) is assumed to hold at least ten
               -- pages, which hides the bug

PREPARE pick(text, bigint) AS
  UPDATE tasks SET picked_at = now(), attempts = attempts + 1
  WHERE id IN (SELECT id FROM tasks WHERE state = $1 AND picked_at IS NULL
               FOR UPDATE SKIP LOCKED LIMIT $2)
  RETURNING id;

-- a worker polling an empty queue (the sixth execution onwards may use a
-- cached generic plan)
EXECUTE pick('pending', 10); EXECUTE pick('pending', 10);
EXECUTE pick('pending', 10); EXECUTE pick('pending', 10);
EXECUTE pick('pending', 10); EXECUTE pick('pending', 10);
EXECUTE pick('pending', 10); EXECUTE pick('pending', 10);

-- a burst arrives, and nothing has re-analysed the table yet
INSERT INTO tasks (state) SELECT 'pending' FROM generate_series(1, 500);

BEGIN;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, BUFFERS OFF)
  EXECUTE pick('pending', 10);
ROLLBACK;

 Update on tasks (actual rows=500.00 loops=1)
   ->  Nested Loop Semi Join (actual rows=500.00 loops=1)
         Join Filter: (tasks.id = "ANY_subquery".id)
         ->  Seq Scan on tasks (actual rows=500.00 loops=1)
         ->  Subquery Scan on "ANY_subquery" (actual rows=1.00 loops=500)
               ->  Limit (actual rows=1.00 loops=500)
                     ->  LockRows (actual rows=1.00 loops=500)
                           ->  Seq Scan on tasks tasks_1 (actual rows=250.50 loops=500)

Run ANALYZE tasks; and execute the statement again, and the plan is replanned and updates 10. Replace the statement with the MATERIALIZED form above, and it updates 10 in both cases.

Final Thoughts

None of the individual pieces here was exotic: the documentation was accurate, the driver cached a plan as drivers do, and the loop – a bug, but a quiet one – polled an empty table. The bug lived in their product, and the failure window was the few seconds between a burst and the next ANALYZE. That is the part I keep coming back to – a contract that held for months without ever being tested under a bad plan. Two questions remain open (the plan itself, and two seconds of latency), and I’d rather leave them open than paper over them. The repro above takes a minute to run; if it prints something different on your version, I’d like to know.

Nothing like debugging what was assumed as an invariant for a couple of hours.