Pragma settings

Following pragma settings are set in ic-rusqlite by default:

journal_mode - PERSIST - persist journal file (is faster than deleting the file every time). Setting it to OFF works even faster, but disallows atomic COMMIT/ROLLBACK.

synchronous - OFF - because writes are always written and not cached, we do not need to explicity "flush" data changes to the disk.

page_size - 4096 - bigger page size works a bit faster by reduring sequential writes. The performance degrades, however, if there are many small writes at random locations.

locking_mode
- EXCLUSIVE
- exclusive mode is faster because we only lock the database once (the canister is the only user of the database).

temp_store - MEMORY
- avoids creating temporary file on disk, saves extra I/O operations.

cache_size - 500000 - set cache size to 4096 * 500000 bytes, this gives a significant performance boost at the expence of the canister's memory used.

The pragma settings can be adjusted with SQL queries, you might want to do that during the init or the post_upgrade events:

    PRAGMA cache_size=1000; -- Reduce the maximum DB cache size