5 ms·

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

mickeyp 11mo ago
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.
- adzm 11mo ago
  Really, no mmap?
  - metrix 11mo ago
    I'm curious what your suggest mmap pragma would be.
    - adzm 11mo ago
      PRAGMA mmap_size=268435456; for example? I'm surprised by the downvotes. Using mmap significantly reduced my average read query time; durations about 70% the length!
- mikeocool 11mo ago
  Using strict tables is also a good thing to do, if you value your sanity.
- porridgeraisin 11mo ago
  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_pragma_optimize_ https://www.sqlite.org/lang_analyze.html#periodically_run_pr...
  - masklinn 11mo ago
    Also foreign_keys has to be set per connection but journal_mode is sticky (it changes the database itself).
    - porridgeraisin 11mo ago
      Yes, if journal_mode was not sticky, a new process opening the db would not know to look for the wal and shm files and read the unflushed latest data from there. On the other hand, foreign key enforcement has nothing to do with the file itself, it's a transaction level thing. In any case, there is no harm in setting sticky pragmas every connection.
leetrout 11mo ago
Explanation of sqlite performance PRAGMAs https://kerkour.com/sqlite-for-servers https://kerkour.com/sqlite-for-servers
e2le 11mo ago
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_options https://sqlite.org/compile.html#recommended_compile_time_opt...

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.

adzm 11mo ago
Really, no mmap?
- metrix 11mo ago
  I'm curious what your suggest mmap pragma would be.
  - adzm 11mo ago
    PRAGMA mmap_size=268435456; for example? I'm surprised by the downvotes. Using mmap significantly reduced my average read query time; durations about 70% the length!
mikeocool 11mo ago
Using strict tables is also a good thing to do, if you value your sanity.
porridgeraisin 11mo ago
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_pragma_optimize_ https://www.sqlite.org/lang_analyze.html#periodically_run_pr...
- masklinn 11mo ago
  Also foreign_keys has to be set per connection but journal_mode is sticky (it changes the database itself).
  - porridgeraisin 11mo ago
    Yes, if journal_mode was not sticky, a new process opening the db would not know to look for the wal and shm files and read the unflushed latest data from there. On the other hand, foreign key enforcement has nothing to do with the file itself, it's a transaction level thing. In any case, there is no harm in setting sticky pragmas every connection.

leetrout 11mo ago

Explanation of sqlite performance PRAGMAs https://kerkour.com/sqlite-for-servers https://kerkour.com/sqlite-for-servers

e2le 11mo ago

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_options https://sqlite.org/compile.html#recommended_compile_time_opt...