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('//increment', methods=['POST'])
def increment(counter: int) -> flask.Response:
    ...
@app.route('//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
(counterid, originalvalue, 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 (generatechanges). 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]

def processchange(c: Change):
    action = 'increment' if c.increment else 'decrement'
    uri = f'http://localhost:7492/{c.counter}/{action}'
    requests.post(uri).raiseforstatus()
starttime = time.time()
changes = generatechanges(countchanges)
arguments = ((c,) for c in changes)
with multiprocessing.Pool() as pool:
    pool.starmap(processchange, arguments)
endtime = time.time()
print(f'Ran {countchanges} changes in {(endtime - starttime):.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/counterid. 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.

SELECT sum(delta) 
  FROM counterlog
 WHERE counterid = 'abc'

CREATE VIEW counter AS
SELECT counterid, sum(delta) 
  FROM counterlog
GROUP BY counterid

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.