logoalt Hacker News

tejinderss • last Saturday at 2:38 PM • 3 replies • view on HN

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

Replies

mickeyp • last Saturday at 2:46 PM

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.

➕ show 3 replies
leetrout • last Saturday at 2:48 PM

Explanation of sqlite performance PRAGMAs

https://kerkour.com/sqlite-for-servers

e2le • last Saturday at 4:11 PM

Although not what you asked for, the SQLite authors maintain a list of recommended compilation options that should be used where applicable.

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