Fixing a queue consumer’s database bottleneck.

01 / Context

The problem

Large settlement groups could take longer to process than the queue's one-hour visibility timeout. Messages became visible again while work was still underway, leading to duplicate processing. The consumer made two database queries for every meter point: one for settlement status and one to reconstruct its event-sourced state.

02 / Responsibility

My contribution

I traced repeated processing to an N+1 query bottleneck, then introduced bulk queries and batches of 1,000 meter points.

03 / Approach

Key decisions

I introduced bulk queries for settlement status and aggregate snapshots, then changed the consumer to work through chunks of 1,000 meter points. This bounds the amount of data handled at once while replacing per-meter database round-trips with two queries per batch. The query pattern changes from 1 + 2N to 1 + 2 × ceil(N / 1,000).

1. 01

   Trace the retry

   Connect repeated queue processing to work exceeding the visibility timeout.

2. 02

   Find the repeated work

   Identify two database queries for every meter point.

3. 03

   Batch the reads

   Fetch settlement status and event-sourced snapshots for 1,000 meter points at a time.

04 / Result

The result

The revised query pattern makes 201 database queries for 100,000 meter points, down from 200,001. It removes the per-meter round-trips behind the timeout issue while keeping each batch to 1,000 meter points.

Related work

• Reusing connections at scaleIn a separate notification consumer handling a reported 247 million notifications per month, I reused the S3 client instead of creating one for every message. This removed repeated connection setup; the source does not include a measured post-change time saving.