Limits of SQLite

[00:00:00]
>> Brian Holt: I just want to throw some notes in about the limits. Some people think that SQLite is more limited than it is, and I just wanted to kind of disabuse you of that notion it's not, right? Max row size is a gigabyte, about right, give or take some if you're doing anything remotely close to this.

[00:00:21]
What's wrong with you and what's wrong with what you're doing? That's my question for you. If you had anything that big, just put it in like a bucket, right? Like an honest three or something like that. I would not store anything of that nature, of in size, in an SQL unless I'm like, some embedded use case where I didn't have another way to do it, but it just all of it seems terrible to me.

[00:00:45]
Doesn't seem like it's intended use case. You can have 2000 columns by default on a table, and if you actually go in and compile the source yourself and kind of remove some of the checks, you can get up to 32,000 columns. Again, I post to you what is wrong with you, who hurt you, and why you like this, right?

[00:01:09]
At that point, you need JSON or a different database. If you have 2,000 columns in your database, you really wanted Mongo to start with. I'm just gonna throw that out there, I think, right?
>> Brian Holt: So we'll talk about JSON here in a little bit. But, yeah, a document based database would be better for that.

[00:01:29]
But, I mean, what is comforting is that the limit is so high that you have approached and ran past insanity so far behind you. That there should never be any sort of limitation on what you're doing. You will notice some theme here, like some of these you have to recompile SQLite yourself from source and remove the checks in the in the compilation step to allow it to reach these heights.

[00:01:58]
I suggest that you never do that ever. It just doesn't make any sense to me. A query can be, what is that, a billion characters? Yeah, a billion characters. And if you can write a query that long, you have successfully written like a turing complete SQL statement. Why?

[00:02:21]
Right? Write us several more queries, I feel like you're rendering whole UIs in in SQL. I'm impressed and upset. Tables and adjoin, 64. This is the only one I could fathom myself possibly ever reaching, right? Still 64 tables and adjoin them together, I'm gonna assert you probably could've made some of those the same table, nut possibilities, I don't know.

[00:02:45]
Maybe we're like joining atoms to molecules, to amoebas, to people to like, maybe have 64 of those? That seems, unlikely.
>> Brian Holt: Max link of like terms. Again, 50,000 characters seems wild to me to try and do some sort of match based on that long. But they limit you to 50,000 I think.

[00:03:13]
Yeah, and they start saying, it can go further than that, but there's, some strong performance implications when you start going that long, that it starts going into N complexity, which is bad for sqlite, so they don't allow you to do that. Attached databases. I can see someone hitting this one as well.

[00:03:31]
So I told you that you can do connect to connect to more than one database. Maybe you can connect to ten databases. That's a limitation there. At that point if you're connecting to so many different databases, I'm gonna say that maybe you't need to have some sort of data ingestion pipeline that dumps you out into maybe, you could dump into another SQLite database.

[00:03:52]
That's one aggregation of many you could dump into BigQuery, Snowflakes, some sort of like data lake or something like that. Rows in a database or slash size, a database can be 281 terabytes, if you have that much data and you're putting it in SQLite that now I'm impressed, right?

[00:04:13]
I'm not even upset. In theory, you can address 1.8 to 10 to the 19th rows inside of SQLite in terms of how big the registry is. However, effectively, based on how small a row can possibly be and how quickly you would reach 281 terabytes, the actual physical upper limit of this is about 2 to the third times 10 of the 13th rows, which is a lot, right?

[00:04:45]
You probably don't need that many rows. The reason why I bring all this stuff up is I'm gonna just assert that like this works for almost all of our use cases. Only the most absurd use cases would exceed almost any one of these. So SQLite can scale. I feel pretty confident in saying that it does, at least from the perspective of querying, inserting, updating, deleting, it can handle load, a lot of load.

[00:05:17]
The question is just, do you want it to? Yeah, cool.
>> Brian Holt: It's powerful enough to talk about any problem.