Upgrading Magic's SQLite VSS Extension

Already back in 2021 I made the decision to make SQLite Magic's default database driver. This resulted in a lot of benefits such as for instance.

Simpler deployments
Less latency
Easier maintenance
Plus a bajillion additional advantages ...

Basically, where Supabase gives you one PostgreSQL database, we will give you a million SQLite databases. SQLite of course is "a bajillion" times faster than PostgreSQL, especially on small and medium sized databases. Among other things because there's no networking to reach the DB, but instead just a simple file IO. It also simplifies deployments of Magic Cloud for us, to the point where we can just deploy a cloudlet, and allow our users to create as many file based databases as they want to, while initialising the cloudlet's database simply implies creating a new file and executing some DDL. SQLite is also much more scalable than you think. SQLite can actually deal with millions of records if used correctly, and backups are a simple file copying operation, so it's arguably much more secure due to its simplicity allowing you to build scalable and secure solutions, with any number of backup routines you wish - We're using 3 different backups may I add.

However, using SQLite also created a problem for me, which is that I needed an SQLite extension that could somehow query its data using Vector Similarity Search (VSS). VSS search is what allows us to use natural language to match prompts specified by our users towards the RAG database. For our AI chatbots, this is a crucial feature, and we wouldn't be able to deliver neither AI chatbots nor AI agents if we didn't fix this problem somehow - So it could be argued that having access to a high quality VSS extension for SQLite was at "the heart" of our software. And since I'd rather eat garbage before I subscribe to Pinecone or something similar, well here we are ...

DIY

I never really found an extension solving the above problem. Or to be exact, I found an extension but it was full of memory leaks, and it was based upon FAISS, which resulted in that it would load the entire index into memory on a per connection basis, for every single database invocation that required VSS search. I spent 3 weeks plugging most of the memory leaks, but when I pushed the author didn't want my pull request, so I ended up forking the library in my local GitHug profile, and "roll my own" based upon its original code.

However, for 2.5 years this was a pain point in Magic, and until just a week ago in fact it was still a pain point. Basically, the VSS extension I was using would use potentially gigabytes of memory if we had a RAG database of 20,000+ records. You can still see the effects of this technological restriction in our prices, and how we count "snippets" in our different plans. This is because up to 10,000 training snippets the old library "kind of works" with the amount of memory we assign to each cloudlet. And on the Enterprise plan we've got just enough memory on each individual cloudlet to serve up to a maximum of 30,000 RAG records. However, anything beyond that is simply impossible for us to deliver. Basically, at around 40,000 RAG records, the internals of our system would literally break down and stop working, and the cloudlet (server) would simply crash and go into a never ending reboot cycle until I assigned more memory to it.

SQLite vector

A week agoe I discovered SQLite vector. My immediate reaction was as follows ...

Good God, this is the exact thing I've been waiting for now for more than 2 years

It took me roughly 5 to 10 hours of coding to completely replace the entire vector library in magic with the new lib, and I had 3/4 questions during the process - All of which Marco Bambini would spend no more than a couple of hours fixing for me. You can see the discussion between yours truly and Marco here if you're interested.

Marco fixed all problems I had, and in less than 10 hours of actual coding, I had a 100% perfectly working solution, with zero memory leaks, that by default would never use more than 30MB of memory per connection. The reason this works, is because instead of having an index based upon FAISS, and loading it in its entirety into memory, Marco's SQLite Vector library will match records directly from disc, only loading a subset of the records into memory to calculate distances.

The bad news is that for very large datasets, the database lookup invocation would be slower - And the time requirements to match VSS RAG data would grow linearly according to the number of records in the table. However, for a database with 30,000 records, we're still talking about 400 milliseconds on an average modern machine, so the trade-off is definitely worth it.

TL;TR - Basically, SQLite Vector was the library I had wanted to build myself for 2.5 years, and it was just an amazing piece of technology, with extremely high quality code

Magic quality

Our plan is to upgrade all cloudlets the upcoming weekend. Once we do, the only restrictions we're left with related to training data, is the amount of storage or "hard disk" we assign to each individual cloudlet. Implying if your cloudlet has hundreds of gigabytes of hard disk, and you're willing to wait a couple of seconds to match RAG data, you could in theory have RAG databases with hundreds of thousands of records - Possibly also in the millions if we turn on quantisation for your database - And still have good performance without crashing your cloudlet due to it running out of memory.

Basically, that little thing makes Magic's core 100 times more "scalable" and "stable".

Incredibly skilled dev heads

I've got 43 years of experience, and I'm still the most productive developer in Cyprus according to GitHub. Most people would say that's a good thing. The problem is that whenever I see code created by others, I typically want to vommit - And unless some open source lib has astoundingly high code quality, I'll just leave it and either implement something myself to solve the same problem, or simply ignore the problem for the time being. Basically ...

I cannot tolerate other people's code!

99% of software developers simply have no idea about how to create good high quality code, with rhythm and taste, poetry for the brain, easily maintained and understood. This is why I don't allow anybody but myself to even touch Magic's code. And if you're now asking me "WTF is 'rhytm' in code"? OK, then we know why I can't use your code I guess. Sorry about that, but it is what it is, and life is simply too short to use code that doesn't look like it was created by Mozart ...

Psst, Marco's code had rhythm!

Wrapping up

There are two reasons why I wanted to write this blog. The first reason is because I wanted to give you good news about Magic. It's getting better, a lot better too in fact. And this particular change is one of those things you can't really see, but over time I guarantee you that you will "feel" it. Less restrictions, more flexibility in the product, less places where we have to say "I am sorry but we can't do that", etc, etc, etc.

The second reason is because I wanted to say something nice about Marco. He solved all my problems related to the upgrading of the SQLite extension for me without ever asking me for anything in return. So he deserves nice words, and credit for his work. If you want to check up what Marco does, you can check up his startup below. The SQLite vector plugin is not the only thing he does. Among other things, he's got an amazing little piece of technology that allows you to synchronise your locally installed SQLite db towards the cloud, to provide "offline first" database access from your phone. The thing is easily installed as an SQLite extension, just like his vector library - And it automatically synchronises the phone's database to the cloud when having internet access again, allowing you to work offline with database heavy "apps", yet still have a centralised single source of truth for your database.

The above is literally such a smart little piece of technology, that I believe 100% of every single "app developer" on earth, interested in offline access, should consider using it. For us delivering backends and servers, this thing is not interesting - But I suspect it would be extremely interesting for most others.

You can find Marco below.

His company is particularly interesting, based upon the philosophy of that you should be able to solve all "business problems" from within SQLite as a "platform for software development". I don't agree with that vision, since I believe in Hyperlambda being the glue between the database and exposing Hyperlambda endpoints to the client - But I can see how such a vision could significantly simplify deployments of complex backends, compared to the standard solutions others are applying today, such as creating backends in Python, C#, Java, or GoLang - Which I consider to be an abomination may I add ...

... so you might want to check up what SQLite.AI is doing. This is especially true if you're using SQLite, on a phone. Thx for reading ... 😊