d’oh, I kind of speculated that this pattern might be possible to apply to WAL above without having read far enough down to see it was implemented in WAL2. Though I mentioned RCU the hot/cold partitions were something I was also thinking of.
I wonder if this could be further extended to better support concurrent writes. Depending on the implementation, with wal2 readers may be reading from both hot and cold files without blocking. So this may potentially be extendable to read from two hot files, or two hot files and two cold files.
It might be helpful to link the docs for some of these configs in the section where you mention them. Some thoughts:
Based on https://www.sqlite.org/wal.html it seems the WAL index is mmapped as a workaround to some edge cases not relevant to many application developers. They say it shouldn’t matter, but with the larger page sizes you’re using, using the original implementation approach they describe (volatile shared memory) actually might improve performance slightly - do you know if your WAL index ever exceeds 32KiB? Not sure as to the difficulty of resurrecting that old functionality though.
Also, this case seems like something that could happen from time to time depending on what you’re doing. Did you encounter it? Case:
> When the last connection to a particular database is closing, that connection will acquire an exclusive lock for a short time while it cleans up the WAL and shared-memory files. If a second database tries to open and query the database while the first connection is still in the middle of its cleanup process, the second connection might get an SQLITE_BUSY error.
Both the WAL docs and the article mention blocking checkpointing/a need for reader gaps to ensure the WAL flushes, or a possibility that WAL files will grow indefinitely. I had some speculation that this was an implementation limitation, and it turns out another comment mentions WAL2 may relax this requirement by using two WAL files split between “hot” and “cold”. Curious how the performance might compare with this: https://sqlite.org/cgi/src/doc/wal2/doc/wal2.md
I wonder if this could be further extended to better support concurrent writes. Depending on the implementation, with wal2 readers may be reading from both hot and cold files without blocking. So this may potentially be extendable to read from two hot files, or two hot files and two cold files.