I wish every program that wanted 'a SQL database' would let me use SQLite

January 30, 2021

There are, out in the (open source) world, a lot of programs that want to use a SQL database but aren't all that picky about which one; for instance, there are a lot of programs that will work with either MySQL or PostgreSQL (which one they recommend can vary). As a sysadmin, I wish all of these programs would also let me use SQLite. Frankly, everything should have an option to use SQLite as its SQL database if at all possible.

Why I want this is simple; if your program can use SQLite, I don't have to run a database server for it. When I don't have to run a database server, I also don't have to install it, set it up, manage the access passwords for it, make sure I'm properly backing it up, manage updates to it (including across upgrading the version of the operating system), and so on and so forth. Every actual database server (whether MySQL, PostgreSQL, or something else) comes with its own collection of necessary work to maintain it. As sysadmins, we can do all of that work, but we only have a finite amount of work time and we would really like to spend that time on more useful things.

(This is pretty much the same as the attraction of SQLite from a programmer perspective.)

In theory, PostgreSQL and MySQL can deal with higher load and bigger things than SQLite is. In practice, most actual deployments of most programs are likely to have small databases and low loads, where SQLite is perfectly capable and bigger, more complicated database servers are frankly vast overkill (as well as comparative resource hogs). We have built some systems where we shouldn't use SQLite (and didn't), but we also have plenty more where SQLite is enough, or at least would have been if the program let us use it.

(I also suspect that modern SSDs and especially NVMe drives raise the threshold of what SQLite can deal with, especially if you have only a low level of concurrent access.)

If the developers of programs are nervous about people picking SQLite when they're going to regret it, I'm fine if you want to recommend MySQL or PostgreSQL and only mention SQLite support at the end (I'm pretty likely to find it). And if you really think that SQLite won't be able to keep up with the database demands of your program, I need to know the details, because it's quite likely that my naively configured MySQL or PostgreSQL instance won't keep up either.

(I say 'naively configured' but in practice we generally don't configure MySQL or PostgreSQL at all. We take whatever the defaults are in the Ubuntu package, or perhaps in whatever MySQL or PostgreSQL configuration file you provided. One reason for this is that we don't currently have much expertise or experience in running demanding database servers.)

Written on 30 January 2021.
« Illustrating the importance of fully multi-core program building today
The limitations on find's -exec option and implementation convenience »

Page tools: View Source, Add Comment.
Login: Password:
Atom Syndication: Recent Comments.

Last modified: Sat Jan 30 23:48:12 2021
This dinky wiki is brought to you by the Insane Hackers Guild, Python sub-branch.