It started with a database CPU spike. Our primary event processing database was running at 85% utilization. The culprit was a query that scanned the security_events table to find unprocessed entries for our background analysis engine. The table had grown to thirty million rows. We were facing classic issues with optimizing PostgreSQL query performance on large scale tables, as the query was doing a sequential scan because the index on the status column had degraded in selectivity.
A quick pull request went out. The fix seemed obvious: add a limit to the query. If the analyzer only processed events in batches anyway, we should restrict the database read to a maximum of 5,000 records per run. The line change was tiny:
- Before
SELECT id, payload, severity, created_at
FROM security_events
WHERE status = 'pending'
ORDER BY severity DESC, created_at ASC;
- After
SELECT id, payload, severity, created_at
FROM security_events
WHERE status = 'pending'
ORDER BY severity DESC, created_at ASC
LIMIT 5000;The code passed code review. It passed the unit test suite. It went through staging and landed in production. The database CPU graph immediately dropped to a cool 15%. The team congratulated themselves on a simple, effective performance win.
Three days later, the product team noticed a problem. The security dashboard, which displays real-time threat intelligence to our customers, had stopped updating for accounts with high event volumes. The system was not throwing errors. The service logs showed the worker loop running every sixty seconds, completing its work, and sleeping. Yet, millions of new events remained unprocessed.
The team had introduced a starvation bug, hidden behind a silent failure mode.
The Mechanics of the Starvation Loop
To understand why the system stopped processing, we have to look at how the worker loop handled failures. The background analyzer processed events in batches. If an event failed to process due to a transient issue, like a network timeout to a downstream threat assessment API, the analyzer left the event status as pending to be retried on the next run.
Here is a simplified version of the processing loop:
func ProcessBatch(db *sql.DB) error {
events, err := db.FetchPendingEvents(5000)
if err != nil {
return err
}
for _, event := range events {
err := analyzeEvent(event)
if err != nil {
log.Printf("Failed to process event %s: %v", event.ID, err)
// We leave the status as 'pending' to retry later
continue
}
err = db.MarkAsProcessed(event.ID)
if err != nil {
return err
}
}
return nil
}When everything worked, this loop processed 5,000 events, marked them as completed, and freed up the queue for the next batch. But look at what happens when things fail.
On a Thursday night, the threat assessment API experienced a brief, thirty-minute outage. During this window, any event analyzed by the background worker failed. While we could have protected the system by implementing the circuit breaker pattern, the worker loop simply retried indefinitely.
Even after the threat assessment API recovered, the system remained stuck. The database query ordered events by severity DESC, created_at ASC. The 5,000 events that failed during the outage happened to be high-severity events. They occupied the top of the query results.
Because the worker could only fetch 5,000 events at a time, it was trapped in a loop. It fetched the same 5,000 high-priority events, attempted to process them, and failed on a few that had malformed payloads. We call these poison pills. The newer, healthy events lower down in the table were never fetched. The limit had turned a temporary API hiccup into a permanent processing block.
How the Test Suite Missed the Bug
We had integration tests designed to catch queue processing issues. They ran on every commit. Yet, they greenlit the pull request without a single warning.
The reason lies in the setup of our test environments. Our integration tests used a clean, database instance for each run. The test seed files inserted exactly ten mock events. The test runner executed the processing loop, verified that all ten events changed status to processed, and asserted success.
func TestProcessBatch(t *testing.T) {
db := setupTestDB()
defer teardown(db)
// Seed 10 events
seedEvents(db, 10)
err := ProcessBatch(db)
assert.NoError(t, err)
// Verify all are processed
remaining := countPendingEvents(db)
assert.Equal(t, 0, remaining)
}In this test environment, the database limit of 5,000 was never reached. The code behaved exactly the same way with or without the limit.
To catch this bug, the test suite would have needed to seed more than 5,000 events, introduce a processing failure in the first 5,000, and then verify that events beyond the 5,000 limit could still be processed. We rarely write tests that exceed arbitrary limits. Large test datasets slow down CI pipelines. We prioritize fast feedback loops over edge-case boundary testing. This is the price we pay.
Another factor was the lack of negative assertions. The tests checked that the seeded events were processed. They did not assert that the system could recover when a subset of events failed repeatedly.
The Illusion of Database Health
The most dangerous aspect of this incident was the lack of alerts. Our monitoring stack tracked database CPU, memory utilization, and query execution times.
When the limit was applied, the database CPU dropped. The query execution time went from three seconds to under ten milliseconds. The APM dashboard showed a flat line of healthy green metrics.
The database was healthy. It was doing exactly what we asked: fetching 5,000 rows quickly. The application was healthy too. It logged no unhandled exceptions, and its container health checks returned status 200.
We lacked visibility into queue lag. We monitored the rate of processed events, but we did not monitor the age of the oldest unprocessed event. Because the event processing rate dropped to zero for new events, but the worker was still active, the alerts did not trigger. The system was functionally dead, but operationally green.
The Trap of Index Selectivity
To understand why the developer added the limit in the first place, we have to look at how PostgreSQL and MySQL handle index scans. The security_events table had a composite index on (status, severity, created_at). This scenario shares many performance characteristics with multitenant database index tuning in PostgreSQL, where index selectivity changes based on data distribution.
Initially, when the application was young, the number of pending events was small (usually under a few hundred). The query optimizer looked at the index, saw that status = 'pending' was highly selective, and used an index scan. The query returned almost instantly.
As the application scaled, the volume of events grew. During peak hours, the number of pending events could rise to tens of thousands. When the database engine analyzed the query, it realized that the index on status was no longer selective enough. If 10% of the table is pending, scanning the index and then looking up the rows in the table heap is actually slower than just reading the entire table from disk.
The database optimizer decided to abandon the index and perform a full table scan. With thirty million rows, this meant reading gigabytes of data from the SSD into memory on every execution of the worker loop. This was the source of the CPU spike.
When the developer added LIMIT 5000, they changed the optimizer's math. The database engine realized it did not need to scan the entire table to find all pending events. It only needed to find the first 5,000. It could go back to using the index, scan until it found 5,000 matches, and then stop.
This is why the CPU usage dropped so dramatically. The database was doing a fraction of the work. But this optimization relied on the assumption that the application would quickly process those 5,000 events and change their status, making room for the next 5,000.
When that assumption broke, the index scan became a loop of doom. The database was fetching the same 5,000 index entries over and over, because their status never changed. The optimizer was happy, the CPU was low, but the application was stuck.
Testing for Starvation
If standard unit tests cannot catch this, how do we prevent it in the pipeline? The answer is not to write slow, massive integration tests that insert 10,000 rows. Instead, we can use stateful simulation.
We can write a test that simulates this using a mock database. The test generator randomly inserts batches of events, some set to fail, some set to succeed. It runs the processor loop multiple times and asserts that the successful events are processed even if the failing events are inserted first.
Here is how we can structure such a test:
func TestQueueProgressInvariant(t *testing.T) {
db := setupTestDB()
defer teardown(db)
// Insert 10 events that will always fail processing
insertFailingEvents(db, 10)
// Insert 1 event that will succeed
targetID := insertSuccessfulEvent(db)
// Run the processor with a small batch limit of 5.
// We run it 3 times. If the queue is healthy, the successful event
// must be processed within these runs, despite the 10 failing events.
for i := 0; i < 3; i++ {
_ = ProcessBatchWithLimit(db, 5)
}
// Assert the successful event was processed
assert.True(t, isProcessed(db, targetID))
}If we run this test on the original code, it fails. The batch limit of 5 means the processor only looks at the first 5 failing events on run 1, run 2, and run 3. The successful event, sitting at position 11, is never reached.
This test is fast. It only uses 11 database rows, but it exposes the starvation bug because the batch limit is smaller than the number of failing events. By keeping the test parameters small but proportional, we can catch concurrency and pagination bugs in CI without slowing down the build pipeline.
The Trade-offs of Offset vs Cursor Pagination
When fixing this issue, we had to choose between offset pagination and cursor-based pagination.
Offset pagination is the easiest to write. You keep the query the same and just add an offset:
SELECT id, payload, severity, created_at
FROM security_events
WHERE status = 'pending'
ORDER BY severity DESC, created_at ASC
LIMIT 5000 OFFSET 5000;But offset pagination has two major flaws. First, it performs poorly on large datasets. The database must still scan and discard all the rows prior to the offset. If your offset is 100,000, the database has to read 105,000 rows and throw away the first 100,000. This causes the CPU spikes to return as the queue grows.
Second, offset pagination is unstable when rows are being deleted or updated concurrently. If you process the first 5,000 events and change their status to processed, they disappear from the WHERE status = 'pending' filter. If you then query with OFFSET 5000, you skip the next 5,000 events entirely because the dataset has shifted.
Cursor-based pagination avoids both problems. By using a unique, ordered key, like a composite of severity and id, as a pointer, the database can jump directly to the next set of rows using the index. It does not need to scan or discard previous rows.
SELECT id, payload, severity, created_at
FROM security_events
WHERE status = 'pending'
AND (severity, id) > (?, ?)
ORDER BY severity DESC, id ASC
LIMIT 5000;This query is deterministic, performs in constant time, and is unaffected by concurrent updates to preceding rows.
The Fix: Cursor-Based Processing and Poison Pill Queues
Resolving the issue required changing both our query strategy and our error handling. We could not just remove the limit; the database CPU spike would return. We needed a way to bound reads without creating a starvation loop.
First, we separated the retry logic from the main processing queue. If an event fails to process more than three times, we move it to a dead-letter table or mark it with a status of failed_permanently. This prevents poison pills from clogging the active queue.
- Query only events that haven't exceeded retry limits
SELECT id, payload, severity, created_at
FROM security_events
WHERE status = 'pending' AND retry_count < 3
ORDER BY severity DESC, created_at ASC
LIMIT 5000;Second, we changed our query pattern to use cursor-based pagination instead of static limits on a fixed order. Instead of asking for the top 5,000 events repeatedly, the worker tracks the ID or timestamp of the last processed event and asks for the next batch after that point.
- Fetch next batch using a cursor
SELECT id, payload, severity, created_at
FROM security_events
WHERE status = 'pending'
AND (severity < ? OR (severity = ? AND created_at > ?))
ORDER BY severity DESC, created_at ASC
LIMIT 5000;This ensures that even if some events remain pending, the worker can move past them to process new data.
Better Monitoring for Background Workers
We also overhauled our alerting strategy. We learned that system health metrics like CPU and memory are not enough to verify that business logic is functioning.
We added a Prometheus metric that tracks the maximum age of pending events:
SELECT EXTRACT(EPOCH FROM (NOW() - MIN(created_at)))
FROM security_events
WHERE status = 'pending';If this metric exceeds thirty minutes, an alert triggers. It does not matter if the CPU is at 5% or if the worker is logging successful runs. If old events are sitting unprocessed, the system is broken.
We also added a metric for queue processing velocity. We track the ratio of incoming events to processed events. If incoming events spike but processed events remain flat, we know the queue is falling behind.
Designing Safe Limits
Limits are necessary. Without them, a sudden spike in traffic can cause database connection pools to exhaust, leading to cascading failures across your entire infrastructure. But limits cannot be applied blindly as a quick fix for slow queries.
When you add a limit to a database query, you must ask three questions:
- What happens to the data that falls outside the limit?
- How does the system process the remaining data on subsequent runs?
- Is there a scenario where the same subset of data is fetched repeatedly, blocking new data?
If you cannot answer these questions, you are setting a trap for your production environment. The database will run faster, the dashboards will look clean, and your users will wonder why their data has stopped updating.
