Django, SQLite, and Production

So hello everyone, uh my name is Anje and I'm going to talk about Django C and production. And um so to start with, um at the speaker dinner uh a few days ago uh we were talking how awesome it is uh DjangoCon that talks Sort of feed off of each other. Um and that's exactly what I did because uh I watched last year's uh talk by Tom about using SQLite in production, and I got really excited. Excited about what Tom was telling us and Tom was essentially asking us to try out a new SQLite in production and uh then

let us know how it went. And that's exactly what they did. I created a site project and I put SQLite in production. I've been running this site project for the last year. I read most of the documentation in SQLite to better understand how it works. And I even wrote a few blog posts about uh SQLite and Django , how they work together, all so that I could hopefully answer this question: whether or not we should actually Actually have SQLite in production. And I'm now here to tell you all that the answer to this question is uh it depends.

Um unfortunately that's uh Is a quite a peculiar uh database. Uh, it has its strengths and weaknesses, and as an application developer, you do have to be aware of those at least a little bit to make sure that. That you make a good decision and not uh create an application that's impossible for you to scale properly. Uh so Tom Stalk did an excellent job explaining uh how SQLite works. So I won't uh go too much into the details on what he said. I've essentially uh put his whole talk in a single slide here.

Uh so essentially what SQLite is, it's a single file that acts as your database. So that means that you don't really need a DBA uh like Karen to manage that. It's on Your file system, uh, you can access it without requir without connecting to the database server through a username and password. And that of course makes it super attractive uh for us uh uh developers since I can understand how a file on my file system works so it's a lot easier and lost less daunting for me to uh work with that. But it's still ACID compliant.

It's in the public domain, so it can be embedded anywhere where you're running your code. It's heavily tested and really, really stable. It's also available anywhere, you can run it on pretty much much any machine. Uh it's primarily been used mostly in embedded devices, uh but as we'll see uh it can also work well for use cases where Django comes in so hosting uh data for your um web application. Uh so just quickly uh the side projects that I was working on is called uh fedidevs. com and it's essentially an aggregate of different Mastodon

accounts so that uh you can find people to follow that have similar interests as t as you do. So here we see a list of Django developers. So it's um And it makes it a little bit easier to to find um friends in the Fediverse. I also built a little feature so if you want to follow the conversation around conferences, uh there's An aggregated view that shows all the posts about DjangoCon Europe. So if you want to check that out as well. And Fedidevs essentially started out out as a read-only application.

I aggregated all the data on my laptop and I then put it into the SQLite file and And then what I did was I essentially just copied the SQL Lite file into a Docker image and then just deployed that Docker image. And this is essentially the benefit of Of the flexibility that uh SQLite gives you, since it's a normal file, you can really stick it anywhere that you and even into places where you probably shouldn't. So I'm not saying That this is what you should do. I'm just saying that if you want something out quickly to gather some feedback, or if you're more of a

data science person and just want to share the results of your work, uh SQL Does give you the option to share this very very easily with others. And it's still quite fast. I'm not sure if you can see the numbers on This graph, but because this is a benchmark, the numbers don't matter anyway. Uh but we'll see how these numbers change through the different benchmarks that I performed for this talk. Uh here we can see that SQL Light is just slightly faster for read-only operations than Postgres and MySQL. And I believe that is due to the fact that it doesn't have the overhead of uh

connections. Uh it can just access the system, uh the the file system directly. So after I deployed Fedidevs uh in the container, I sort of entered this honeymoon phase with SQLite, everything was going well. it with just rainbows and butterflies everywhere, uh things were fast. I was I was super happy. But then as people started to use uh the um the site project that I built People also started to ask for new features. And some of these features, of course, required me to support writes as well as just reads.

And because the way that I had deployed SQLite. This wasn't just a simple uh um change uh in the code. I now had to do a few things to make sure that uh I can actually support rights as well. And most cloud providers they offer you a way to attach a volume, so some permanent storage to your container image. And then you can put the SQLite file there and it's going to stay there between your deployments and that makes and that essentially solves this issue uh for you.

Of course this box Can only be attached to a single instance or a single uh deployment, which means that some of the fan benefits that I've had before, where I could just ship that container image pretty much in any data center. In the vault now isn't uh as easily doable. Um so yeah, I had to uh remove it from Docker um and that sorted out the deployment. Issue, but I immediately figured out that there are other problems when you introduce writes uh into your application with SQLite. And this fact is that writes are

a block Your database operations. And by default, if you don't adjust any of the settings, writes not only block other writes to the database, they also block your reads, which means that your throughput is essentially going to plummet um if you just start writing to the database or while you're reading to it. So in the previous slide we were doing like let's Say 2000 requests per second. When I introduced writes to the mix, this number plummeted to 200, 300 requests per second. And this I mean, this is a 10X decrease in performance and does

Doesn't really sound that uh appealing anymore. Like we're sort of seeing some issues in the relationship uh with me and SQLite at this point. Fortunately uh you can at least unblock reads very easily uh and you've probably uh seen uh this um configuration option uh that enables the write ahead logging uh in SQLite. Now the Only reason that this isn't the default already for SQLite itself is because SQLite like Django always wants to make sure that all the new features and changes are backwards compatible.

Uh so they never uh change the default to this new journal mode that was added um years after the initial release. Uh but it is a stable way to um resolve this um issues, at least for your reads. And as we can see for this new benchmark with uh right ahead logging enabled, uh we are back in like around one thousand requests per second, which makes it It's uh still slower than when we were doing the the re only reads, but um uh it it is usually fast enough for um certain type of uh s certain type of of applications.

Now these blocking rights have another issue that you might s encounter when you're working with SQLite and these are database And this sort of confused me quite a bit at first, mostly because so essentially you you get this database's locked error if your right operation can't uh acquire a lock in the specified amount of time, which is essentially your timeout. So if your right operation doesn't uh uh isn't able to get the lock in five seconds, which is the default, it's g to raise this databases locked error.

But um what I've noticed is that I was I kept getting this error on requests that finished in 200 milliseconds Seconds or less. So there was something else going on. And when I was debugging this, I even tried setting the timeout to an insanely high number to see if that's going to resolve the issue, but for some reason it has hadn't and I still kept getting this database is locked errors. Um and yeah, I keep g I kept getting these emails um and at this point in time My relationship with SQLite sort of entered this crisis stage.

I was considering um maybe migrating the project to Postgres. Maybe maybe um Um just s um yeah, just try out my SQL. I don't know, I it felt like there was there were issues um with how we were doing things with SQLite. of course complained about this on the internet and as it happens um usually when I'm wrong or uh confused uh the internet sets me straight and thankfully uh someone on the internet actually Told me to look at how transactions actually work in SQLite

and because that might solve uh some of the problems that I've been having. Uh so quickly uh Without going into too much details of ACID isolation levels, um I do want to point out that by default uh SQLite tries to um defer the acquisition of the lock as long as possible. This means that this is because as we saw before in the uh right benchmark, uh acquiring a lock Is expensive and it slows down your database. So SQLite tries to be helpful

and tries to do this as late as possible. So in this example here, uh we start Started the begin statement. Uh SQLite doesn't uh essentially do anything at this point. We do our first uh query uh because this isn't a write query, SQLite still doesn't acquire a lock at this point. Point. And then finally, when we get to this update statement, uh this is now where SQLite actually needs a lock. So this is where it is going to try to acquire one for you. And if there There's no other operation going on at the moment, everything's going to be well, the transaction is going to commit, and at that point the lock is going to be released.

But if there is another right happening at this very moment where we try to access the lock. Um SQLite won't be able to of course attain that lock and it won't Even be able to retry and wait until the timeout passes before raising the next option. And the reason for this is that uh SQLite has uh um the serializable isolation guarantee and if because at this point we would have to wait for another transaction to continua uh to finish, that other transaction

might change data in the off user table that we read at the beginning of the transaction and that could essentially invalidate our assumptions on what we're doing with the update statement and thus causing uh potential problems for us. So because of this, there's no retrying in this particular case and you're going to get the database's locked error in your application when this happens. Um Um the solution for this is um the begin immediate uh clause which basically tells SQLite to acquire the lock at the beginning of the transaction and then

uh the And then you don't have this problem where you've already read the data when somebody before somebody changed it, and that makes um uh makes this problem go away. But of course this makes your whole application a little bit slower because the locks are a little bit longer and that of course uh impacts performance as well. So we probably all can now see That bright heavy workloads probably aren't the best fit for SQLite because of these limitations. Like the serializable isolation um mode is not ideal for just

Django applications where we're usually used to running in read committed mode, but read committed isn't something that SQL uh that uh SQLite actually supports. it right with writes it gets even worse because you might assume that writing to one table in your database doesn't uh impact writing into other tables in the database, but in SQL Like this actually isn't the case. So if you have a big uh l table that is constantly getting writes, like an events table or something like that, um it is going to

impact writes to the auth users table or any other table in your system. And in this write-only benchmark we can see that you there is an impact um to SQL like performance due to all of this. Uh this benchmark was simply inserting a single row into a single table and even then uh it was the performance was much slower like half the performance of Postgres and sick and my SQL. Although I do have to point out that um in this particular benchmark I was writing about six hundred rows rows per second, which is still quite

quite a lot for most applications. So even though it is slower, measurably slower, it might still be plenty for the use case of your Django application. Now Now of course, not for every company, not for every size of load, but for a lot of Django projects, uh this is probably going to be fast enough. And if you need just a little bit more speed, out of your rights, you do have an option to split your database into multiple smaller uh database files. So like if the events table that I mentioned, an option for you is always to split that into its own database server, uh

database file, and um then the rights uh are going to be uh unblocked from your other tables. Um so this is also an option. That you can use. Now, the main question on everybody's mind right now is probably how to go beyond a single server. So a SQLite doesn't have any support. Support for networking or managing connections, which of course makes it a lot simpler to deal with, but when you do have to scale horizontally, things get a little bit more tricky. One solution for this is to use LiteFS, um

but even then uh I would say that for right heavy applications it might not be a good fit since All the rights still have to go to a single database server with um or your single application server that runs your primary um database and you're going to have all the same problems That we had while running in on a single server. And of course, as also Karen mentioned, backups are super important when you're run A database in production, and since SQLite is just a single file on your system, you might be tempted

to just copy paste it and make a backup that way. But that is Essentially not safe you have if you have active connections on the database and you might end up with a corrupt backup file. So when doing backups, always make sure to use the dot backup command uh inside SQLite and that's going to make a full backup for you uh safely. Uh full backups can sometimes take a long time, especially if your data database size has grown quite a lot. Uh so there is also an option of online backups uh and that's uh a tool that you can use called Lightstream uh that can essentially

stream data coming into your SQLite database uh to some other server or S3 or anywhere else that is safe. So finally after all of these struggles and learning on how SQLite works, we have finally enter this partnership stage of the relationship where I now think I understand uh how SQLite thinks, how it works and we're now in this healthy relationship. Um And my goal uh was to also make sure make sure to help other people trying to be in a relationship with Sick

for Light. Uh so um I wrote a few blog posts on all of this and um I've also helped make some small contributions to Django itself to make it a little bit easier to to deal with. So Coming in Django five point one, uh there's a new transaction mode option uh for you to um be able to configure immediate transactions uh to for your whole application and that um so that you don't have to deal with databases lock terrors the same way that I do

uh that I did. Um and if If you're not on not able to upgrade to five point one uh immediately, uh the other option is to you have to um basically uh subclass the SQLite backend and then overwrite one of the methods there. Uh you we can talk a little bit about this afterwards if you're interested on how to get it to work. And this was actually my first contribution to Django itself. Uh so thank you. So hopefully this is going to be the first of many, so I'm I was really excited to get this uh change merged into Django itself.

Uh the The other thing that was added in five point one was the init command that you can use to configure uh certain uh properties or settings for SQLite so that you can make it a little bit more performant. So these settings are uh uh uh I basically borrowed them from the Ruby community that is uh currently going really headfirst into SQLite for pretty much everything. uh and there's uh this blog post that I have linked there that goes into a lot of details on how what these uh settings do and why they might be um really appropriate for you uh for

the Use case of web applications. And this brings us to future improvements. So ideally, I would say that you, as the application developer, shouldn't really have to know the intricate Of using um deferred uh transactions and all these configuration options. Uh so I do believe that Django should help Help you or at least guide you to get some good defaults out of the box. Now unfortunately changing the defaults is difficult because or we usually don't want to do that because

we don't want to break any existing application. But maybe there's other things that we can do. Perhaps the start project template would configure SQLite in a way that's a little bit more appropriate for web application use cases instead of more the embedded world that is the default for SQLite. And I'm planning to start a discussion around this after the five point one release and when we see uh how that uh goes. And there's always uh things that we can do to improve the documentation around all of this, so that's also on my mind. Unfortunately there's

a lot more topics uh about SQLite that I wanted uh to cover but just didn't have enough time. So to circle back to the beginning of my talk when I said that I was inspired by a talk from Django Con last year. So at least me personally I would love to see more talks about SQLite, about all of these topics that I have listed here. Like did you know that you can even have full text search in SQLite? I think that's mind-blowing. So there's there is definitely a lot more content here. And like the the main benefit uh of using Using SQLite, like

for a lot of use cases, the database is fast enough and reliable enough for us to consider it, and it does simplify the maintenance and the um the way that we developers uh interact with the database. So um I think it is it has its place um in the Django community, even in uh production environments. Uh so that's it. Um if you have questions