Skip to content

Instantly share code, notes, and snippets.

@james2doyle

james2doyle/sane-defaults.sql

Created December 6, 2024 17:35
Show Gist options
  • Save james2doyle/74cc98afda6b1ee8fdf2d5076aa1fe15 to your computer and use it in GitHub Desktop.
Save james2doyle/74cc98afda6b1ee8fdf2d5076aa1fe15 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. james2doyle renamed this gist Dec 6, 2024. 1 changed file with 0 additions and 0 deletions.
    0 sane-defaults.sqlite → sane-defaults.sql
    View file
    File renamed without changes.
  2. james2doyle created this gist Dec 6, 2024.
    28 changes: 28 additions & 0 deletions sane-defaults.sqlite
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,28 @@
    -- @see https://briandouglas.ie/sqlite-defaults/

    -- Set the journal mode to Write-Ahead Logging for concurrency
    PRAGMA journal_mode = WAL;

    -- Set synchronous mode to NORMAL for performance and data safety balance
    PRAGMA synchronous = NORMAL;

    -- Set busy timeout to 5 seconds to avoid "database is locked" errors
    PRAGMA busy_timeout = 5000;

    -- Set cache size to 20MB for faster data access
    PRAGMA cache_size = -20000;

    -- Enable foreign key constraint enforcement
    PRAGMA foreign_keys = ON;

    -- Enable auto vacuuming and set it to incremental mode for gradual space reclaiming
    PRAGMA auto_vacuum = INCREMENTAL;

    -- Store temporary tables and data in memory for better performance
    PRAGMA temp_store = MEMORY;

    -- Set the mmap_size to 2GB for faster read/write access using memory-mapped I/O
    PRAGMA mmap_size = 2147483648;

    -- Set the page size to 8KB for balanced memory usage and performance
    PRAGMA page_size = 8192;