Hacker Neue

Preferences
polyrand parent
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).

[0] https://github.com/coleifer/pysqlite3

lrossi
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.

https://www.sqlite.org/pragma.html#pragma_cache_size

polyrand OP
I agree it's a very weird API, but you get used to it. In python, when initializing the DB I always use:

  conn.execute(f"PRAGMA cache_size = {-1 * 64_000}")
That way I never forget about the minus.
jug
Here are the pros and cons of WAL mode: https://sqlite.org/wal.html

For us, the biggest thing would be that it can't be used if the database is on a network drive relative to the host process.

liuliu
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

This item has no comments currently.