My experience with WordPress SQLite

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 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 WordPress with Genesis Framework which is a nice blogging platform, $10 VPS which offers 1 GB 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 Asynchronous query 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.

  1. Your plugin page for ‘sqlite-integration’ is no longer working: https://wordpress.org/plugins/sqlite-integration/

    I realize it was too slow for you, but it might work for my situation, and perhaps other people. I’d love to play with it. Could you please post the plugin code here on your site (or elsewhere, lite github, and link to it from this your site page)? Thanks much, I’d really appreciate it, and I think some others will too.

      1. OK, thank you so much for responding with the link. I will give it a try.
        P.S: You have some great blog articles. But there is a problem with your site: it doesn’t work in dark mode, with the ‘Dark Reader’ addon, at least on firefox. That makes it impossible for me to read directly (white backgrounds blind me), so I have to copy your pages into a text editor to read them. Your site is the only one I’ve seen that does this; I don’t know where the conflict could be.

        1. My website has it’s own dark mode option, if you press the settings icon next to the search icon, it will show you a drop-down all need to do is press the Night Mode button, once enabled everything should go dark.

          I was not sure that my website is not fully compatible with Firefox, thank you for bringing it to my attention, I will work on it and try resolving the issue.

          Let me know if you need anything else, thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *

Up Next:

Remove all installed Homebrew packages

Remove all installed Homebrew packages