This is pretty much how we've been running in production with several different banks over the last half decade.
1 connection per database is critical. WAL is important. Not double-locking helps (most builds of SQLite serialize all writes internally).
You do all of these things correctly at the same time, then you can easily handle tens of thousands of transactions per second.
Additionally, we also do a per session database concept where data is scoped as specifically as possible. Using just 1 sqlite database for the whole application would be a mistake imo.
Synchronous replication is our next step, but we might build something in-house for this.
Are you aware of litestream? It's become quite useful in the last year or two, and recently provides synchronous replication. Worth checking it out (as well as rqlite and dqlite and bedrock) before starting your own project.
1 connection per database is critical. WAL is important. Not double-locking helps (most builds of SQLite serialize all writes internally).
You do all of these things correctly at the same time, then you can easily handle tens of thousands of transactions per second.
Additionally, we also do a per session database concept where data is scoped as specifically as possible. Using just 1 sqlite database for the whole application would be a mistake imo.
Synchronous replication is our next step, but we might build something in-house for this.