Adventures in databases 01: so it begins

Years ago, back when I was in undergrad, I wanted to write a simple command line application. This application needed to have persistent data, and for that I would need some sort of database. The specifics of that application aren't important, just know I wanted the database to:

1. Start up when the program started up and shut down when the program shut down
2. Startup should be as fast as possible, shutdown could take however long
3. Reads should be as fast as possible
4. Writes could take their time
5. There was roughly one table's worth of information, <10 rows, and maybe 10,000 entries

Basically, I wanted something I could use immediately and read from right away. Notice I didn't include anything about consistency or whether multiple connections should be supported or any of a number of very important considerations. What can I say? I was young.

So, knowing nothing else, I created my program from a single Ruby file (I love Ruby to this day) that used a simple file that I Marshal'd¹ to and from whenever I read or wrote to it. Like most projects this started off tickety-boo (computed are fast). Inevitably, however, it did start to feel quite sluggish. Just starting up the codebase took on the order of hundreds of ms, not to mention writing a few megabytes to disk for every single write. On top of that everything was single-threaded, nothing was debounced, it was a mess.

Rather than keeping my mound of Ruby code and just fixing the actual problem, my database, I took this as an opportunity to migrate to a language I had been wanting to try out anyway: go. Which went well! I don't use go nowadays due to an attachment to my feet, but I still think it's a well-designed, beautiful, and simple language.

Anywho, since we're already re-writing everything, why not try out a new database. Like a real, proper one. Unfortunately I went to school during the height of the NoSQL craze. I was under the impression that databases are slow, but I wanted speed. So I dug around and found BuntDB, a key-value store that you could persist to disk. It used a append-only file for persistence but during runtime kept the entire database in memory. Which meant that writes were so supposed to be instantaneous, though you did need to ensure that you reliably persisted to disk on close.

This worked great! Startup times were now somewhat negligible, writes were instant, and I eventually figured out how to connect the cleanup code so that it ran even on SIGINT. Life was good.

That is, until the day I wanted to run two instances of my application and have them connect to the same database. Yikes. I ended up implementing my own custom lock file system. This technically worked - exactly one application would be deemed the primary and was allowed to persist its copy of the database to disk, whereas the rest would dump and changes they had made. It ended up being like a convoluted Mutex with file handles rather than Atomics that only worked some of the time. Plus I was having some issues with concurrency and consistency and the key-value store was a bear to work with. Everything was a string so I had to write my own serialization/deserialization, and anytime I wanted to change my "schema" I had to write custom migration code and remember to run it.

So finally, after years of hemming and hawiing, I finally bit the bullet and decided to give good ol' SQL a try, if only to assure myself that I was on the right track, that since my application needed to have a super fast startup time that SQL was just going to be a non-starter. But nothing too big, I don't want to manage a full PostreSQL install. Let's just try SQLite to get our feet wet.

My. Word.

The speed! The features! The easy of use! The wonderful tooling! Setting up my tables was kind of annoying, but way less annoying than rolling my own serliazation! Plus, now I can trivially support multiple concurrent readers and writers, create indexes for performance, perform migrations when my schema changes. I can even do ad-hoc analysis against the database file. File! We're back to where we started, but it's so much better this time around. If I want a backup I just copy that file. And the thing I thought I was going to lose, my precious snappy startup time? Well I measured it, before and after, and the startup time with SQLite is less!!! The write time isn't quite as good, as you might expect, but I get around that with batch writes that I do in a background thread, which should have been the answer in the first place[3].

And that's what I've stuck with to this very day. I've written a number of desktop applications² and whenever I need some structured data stored persistently I reach for my good friend, SQLite.