Use SQLite in production

Speaker 1: Thank you everybody. I'm Tom, Tom Dyson. My pronouns are he, him. I'm uh I was one of the founders of Torchbox, one which I think is maybe one of the biggest Django teams in the UK. And uh I'm very proud to be the colleague of Esther who just gave a wonderful talk. If you would like to be Esther's colleague, Torchbox is hiring. We're also the company behind Wagtail, the open source Python CMS. But this talk is about my favorite database, SQL Lite. I'm going to try to remember to call it SQL Lite, not SQLite, but I might flip between them. I actually um I wrote the uh proposal for this talk before I'd done the research on it and um and so I have had a nervous

Speaker 1: couple of weeks uh uh just wondering, worrying that uh actually S S SQL that really isn't suitable to be used in production, but I I'm I'm I'm relieved that uh in most cases having done the research my hunch has been backed up. So uh I'm I'm going for it. So you might you might recognize some of these lines. Anyone recognize these lines? Yes, Carlton. So uh they're from the Django docs. I I've been ignoring them for a few years And and ignoring them is something that uh that I used to feel guilty about, like um like debugging using print or uh or not writing tests. But gradually over the years I have learnt not to feel ashamed about using SQL Lite.

Speaker 1: Initially thanks to Simon Willison and his inspiring work on dataset. But then on from my own research into the misconceptions about this database. So the short-term goal of this talk is that you won't feel guilty or ashamed about using SQLite either. And the medium-term goal is to change the official position of the Django community. So just leading on from the last the last paragraph of these docs, I want to be clear that I love Postgres. I think Postgres is a miracle of modern software. It's a shining beacon in the world of open source. It gets better and faster every year. There's no dramas, no hype. Postgres has overtaken corporate megaliths

Speaker 1: like Oracle. It runs on a Raspberry Pi or a megacluster. I'd like to imagine the authors of Postgres Just calmly observing the rise of uh web scale next generation databases like MongoDB, and then quietly adding JSON support and becoming the best option for document storage. Torchbox adopted Postgres about 20 years ago at a time when MySQL was in its ascendancy. It was by more by luck than judgment, really, but we've never regretted it. And yet, and yet there may be times when SQLite is a better alternative. Here is a question whose answer may surprise you. And some of you have helped me answer it. I asked people on Discord to

Speaker 1: run this bash script, which ChatGPT wrote for me, and to report back with the numbers. If you have a laptop open now and you can read this on the screen, then and you have uh five minutes, then you could try it The results that have been sent in range between 800 and 8,963. Shout out to Bartek G. Bartek, are you in the audience? Uh Bartek had the 8,000 result. And that's because SQL Lite is used in many of our desktop apps like Dropbox and Spotify. And also your browsers and your browser plugins and bits of the operating system itself. But it's also used by the apps on your phone and on your smartwatches, although these are harder to count. So

Speaker 1: we can calculate a little bit. So uh the average number I think is roughly about 2,000 for the people who who answered. Um so 2,000 SQLite by SQLite databases per laptop. I think about 500 per phone from the from the results from the research I've done, maybe fifty per watch. And now I'm imagining that of all the people here, almost all of you have have laptops. Pretty m I would say all of you has a phone. Maybe one in ten has a has a smartwatch. So then the the the total number of SQL light databases at this conference, roughly 350 people So that is a lot of little databases just quietly hanging out there waiting for you to ask them questions. It's really staying calm. And although actually when I say little databases, as a point of clarification, SQLite

Speaker 1: databases don't have to be digital. Does anyone know the maximum size of an SQLite database? It's two hundred and eighty one terabytes. So uh I I dow many people have pushed that limit So, SQLite is everywhere. And uh according to the official website, it's only second to libz, the C library for compression and decompression. So I think we can say that SQLite is in production. But apart from being one of the world's most widely used pieces of software, why would we use it? It's also famous for its test suite. I thought with Wagtail we were doing pretty well. I asked my colleague Matthew to to crunch him numbers from me earlier. We for every two lines of code with Wagtail, we have just over one line of tests.

Speaker 1: But for SQL Lite's 155,000 lines of code, they have an extraordinary 92 million lines of tests. That's a ratio of 1 to 570. It's legendary in the world of software engineering. It's so huge that they have to have many slices of their tests so that the basic ones can be run more quickly. Okay, so apart from SQLite being the most widely used and rigorously tested database in production, why would we use it? I think simplicity is the reason that I have continued using SQL Lite, despite the official warnings from the Django docs. And on a more philosophical point, I feel like simplicity is a value that software developers can lose sight of.

Speaker 1: Because perhaps making thing making complicated things is fun. And maybe because we are unintentional gatekeepers of the complicated things that we've had to learn over the years. As an example of this, maybe you in this room, the the many Postgres experts here Maybe you know off by heart the commands to do all these things in Postgres. I have done them hundreds or maybe thousands of times, but I would still look it up now. On the other hand, if I have to carry out these actions for an SQLite database, I know the commands already. I can deploy with SCP, I can delete it with RM, I can rename it with MV. Sharp-eyed SQLite

Speaker 1: users may be thinking May may have spotted some flaws in this in particular CP if you or SQLite databases under active rights And is very busily used, then copy isn't completely s failsafe. And in that case, you should use the dot backup command, which waits for writes to finish before it does the copy. But generally, I think you you see the point that because SQLite databases are contained in a single file, we all know how to work with them. I know it's a bad presentation practice to put up a wall of text, but there's so much that's relevant in this blog post by Ben Johnson that it it was quicker for me to point you at his words than to make up new ones.

Speaker 1: I just want to highlight the parts here that all the wonderful features that Postgres includes make it more difficult to understand the software that you're running. and and and create more documentation to to be comprehended. And while SQL Lite has a small subset of the features, in many cases that subset is everything that you need. Incidentally, Ben Johnson, the author of this post, is also the author of Lightstream and Lightfs, which I'm going to come back to later. The next thing I want to talk about is vectors. Vector databases are the vectors are vector support is the hot thing in databases right now. And vectors are important because

Speaker 1: of large language models and because large language models have small context windows. So that means that when you ask a question to JATGPT or one of the other large language models, there's a maximum number of tokens, which is roughly like one and a quarter words per token. No, I want to go to tokens per word. There's a number of tokens they can they can take in. So you can't easily give a large language model an entire code base or a huge book. And ask it to answer questions about it. So the solution to this, uh, which is kind of rapidly becoming a standard approach, is to chunk up your content, split it up. and to put it into a database and then to to run similarity searches on those chunks.

Speaker 1: So you you ask your question, then you find out the the top the top five most similar chunks to your question and then you supply the large language model with those chunks. We don't need to get into background of that now, but the point is that vectors, so this this way of uh storing the numbers, the embeddings of which are representations of words. turns out to be something that's really important in this new world of working with large language models and that uh natively most most database engines don't work with them well. So we have new products like Pinecone Weavate My prediction for what it's worth is that Postgres again will hoover up vector use cases. Um and maybe options like Pinecone and WeV8

Speaker 1: will will fall by the wayside but but right now SQL Lite is also a solid option for this. The way I uh initially started playing with with large language models again through Simon Willison was his dataset FICE F-A-F-I-A-F-A-I-S -S library, which is I recommend is the simplest possible way of of um of doing similarity search uh with vectors. And um since then there's a new SQLite tool, SQLite -VSS, for vector search, similarity search probably, uh which does the same. So these are really good options for handling vectors. Okay, moving on from something really small to really big. Climate change, I'm not gonna preface this with in my opinion, climate change is the biggest crisis that has ever faced humanity.

Speaker 1: And uh and while well you know uh thinking about that crisis and then comparing our database options in Django feels absurd. I do think that Climate change should factor into every decision we make. This is me at uh DjangoCon Europe in 2017 in Florence, a city almost as beautiful as Edinburgh, where where I gave a talk on serverlessness. I uh I predicted that we would move, as a community, we would move to the serverless model for hosting our Django applications. for reasons of efficiency, cost, and climate, which are all directly related. Six years later, I think that prediction is looking pretty shaky.

Speaker 1: Maybe a few of you are using tools like Google Cloud Run or AWS Lambda. Any hands up for serverless users? Two, three, four, five? Pretty small proportion. It's not the dominant model. My prediction was wrong. I imagine that's partly because serverless databases are hard So uh if if your app is running a database with probably 99% of the things that we build in Django are, then then the serverless model only gets you so far. There is an interesting approach from a new platform called Neon, who are doing some really interesting things with serverless Postgres. But essentially this is a hard problem because Cold starts are less accessible, uh less acceptable in a in an environment where you need to hire good latency.

Speaker 1: On the other hand, SQLite is the original serverless database. I wrote this this line. This is a line going example of a line that I wrote before having done the research, just as a joke really, but then I thought I should look it up And uh turns out to be true and actually more true than than I could have hoped for. Uh I looked in Fossil, which is the source control tool that SQLite uses. And it was in 2007 that they published their page, serverless. I don't think serverless was even a term in 2007. And uh anyway, and and and I know I know this word serverless is something that people feel uncomfortable about because Of course they're always servers, they're just kind of managed by somebody else. In this case, I think SQLite truly is serverless. Just to kind of to bring this point home, you could think of a traditional relational database server as a school or a university.

Speaker 1: Edinburgh University here, with teachers, support staff, heating lights, all ready and waiting to help you. And an SQLite database is just more like a book sitting on your table, just taking up a bit of space, waiting for you to open it and find stuff out. I know this analogy breaks down pretty quickly, but it's an important point. Imagine if all the 802 ,000 SQLite databases in this room All needed a few watts of energy each. We'd need a we'd need another power station to run DjangoCon. Do you, in your apps, do you have universities that you can replace with books? Okay, so I know what you're thinking. We get it. So SQLite, it may be the world's most popular and rigorously tested software.

Speaker 1: Okay, it's wonderfully simple to use, it's already built into Python, it can handle vectors, serverless databases, will save the planet, yada yada. But when is he gonna get to the good stuff? Okay, I hear you. SQLite is really, really fast. I don't really understand the ways that The underlying query engine is fast compared to alternatives. The bit I understand is that because it's embedded, there's no latency So when you make a query to a traditional database, you have to serialize your query, you have to choose the right protocol, send it across the wire, unserialize the other end, backwards and forwards. Computers are really fast and networks are fast.

Speaker 1: But there's a little latency there. And then if your application server and your database server aren't are in the same building or the same data center, then you have the pesky speed of light problems to deal with. All of that goes away with SQLite. And the result is that simple select queries, the the type that fit in memory, tend to be measured in microseconds, not milliseconds. Sometimes it feels like it hasn't worked because you You hit the button and it and it re it replies so quickly you think there must have been a mistake. And this this leads to some interesting behavior changes in the way that you write software. So A kind of classic thing that we uh as as Django developers worry about is N plus one queries. So it's something that's really easy, an easy to

Speaker 1: mistake to make when you're using an ORM. And um, you know, maybe that one of the first things you do when you think your application is running slowly is turn on Django Debug toolbar, look for your queries, see which bits might be creating N plus one queries, drop in the right, select related, see the query count fall. That's just it's not really a problem in SQLite. For lots of little queries, the performance may even be better. In some cases, the recommendation is to actually refactor your app so that it runs lots of small queries. And sometimes this is feels a bit sort of heretical, but sometimes that can that can lead to a better approach. It can mean that your your your kind of decoupling or you're you're correctly containing the um the

Speaker 1: the logic. So you don't have to think about combining all your queries to to create the page. You can just think about the queries that are generated for each part. The first time that I think I really experienced this sort of viscerally was when my colleague Tom Usher put up a demo of something called bakery demo. This is uh like a uh a a standard wagtail instance with a few bits of content in that we use for testing. And um he ran it on fly. io, but it could have been anywhere with with SQLite in the background. And it just felt different in a way that I hadn't experienced before. So this is this was not a little scientific test, but it's just something it was just immediately noticeable. And um Tom's figures on it were uh were actually sort of less striking than I expected.

Speaker 1: 85 milliseconds rather than 150 were with Postgres. So you know 150 is really fast and plenty fast enough. But SQLite on the same pretty classic Django application just felt different. Okay, so now let's get into the tricky things. What about concurrent rights? This is this is the first thing that people always worry about. This is w this is why SQLite's not going to be appropriate for my database. Uh well since twenty ten this probably hasn't been the problem that you think it is. Uh there's there's a command, this pragma command Where you turn on right-ahead logging. This is um uh a different way of journaling, the non-standard way. And and there's kind of quite a uh a strong opinion that this should be the standard approach in in SQL

Speaker 1: Lite. Interestingly, although it looks like something you might apply as a connection string, it's more like a property of the database. So you do sort of apply it as a connection string, but it's persistent. So once you've applied it, then uh it then then then it stays there. So one way of running it might be as part of your your Docker build. And once it's in there, it's always going to be it's always going to have the write-ahead logging turned on. So generally you just turn it on. That's that's kind of top line advice if you remember one thing, journal mode equals well. Um I had an interesting chat with Will from Colo at lunchtime who who had an issue with concurrent rights, even using this mode. And and that kind of that that's in line with the bad point here. So good, yes, it's faster in in most scenarios.

Speaker 1: Um and importantly you can read and write concurrently. bad while doesn't currently work over a network file system. And um I I I sounds like the issue that Will had was to do with uh the way that Docker mounts and it was processes within and without the container. Something to be aware of If you want to speed up rights even more, you can reduce the synchronous level. The default is two if you uh if you go to level one. then SQLite database still syncs, but just at at the most critical moments. In while modes, then it's recommended that you always choose one. However, you probably don't even need any of these optimizations. In my tests, keeping strict mode turned on and uh with no right-a-head logging

Speaker 1: I can update 50 wagtail pages per second using a single Unicorn worker. And with a single worker, I don't even have to worry about concurrent writes because Unicorn will just queue them up for me. 50 requests a second is not an impressive number. But of all the massive Wagtail instances that I know about, like the main NHS website, Google, NASA We have never seen traffic like that. We've never had anyone creating updating 50 Wagtail pages a second Okay, here's another one. What about the weak typing? On the whole, for me this hasn't been an issue. The ORM will handle it. But if you have other processes writing to the database, so I should have said upfront, so uh the the typing is very loose in SQL Lite.

Speaker 1: Um so uh so it could be a legitimate concern about data integrity. If you have other processes writing to the database, then uh then this could be a concern. And again, there's an answer to this. Use strict. Strict, unlike the uh the pragma commands, is a is a an attribute of a table. So um I like this slightly sort of Snooty comment from the version notes from November for developers who prefer that kind of thing. This is how you enable strict mode. You just put it at the at the end of your table definition All right, moving on. What about point-in-time recovery? So this is a legitimate concern again if you are running a database on your data center.

Speaker 1: Burns down overnight and uh there's been a lot of data since your last snapshot twenty-four hours ago, then then you're in trouble This I think has been like probably the you know maybe the most significant weakness for using SQLite in a in a sort of industrial setting up to now. But there is now a solution. So there's uh this guy Ben Johnson I mentioned before, he wrote a key value database called Bolt, uh which works for Go. He now wro works at Fly. io, working full-time on SQLite stuff. And his his project Lightstream cheekily updates this is the SQLite strap line, he updates it from this to small files reliably globally distributed, choose only four. And the way SQL Lite works is described in this diagram by Michael Lynch. uh in his post

Speaker 1: how lightstream eliminated my database server for three cents a month. It's a s it's a tiny bit of software written in Go that listens to that uses the right head logging in fact to listen to changes and push them up to S3. It's a tiny cost and just sort of solves this problem. For a more enterprise example than Michael Lynch's blog post, Tail Scale. who you probably know, this sort of really amazing next gen kind of VPN type provider. As I understand it, they run their main database now on SQL Lite. using Lightstream. I I you will notice the date of this blog post, but uh they do they promise at the bottom of it that it is not an April Fool. SQL Lite is the solution that they are now using. All right, okay, so we're we're we're pushing on.

Speaker 1: What about horizontal scaling? Um what happens when I need to to to just uh my use case gets so big, my traffic gets so large that that that one server, one instance isn't enough. So first up, you know, my y answer to this is usually Yagney. You you you ain't gonna need it Probably. Computers are fast. And it turns out that SQL Lite scales vertically very well Expensify this blog post here. They have this famous blog post where they show four million requests per second into their SQL load database. Um Simon Willison in his load testing gets more like 500 requests per second. In my test before I said 50, which is still enough for me. So you're probably not gonna need it. But if you do, then again, Michael Johnson comes to the rescue.

Speaker 1: uh with his Lite FS project. This is in beta at the moment and it's a bit more complicated than the Lightstream. And essentially it's like a it's like a sort of replacement file system that that handles replication for you. I'm not gonna read out what it does because you can you can see it here. My colleague Tom Usher, the one who who showed the really fast example, uh has written a good case study of how to do this and which is a much more kind of practical level using Django and if you're interested in LiteFS this is the resource that I recommend. So I'm I'm coming to the end now and and my request really is that if any of this sounds reasonable uh that uh and that you're interested that the the the the simplicity of SQLite appeals to you.

Speaker 1: that that you help by by continuing the work that Simon started at the the DjangoCon, the last DjangoCon in the US um uh by load testing and and sharing the results. Uh I've also because like everyone I suffer from not invented here syndrome I've made my own load test which you could try as well but I think uh Simons is probably the best place to start And finally, if you don't take anything else away, journal mode equals well. That's my top tip. And I hope that you will all join me in embracing SQLite as a production database of the future.

Speaker 2: Thank you, Tom. That was a great talk. Any questions? We have time.

Speaker 3: Yep. Hi, thanks. That was wonderful. And uh we use SQLite at work for in production as well for a few things. One other thing we've been struggling with recently is that depending on developer machines like Windows, Mac, Linux, depending on Linux environments How do you make sure people use the same SQLite version? Because then in the deployed environment it's yet another one. And that's been yeah a bit of an issue on our side

Speaker 1: Yeah, thanks for the question. I um I don't know the canonical answer to this and it's something that I've been wondering myself through making this talk. It seems to me that the safest way to doing it is just is specifying the Python version very carefully. So the the the the SQLite 3 library in Python um with each Python upgrade will target the the later version of SQLite seems to seems to be pretty current. So it seems to me that 311 is targeting the latest version of uh of SQLite even though that's only a few months old. But yeah, my understanding is that the the way to solve that problem is to be as precise as possible about your Python version. Interested to hear if anyone else has any alternative experience to that.

Speaker 4: Hello, thanks for your talk. I thought the part where you're talking about vector databases was really interesting. And um specifically used this historical example where SQLite was integrating JSON from MongoDB. Can you speak a little bit more about that kind of historical example and why we think that vector databases ultimately won't be necessary compared to Postgres or SQL?

Speaker 1: Sure. So actually my um uh SQLite has has definitely adopted JSON and has vector support through those plugins, but actually but it was really um Postgres initially that when I was talking about it being the sort of m beacon in open source that uh that supported JSON and document storage in the way that uh tools like Mongo had. So I I I can't speak for how the open source communities behind SQLite and Postgres work, but it does seem to me, particularly with Postgres, that they take this quite calm approach to observing what's new and then and then adopt it. And and one one of the ways is through plugins. So actually I was involved a long time ago in the early 2000s in an XML plugin for Postgres. And um uh

Speaker 1: and so that was available as a third party, third-party plugin and then two two versions later, Postgres. had their own actually much better XML plugin that that worked in a similar way, which mean meant you didn't do the didn't need the plugins. And it so I I imagine that's one of the way that that Postgres works and similarly with SQL Lite. So Uh SQLite plugins work in quite a uh an unusual way where you can you you just have to point to the file at the top of the query which means that they're quite easy to add. As I understand it, the SQLite open source community is quite unusual because although it's open source You can't really submit pull requests. The author uh the author basically just likes to make the decisions himself Um

Speaker 1: so yeah, I I guess it's not a very good answer because I I I I don't know how that's going to change, but from observing how it's happened in the future, it's been uh what seems to me quite a kind of calm evaluation of the environment and then a decision to to adopt.

Speaker 4: Thank you.

Speaker 2: Let's have one last question.

Speaker 5: Thank you. It was a great presentation. To be honest, I didn't use SQL Lite for the last 10 years or so. We are stuck with PostgreSQL SQL So I was wondering uh how is Django's support for SQL light? As far as I remember, for example, this thing function wasn't working because of the limitations of the SQL. Are there any change on that side or what changes can be made to make it more pluggable.

Speaker 1: Thanks for the question. I'm I don't pray I don't have a good answer on this but but others may know. My impression is that uh that the basic ORM features are consistent across SQLite and Yeah, I'm getting some encouraging nods. Yeah. So I I don't think there are any limitations for the for the basic features now.

Speaker 5: Okay, thank you.

Speaker 2: Well, Tom, thank you again. Let's give him a big applause.