SQLite concurrency and why you should care about it

SQLite is a cracking database -- I love it -- that is let down by its awful defaults in service of 'backwards compatibility.'

You need a brace of PRAGMAs to get it to behave reasonably sanely if you do anything serious with it.

Do you know any good default PRAGMAs that one should enable?

These are my PRAGMAs and not your PRAGMAs. Be very careful about blindly copying something that may or may not match your needs.

    PRAGMA foreign_keys=ON
    PRAGMA recursive_triggers=ON
    PRAGMA journal_mode=WAL
    PRAGMA busy_timeout=30000
    PRAGMA synchronous=NORMAL
    PRAGMA cache_size=10000
    PRAGMA temp_store=MEMORY
    PRAGMA wal_autocheckpoint=1000
    PRAGMA optimize <- run on tx start

Note that I do not use auto_vacuum for DELETEs are uncommon in my workflows and I am fine with the trade-off and if I do need it I can always PRAGMA it.

defer_foreign_keys is useful if you understand the pros and cons of enabling it.

You should pragna optimize before TX end, not at tx start.

Except for long lived connections where you do it periodically.

https://www.sqlite.org/lang_analyze.html#periodically_run_pr...

Also foreign_keys has to be set per connection but journal_mode is sticky (it changes the database itself).