My experience with WordPress SQLite

A few months back I wrote a post on how to get WordPress running with SQLite which received a great response and turned out to be something people were looking for quite some time.

This means instead of using a production-grade SQL Database you can use a file that can be queried as a DB, making the footprint smaller.

I would like to kjmtsh to make this possible by creating the SQLite Integration plugin.

This website was previously powered by WordPress and SQLite which was great. It did not require configuring of a MySQL database, it ran very well on my VPS, which was a $5 Digital Ocean droplet that did a great job serving the site as it was relatively new and had only 60 to 100 views a day.

It was all going well until I started using plugins to get some extra functionality and 3rd party services that made the file size bigger and took longer to query, over time the website start receiving more visitors, now 200 to 300 which made it slow, when multiple visits took place serving them took even longer and even the admin interface was affected by them.

The “vacuum” option worked well to reduce the file size, the main problem was not of the file size. It was of unused columns and tables created by plugins that were never deleted or cleared after disabling it and removing it from the system.

WordPress does not remove an unused information and does not give you an option by default.

I found that using cache plugins would be a temporary fix, which did fine, but then it started making the admin part sluggish and things, like drafting and editing previously published content, was a nightmare while the site still grew and had, even more, larger amounts of viewership and made it super slow a request would take 30 seconds to load.

I spent some good time doing research on troubleshooting this problem and it turned out that the SQLite setup is great for websites that have smaller viewership and those that don’t update very frequently.

This website has over 172 posts which no longer made it small and continues to grow.

The reason I was using SQLite was that MySQL would be too heavy on a single core 512 MB VPS and I did not expect to have a large amount of audience.

Right now this website runs Ghost which is a nice blogging platform, back to WordPress and is powered by MySQL Database and I have upgraded to a $10 VPS which offers 1GB of RAM.

WordPress was never designed for SQLite but can be a good alternative for those who don’t prefer or don’t have the resource to host a MySQL Database.

This would be the perfect solution for devices like Raspberry Pi.

It would fail if there is more than one editor working on the same or different content as SQLite does not do Async quite well and will be slow as hell.

Hope you enjoyed the read, leave a comment below to let me know what you think or if you are planning to go with SQLite.

Leave a Reply