Laravel: Optimizing SQLite to dangerous speeds

You bought the whole tachometer, you will use the whole tachometer.

Photo by Conor Samuel on Unsplash

SQLite is quite handy when you need to store a small set of data without having to spawn a whole MySQL instance or else. Create a file and let PHP’s PDO handle the rest, is that’s easy.

The problem I had the most with SQLite databases was when doing anything but a SELECT statement. When you INSERT data, DELETE a record on the database, or UPDATE an existing one, SQLite goes the safe way and wraps each single operation inside a “transaction”. The SQLite docs explains why this is so slow:

Transaction speed is limited by disk drive speed because (by default) SQLite actually waits until the data really is safely stored on the disk surface before the transaction is complete. That way, if you suddenly lose power or if your OS crashes, your data is still safe.

This may be relatively good when you’re storing critical data into SQLite, and you need your database to be 100% safe. But when doing numerous inserts, updates and deletes across multiple parts in the application, this becomes cumbersome since you need to plan ahead to wrap everything in a transaction… if you can. That’s why sometimes is just better to use MySQL and call it a day, at least for most people.

I tend to use SQLite for anything non critical, like Laravel Telescope. To avoid slowing the application by multiple requests and inserts into an SQLite database, we can optimize it by using asynchronous statements and a WAL journal.

Word of advice: this is kind of risky. Don’t use it for critical data. Y’all been warned.

Decoupling writing and reading

The first thing that will speed up our SQLite connection is the WAL mode, also called write-ahead log:

The original content is preserved in the database file and the changes are appended into a separate WAL file. A COMMIT occurs when a special record indicating a commit is appended to the WAL. Thus a COMMIT can happen without ever writing to the original database, which allows readers to continue operating from the original unaltered database while changes are simultaneously being committed into the WAL. Multiple transactions can be appended to the end of a single WAL file.

In a nutshell, it saves the data into a separate file, and once this file reaches 1000 pages (or whatever you set), it will be copied over the database and start again. The WAL file uses shared memory, so the writes and reads are done in memory and then persisted to the WAL file.

Since Laravel Telescope makes mostly writes, where the WAL mode excels, this will be very handy. Because the data is just for debugging reasons, no crash will kill the database.

Don’t wait for the OS

Once SQLite commits something into the database, it will wait until the write has been confirmed by the OS. Since we want speed, we can just NOT wait for this:

With synchronous OFF (0), SQLite continues without syncing as soon as it has handed data off to the operating system. If the application running SQLite crashes, the data will be safe, but the database might become corrupted if the operating system crashes or the computer loses power before that data has been written to the disk surface. On the other hand, commits can be orders of magnitude faster with synchronous OFF.

So we are on the good side if we don’t pull the plug on the server, shower it on sulfur, put it alongside Kilauea, or accidentally remove the hard-disk attaching it to an ICBM.

Is there more? Like NO2?

There is a great article by Adam Michael Wood over Who Is Hosting This that talks about multiple ways to speed up SQLite. The tradeoff is security and reliability, but in some escenarios this may be enough, like when saving data that can be regenerated easily.

You may want to check out optimizing the cache size, the where clauses, put temporary tables in memory, and use transactions when possible.

Pedal to the metal

Now that we know the top two options to optimize SQLite, we need to use them.

First, we will create an Artisan command to enable the WAL mode. Luckily for you, I already made one that I use almost every time:

What it does is simple: it will create a connection to the SQLite database and execute the WAL journal mode. If you drop something not-SQLite or the journal change is not successful, you will know. Just hit it:

php artisan sqlite:wal-enable sqlite

Technically the WAL mode can persist between connections. No need to execute it every time you boot up the application. Create the file, set is as WAL, done.

Next, we can just go into our AppServiceProvider and put inside the boot() method this line:

public function boot()
{
    // Don't kill the app if the database hasn't been created.
    try {
        DB::connection('sqlite')->statement(
            'PRAGMA synchronous = OFF;'
        );
    } catch (\Throwable $throwable) {
        return;
    }
}

This line must be the first in the application for one simple reason: it must be executed before Telescope starts using the database. If this is executed after, the synchronous mode will be on for all the commits, and there will be no performance gain.

And that’s it, be responsible when driving.