PRAGMA journal_mode=WALwill enable the write-ahead log. This is persisted into the database and so only needs to be done once when the file is created.
PRAGMA case_sensitive_like = truewill cause the LIKE operator to match case sensitively. Note that there is no
ILIKEoperator, so you have to choose one or the other. The
GLOBoperator can help here though, since it is case-sensitive (and uses
%for its wildcards).
- Enforcement of column types can be enabled by adding the keyword
STRICTto the end of the
PRAGMA defer_foreign_keys = truecan be used to indicate that deferrable foreign key constraint checking should be deferred until the end of a transaction. This only applies to the current transaction, so it needs to be done again every time you are in a transaction that needs it.
- Bulk Loading
- When bulk loading data, the following settings can improve write performance significantly. Most of these settings should absolutely not be used in other scenarios or you can't recreate the entire database in the event of a crash, as they trade away all the the safety guarantees in exchange for faster writes.
PRAGMA journal_mode = OFF; PRAGMA page_size = 16384; -- if using the zstd VFS extension PRAGMA synchronous = 0; PRAGMA auto_vacuum = NONE; PRAGMA cache_size = -10485760; PRAGMA locking_mode = EXCLUSIVE; PRAGMA temp_store = MEMORY;
Thanks for reading! If you have any questions or comments, please send me a note on Twitter.