Skip to content

Instantly share code, notes, and snippets.

@cosmez

cosmez/sane-defaults.sql

Forked from james2doyle/sane-defaults.sql
Created May 16, 2025 17:15
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save cosmez/b4068b7e7e6f092b75723d65a02edbed to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save cosmez/b4068b7e7e6f092b75723d65a02edbed to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @james2doyle 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 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;