13 ms·
How SQL Database Engines Work, by the Creator of SQLite (2008) [video]
- serioushaha 8y agoslides : https://www.slideshare.net/VikasBansal23/how-sqlite-works https://www.slideshare.net/VikasBansal23/how-sqlite-works
- anothergoogler 8y agoLove how he started. People who don't stop their conversations for a presenter are the worst. People who don't stop their conversations for a presentation by Richard Hipp deserve a spell of laryngitis.
- Jacq5 8y agoSpell of laryngitis for Darwin awards winner is the new prize.
- ryanworl 8y agoThere is a more recent lecture on the same topic from 2015 at CMU: https://youtu.be/gpxnbly9bz4 https://youtu.be/gpxnbly9bz4
- okket 8y agoMuch better quality, thank you.
- codetrotter 8y agoMods please update link to this and change title to reflect the year of the replacement video
- dang 8y agoDoes it cover the same material though? I don't want to deprive people of the original survey.
- ryanworl 8y agoI don’t think it is worth changing the link. They are not the exact same material and anyone interested in SQLite should just watch both.
- wilburlikesmith 8y agoAnd wow, there I thought programmers know it all... Best practices, instances and implementations... Suppose we must get a Google / Tesla AI bot to create and write us an effecient and suitable database (plus structure and auto AI joins :p) haha, what do I know... Hey: @ryanworl wasn't aimed at your comment, you're right, they should watch and learn it all..
- prudhvis 8y agoAt 34:30 mark he goes on to say that there are some buggy implementations for mmap. Is that in anyway related to how Linux handles pages marked as free?
- provlem 8y agoThis requires more upvote to remain on top.
- _wmd 8y agothis video is a 100ft overview, original link is about SQL internals
- netgusto 8y agoDetailed explanations start at 26m40s: https://youtu.be/gpxnbly9bz4?t=26m40s https://youtu.be/gpxnbly9bz4?t=26m40s
- okket 8y agoSadly bad audio (room mic with all the ambient noise) and bad video quality (slides are almost unreadable). But great content.
- jokoon 8y agoI don't like to use SQL engine because I don't understand how they work, I never really know if my query will be O(1), O(log(n)), O(n), etc, or what kind of algorithm will optimize my query. Who really does understand how a SQL engine work? Don't you usually require to understand how something work before starting using it? Which SQL analyst or DB architect really knows about the internals of a SQL engine? Do they know about basic data structures? Advanced data structures? Backtracking? That's why I tend to avoid systematically using a SQL engine unless the data schema is very very simple, and manage and filter the data case by case in code. SQL is good for archiving and storing data, and work as an intermediary, but I don't think it should drive how a software works. Databases can be very complex, and unfortunately, since developers like to make things complicated, it becomes hairy. I think SQL was designed when RAM was scarce and expensive, so to speed up data access, it has to be properly indexed with a database engine. I really wonder who, today, have data that cannot fit in RAM, apart from big actors. I tend to advocate for simple designs and avoid complexity as most as I can, so I might biased, but many languages already offers things like sets, maps, multimaps, etc. Tailoring data structures might yield good results too. Databases still scare me.
- josteink 8y agoThis is what you get when developers are afraid to touch anything which doesn't look like Javascript or JSON. The incompetence and ignorance shown in this post is simply astounding.
- jnwatson 8y agoFor Postgres at least, you can literally ask it how a query works, via EXPLAIN. Now, there’s a skill to understanding the output of that, but at least it isn’t a black box.
- sebojanko 8y agoThere's something similar for SQL Server too.
- wolf550e 8y agoAll RDBMS engines have some kind of EXPLAIN, otherwise it's impossible to troubleshoot performance issues. The differences are in the amount of detail you get from the optimizer/query planner, whether you get profile of actual run side-by-side with the plan, etc.
- pipu 8y agoI truly recommend CMU's Andy Pavlov's video lectures on the topic (and also more advanced stuff) https://www.youtube.com/playlist?list=PLSE8ODhjZXjYutVzTeAds8xUt1rcmyT7x https://www.youtube.com/playlist?list=PLSE8ODhjZXjYutVzTeAds...
- manigandham 8y agoYes, they put several courses over the years and they're all great: https://www.youtube.com/channel/UCHnBsf2rH-K7pn09rb3qvkA/playlists https://www.youtube.com/channel/UCHnBsf2rH-K7pn09rb3qvkA/pla...
- PretzelFisch 8y agoI found Prof. Dr. Jens Dittrich database playlists interesting and pleasant to watch. https://www.youtube.com/channel/UCC9zrtAkl6yY4dpcnWrCHjA https://www.youtube.com/channel/UCC9zrtAkl6yY4dpcnWrCHjA
- swinghu 8y agovery good
- deleted 8y ago[deleted]
- dmoreno 8y agoI recently created a database engine (exosql [1]), only query and no storage. It uses postgres-like foreign data wrappers to get all data. It's not valid for big datasets, as it stores all in memory (or maybe it is?), but as a learning experience has been amazing to think and develop a real database: planner, executor, choose algorithms, implement features as lateral joins and so on. I will definetly listen very carefully to these talks. [1] https://gitHub.com/Serverboards/exosql https://gitHub.com/Serverboards/exosql
- zerr 8y agoAny books/lectures/articles you followed?
- dmoreno 8y agoI followed specially the postgres and sqlite documentations. For some specific areas I checked their source codes. But mainly I used explain from postgres as reference on what algorithms (seq scan, hash scan and do on) use for specific queries.
- suj1th 8y agoA similar learning experience for me was when I was exploring Apache Calcite. That again is only query, and no storage. It has a concept of 'adapters' which, I assume, is similar to the postgres-like foreign data wrappers you mention.
- logicallee 8y agoAny video filter experts here? Request to any video filter expert ------------------------------------ I started watching this. The slides are unreadable but the camera is perfectly still and the slides are for several "key frames" where the compression algorithm decides to replace one set of compression artifacts for another. For example try to read the first keyword under "Translates into:": https://www.youtube.com/watch?v=Z_cX3bzkExE&t=2m14s https://www.youtube.com/watch?v=Z_cX3bzkExE&t=2m14s The keyword is unreadable at the start but as you keep looking at it over 50 keyframes it becomes readable to me. Since the camera is in a fixed position it should be possible to combine the data from those artifacts into a single superresolution video with very small assumptions. (i.e. the assumption that the image is the same image until at least 5% change or something). There's not even anyone moving in front of it. -> Can someone who actually knows this stuff apply a superresolution interlacing filter to this video and post the superresolution version somewhere? I hope this is not too much work, and I am sure we would all appreciate the results since the slides are not human-readable before applying some kind of superresolution!
- coleifer 8y agoHow precious that you think someone cares.
- logicallee 8y agoI think there's an Adobe After Effects plugin. Granted the people who are set up for heavy video editing are probably a very small percentage of HN readers :) There's probably more HN readers who would take is as a challenge to do an unsupervised convolutional neural net :D and come back with a research paper. I myself don't use Adobe After Effects.
- peterwwillis 8y agoI'm sorry you got downvoted for this comment. HN voting is the worst.
- logicallee 8y ago
- cup-of-tea 8y agoWhat is that acronym he keeps saying? MBCC?
- ntonozzi 8y agoMVCC - Multi version concurrency control.
- api 8y agoSqlite is incredible. Tiny and usually used for small stuff but I have heard of 1TB+ databases with acceptable performance.
- dicroce 8y agoso I was trying to figure out why a query was slow the other day... it was a nasty query with like 14 joins... I used explain and saw that it was a mess... now in my case I was able to switch to outer joints and nest related joins and got it fast.. but I had some interesting thoughts. In SQL, indexes are implicit.. they are used if available but it's easy get a large query to scan sometimes when it shouldnt... what if there was a different query language with explicit index syntax.. I think you'd get a lot more predictable performance.
- wgjordan 8y ago> what if there was a different query language with explicit index syntax.. There is, it's a feature in MySQL called Index Hints [1]. [1] https://dev.mysql.com/doc/refman/8.0/en/index-hints.html https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
- brianwawok 8y agoOr oracle has it A DBA can even sit there as queries fly past, and add hints on the fly. And then you change a query from "select" (lowercase) to "SELECT" (uppercase), and query plans break and you break production. Fun times
- DenisM 8y agoTwo reasons to not have indexes in the query: 1. Query expresses the result it produces, not the method that was used to obtain it. Semantic vs implementation. It may be a pain to write, but it will be easier to read later. 2. DBA could add/drop indexes on the fly to tune performance of a live system without making any changes to the application code. And being 100% certain he is not changing the semantics of what's going on. As others noted, if you must you can use query hints for force particular index to be used for a particular operation. MSSQL also allows to pin down a query plan you like for a given query so that it doesn't drift away later due to environment changes. I agree it is sometimes a pain to force SQL to use the index you wanted it to use.
- bitmapbrother 8y agoThis is a much better talk with better video and sound. https://www.youtube.com/watch?v=Jib2AmRb_rk https://www.youtube.com/watch?v=Jib2AmRb_rk
- blackrock 8y agoI'm not going to call anyone out here, but why do people keep using the word orthogonal? It doesn't even compute. It doesn't even make any sense, in how they use it in relation to the topic. Are the issues at right angles of one another? No. Are the issues statistically independent of one another? Perhaps. I suggest to use a more appropriate descriptive word to describe the situation. You folks should read the urban meaning of orthogonal, to understand how people roll their eyes at you, when you inappropriately use the term. https://www.urbandictionary.com/define.php?term=orthogonal https://www.urbandictionary.com/define.php?term=orthogonal Just another friendly PSA.
- ternaryoperator 8y agoThat's how languages evolve. Words that meant one very distinct thing come to mean something only partially like the original. People decry the misuse. And finally the new meaning becomes the one true meaning and the original sense is marked in dictionaries as "archaic." For a word that's gone through that exact cycle, have a stare at "artificial," which was the adjective for "artifice," which at one time meant craftsmanship. When St. Paul's Cathedral was first shown to King Charles II, he praised it for being "very artificial" -- a compliment. [1] In the meantime, I agree that it can be frustrating to see words apparently misused. But I think this is hardly the mark of an "idiot," as you put it. [1] https://quoteinvestigator.com/2012/10/31/st-pauls-cathedral/ https://quoteinvestigator.com/2012/10/31/st-pauls-cathedral/
- sanderjd 8y agoOh wow I totally disagree. I have always found the computer science usage of "orthogonal" to be very analogous to orthogonal sets in linear algebra. It is only in two dimensions that it boils down to right angles; That's the uninteresting case! The more general concept is one of independence, of something being broken down into its constituent parts, such that each part is pulling its weight in some way that even all the other parts combined could not. Which is exactly how I see it being used here. I still remember the moment my programming languages professor introduced the concept of orthogonality in that field; I intuitively grasped the meaning and was awed by that power of analogy.
- wwweston 8y ago
- randop 8y agoThank you. Very educational. Interesting to know that ORDER BY includes significant performance penalty without LIMIT.
- ericnyamu 8y agocheck this out https://tweetsverse.blogspot.com/2018/06/welcome-to-new-forum.html https://tweetsverse.blogspot.com/2018/06/welcome-to-new-foru...
- angelfreak 8y agoReally great, thanks for posting.
- A_Person 8y agoGosh, I must say there seems to be some misunderstanding of RDBMS concepts in some posts in this thread! I was writing database systems professionally, back in the days before the RDBMS concept was even a thing. So here's my (enormously long and convoluted) 2 cents worth. Please be sure to pack a sandwich and get hydrated before you continue. Say you were dealing with doctors and patients, and needed to store that information in a database. Back in the day, you'd typically use a so-called hierarchical database. To design one of those, you need to decide, what is the most common access method expected to be: getting the patients for a given doctor, or the doctors for a given patient? You'd design the schema accordingly. Then, the preferred access method was easy to code, and efficient to run. The "other" access method was still possible, but harder to code, and slower to run. The database schema depended on how you thought the users would access the data. But that is absolutely what NOT what to do with an RDBMS. Certainly you look at the users' screens, reports, and so on - but that's just to determine what unique entities the system must handle - in this case, doctors and patients. Then you ignore how the users will access the data, and work out what are the inherent logical relationships between all the entities. Your initial answer might be this. A doctor can have many patients, and a patient can have many doctors. As any competent relational designer will instantly know, this means you need a resolving table whose primary key is a composite key comprising the primary keys of the other two tables. So if Mary was a patient of Tom, you'd add Mary to the patients table (if not already there), Tom to the doctors table (ditto), then add a Mary/Tom record to the resolving table. By that means, a doctor could have any number of patients, a patient could have any number of doctors, and it's trivially easy to write simple, performant SQL to access that data however you want. But then you'd have a ghastly thought: patients can also be doctors, and doctors can also be patients! Say Tom was also a patient of Mary! Now you need a Tom record in the patient's table, but that would inappropriately duplicate all his data from the doctors table! Something's clearly wrong. You'd soon see that from a data modelling viewpoint, you don't want doctors and patients as separate entities - you want a single entity Person, and a resolving table to relate arbitrary people in specified ways. So what?!! So this. IMHO, many developers using relational databases have absolutely no idea about any of that. They design hopelessly unnormalised schemas, which then need reams of ghastly SQL to get anything out. The query planner can barely parse all that crap, let along optimise it. The database has to stop every five minutes to wet its brow and take a drink. So here's my advice to any inexperienced relational database designers who have actually managed to get this far!! If you can answer the following questions off the top of your head, you're probably on the right track. If you can't, you're lacking basic knowledge that you need in order to use an RDBMS properly: - what is a primary key? - what is a foreign key? - what is an important difference between primary keys and foreign keys? - what is a composite key? When would you use one? - what are the three main relations? - what is normalisation? - what is denormalization? - what is a normal form? and so on. Just my 2c! :-)
- stevoski 8y agoIf you are a Java programmer and want to learn how an SQL database engine works, take a look at the source code of H2. Even better, try to add a basic feature to H2 (eg. a new built-in function). It is surprisingly easy, and you come away with a decent understanding of the basics of building an SQL database engine.