SQLite Uses ByteCode (And For Good Reason)
Вставка
- Опубліковано 7 тра 2024
- Recorded live on twitch, GET IN
Article
sqlite.org/draft/whybytecode....
My Stream
/ theprimeagen
Best Way To Support Me
Become a backend engineer. Its my favorite site
boot.dev/?promo=PRIMEYT
This is also the best way to support me is to support yourself becoming a better backend engineer.
MY MAIN YT CHANNEL: Has well edited engineering videos
/ theprimeagen
Discord
/ discord
Have something for me to read or react to?: / theprimeagenreact
Kinesis Advantage 360: bit.ly/Prime-Kinesis
Hey I am sponsored by Turso, an edge database. I think they are pretty neet. Give them a try for free and if you want you can get a decent amount off (the free tier is the best (better than planetscale or any other))
turso.tech/deeznuts - Наука та технологія
SQLite documentation is a gem. If you want to write a SQL parser you should definitely see their railroad diagrams (don't ask me why I know this).
why do you know this
@@tauiin I’m writing a db in Rust…
@@vcankeklik madlad
@@vcankeklik please write it in javascript
I will like to see a SQLite dB in Rust will be OP
Fun fact, jq - the json processor also uses bytecode and some kind of virtual machine to execute queries
sqlite is one of a tiny list of genuinely good pieces of software. probably #1. almost guaranteed to be the most used piece of software ever in the history of forever. everything else pales in comparison, quality, testing, reliability, documentation, usage, portability. truly the mvp of all time. most valuable program.
Yep.
would love to see @theprimeagen do a vid on top 5 (or 10) programs of all time. if he hasnt already
@@julsius that would be sick yea
zlib is a pretty good competitor
The reason why SQLite is quite close for contribution si because they want to make sure it stays in the Public Domain.
But since in a lot of countries you can't just put something into PD (except by dying and then waiting for Copyright to run out) and in others it's a lot of bureaucratic mess, they decided it's easier to just restrict who can contribute.
that’s actually what rubs me the wrong way about libSQL. The way they describe their fork they make it seem like sqlite just decided for no reason to not accept contributions. They have an incredibly valid reason, you could even say they literally cannot accept contributions because it’s used in so many different places and a licensing problem would be an insane mess.
@@mxlje also, SQLite is a source library, not shared library. Which is why it has to be PD to be most widely useful.
@@mxlje What practical reasons are there to prefer PD over an MIT licence?
It is very simple - all you need to do is sign CLA which transfers your contribution to a public domain. This is a standard practice in every open source project which accepts any form of code contribution.
"the project does not accept patches from people who have not submitted an affidavit dedicating their contribution into the public domain" (c) SQLite licence
That is why libsqlite will be forever BS because they either do not understand licensing or they on purpose paint sqlite bad rep.
@@andrejsk6211 As far as I am aware, the only practical difference is whether or not you have to include a copy of the license or provide attribution to the original author in the modified work. As most people in the open-source world are happy to give credit when deriving from someone else's work, and will do it unbidden anyways, the actual "practical" difference is nearly none. The MIT license has a basic restriction for a person trying to take credit for someone else's work as their own, whereas they technically could do that under Public Domain.
I’m in the middle of the academic research into bytecode vs ast compilation/execution and this is right up in my valley. Sweet
I only know the saying "right up my alley“. Is right up in my valley also a thing?
@@pianochess1882 No, the latter is only a thing with people who've misheard the original saying.
@@gaiustacitus4242 that’s not what your mom said last night.
Sorry I couldn’t help it 😅
@@ttred7621 You shouldn't admit to your having desecrated a grave and committed an act of necrophilia in a public forum. As you are well aware, my mother has been dead and buried for 21 years. Even the worms no longer much on her corpse.
You really are one sick person. Seek help.
all up in that valley, huh? =) right up it. lol. It's "right up my alley" btw =)
You should read on how they do testing (Richard Hipp also done some talks on it), that's some next level stuff.
He actually talks about it in another vid, as you say, really hardcore the way these christian squealers test
SQLite devs: yeah so prepared statements are really just another table in your db
I would heavily challenge the statement that no tree-of-objects database provides the level of detail as sqlite. MS SQL and Oracle both have amazingly powerful query inspection tools. In many places it doesn't just explain what it would do or has done but why it chose that physical implementation over another based on stored statistics of the tables and indices. They also allow you to override those choices in the case where you know something the database engine doesn't.
Oracle's RDBMS is the best database engine ever created. It provides an architecture that, when properly configured, completely avoids the problem of reader blocking writer without compromising ACID. This is particularly important for implementing systems which require distributed transactions.
The Oracle RDBMS not only has transaction logs but redo logs. The latter is the "secret sauce" that permits access to the currently committed version of data while a transaction is running that has placed a lock on the data for update.
Of course, out of the box the Oracle RDBMS is tuned for a developer and will not support heavy transaction loads. It is also a complex RDBMS with many tuning parameters that even many DBAs are unaware of, which is why establishing a proper number of redo log segments to support the transaction volume is rarely done. When this is not properly addressed the mutating table error will be encountered under heavy loads even when all other factors which can cause this error have been mitigated.
@@gaiustacitus4242 If you haven't used MS SQL in a while (or ever), I'd recommend taking a gander. It can absolutely go blow for blow with Oracle now. It also has full, high performant multi-version concurrency control, which they call snapshot isolation. And it too suffers from having so many knobs that you actually need a DBA to run it :/.
@@gaiustacitus4242doesn't MSSQL servers read committed snapshot isolation do the same thing?
Don't postgres and MySQL have similar Multi version concerncy control features?
Weird timing to see this video, since I just discovered that it used byte code myself while reading some of the SQLite documentation last week.
Hope to see Prime do a video on DuckDB sometime, since it's in-process like SQLite but made for big data and insanely impressive both in features and performance. I've evaluated many databases over the years, and DuckDB is the first one ever that checks every box I have. Was cool to see the creator of SpacetimeDB shout out DuckDB during his presentation on this channel.
Same here about the timing.
Emulator devs that write in Rust may already know, but enums in Rust can be both an AST and a bytecode since the discriminator can have a repr of u8. My implementation does use "unsafe" code to move and dereference the Program Counter pointer, but enums are actually enums in Rust (a way to represent a finite number of states). You had said something about not liking Rust enums, but Haxe and OCAML have pretty much the same thing. They are very useful for making AST representations.
Rust mentioned 🎉
Rust enums are not memory efficient when stored in a contiguous block of memory.
@@manilladrift An enum is as large as the largest variant. That can be efficient depending on what you put in it, but pointers can be put in them as well To make the enums behave well in memory where each is adjacent to the next, you have to be careful how large you let variants get, and be mindful of the discriminate's size as well.
Haxe mentioned
What you're talking about is a tagged union not strictly an enum. A tagged union uses an enum to represent the type of the union and therefore the num is the "tagged" in "tagged union" (although ocaml famously ellides it in compilation most of the time)
Good video.
I remember reading about SQLite dungeon code in college during a compiler class. We had to make a SQL parser. We also had to do a matlab like parser and other stuff. I remember, that I did it in perl. It was like 60 lines of perl (?(DEFINE))xi . I was sophomore and I remember everybody else in the class was a senior. Today I guess most people use ANTLR or LLVM. This was a good clip (refresher).
11:24 Rusts sql library sqlx actually connects to it's database at compile time and generates those prepared statements already then.
That way prepared statements can be used all the time.
Edit: Turns out I was wrong and misremembered that part of the documentation. Though having compile-time validation of your queries and per connection caching of your prepared statements is still really cool!
I love SQLx so much! Type-checked queries have saved me countless painful bugs. SQL is a really good declarative language for querying data (I can't stand ORMs now that I've been spoiled by SQLx) and Rust is a really good language for type safety. SQLx is the happy marriage of the two. I'll have to give the SQLite support a try sometime, we use it in prod with Postgres, makes sense they would compile the queries ahead of time.
This is not true
That's not quite true. Prepared statements are bound to the lifetime of a database connection. What sqlx does is that it just gets the database types at compile time, but the database doesn't generate a query plan at compile time. This is because data and tables can change over time and a query plan based on outdated data kan really kill your performance
@@spicybaguette7706 Yeah, seems I have misremembered that one. Thank you for explaining. :)
I think I'm going to have learn more about this it sounds a lot more interesting than I would have expected
Is he pronouncing SQL like “Squeal“ on purpose?
wdym, it's always been pronounced squeal
Yeah it's intentional he does it with a lot of things
yes he is and it is annoying
Sequel
Why do you think the logo is a dolphin?
Not sure, but I guess one of the reason that other dbs don't use this approach is that each engine can optimize a query in a different manner, so it's more appealing to rely on a implementation agnostic AST.
Lol, falling in love with SQLite when I first started using it over 20 years ago. I used it when writing my own client server database system. The server ran SQLite and managed multiple clients by making sure the accesses where put into a queue to run one at a time thereby not trying to do concurrent access on the database. It was for a point of sale system, so custom-ish hardware and at most about 10 clients all on a local network. The real advantage of it was that both the client and server where single exe files, with no complex installation process required. Ever since then I've stored local config in SQLite databases, and just about every application I've ever written consist of only a single exe file. Installation and usage instructions - run the file from wherever you want to. Uninstall instructions - delete the exe file, if you can be bothered.
You could have just used CodeBase by Sequiter or a dBase compatible language like FoxPro, both of which were far faster, offered multiuser access on a LAN, and were commonly used to build such applications. These tools have fallen out of mainstream use over the years, but they were better for creating business software when the application would run on a standalone computer or a LAN in a small office.
Small Business Technologies (SBT) built a comprehensive suite of accounting applications originally developed in dBase III. It was capable of handling multi-company accounting for organizations with annual revenue exceeding $100 million. I was a reseller of this software back in the 90s, though I deployed with a FoxPro runtime instead of installing a copy of dBase IV on each workstation. I sometimes used Arago QuickSilver to build solutions because it could compile dBase code into executables, thus preventing users from monkeying around with the code and breaking the application.
Most RDB (relational database) that do optimisation will transform the SQL statement into a formal mathematical representation that can be manipulated for optimisation before execution.
Database creation is a really interesting thing, you are right
THX! dude, i really enjoyed every mitine of the video
Clang uses byte code (IR) inside LLVM. I would be quite surprised if GCC doesn’t use a byte code internally in the process of compiling. Byte code doesn’t need to be interpreted by a computer simulator to be useful (VM). Byte code isn’t slow or fast on it’s own, like always, it depends.
GCC also has IR. It just makes sense when you support compiling of multiple languages.
man with 20 years experience just learned about sql, nice
The first time I used a DB was in college and it was SQLite. I was too naïve to appreciate its beauty. Their documentation is *chef's kiss*
You calling SQL as "squeal" made me smile. Not gonna lie. I've always heard it referred to as "sequel". Nice.
Don't forget the ""squirrel" people!
Randal, a viking i am, i aint no Knight but on quest we go.....
Wait, PostgreSQL doesn't use byte code? I just had assumed that of course any SQL engine would use byte code, because it is very easy to do and has quite the performance benefits (especially when using the GCC/clang "address from label" extension with which you can improve byte code interpreter performance).
You might be pronouncing squirrel wrong
SQUEAL
He is the only one who says squeal. Everyone else is saying sequel (sick-well) or ess-que-ell
I've been adopting squeal too and it's been spreading :)
That’s the joke
19 mins of Prime squealing into my ears
From now on, SQL is squeal to me
SQLite's the first (and currently, only) DBMS that I've used while on my development journey and I love how straightforward and simple everything feels, I don't really know enough to know what areas it sucks with, but I've enjoyed using it so far.
It you only need to store a (relatively) small amount of data, and you only need one node to be able to access it it's great. It doesn't have great concerncy.
Do you have a guide on your TV? Yes, its probably a Sqlite database. Updates of the guide is just downloading the database file😊
While bytecode (or even machine language) is faster than interpreted code, the resulting implementation of bytecode may actually be slower than interpreted code. It is all about the underlying algorithm used for retrieving the data. A well written algorithm in an interpreted language that also has language commands which are optimized for data retrieval and manipulation will greatly outperform an inefficient algorithm written in bytecode.
So this îs like compiled stored procedures in mssql?
makes sense that it's interpreted for different platforms?
writing the three looks so much easier than doing on SQL :,c
One thing I don't like is the terse error messages for constraint violations. I mostly use SQLite for automated tests, so clearer error messages would be very valuable.
And Python includes it in the standard library.
The text to speech engine in this video could use some improvements 🙈
I often used an in memory SQLite DB for testing. Much faster!
Drive by community contributions is technical debt for everyone else.
See-qual.
Whatching on 0.75x speed 😂
If I ever invent an alternative to "SQL", Imma call it "SQUEAL".
Why do you keep calling it squill? is this some sort of rage engagement hack?
AST integrated into VS Code btw fyi
"Squeal", LMAO
Can you make a poll to ask where we are from, how we pronounce SQL and how noobs pronounce SQL in their region? How da hell did you pronounce here?
The profesional way in Brazil is the noob way In argentina
I tried to make sqlite work with 200k data
and well, my sqlite database died.
Tree sitter is not very different. It should basically preserve all informations squealitté need to work
PrimeDB when
Amazing that none of these small databases don't use EVIs (Encoded Vector Index). Way faster. Come to think of it IBM probably still owns the patents.
I dont really get the point of so much comparison with engines like mysql or postgress. Even though I am not well versed in the database engine development I can imagine that having concurrence is a thing that would make some of the approach to things not possible.
Sure sqlite is fast compared with postgress or mysql. but also cat > mydb.txt is faster to mysql, plus as orthogonally similar.
vim is public source and it got slandered for that...
Say squeal one more time…
True
Skew-Kuhl is the only correct way to say it
I like how you deal with sponsoring. If you feel you might possibly be biased, you just say so.
And even when it's sponsored, I've never felt that you've distorted your point.
i cannot get over the fact how you pronounce "sql"
Open-source doesn't necesarilly mean your are allowed to contribute.
For an excellent example, refer to the contributions by UMN “researchers” to the Linux kernel and consequent fallout.
I couldn't watch this the word squeal killed me too much
It's not squeal-lite, it's "seek-light".
Realm DB is even faster than SQLite
SQLite is open source, but closed contribution. This is a very important distinction because things like Redis and Grayjay *_reaaaallly_* want to be able to twist the narrative and FOSS-wash themselves by using terms like "source available" and convincing people it's basically the same or even better than open source.
What defines open source is purely and simply "can I use this how I want without your permission?". You can get into some iffy territory when it comes to "free"/Libre distinctions where you reach the question of the right to concede your rights ("Should I, as the user, have the right to concede my right to fork, look at the source code, etc. by using a less-permissively licensed version of the software?") but contribution is entirely irrelevant to that question.
Another reason why this is important (aside from shitty people/companies who try to FOSS-wash themselves and weasle into stealing some good PR via deception) is that a lot of people *_think_* open source requires you accept contributions and such, and so use that as an argument to *_not_* open source. Literally all it takes to open source something is to make the repository public and ctrl-c ctrl-v a license into it. Accepting contributions is *_entirely_* irrelevant to it being open source or not. (you'll see a lot of projects that don't quiiiiite FOSS-wash but do try to pretend to be more pro-user than they are hide behind a lot of these sort of arguments)
SQLite being open source is precisely why things like like LibSQL can exist; terms like "source available" should be kept exclusively for the shitty projects that try to pretend to be open and pro-user while *_actually_* keeping the user duct taped to the passenger seat, along for the ride wherever the actual driver takes them. (I mean, I'm sure *_some_* projects are pretty honest about it, but I sure as hell haven't seen one)
not Squeel its seaquel.
What is squeel?😅
SQLite was always based. It was always underappreciated
Sho me some fing javescript
Not sure I can bring myself to listen to someone call it "squeel" for 20 mins
Yet here you are
SEQUEL
I think SQL is pronounced “sequel” not “squeel”
It's a joke
lol, seems like prime never learned relational algebra 😅
The article screams "developer" and not "DBA". Everyone calls "prepared statements" Execution Plans. A prepared statement is something else. Never heard of a "Tree of Objects". Oracle calls the "objects" in an execution plan Operations. SQL Server calls them Operators. An execution plan is usually, but not always arranged into a tree structure. Things get murky with stored procedures, parallelization, and functions.
Don't know about the accuracy of the Bytecode part, but if you're going to analyze a database product's function, do some basic research...
from my understanding, prepared statements where most of the query is constant, you only the comparison variable changes
Mysqueal and postsqueal... I swear Prime, if I accidentally say this shit aloud on an interview, imma use my particular set of skills and find you... ;)
i literally cant listen because of how you pronounce squeel 😭
You are right, it should be pronounced "squeal".
I wish Prime would pay a little more attention to what he says is and is not open source. Just because they do not accept changes back into their repo does not mean SQLite is not open source. All that's required of open source is that you are allowed to do whatever you want with your copy of the code
Open Source has a specific definition behind it that SQLite does not actually meet. Like Redis, it’s source available, not open source.
@@rdil SQLites license doesn't have the restrictions Redis does. It's actually a pretty open license. Basically just don't be evil, whatever that means
Can the subset of change requests that are accepted on an open source project be zero? Yes - and it frequently is. Does stating it up front make a difference? I think not. It comes down to the permissions granted in the license.
To the best of my knowledge "Open Source" is not a legally protected term (not even with capital letters and quotes). The shadowy deep state cabal "The Keepers of the Internet" or FSF or EFF or Linus or Stallman or GNU or whatever or whoever might have definitions and approved licenses etc. but if the answer is "yes I can see the source code; yes, I can modify the source code; yes, I can sell derived works' yes, I can use it commercially in perpetuity for zero license fee" then that sounds pretty open-y and source-y to me.
like Linux also has its own system to accept contributions
@@stephenreaves3205 "don't be evil".... I love the idea of a group of corporate lawyers gathered around to decide if they are willing to give that assurance, and coming to the conclusion that, not having a soul between them, they can't even comprehend its meaning.
I hate it when people misquote things, you have an excuse for it, but there are plenty of other people on the internet misquoting things, and it might not be a huge or significant change, but it's still annoying.
I'm referring to 0:00 when he read "SQLite Uses Bytecode" as "SQLite Used Bytecode"
He has pretty bad dyslexia
I think you should be more forgiving of the personality driven recorded live stream genre (another word he mispronouces, as it happens). This is not a BBC documentary, or an infrequent high production value video essay. He is the solo Primeagen, not David Attenborough and his team, or Contrapoints.
Personally, I hate workshop videos where the person keeps mis-typing things... but that's not just an endearing personality quirk on their part, that is just irritating 🙂 I'd be a very poor "pair programmer".
I guess everyone has forms of "sponteneity" they love and others they cannot live with.
I can't take anyone seriously that doesn't know that it's pronounced sequel
You actually think he doesn't know how to say it?
It's a joke
I take anyone serious who thinks how you pronounce word is important
Come on man, it's "Squeal-ity", not "Squeal-Light"
What the fuck is squeal
Its what you are doing right now 😂
A joke
Can you remove the scrolling chat from the video? It is distracting
I think most of the time public source is shitty. It means there is no real community and you could just have used closed source as well because you have to rely solely on the vendor for everything. You can't even fork it if you think you need to.
You can fork public source. LibSQL is a fork of SQLite
I'm sorry, but I just can't get past the pronunciation of words.
It's pronounced "Skull"
SQLite is great and I use it a lot. I'm very thankful to the creators of it, tho I'm a bit disappointed that they're fanatics 😂
do not worry , coder will be needed for a long time still . Yes even with ai . Your ustensil are smarter
Structured Query Language (SQL) (pronounced S-Q-L; historically "sequel") is the proper pronunciation. The following quote is from Don Chamberlin who was a researcher at the IBM Almaden Research Center. His employment is notable because it was the birthplace of SQL, and he was a member of the team that developed Structured Query Language (SQL). From a 2001 interview he writes, "We changed the original name "SEQUEL" to SQL because we got a letter from somebody's lawyer that said the name "SEQUEL" belonged to them. We shortened it to SQL, for Structured Query Language, and the product was known as SQL/DS"
You calling it 'Squeal' is killing me. I could only watch the first 49 seconds of your video before moving on. lol
GUI is pronounced "gooey".
@@NostraDavid2no it's guhwe