Taking many row level locks on a rows with little contention - how does that affect PostgreSQL scalability (and RDMS in general)

Question

I use transactions to lock on rows in my database. Generally, the number of 'things' accessing the same rows at a single time is small, however, for correctness I would still like to use transactions to have 'all or nothing' changes across multiple tables to ensure correctness.

I am wondering, if I don't ever expect the number of 'things' accessing a row to never be significant (like on average once per minute), and these operations are quite small, then does locking, in this case, affect my ability to scale the database? My understanding is that RDBMS don't scale as well as, say, NoSQL technologies because of the ACID properties. What about things like indices... e.g. updating data may still require an index change, which I'm guessing is synchronous in RDBMS world?... or maybe that is configurable?

While I may not have a lot of contention on a single record, I will have many, many locks on records throughout the database at the same time. If these locks are isolated, am I limited in anyway by the overhead/number of simultaneous locks? Is my database scalability good?

More context:

I use postgres and part of our product includes a large inventory stock system which maintains quantities of various inventory. There are many scenarios that can modify inventory quantity from many different ways (e.g. users can manually adjust quantity using UI or automatically when a stock request or refill event comes into our system through our exposed APIs). Between reading the stock quantity and adjusting it (assuming I am not using increment/decrement in this scenario) I hold a row level lock as part of the transaction.

Interested to hear people's thoughts Thanks

Answer 1

If you have several transactions modifying the same row, the first will block the others. So your concurrency depends directly on the duration of these transactions. For example, if the average transaction locking a single row takes 5 milliseconds, you can perform 200 such transactions per second.

If the transactions lock different rows, then such a limit does not apply, because the transactions can run in parallel. Still, the shorter your transactions are, the better.