sqlite
Table of Contents
1. Basic
1.1. Outputs and Predicate
1.1.1. String
- string concat happens with
single&double quotes
and+
.strings
are also case sensitive.
1.1.2. Date&Time
-- time since SELECT julianday(CURRENT_TIMESTAMP) - julianday('2023-01-01'); SELECT CAST(julianday(CURRENT_TIMESTAMP) - julianday('2023-01-01') AS INT); -- casted -- now SELECT date('now') SELECT date('now', 'start of month', '+7 days', 'weekday 3', 'utc');
1.2. PRAGMAs
PRAGMA table_info(<table_name>);
: Lists columns of the table with types
2. Configuration
- See SQLite performance tuning
- Config can be set via env vars or else during runtime using
pragma
statements
2.1. Basic config
pragma pragma_list; -- list all pragma
2.2. Data corruption
pragma synchronous = normal;
full
(default): fsync every updatenormal
: don't fsync every update but wal checkpoints need to wait for fsyncoff
: don't wait for fsync for updates and wal updates (somewhat risky)
2.3. WAL
pragma journal_mode = WAL; -- default is "rollback"
- Enabling WAL allows read and write to happen simultaneously.
- sqlite currently is single-writer, multi-reader. There are ways to do multi-writer, see
cr-sqlite
- There is also an experimental
WAL2
mode that allows multiple concurrent write transactions as long as they don’t use the same pages.
- There is also an experimental
- If you have lots of writes happening it can lock the table and cause issues w wall checkpointing. See this blogpost for more info.
2.4. Others
Vacuum
pragma vacuum; -- following useful if db shrinks pragma auto_vacuum = incremental; -- once on first DB create pragma incremental_vacuum; -- regularily
Re-analyze
pragma optimize;
- helps in better query plan?
- apply before closing connection/periodically
sqlite page size
pragma page_size = 32768;
- Can help if storing large blobs
- SQLite will always only replace whole pages, so bigger
page_size
will add overhead to write queries.
mmap
pragma mmap_size = 30_000_000_000;
- W if db size less than
mmap_size
- If db size > mmapsize, first part will be mmaped, later will use syscalls
- W if db size less than
In memory
pragma temp_store = memory; -- temporary tables/indexes in memory
3. Tools
sqldiff
: command-line utility program that displays content differences between SQLite databases.- The sqlite3analyzer.exe Utility Program