The Laravel Optimize DB package provides a good starting point for production-ready SQLite databases. Pest creator and core Laravel team member Nuno Maduro created this package.
This package is meant for SQLite (3.46+) in a Laravel project and works by applying migration to your project and runtime configuration applied via the package's service provider. It applies the following settings at the time of writing:
┌───────────────────────────┬─────────────┬───────────┐ │ Setting │ Value │ Via │ ├───────────────────────────┼─────────────┼───────────┤ │ PRAGMA auto_vacuum │ incremental │ Migration │ │ PRAGMA journal_mode │ WAL │ Migration │ │ PRAGMA page_size │ 32768 │ Migration │ │ PRAGMA busy_timeout │ 5000 │ Runtime │ │ PRAGMA cache_size │ -20000 │ Runtime │ │ PRAGMA foreign_keys │ ON │ Runtime │ │ PRAGMA incremental_vacuum │ (enabled) │ Runtime │ │ PRAGMA mmap_size │ 2147483648 │ Runtime │ │ PRAGMA temp_store │ MEMORY │ Runtime │ │ PRAGMA synchronous │ NORMAL │ Runtime │ └───────────────────────────┴─────────────┴───────────┘
High Impact Settings
I won't cover each setting, but the following three settings could potentially have a highly positive impact on SQLite performance.
journal_mode = WAL:
- Why it’s beneficial: Write-Ahead Logging (WAL) is one of the most effective ways to improve concurrency and performance in SQLite, especially for applications that need to support multiple readers and writers. It allows the database to handle many reads simultaneously while still maintaining atomicity and durability for writes.
- Use case: Great for databases with mixed read/write workloads and high concurrency requirements.
⠀cache_size = -20000 (20 MB cache):
- Why it’s beneficial: Increasing the cache size helps store more pages in memory, reducing the need for disk I/O. This is especially useful for databases that are read frequently or have hot data. A larger cache means fewer page reads from disk, resulting in faster query responses.
- Use case: Important when dealing with large datasets and you have sufficient memory resources available.
⠀mmap_size = 2147483648 (2 GB memory mapping):
- Why it’s beneficial: Memory-mapping the database file can dramatically improve performance by reducing the overhead associated with file system operations. It allows the database to access data directly in memory, bypassing the need for repeated system calls to read or write data.
- Use case: Very beneficial for large databases on systems with ample memory, where file access speed is a priority.
The package is considered a work-in-progress, so use it carefully. The package advises not to use it in production yet and to back up your database before requiring it. You can learn more about this package and view the source code on Github.
