SQLite async connection pool for high-performance

Regarding shared caching: Use `PRAGMA mmap_size` to enable mmap for reading your database. This way SQLite won't add another layer of page caching on top saving RAM and making things faster. SQLite only uses mmap for reads and will continue to write to the database with pwrite().

You must set it to a value higher than the size of your DB. I use:

    PRAGMA mmap_size = 1099511627776;

(1TB)

Unless you compile SQLite yourself, you'll find the maximum mmap size is 2GB. ie even with your pragma above, only the first 2GB of the database are memory mapped. It is defined by the SQLITE_MAX_MMAP_SIZE compile time constant. You can use pragma compile_options to see what the value is.

https://sqlite.org/compile.html#max_mmap_size

Ubuntu system pragma compile_options:

    MAX_MMAP_SIZE=0x7fff0000

That seems like a holdover from 32-bit days. I wonder why this is still the default.

SQLite has 32 bit limits. For example the largest string or blob it can store is 2GB. That could only be addressed by an incompatible file format change. Many APIs also use int in places again making limits be 32 bits, although there are also a smattering of 64 bit APIs.

Changing this default requires knowing it is a 64 bit platform when the C preprocessor runs, and would surprise anyone who was ok with the 2GB value.

There are two downsides of mmap - I/O errors can't be caught and handled by SQLite code, and buggy stray writes by other code in the process could corrupt the database.

It is best practise to directly include the SQLite amalgamation into your own projects which allows you to control version updating, and configuration.