[marktangotango]

For reads just create a new connection for every request (be sure to set connection properties for wal etc). Then create a global (or something equivalent to global, like a singleton) connection to serve as the writer and put a mutex around it when doing writes. Easy, scales like a mofo.

[firebacon]

What does "mofo" mean here. Can you give us a rough estimate on the transaction rate you achieved with this setup? My own experience and all independent benchmarks I can find seem to indicate a limit of 100-1000TPS on reasonable hardware.

Note that you can "batch" up many inserts into a transaction, which gives you a high "nominal" insert rate but still only ~100 actual transactions or so per second. To see why this is not the most useful number when comparing to a database like Postgres, consider that the limiting factor in a SQLite/Posgres design are cache flushes which outweight the costs of actually writing the data, so the number of rows per batch is mostly arbitrary; using this metric you can always claim a huge insert performance by choosing a suitable N. Also, if you do the batching, you of course loose some of SQLite's consistency/durability guarantees for your writes, which is probably fine if you didn't need them in the first place, but begets the question if an embedded ACID database is the best tool for the job at hand.

[marktangotango]

Test it yourself, you can try all the different combinations of journalling mode and synchonous modes. I'm not here to proscribe anything to anyone. Every use case is different.

[skrebbel]

> What does "mofo" mean here

Motherfucker

[no_wizard]

I think they meant what makes it a positive `mofo`, if you will.

[skrebbel]

Ah yes of course. Not deleting my comment because I think my misunderstanding is funny.