SQLite, the elephant in the cloud.
So, I've been trying to write this post for about a week now, and its been surprisingly difficult. Mostly because I've been trying to distill why I have such an issue with SQLite, and what experiences cause that.
First before we go into that, lets talk about what SQLite is, and what its designed for, at least from my perspective. SQLite is a lightweight database, effectively a glorified CSV/Excel doc. It uses a single file for data storage, has a defined structure, and thus can do "fancy" things like table lookup, and indexing. Its purpose is for applications to quickly store non-essential data, things that the application has generated, and could generate or gather again if needed. Its quick, useful, flexible, and now ubiquitous. This makes it a great database for development of opensource tools, and local development of applications. I use several applications in my home lab that utilize SQLite, such as Tautulli, Tdarr, and Pinchflat**. All of these applications work well for the job they're intended to do, but recently all of these applications have been more of an irritation to me then helpful.
The reason they have been an irritation is partially because of the setup I have, and partially due to the nature of SQLite itself. The main source of irritation is that SQLite doesn't perform well over the network. This may be obvious to some, but not all, since its basically just a file right? And we stream multi-gigabyte videos from the cloud with no problem, why would a couple of kilobyte(or even a could hundred kilobyte) file be an issue? Simply put, its an issue because of the way that the file is used. As a database file, it is continually read and modified by a single process during user interactions(Select, Update, Delete, etc), along with background tasks that run to update indexes, mark tables as deleted, and the like. Traditionally, that file sits on the hard drive, right next to the executing code, and is locked by the single process that reads and modifies the file (usually the executing code). This means that the read and write operations are fast, like REAL fast, or at least as fast as the backing media (SSD, Spindle) can read and modify the file. Unfortunately this does not translate to a network file. Because a network file can be modified by several processes at the same time, along with the overhead of transferring the data itself over a TCP connection, reading and modifying a network file is medium to slow to do, depending on your network. This means that if you were to put a SQLite database on a network server (NFS for example), you have instantly created a bottleneck for yourself, and your application, regardless of what it is. This now begs the question "Why would you put a SQLite database on a network share?".
In short the answer is because of the cloud. We now live in a world where local storage is no longer an immediate physical constraint for home labs (and hasn't been one for data centers for a long time now). A lot of home labs have transitioned+ from physical servers, through VMs, and Docker hosts, to self-managed groups of Docker hosts, also known as Kubernetes(K8s). The downside to these groups is that they generally cannot use the local storage of the host, since its not guaranteed that any one host will ALWAYS be the host that runs the docker image. This means that to correctly utilize these groups all the storage must be a network share, or at least a network accessible storage (e.g. ISCSI).
Home labs are not the only one who have navigated this transition. A lot of the open source tooling/applications have also navigated the transition from a physical host(or VM) to a docker image. Many come with not only a link to the docker image, hosted on various services, but a docker compose file showing exactly how to run the image, and the volumes/bindings/networks establish for the application. Yet, the applications are "stuck" in the Docker host era, due to the reliance on SQLite. Instead of being able to fully utilize the group of Docker hosts, they rely on you to either have a local storage, or accept the poor performance of SQLite over a network connection.
This leads me to the question of
"Where is the SQLite-Net Database?"
I realize that its probably not going to be called that, but the sentiment is there. With the rise of K8S clusters in home labs (and in general), I would hope that we see a new DB software that is as easy to integrate as SQLite, but handles network connections better.
Now the more astute among you may ask, "Why not just advocate for devs to use a RDMS server instead?". Simple, if the devs were inclined to do that, they would already be doing so. RDMS systems have been around for long enough that they are well understood and can be implemented fairly quickly, but they add a layer of complexity to the application since now you must include a {separate} RDMS system along with your application OR have the user setup they're own RDMS and have the configuration point to it. That, at a minimum, is Not Ideal, and at a higher level, adds a complexity that 90% of your users don't want to do to run your application. Hence the need for a SQLite-Net, something that still can be deployed along with the application, but handle network connections better.
For now though, I guess I will just deal with the poor performance of SQLite over the network*** and hope that a dev will make a more network robust SQLite.
** Technically I actually run Pinchflat off a MariaDB instance now, see the last post, but the general release is SQLite only
+ I say transitioned, but in reality its more like appended, as a lot of home labs, including mine, have a combination of all four levels, depending on each home lab owner and their goals.
*** I'm also looking into if I can add support for RDMS to the applications, but that's a whole other thing.