I'm working with PostgreSQL v10 + golang and have what I believe to be a very common SQL problem:
- I've a table 'counters', that has a
- Strictly, once
current_value >= max_value, I would like to drop the request.
- I've several Kubernetes pods that for each API call might increment
current_valueof the same row (in the worst case) in 'counters' table by 1 (can be thought of as concurrent updates to the same DB from distributed hosts).
In my current and naive implementation, multiple UPDATES to the same row naturally block each other (the isolation level is 'read committed' if that matters). In the worst case, I have about 10+ requests per second that would update the same row. That creates a bottle neck and hurts performance, which I cannot afford.
I thought of several ideas to resolve this, but they all sacrify integrity or performance. The only one that keeps both doesn't sound very clean, for this seemingly common problem:
As long as the counter
current_value is within relatively safe distance from
max_value (delta > 100), send the update request to a channel that would be flushed every second or so by a worker that would aggregate the updates and request them at once. Otherwise (delta <= 100), do the update in the context of the transaction (and hit the bottleneck, but for a minority of cases). This will pace the update requests up until the point that the limit is almost reached, effectively resolving the bottleneck.
This would probably work for resolving my problem. However, I can't help but think that there are better ways to address this.
I didn't find a great solution online and even though my heuristic method would work, it feels unclean and it lacks integrity.
Creative solutions are very welcome!
Thanks to @laurenz-albe advice, I tried to shorten the duration between the UPDATE where the row gets locked to the COMMIT of the transaction. Pushing all UPDATES to the end of the transaction seems to have done the trick. Now I can process over 100 requests/second and maintain integrity!