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

2.1. Basic config

pragma pragma_list; -- list all pragma

2.2. Data corruption

pragma synchronous = normal;
  • full (default): fsync every update
  • normal : don't fsync every update but wal checkpoints need to wait for fsync
  • off: 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.
  • 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
  • In memory

    pragma temp_store = memory; -- temporary tables/indexes in memory
    

3. Tools

4. Important links

Created: 2024-07-16 Tue 16:44

Validate