SQLite is cool now. DHH uses it, Laravel defaults to it. Here is a list of sensible defaults when using sqlite.
The whys?
PRAGMA journal_mode = WAL;Why?: Allows concurrent reads and writes, making it more suitable for web applications with multiple users accessing the database simultaneously.
PRAGMA synchronous = NORMAL;Why?: Balances performance and data safety by ensuring that data is written to disk in a reasonable time frame without slowing down writes as much as FULL mode.
PRAGMA busy_timeout = 5000;Why?: Prevents "database is locked" errors by giving SQLite 5 seconds to wait for a locked resource before returning an error, useful for handling multiple concurrent accesses.
PRAGMA cache_size = -20000;Why?: Sets the cache size to 20MB, allowing more data to be cached in memory, improving query performance by reducing the number of disk reads.
PRAGMA foreign_keys = ON;Why?: Ensures referential integrity by enforcing foreign key constraints, critical for maintaining consistent relationships between tables (e.g., users, posts, and comments).
PRAGMA auto_vacuum = INCREMENTAL;Why?: Reclaims disk space gradually as rows are deleted, instead of performing a full vacuum, reducing performance impact during database operations.
PRAGMA temp_store = MEMORY;Why?: Stores temporary tables and other temporary data in memory, improving the performance of operations like sorting and indexing that are common in web applications.
PRAGMA mmap_size = 2147483648;Why?: Uses memory-mapped I/O with a size of 2GB, which can speed up database access by reducing disk I/O, especially beneficial for large databases with frequent reads and writes.
PRAGMA page_size = 8192;Why?: Sets a page size of 8KB, which provides a balance between memory usage and disk I/O performance for a forum database that handles many reads and writes.
Copy paste
For your convenience.
-- 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;