That’s a limitation you’ll hit pretty quickly unless you’ve specifically planned your architecture to be mostly read-only SQLite or one SQLite per session.

You certainly won’t hit it with most corporate OLAP processing, which is nearly all read-only SQlite. Writes are generally batched and processed outside ‘normal’ business hours, where the limitations of SQlite writing are irrelevant.

Where are they batched?

In a separate system maintained by the DuckDB cargo cultists.

I'm having a hard time imagining this. Aren't most CRUD apps using OLTP mostly read-only in the first place?

I just feel like you'd need thousands of concurrent users on a typical CRUD app to even get close to straining SQLite.

I'd recommend going with postgres if there is a good chance you'll need it, instead of starting with SQLite and switching later - as their capabilities and data models are quite different.

For small traffic, it's pretty simple to run it on the same host as web app, and unix auth means there are no passwords to manage. And once you need to have multiple writers, there is no need to rewrite all the database queries.