Regarding SQLite's performance, some things I've found very useful:
Use WAL mode (writers don't block readers):
PRAGMA journal_mode = 'WAL'
Use memory as temporary storage:
PRAGMA temp_store = 2
Faster synchronization that still keeps the data safe:
PRAGMA synchronous = 1
Increase cache size (in this case to 64MB), the default is 2MB
PRAGMA cache_size = -64000
Lastly, use a modern version of SQLite. Many default installations come with versions from a few years ago. In Python for example, you can use pysqlite3[0] to get the latest SQLite without worrying about compiling it (and it also comes with excellent compilation defaults).
The negative value in the cache size pragma seems a mistake, but it isn’t. Negative values are used to specify a cache size that is a multiple of the page size (more or less) and the sign is discarded.
Not a great choice of API IMHO, but it’s a database, I’ve seen much worse.
In case you don't control which SQLite version you use (for example, on iOS or on Android, iOS normally comes with sane defaults), SQLite's default page size only recently changed to 4KiB, and update that can help with performance as well: https://www.sqlite.org/pgszchng2016.html
Use WAL mode (writers don't block readers):
Use memory as temporary storage: Faster synchronization that still keeps the data safe: Increase cache size (in this case to 64MB), the default is 2MB Lastly, use a modern version of SQLite. Many default installations come with versions from a few years ago. In Python for example, you can use pysqlite3[0] to get the latest SQLite without worrying about compiling it (and it also comes with excellent compilation defaults).[0] https://github.com/coleifer/pysqlite3