SQLite optimization for Laravel

01-README.md

The SQLite optimizations must be carried out at two different times: once in a unique and permanent way for the database and another time for each connection that is made. Below are the configurations that should be made in each case.

Unique and Permanent Configurations

These configurations are set only once and affect the database persistently, meaning they do not need to be reconfigured each time a connection is established:

PRAGMA journal_mode = WAL;

Sets the database journal mode to "WAL" (Write-Ahead Logging), which improves performance in concurrent operations.

PRAGMA page_size = 32768;

Defines the database page size to 32768 bytes. This change is persistent but can only be made when the database is empty.

PRAGMA auto_vacuum = incremental;

Configures the auto-vacuum mode to "incremental", allowing space to be reclaimed without blocking the database.

Per-Connection Configurations

These configurations need to be set each time a connection to the database is made, as they only affect the current session:

PRAGMA synchronous = NORMAL;

Controls the synchronization policy, balancing security and performance.

PRAGMA cache_size = -20000;

Defines the page cache size. A negative value indicates the size is specified in kilobytes, in this case, 20 MB.

PRAGMA foreign_keys = ON;

Enables foreign key constraint enforcement to ensure data referential integrity.

PRAGMA temp_store = memory;

Indicates that temporary tables should be stored in memory, which can speed up temporary operations.

PRAGMA busy_timeout = 5000;

Sets a timeout of 5000 milliseconds for locks, improving the database's ability to handle concurrency.

PRAGMA mmap_size = 2147483648;

Sets the memory mapping space size to 2 GB, which can enhance performance by allowing more data to be directly mapped into memory.

PRAGMA incremental_vacuum;

Performs an incremental vacuum to reduce the database size. This process permanently frees up space.

2024_07_26_000000_sqlite_optimize.php

<?php declare(strict_types=1);

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration {
    /**
     * @return void
     */
    public function up(): void
    {
        $db = DB::connection();

        if ($db->getDriverName() !== 'sqlite') {
            return;
        }

        $db->unprepared('PRAGMA journal_mode = WAL;');
        $db->unprepared('PRAGMA page_size = 32768;');
        $db->unprepared('PRAGMA auto_vacuum = INCREMENTAL;');
    }
};

AppServiceProvider.php

<?php declare(strict_types=1);

namespace App\Providers;

use Illuminate\Support\ServiceProvider;
use Illuminate\Support\Facades\DB;

class AppServiceProvider extends ServiceProvider
{
    /**
     * @return void
     */
    public function boot(): void
    {
        $this->sqliteOptimize();
    }

    /**
     * @return void
     */
    protected function sqliteOptimize(): void
    {
        $db = DB::connection();

        if ($db->getDriverName() !== 'sqlite') {
            return;
        }

        $db->unprepared('PRAGMA synchronous = NORMAL;');
        $db->unprepared('PRAGMA foreign_keys = ON;');
        $db->unprepared('PRAGMA temp_store = MEMORY;');
        $db->unprepared('PRAGMA busy_timeout = 5000;');
        $db->unprepared('PRAGMA mmap_size = 2147483648;');
        $db->unprepared('PRAGMA cache_size = -20000;');
        $db->unprepared('PRAGMA incremental_vacuum;');
    }
}