SQLite optimisations in Laravel
The winds of opinion have changed on databases in the last ~6 months, and SQLite is popular again. Rails and Laravel have adopted it as the default DB.
Rails is working towards pre-optimising SQLite DBs by default. I'll show you how to do the same in Laravel.
1. Multiple SQLite databases
This is the first pattern I see as an "easy win" for scaling: Split out connection-heavy functionality to separate SQLite files. This can be done for anything that uses a database driver. Cache, queues, Pulse, Telescope, etc.
This effectively gives you multiple database connections (solving a SQLite limitation) "for free" and lets your SQLite performance scale to the level of your server hardware (SSD).
To do this with Laravel, you'd first need to add extra database connections to your config/database.php file, as well as modify your migrations for cache, queue etc to use those databases.
Here's an example of the connections for commonly db-heavy areas of your app that aren't related to your core data:
//in connections array
'thing_db' => [
'driver' => 'sqlite',
'url' => env('DB_URL'),
'database' => env('THING_DATABASE', database_path('THING.sqlite')),
'prefix' => '',
'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),
],
THING here can be any aspect of your app that can be database-heavy, while not being part of your core data. Good examples are cache, queues, Pulse, and Telescope. Session and notifications are tied to user data however, so they can't be easily split out.
2. SQLite-specific optimisations
Straight to the point, these are the SQLite PRAGMAs that will help your database performance:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA journal_size_limit = 67108864; -- 64 megabytes
PRAGMA mmap_size = 134217728; -- 128 megabytes
PRAGMA cache_size = 1000000000;
PRAGMA foreign_keys = true;
PRAGMA busy_timeout = 5000;
PRAGMA temp_store = memory;
The first pragma can be run in a migration as it is persistent. This example does it for all your SQLite databases if you followed the previous section:
$connections = ['sqlite', 'cache_db', 'pulse_db', 'queue_db', 'telescope_db'];
foreach ($connections as $connection) {
DB::connection($connection)
->statement(
'
PRAGMA journal_mode = WAL;
COMMIT;
'
);
}
The other pragmas however need to be run on every connection. As far as I'm aware, this is how you'd do it in Laravel, by modifying the connection while the app is booting in the AppServiceProvider:
// In app/Providers/AppServiceProvider.php
public function register(): void
{
$connections = ['sqlite', 'cache_db', 'pulse_db', 'queue_db', 'telescope_db'];
foreach ($connections as $connection) {
DB::connection($connection)
->statement(
'
PRAGMA synchronous = NORMAL;
PRAGMA mmap_size = 134217728; -- 128 megabytes
PRAGMA cache_size = 1000000000;
PRAGMA foreign_keys = true;
PRAGMA busy_timeout = 5000;
PRAGMA temp_store = memory;
'
);
}
}
The numbers can be tweaked if you know what you're doing. Read this great article for an in-depth explanation. All you need to know is that these will make your SQLite DB more robust and performant.
That's it!
Obviously it's a little bit more config than just spinning up a default MySQL/Postgres database, or Laravel's default of just creating a SQLite file, but these are also such quick wins that it's hard not to recommend them.