[InfraScaler]

Does WAL really offer multiple concurrent writers? I know little about DBs and I've done a couple of Google searches and people say it allows concurrent reads while a write is happening, but no concurrent writers?

Not everybody says so... So, can anyone explain what's the right way to think about WAL?

[gobdovan]

No, it does not allow concurrent writes (with some exceptions if you get into it [0]). You should generally use it only if write serialisation is acceptable. Reads and writes are concurrent except for the commit stage of writes, which SQLite tries to keep short but is workload- and storage-dependent.

Now this is more controversial take and you should always benchmark on your own traffic projections, but:

consider that if you don't have a ton of indexes, the raw throughput of SQLite is so good that on many access patterns you'd already have to shard a Postgres instance anyway to surpass where SQLite single-write limitation would become the bottleneck.

[0] https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_co...

[pixelesque]

No it doesn't - it allows a single writer and concurrent READs at the same time.

[InfraScaler]

Thanks! even I run a sqlite in "production" (is it production if you have no visitors?) and WAL mode is enabled, but I had to work around concurrent writes, so I was really confused. I may have misunderstood the comments.

[yomismoaqui]

Writes are super fast in SQLite even if they are not concurrent.

If you were seeing errors due to concurrent writes you must adjust BUSY_TIMEOUT

[InfraScaler]

Thanks I'll have a look. For now I just had a sane retry strategy. Not that I have any traffic, mind you :-)))