Seriously SQLite

00:00 All right. So the one thing I don't know is JavaScript.

00:18 And if you're wondering why the hell am I here, just look at me, it wasn't for my looks that Kent invited me, but I promise that I'll do my best. Is the timer on? Just so I know, because it says one here, and I haven't spoken for, yeah, zero. OK. Ready, set, go. All right. So I'm Glauber Costa.

00:40 I am the founder of Turso. Turso is a serverless database based on SQLite with a mission of making running production applications in SQLite an epic experience. And I have the challenge now, and in the next five minutes, explaining to you why you want to potentially build your

00:59 next application on SQLite. I also want to get this one here. OK. So the reason people sometimes think I wouldn't really build my application on SQLite is because, as the name implies, it's light, maybe it won't scale, maybe it won't do the things that I need it to do. So the first thing I want to do today is walk you through some of those limits of SQLite. So yeah, it's a small database.

01:18 But what does it mean to be a small database? Maximum size of a value string or blob on SQLite, a gigabyte, maximum numbers of columns in a row, 2,000 columns, maximum number of tables in a join, 64, maximum numbers of parameters in a query, 999. I actually do not understand why not 1,000.

01:37 But be it as you may, usually enough for everything you want to do. In terms of size, maximum number of tables you can put in a database, 2 billion. If you have more than that, please let me know. And the maximum database size, 281 terabytes. Now, I fully appreciate the size is not everything.

01:55 Actually, I did not intend that joke. What I wanted to say is that in practice, when you're running something in production, just because it can create a big file, it doesn't mean it's going to perform well. So I brought you a benchmark that I took from the Expensify blog, they do run SQLite in production. And the funny thing is that this benchmark was not even

02:14 designed to test SQLite. It was designed to test their SQLite workload on AWS versus Query Metal for a similar price point. AWS lost. That's the yellow line over there that we're going to ignore. But if we look at the blue line, and we only have the yellow line there because I suck at editing, so I want to remove it, fail to do that, so whatever.

02:32 So the blue line is a database with half a terabyte in size. And the red line is a database with 1 and 1 half terabyte in size. And the number of queries per second there is like 4 million, so 4 million queries per second. Again, should be enough for most use cases out there. And you don't see any performance degradation

02:52 between those things. So in practice, I can guarantee you this database could be 10 times larger. It wouldn't cause any problems whatsoever. Writes is where the rubber meets the road, is where some of those problems start to show. SQLite infamously has a single writer design. But people mistakenly believe that what that means is that you cannot write concurrently to SQLite.

03:11 But that's not true. You can't write concurrently. Writes are just going to queue. In practice, what that means is that your write throughput is, in fact, limited. But for example, if we very conservatively assume that every write takes a millisecond, in practice, it tends to take a lot less because you're just writing to a local file most of the time.

03:30 You can still do 1,000 requests per second, and many thousands if it takes less than that. Yes, this is a limit that you're more likely to hit. So there are many workloads there for which this will not be enough. But in practice, it should be enough for most workloads.

03:47 Now, running a database is not only about how many reads and how many writes you can do. There is also operational aspects of that, backups and scaling out and all stuff like that. So let's look at some of those things. This is, by the way, one area where the single file design of SQLite, full of advantages, can become a disadvantage.

04:06 But there are, thankfully, a lot of tools that you can use to overcome some of those problems. In terms of incremental backups, you do want to do incremental backups. I mean, backing up a file is fairly easy. But if you want to be backing up a large file all the time by copying the file, it doesn't work. So you want to do incremental backups. And there's a tool called Lightstream that can do that for you.

04:24 And Turso also does it out of the box. And it's completely open source. So you can use that. And when you're doing that, you're only backing up the changes to the file. There is also, how do I do this from serverless? Is it even possible to use it from serverless? Serverless environments do not have a file system. So how do I do this?

04:42 Once more, Turso ships with an HTTP transport. So you can add this HTTP transport around your file. Now you're connecting to your file over the network. You can use that from serverless environments. And Cloudflare also has a very similar offering called Cloudflare D1 that works from the workers' environments.

04:58 Finally, what if I want to do horizontal scaling? And essentially, I have many servers. And I want to connect to the same database from all of those servers, the files in one of those servers that doesn't work. LightFS is a solution for that. They understand you can't use this profusely in the Epic stack.

05:16 And it allows you to essentially stream changes from one replica to the others. And then you can use the same file or a replica of the file in many servers. Turso does that as well, first from its HTTP-based nature, and also because we can also replicate. I did it pretty well. I have seven seconds. And in the five seconds that I have left, in the four

05:34 seconds that I have left, I will tell you that if you want to use SQLite, you don't necessarily have to use Turso. Of course, we would love for you to do so. And if you decide to do so, you can get 10% in any of our plans, actually scale it up with turso.tech slash epic. And there's an example about the, man, I got a bonus.

05:54 I didn't get a bonus, right? OK. The links are there. So you go to our GitHub as well. You're going to see an example. Thank you very much. I hope I managed to convince you in five minutes. Good luck.