4 ms·
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 ge
by mickeyp 11mo ago
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.
- tejinderss 11mo agoDo you know any good default PRAGMAs that one should enable?
- mickeyp 11mo agoThese 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 agoReally, no mmap?
- mikeocool 11mo agoUsing strict tables is also a good thing to do, if you value your sanity.
- porridgeraisin 11mo agoYou 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 agoAlso foreign_keys has to be set per connection but journal_mode is sticky (it changes the database itself).
- porridgeraisin 11mo agoYes, 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 agoExplanation of sqlite performance PRAGMAs https://kerkour.com/sqlite-for-servers https://kerkour.com/sqlite-for-servers
- e2le 11mo agoAlthough 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...
- mkoubaa 11mo agoSeems like it's asking to be forked
- justin66 11mo agoIt has been forked at least once: https://docs.turso.tech/libsql https://docs.turso.tech/libsql
- kbolino 11mo agoSQLite is fairly fork-resistant due to much of its test suite being proprietary: https://www.sqlite.org/testing.html https://www.sqlite.org/testing.html
- pstuart 11mo agoThe real fork is DuckDB in a way, it has SQLite compatibility and so much more. The SQLite team also has 2 branches that address concurrency that may someday merge to trunk, but by their very nature they are quite conservative and it may never happen unless they feel it passes muster. https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_concurrent.md https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_co... https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html As to the problem that prompted the article, there's another way of addressing the problem that is kind of a kludge but is guaranteed to work in scenarios like theirs: Have each thread in the parallel scan write to it's own temporary database and then bulk import them once the scan is done. It's easy to get hung up on having "a database" but sharding to different files by use is trivial to do. Another thing to bear in mind with a lot of SQLite use cases is that the data is effectively read only save for occasional updates. Read only databases are a lot easier to deal with regarding locking.