Laravel: Optimizing SQLite to dangerous speeds

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

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 statement. When you data, a record on the database, or 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 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:

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 and put inside the method this line:

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.

The Startup

Medium's largest active publication, followed by +756K people. Follow to join our community.

Italo Baeza Cabrera

Written by

Graphic Designer graduate. Full Stack Web Developer. Retired Tech & Gaming Editor.

The Startup

Medium's largest active publication, followed by +756K people. Follow to join our community.

Italo Baeza Cabrera

Written by

Graphic Designer graduate. Full Stack Web Developer. Retired Tech & Gaming Editor.

The Startup

Medium's largest active publication, followed by +756K people. Follow to join our community.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store