SQLite should be fine if your workload biased towards reads. But its design does prevent many-writers and these, even operates on completely different tables, has to be serialized between each other. This can be more problematic since many SQLite articles recommend big transactions to improve performance. And write transactions have to be serialized between each other. If you are a write-heavy workload, at some point, you may need to shard and may even need to implement custom transaction support on top of that yourself (or is there open-source libraries does that? rqlite from my understanding only handles consensus / replication, doesn't do sharding).
Yes, SQLite does serialize writes and write-heavy workloads and long-running transactions are not a good fit. Small batching of writes can help but SQLite provides options to tradeoff throughput for durability if that works for your situation:
PRAGMA synchronous = NORMAL;
That avoids fsync() calls on the WAL until checkpointing. I don't know of any libraries that implement transaction coalescing as it can be different depending on if you need to batch sequential writes versus writes coming from multiple parallel threads
re: rqlite, yes it handles consensus/replication through Raft but IIRC it doesn't do sharding.
