No, it's indeed a very real problem. I ran into with a very small service.
The only difference between SQLite and Postgres write locking is the granularity.
But it’s talked about as if it’s a categorical limitation, the app will fail if there is concurrency. But it’s just a question of how much time will be spent locking.
A website with a 16 process pool for handling requests will be fine.