One technique to massively reduce contention is to have multiple counters. Instead of one counter you have say 10. The “official” counter is the sum of those ten counters. To update the “official” counter, I pick one of the ten counters at random. Contention is now much lower.

Is a single worker thread sufficient to keep up with ~500 RPS in real-world systems?

There is no way we would know it, as there are absolutely no details to test it. Maybe you're running the thing on a Raspberry Pi 3, with the database file on an old USB flash drive. It should still probably work fine, but who knows.

There is, however, a definitive way for you to answer your own question.

Do the test.

how can this be safely scaled to multiple workers without double-counting (i.e., ensuring workers don’t process same log records)?

Sharding. Or communication between workers. Or a message queue system.

But before considering those approaches, test if the most basic system works. What happens when you process your 500 requests by changing the counters in database?

That's literally a few dozens of lines of code to write.

I believe we should still run multiple (3-4) workers for availability while guaranteeing that only one worker updates counters and checkpoint at a time? How do we ensure this safely?

Define safely.

Sooner or later, you will face the situation where you'll have to chose between:

1. Not receiving a message.
2. Receiving the same message multiple times.

Depending on the business case, it may make sense to lose messages. The counter doesn't get changed; that's it.

Or maybe it is critical to have the counter changed no matter what. In which case, you'll have to implement an additional feature where the requester would continue sending the same message until the server acknowledges it received it. This means that you'll probably need to de-duplicate messages, for instance by storing their identifiers in the database to know whether you already processed them or not.

Actual test

Following the advice from above, here's what you get.

The server has two routes:

@app.route('/<int:counter>/increment', methods=['POST'])
def increment(counter: int) -> flask.Response:
    ...

@app.route('/<int:counter>/decrement', methods=['POST'])
def decrement(counter: int) -> flask.Response:
    ...

Each route executes two SQL queries. The first one adds an entry in the log table:

insert into operations
(counter_id, original_value, increment)
values (%(id)s, (select value from counters where id = %(id)s), %(increment)s)

The second one adjusts the actual counter:

update counters set value = value + %(delta)s where id = %(id)s

As they run in a transaction, there would be no inconsistencies between the two tables if something goes wrong.

On client side, a simple script creates a given number of changes to run (generate_changes). A change affects one of the five counters, given that the probability is that it would be the first counter in most of the cases, to test the “high contention” scenario you mentioned. Here's the probability I picked:

indexes = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 2, 3, 4, 4]

Each change either increments or decrements a counter, given that I arbitrarily decided that counters will never go below zero.

The client application starts by clearing all the data, then runs the changes, in parallel. The duration of this second action is measured and reported:

def process_change(c: Change):
    action = 'increment' if c.increment else 'decrement'
    uri = f'http://localhost:7492/{c.counter}/{action}'
    requests.post(uri).raise_for_status()

start_time = time.time()
changes = generate_changes(count_changes)
arguments = ((c,) for c in changes)

with multiprocessing.Pool() as pool:
    pool.starmap(process_change, arguments)

end_time = time.time()
print(f'Ran {count_changes} changes in {(end_time - start_time):.3f} s.')

The overall thing takes about thirty minutes to draft (or less: I was being particularly slow on this one).

With an Intel i5-10600 3.30 GHz CPU with a Force MP510 SSD, PostgreSQL 15 and Python 3.13.5, running 2,000 changes takes 2.27 s. That's about 880 changes per second. When I change the test to perform 10,000 changes or 50,000 changes, I find the very same 880 changes/s. speed.

If I don't reset the changes, i.e. accumulate the data, the speed seems to remain the same. I haven't tested what happens if the database contains millions of rows.

During the runs, all CPU cores are used around 65%. Here's a view of two consecutive runs:

Although I haven't checked it, my impression is that the actual CPU load comes from the client script, not the server or the database. As a matter of fact, removing parallelism on client side leads to the process taking ten times as long as its parallel variant.

Conclusion:

A naive implementation, with no optimizations, can easily reach a rate of 880 changes/second to the counters on “ordinary” hardware, given that it is not impossible that the actual rate is much higher, if the slowness ends up to be on client side in my test.

The implementation being so basic, it is easy to implement as a proof of concept, check the actual performance on production hardware, and, if needed, act accordingly by using one of the most common approaches:

• Distributing the work to multiple servers (horizontal scalability).
• Moving to better hardware (vertical scalability).
• Optimizing.

Is a single worker thread sufficient to keep up with ~500 RPS in real-world systems ?

There will always be a way to make the answer to this be, “no”.

Given that, the thing to care about is finding a way to make things better.

You’ve already hit on the idea of using aggregation to reduce query overhead. Your next idea is parallelizing the aggregation.

Let’s say you’re a bank. You’re processing deposits and withdrawals. Your aggregator is just someone grabbing a stack of them.

The easy way to paralyze the aggregation is to make different stacks out of the ones that act on different accounts.

Aggregators - and their territory

id | range
1  | a-g
2  | h-m
3  | n-t
4  | u-z

With territory worked out ahead of time you avoid all contention on the log table.

Log table -

id | counter_id | delta

1  | abc        | +3
2  | abc        | -1
3  | vr         | +50 
4  | tt         | -1 
5  | vr         | +20 

Assignments - Aggregators can independently scan the log

Aggregator id | Log id
           1  | 1 
           1  | 2
           3  | 4
           4  | 3
           4  | 5

Done this way you don’t actually have to shard the DB. Your aggregating workers can stake out responsibilities in the log ahead of time and stay out of each others way. They don't need to communicate with each other at all. You will need them to adjust their territory as the number of them change.

One school of thought has you bind the thread to the resource. So if your CPU had 4 cores you'd dedicate 4 threads to reading the log. However, what the OS is up to with other running programs at the time can impact how optimal this is.

But before doing that, do yourself a favor and actually look at some of these logs. That is likely more important than these numbers you’re throwing around.

This is a bit of a "frame challenge" meaning that it's not a direct answer to your question as written but offering a different approach to a working solution.

You say you need a counter stored as a row in a DB, but if we step back, for a moment, I think what you really need is a way to see the current total for a category/counter_id. When we reframe the problem that way, we can see that storing a total in a table is not a necessary step. Calculating the total is necessary but storing that value is not.

At any point in time, if we need to know the most current total for the 'abc' counter, we can run a query like this:

SELECT sum(delta) 
  FROM counter_log
 WHERE counter_id = 'abc'

This will give you the most up-to-date total possible for the 'abc' counter given the current state of the database. It will never be 'stale'. There aren't a lot of ways to get the wrong answer with this approach, either.

The problem that most people think this creates is that your query is more expensive to run. This is true but in all likelihood, you are going to be retrieving these totals much less frequently than you are writing deltas. Throw in the fact that reads are much less costly than writes and that a RDBMS tends to be highly optimized for reads. Any decent database will allow for reads to be done without locking the tables being read from. That is, there is low of no contention at all when you are reading, no matter how many other sessions are reading or writing to the tables being read from.

In contrast, writes to a table are costly. And as you correctly note, updates to exiting rows from different connections may require locks to be ensure correctness and that leads to latency and other performance issues.

Inserts, on the other hand require less coordination between processes. An insert-only approach, where there is no possibility for collisions between rows, is much less costly. There may be some contention around indexes, which I will come back to.

I am not saying that storing a precalculated total is always a bad idea but you need to weigh the costs of that against the benefits. The main problem with this kind of thing is that any solution that performs well is likely to contain subtle defects. This could include errors in the total, possible deadlocks, and other thorny issues. Unless the calculation has a significant cost, it's almost never worth bothering with 'caching' the final result. You might want to do this as an archival strategy, however. For example, at the end of the day (week, month, etc.), you may want to write a final total off to separate total and cleanup the log table.

In your case, I would argue that you are optimizing around the cheap (nearly free) cost of querying the current total and making the hard costly part of writing the new records slower, more costly and harder to get working. Querying the total is very straightforward and robust. Also, I would wager that your current approach will lead to more queries against the log table than if you just let the query run when the total is actually needed.

You don't technically need this but if you have dependencies on the existing counter table you can create a view with the same name:

CREATE VIEW counter AS
SELECT counter_id, sum(delta) 
  FROM counter_log
GROUP BY counter_id

A common misconception around views is that they are more costly than a normal query. This isn't the case, the DB will literally rewrite the query and execute it the same way as if you wrote it out explicitly. They can be problematic if they hide costly queries. The above is not terribly costly but I won't completely dismiss that concern. If you are really worried about it, just use the explicit aggregate query as needed and skip the view.

With regard to indexes on your counter log, if you have high write volume, you may want to consider using a reverse index on your surrogate key (the serial integer.) This kind of index minimizes contention when you have a monotonically increasing key, as in the case of using a sequence or identity column.

You will want to make sure you index on the counter id in the log table. There's a small risk of contention on the index when two inserts are made on the same counter but that is unlikely to be noticeable or create any real issues in practice.