Designing Data-Intensive Applications: Chapters 1 and 2

Hello, hello. I think we are live. Hi, everyone. How's it going? Let's look here. Looks like it's working. I'm just making sure it's streaming, working on YouTube. Great. You guys can hear me okay? Looks like we have a few people already. Drop a little hello in the chat. Hello. Nice. So, today, the reason I'm doing this stream is not to talk about database migrations, but to talk about this book, which is a really good book that I've been reading. I shared about it on Twitter like a week or two ago that I bought it, and I've been reading it. A lot of people seem very interested. Obviously, it's a pretty popular book. So, I'm just going to talk about... I've read the first two chapters, and I have a couple of things that I want to talk about, but I also am open to suggestions. I assume some of you watching are going to have read this book or are reading it right now. And so, I'm going to wait for people to stream in before we really get going, but that's kind of the plan today. So, I have a few technical things that I personally found interesting about it, and then I'd love to hear what others have to say. Let's see. Nice. Carlos says, this is an awesome book. Yeah. So far, I agree with you. Very good book. So, yeah. I'm just waiting for people to stream in. Supposedly, I'm streaming this to LinkedIn, too, but it doesn't show that anyone... Oh, there we go. Someone from LinkedIn is here. Nice. It wasn't showing... Oh, it doesn't show me viewer counts from LinkedIn here. I'm still learning this. I'm using StreamYard. So, it shows me viewer counts from X and from YouTube, but not from LinkedIn. Let's see. Good morning, Ben. Hi, John. How are you? Camera quality, wonderful. Thank you. It took a lot of thought and repositioning things in the office and all that to get this looking nice. So... Ah, here we go. Your tweet was the third time in a week I saw the book mentioned. They could have all been by myself, but hopefully you mean by other people, too. You ordered it over the weekend and finished chapter one. Nice. Yeah. That's pretty cool. Yeah. I've heard... I've seen people talk about it before. And as a lot of you know, I work for a database company. I work for PlanetScale. I do database stuff, developer education for them. But I'd never read this book, even though it's pretty popular in the databases space. So, I thought it's finally time to do so. Yeah. All right. Well, let's see. I think we'll go ahead and get started here. And then people will keep trickling in, I'm sure. The reach on X is pretty good, because I think most people just get notifications by default. Let's see. I read last year, but I don't think I remember them all. Yeah. Nice. Okay. Well, so my goal today is to talk about this, like you know. So, this is a very popular book by Martin Kleppmann, called Data or Designing Data Intensive Applications. And I've never read it. So, I've been reading through it. And because there was so much interest online, I thought it'd be fun to just do a live stream and talk about it. And my hope is, if this goes well, and if people seem to be into it, then maybe we'll do more. So, maybe I'll read another chapter or two. And then in a week or two, we can do another live stream and keep it up for the next month or two as I go through this book. That'd be kind of fun. And others can, you know, sounds like some people have ordered the book, which is great. If you want to like follow along with what we're doing, order it. Or if you already have it on your shelf, grab it or borrow it from somebody. And yeah, I think this will be pretty cool. So, the other thing I will say now, if any of you either have like read the book, or are reading the book and are in the database space and kind of know a good amount about this stuff, or if you happen to know people personally who have worked on some of the like pieces of software that they talk about in this book, I am interested in potentially getting some guests to come on here and like talk about it with me in future streams, not necessarily today. So, let me know if that is something that's of interest to you. Message me on X or on LinkedIn or send me an email, something like that. Let's see. Yeah, let's see. So, let's look at a couple of comments here. I read this book a year ago. One of the only technical books I've read and found it impossible to write down notes for just so much new content. Yeah. You know, one of the things I will say too, is I kind of thought from the comments that I got online when I posted about like, I've never read this before, there's some people that kind of made it sound like, well, if you're already in the database space, you won't really get a lot out of it. And there's definitely things where I read it. I'm like, okay, I already know that, especially about relational databases, since that's the space I work in. But one of the cool things about this book, just from a high level, is it's not a book of just like facts, right? So, there's some books that are like, what's the one that I have? I have a MySQL book, Learning MySQL. So, that's another O'Reilly book that I have. And I've read parts of it. And that's more of a, just here's what MySQL is, here's what MySQL does, here's how you use it, here's how indexes work, here's how you design schemas. This book is not really a list of facts to just like learn a new database technology. It's really, at least so far, very much about design, but also about trade-offs, right? So, like the second chapter, which we'll talk about some, spends a lot of time just talking about the trade-off between different data models, the relational model, network model, document model, like MongoDB type databases. So, even if you like, feel like, oh, I already know Postgres really well, I already know whatever you're like, data analytics technology as well. It's just, you'll see some things in it that are like trade-offs that maybe you've never thought about before. Trade-offs between schema designs or whatever. So, it's really good. Yeah, been reading second edition. Okay, yeah. So, I don't know if the, I think, yeah, somehow you can get access to the draft. I will also say too, if anyone's thinking of ordering this book, there is a second edition that is coming out soon-ish. When I looked on Amazon, I don't know if this is the actual release date or not, it was like March or something of 2026. And so, if it was like, oh, it's coming out in a month or a couple of weeks, I would just wait for it. That's like half a year away. And I'm like, well, I'm just going to order it now. But it was written, I think, in like 2012-ish, sometime around there. So, it is like, there are some things in it that are outdated. Like in the chapter where it talks about trade-offs between relational and document databases, it says some things about document databases that are a little bit outdated. Like it basically says, you can't do joins in document databases, which I'm fairly certain is not true anymore. I'm pretty sure you can do at least limited types of joins and things like MongoDB these days. Someone can correct me on that if I'm wrong. So, you may just want to wait for the newer version. That's fine. Okay. So, let's talk about the book. And where is it? There we go. Screen share. Yeah. So, I do want to do a little bit of screen sharing eventually. But I just basically like, the way that I was going to go through this is I like highlighted a couple of things that I want to talk about. And we can like look at some of the diagrams in the book. And we can talk about some of these like interesting trade-offs. So, let me go over and get this first diagram up, talk about here. And I'll do, how do we do this? Here we go. That looks pretty good. Or we can do it like this. So, this is like the first chapter is basically giving the high level overview, right? Of like, how do you architect data driven applications? What are they like? And I really like this diagram. Like it's pretty basic, right? If you've like built a web application before, you've like probably architected something similar to this. But it's really like, it captures well, especially for those who are new, like some people in the chat have said like, not really that technical, but I've read this book and it's very interesting. This does give like a very nice overview of like the basics of how a lot of web applications are architected. Obviously, depending on like the scale of a web app, right? If you're operating at, you know, Twitter scale or Facebook scale or something like that, there's a lot more complexity in each one of these buckets, right? Your primary database is not just a single server. It's probably thousands of servers doing sharding, some kind of distributed system, right? But the like, the very basic architecture is essentially like this, right? You have lots of application servers or one, if it's a smaller app, and then you're connecting to, you know, your primary database is usually something like MySQL or Postgres, right? Like those are the popular options. And this is like your OLTP workload that's powering, handling your user data, handling, you know, metadata, right? Like tracking what your users are doing, all this kind of stuff. But then often there's other sort of data systems involved. Like sometimes it's as simple as an app and a MySQL database, and that's it. But oftentimes you'll have additional data stores. So you might have some kind of data store like Elasticsearch or Algolia or something like that, that's specifically designed for doing full text search if your application needs sort of like dynamic searching capabilities, right? So you offload some of the data powered work to a full text index type of piece of software, right? A lot of applications have asynchronous tasks that they do, like most, you know, software as a service type stuff. So there's usually some kind of like data ingestion message queue system that's kicking off jobs to go take care of things, and then it comes back later, right? And then in some cases, like just the primary database is enough of a cache because like results are cached in RAM sometimes. But sometimes there's also dedicated like Redis cache or Memcached or other types of result caches to help like reduce load on your database and keep things loading snappier and all that kind of stuff. So I like this just as like, again, for some people, this is obvious. But I like this as a kind of a nice overview of, like, what are we trying to build? Right? By the way, I'm checking comments as we go. There hasn't been much since the beginning there. But drop comments if there's like anything you think we should chat more about or questions about as we go. Just kind of hanging out. This is fun, though. Let's see. I've got sorry, I'm going to look at YouTube over here. Okay. Cool. So a little bit more interesting stuff. That's just like a nice overview kind of diagram. Chapter two has things that I think are a little bit more maybe interesting to spend more time on because that's where it's basically giving the breakdown of data models, right? So kind of earlier on in the history of computing and databases, one of the more popular models for like storing and querying data like it talks about here with some old like IBM based products is the hierarchical model, which is basically very different than what most of us do these days for databases. Right? Mostly what we're doing now is relational databases, right? For OLTP type stuff. Sometimes people choose to use document databases like MongoDB. And then there's some like specialty database products, right? For doing analytic stuff or for doing full text index searches, things like that. So but for a long time, like this, the hierarchical model of basically sort of representing everything, essentially like a tree type structure was pop common. And there was sort of a debate in the history between like, you know, relational started to become more popular and which one is better to query or which one is better to use. But one of the examples I really like, that they they kind of argued why, for one of the reasons why the relational model became so popular is because the way that you query using SQL, right, which over the years has become like the de facto standard for how you create your relational database is not it's a it's not imperative, it's declarative. So you're essentially when you write a SQL query, you're declaring this is what I want, you're describing like, these rows from these columns from these rows and filter by this and joined by this. And you let the database choose how it's going to fulfill that, right. So there's a lot of opportunity for optimization. Trying to remember this like page 43. Go over to page 43. Here, hold on. Oops, zooming out the wrong thing. Go 43. Wow, this is like jumping around way too much. Still working on my live streaming skills. So hang tight. Oh, I see why it's doing that. Okay. Let me find this here. Yeah, okay. There we go. This is the page that I wanted to look at. So let's bring this back up here. All right. So this is what it's talking about is like, a lot of the time when you would use hierarchical models, or just in general, like some older style databases, right from the 80s, 90s, one of the ways of querying these databases was essentially imperative, where you're essentially writing code or pseudocode to describe your query. Like, this is what I want to happen. And this makes sense, right? Like, hey, I could just write a function to query my hierarchical data and say, like, go find all of the animals and iterate through those. And if they match the name shark, I'm going to push that into a list and then return sharks, right? But this is good and bad, because this requires you to like have this requires you to optimize your queries, basically, right? It's very expressive, because you get to say exactly what you want to happen. But you also have to say how you want it to happen. And so if you if you don't have good fundamentals in like knowing good algorithms for these things, or if you don't know the underlying structure of the data very well, or, you know, a bunch of different reasons, right? This could be slow, right? Like, imagine having to write every query like this. And this basically becomes like a table scan, right? If that animals table has like a billion rows in it, this is a table scan. And every time you need to just find all of your sharks, you have to like iterate over your entire table, right? So the reason why like a part of the reason why SQL is so powerful, just move this over here, is they're basically saying, like, in SQL, this is the equivalent thing, right? You say, select star from animals or family equals sharks. So you you do not have to tell your database at all how to go figure this out. It's responsible for, for knowing the structure of your data indexes that you have a lot of databases, like MySQL and Postgres, they maintain statistics about like, how many rows are in each table, and what kinds of values are in the columns and all of this kind of stuff. And this is why query optimizers are such like a big deal in these databases, because both Postgres and MySQL and pretty much any relational database, a huge component of the complexity of those databases is the optimizer, which is the thing that takes a query string like this breaks it up into its individual components, and then goes through a big decision making process of should I do a table scan? Or should I use the B-tree index? And if there are multiple indexes, which one do I choose? What order like if there's multiple where clauses here, there's just one, but if I had lots of different filters, the order you apply those filters in can affect performance, right? So figuring out what order to do those in, if you're doing joins, figuring out like, what the best performance, most performant way to do that join between tables is depends on the size of the tables, right? So there's like a whole big subfield of databases, just query optimizers. And that's what their job is, right? It's to take queries and figure out how to make them fast. But the good thing is, they've gotten really, really good at this for the most part. So it's very nice from a from a developer perspective, to be able to just write something like this, or a lot of us just let our like, our ORM, write it for us, right? And again, it's even more nice there, because the ORM might like, do some somewhat dumb things sometimes or makes queries more verbose than they need to be or whatever. But optimizers generally do a pretty good job as long as you have good indexes and all that, of like, figuring out the fastest way to execute those things. So I like that perspective on like, you know, we're all a lot of us just very used to SQL, right? And maybe some of us take it for granted, including myself. But there's a lot of other ways that either currently or especially used to be more popular ways of querying data, or SQL just became like the mainstream thing. And so it's cool to think about, like, what are some of the reasons why that became popular? And one of the big reasons is that, right? You specify semantically what you want, it figures out the rest. See, got many questions when we get to the document data model section. Yeah, you know, it's funny, I used to, I used to actually teach at a university. And I would teach a web development course is one of the courses I taught. And I taught MongoDB in that. And now I work for a company that does MySQL and Postgres. So don't tell them. Just kidding. They already all know. All right. Can't post comments to some destinations. Okay. So that's interesting. Another thing that I particularly liked, and again, I'll clarify here, we're going to go for at most an hour. So definitely going to stop by 10. 10, my time. And so there's no way we can really cover every interesting thing in the first two chapters of this book. It's like 60 pages, and there's a lot of good stuff in there. So like I said, I highlighted stuff that I want to talk about. But I'm also happy to like, veer off into like other questions that people have. It's an interesting question. Maybe this is one the chat can reply in. Interested in people's thoughts on ORMs. My experience is they get in the way more than they help. But it's so prevalent. Generally considered a correct approach. Yeah. My perspective is it does seem like ORMs are in some way, shape, or form. Most like modern web apps are built with some kind of ORM, right? There's things like Drizzle and Prisma. If you're coming from that world, there's stuff already in Rails and Laravel and all that. But sometimes it makes sense to like write queries by hand if you need to do performance optimizations and stuff. Never seen BadBen. Sounds like I don't want to. But yes, I should not have used Mongo. Ecto and their Elixir for this reason. It's more of a query builder than ORM. Interesting. Yeah. Yeah. And I guess the ORM thing too, like depends on how much control you get. Some ORMs are a little more restrictive. Some give you really easy to control to like jump in and handwrite the query yourself if you need to. Yeah. Interesting discussion. Yes. I will not watch the movie. All right. What was the other... Let's see. There was another thing. Oh, yes. So the book talks about two... Let me figure out how to do polls on these things. I don't know if it lets me do polls. It talks about P99 or P50, P99, P99.9. And these are like if you're in the databases space or really just if you're building web apps because this doesn't only apply to databases. These are really useful metrics to look at basically for monitoring the performance of things. But you got to be careful with them. So that's one of the things I want to look at over here. So let's go. I think I have a drawing tool pulled up. I'm going to do my best Theo impression here today and use this. So you're probably familiar, again, if you build web apps and stuff with these kind of things, right? But like let's say that these are all of your queries to your database. It's going to be a little bit of an abstract drawing, but hang tight. So these are all your queries that are going to your database over some period of time, right? Maybe this is all the queries that happened over a minute. And we'll just say that there's, I don't know, 1 million queries that hit your database. Didn't need that to be a separate thing. Oh well. And the reason why I have a stack here is we're going to consider them from the fastest queries to the slowest queries. So let's say down here at the very fast level, these queries are happening at like, you know, less than one millisecond, right? Like ideally, a lot of the queries that you have are going very quick because in an OLTP database, many of your queries are just going to be things like point lookups, where you're looking something up by ID and you're returning either one row or a very small set of rows. And if you've got your index built properly, then those should be able to be fulfilled very fast, right? At least on the database side. There's maybe network overhead and all of that kind of stuff. And then again, how fast all these go is very dependent, right? Because it depends on what kind of application you're running. But maybe once you get up here to the middle of your queries, maybe these are averaging five milliseconds or something like that, right? Why do I have greater than? What I meant is less than. So these queries still are going fast, but maybe there's some slightly more complex stuff going on over here. And then we get up to sort of higher up, right? Once you get into like the slower queries, maybe 10 milliseconds. But then often what happens, and again, there's a bunch of it depends caveats on here, is there's kind of a long tail where it doesn't necessarily go linearly. As you get into your slower queries, you start to get into like more significantly slow queries. So, you know, as you get to the top, you got 50 and then you got 100 milliseconds, right? And so what these different like P values are, so like P99, or sorry, P50, what P50 is saying is if we take the middle, that means your P50 value. So if you look at your database and it says, hey, your P50 over this time range was five milliseconds, what that means is half of your queries executed faster than five milliseconds, half executed slower. Pretty, pretty basic, right? I'm not rocket science here. And then another popular one, like sometimes things show P75, but oftentimes after P50, it jumps right to something like P90. So 90% of your queries were faster than whatever this value is. Again, let's say this was like five milliseconds, maybe your P90 is 15 milliseconds. And I'm just making numbers up here, right? Like, obviously it can be different. Sometimes there's P95, maybe that's 18 milliseconds. Man, why is the highlighting in this so bad? There we go. Anyway, and P99, say it's like 20. So again, 99% of your queries faster, 99% slower, and then P99.9. Maybe this is like 30 milliseconds, right? So these are often like values that you would look at, like, I work for PlanetScale. So we show these graphs when you look at our insights graph. But there's a lot of other places, again, not just databases, even like web app services, right? Like it might be useful for you to look at, for all of the web requests coming to my application servers, what percent of those are below, you know, five milliseconds, right? My P50, and what percent are above, right? Or you look at your P99. 99% of my web requests, I would like to be going faster than whatever your goal is, 10 milliseconds, five milliseconds, 20 milliseconds. So they talk a lot about this, right? And one of the things though, and I actually like, I tweeted about this earlier today, P50 is 1.7 milliseconds. Yeah. Ideally, like I'm actually kind of exaggerating here a little bit. If you're running a MySQL or Postgres database, that's like powering an OLTP workload, right? Like I said, lots of point lookups and stuff. Really P50 should be like one millisecond or something, maybe even sub millisecond. And it's really only as you get up to these. And again, it depends a lot on what kinds of queries you run in your database. So it's not a one size fits all answer here. But you want these to be as low as possible. But the point that I was going to make here is like, okay, if over the span of one minute of time, your P99 was 30 milliseconds, okay, that's not great. 30 millisecond query, especially when getting a lot of queries on this database. But like, that's not terrible. But the interesting thing is like that, if you just look at that line, whatever that line is on your graph, you might think that you're okay. But that's actually ignoring a lot of queries, right? What if we do the math on that, I guess it's 1000 every minute. So we can double check my math on that. But there's actually still 1000 queries that executed on your database that are above that p 99.9 threshold. And those could be going way slower than 30 milliseconds, it could be that every single one of these queries 1000 queries that happened over the course of this minute, could all be like giant full table scans that all take, you know, 10 seconds each. Obviously, that's like a made up very bad scenario. But the point is, even though these metrics are very useful to look at, but you should look at p 50, you should pay attention to p 99 and p 99.9. They don't actually tell the full story. So you actually need to really look at every single query, not just your, your percentile values. Let's see. So anyway, hope that's helpful. john says our p 50 on PS planet scale is 1.7 milliseconds. Yeah, it's very nice. I like to hear that. john, are you on metal? If not, can make that even faster? Let's see. I would be eternally grateful if the queries at my org only took 100 milliseconds. Yes. So it again, a lot of this is it depends, it depends, right? So like, I'm talking here about an OLTP type workload, right? transactional, and generally speaking, for those kinds of workloads, you're looking at small short queries for the vast majority of what you're doing. Very different if you're talking about an OLAP type system, where you're doing analytics, where you're doing big scans, and aggregations, and all that kind of stuff, even specialized tools for that, like using something like Clickhouse, or using Google BigQuery, right, like specialized analytics databases, you're still going to expect queries to take longer, because it's having to scan over very large amounts of data, assuming things aren't like already cached answers. So it depends. But kind of my example here was supposed to be more OLTP ish. Not yet, but soon. Nice. What would you suggest for using Postgres as a time series database? Interesting. Yeah. So it's not really designed for that out of the box. And I guess it depends on what you mean by time series database. But like there are databases that's like main purpose is to be able to ingest lots of time series data, things like logs, or click events, right, things that basically come in in some ordered sequence, and can be nicely compacted, like, you're assuming that pattern of access. An OLTP database, again, not including extensions and stuff, but Postgres and MySQL are not necessarily designed for like, hey, everything is coming in in a stream. And it's going to always be accessed in the same order that this stream is coming in. But there are databases like click house that I mentioned earlier, that actually take into account like their data storage system, instead of relying on, like, B trees and heap files and stuff, they're like main storage system is similar to an LSM tree, which is really designed for like time series stuff. So you might be better suited with something like that. Although in the Postgres world, there's lots of extensions. And I believe there's a few out there that can actually make Postgres work a lot better for time series too. Nice. Okay, someone had a question earlier, about point three on page 30. Hopefully our books are the same. I'm going to look at that real quick before we go to our next thing. See, we got 300 people here. Hi, everybody. If you came late, welcome. We're just talking about Yeah, for those that came late. Let me refresh everybody. We're talking about this book. I read the first two chapters recently. And we're just chatting about interesting stuff that we've learned. Let's see. Okay. Okay, page 30. Encode jobs, education, contact info as JSON or XML document. Okay. Interesting. Yeah, so if you are have a lot of data, basically, this bullet point is talking about like, you have a bunch of information, the example it's using is like a LinkedIn page, like someone's LinkedIn profile, you have the person's information and where they've worked and where they studied school and all that. And so you can treat that like a document in a document database, or you can kind of structure that in a relational schema in a relational database. And it's basically saying, you could also just store this as a text column in your relational database, like store the whole JSON or XML or whatever it is. And then query that and you fetch that out and then you parse it and you do stuff with it. I would not recommend that. But obviously, there's like some organizations where that's the structure they've chosen, probably a lot better to, at the very least, right, use a relational database, but use like a JSON column or something if you if you, ideally, you have your schema sort of more rigid, where everything that needs to be a distinct value has its own column and tables for doing joins and stuff. But if you really have data that's just like super inconsistent, and you need the flexibility, but you also want to be using relational database, then you probably want a JSON column. Nice. Cool. Hopefully, guys are liking it. I see more people joining and leaving. So that's good. All right. Another interesting thing, let me find the page here. Yeah, there was that. And that those are both near each other. So 41 and 43. All right, let me find the right page here. I want to talk about, it's actually quite near what we were just talking about before. Yeah, is a little bit more talking about performance, specifically, and the comparison between a document database and a relational database. And again, some of the information in this book is a little outdated about, I think, more so because they're a little bit of a newer phenomenon, it's a little bit more outdated about document databases, things like less limitations these days on joins, but some of this stuff still rings pretty true. And so the part, let's find it here. Yeah, I want to talk about data locality. And this is an interesting little compare and contrast kind of thing. So when you're when you're using something, again, I'll just use like MongoDB as the example, that's not the only document database that exists. But when you're using that you store things as documents, generally, just think about it like you have a bunch of JSON entities, right at JSON documents. And you can store those with some amount of structure in a database. But the example that it was using in the book, like I said before, is like a LinkedIn profile, and it kept using that example. So if you have, if you're loading your LinkedIn page, should we go to LinkedIn right now and load it? How do I how do I do that? Where is my browser? LinkedIn. Okay, so you go to LinkedIn, right, and you look at your page or someone's page, whatever, there's a bunch of information that when I landed on this, had to get pulled in. So I'm using LinkedIn, just because the book use LinkedIn. So you have to pull in some stuff is like media assets that aren't necessarily stored in your relational database, like profile picture and all that. But like, name and where you work. And I guess you're seeing like profiling stuff, but like where you've worked at before and the description for your jobs and how long you've been there and where you went to school, all this stuff is stored in a database somewhere that has to get fetched and aggregated and then sent to the app servers and then back to the browser, right in order to be rendered. So what's the like the trade off that it's talking about here is if you had a document database, every user on LinkedIn could have their own documents. And in that one document, just kind of like arbitrarily structured JSON, maybe there's a little bit of structure enforced, but MongoDB doesn't necessarily enforce that every profile has to have the same structure, you could just put everything in there, you could put my name, where my profile image is stored, all the entries for everywhere I went to school entries for everywhere I've worked for the lengths of times the job descriptions, awards, all of that kind of stuff. So when you go to load that page, your app server says look up, you know, Ben Dicken by ID, send that stuff back. And then we load that page, right. And basically one database request, it got either everything or at least like most of what it needed to do the initial render of my page and populate all of my information. Whereas in a relational database, you probably would structure this different. Let's go back over to Scala draw. Let's draw some stuff. You guys like the drawings, drawings nice, kind of a newbie streamer, but I know a lot of people like to draw on their streams. Well, let's, let's look. So like in a relational model, I'm just going to make up something really quick. You might model the information on LinkedIn as something like this, right? You have, why is the default color here? Red? I don't know. Let's go with blue. So you have like a user table, and this is going to have your username, your, your email, like your basic user profile stuff. But for something like your like places that you've worked, that's probably a separate table, maybe called like employment. And there is a foreign key that connects users and their ID two rows in this employment table. And every like, okay, Ben works at planet scale. That's a row in employment. And then Ben used to work at the university of Arizona. That's another line in employment. Right. And then similarly for actually for education, maybe I guess that could all be bundled in one table, but maybe there's another education table where again, there's like a foreign key where you can look up by ID, right? Like where people went to school. What's another example from LinkedIn? I don't know. You can talk about like, Oh, there's like recommendations as in like one person can recommend another person publicly on their profile. Right. So then there's probably users can be associated with recommendations and actually more than one user can be associated with a recommendation because it's a two way thing. Right. So anyway, you start to get these schemas where if you think about this for that one page load to load up my LinkedIn profile, I'm not hitting one single location, like one document that ideally is like all maybe, maybe in the same, maybe in a single page on disc, or maybe if it does span multiple pages on disc, all hopefully like right next to each other. Whereas this, the user row over here, and then a couple rows from employment, which could be scattered all around and a couple rows from education, which could be scattered all around a few recommendations. And then maybe there's an awards table to people can put in like when they won awards or whatever on their scholarships, all these kinds of things, right? Like all of these have to be pulled in from different tables. And this could end up being a lot of different separate requests to disc assuming it's not already all in RAM, right? Like this could be at least one, two, three, four, five different locations on disc that you have to hit. And then maybe more if there's actually spread out stuff throughout the tables, right? So it could be 10 or 15 or 20, or 50 disk IOs, just for loading one page. And if most of that is cached in RAM, that's not a big deal. But if you have to like hit disc for a lot of those, especially in the cloud environment with where people use lots of EBS volumes and stuff, use planet scale, you can use metal where all that goes a lot faster. But still, that's a lot of disk IOs, right? So that's one of the like, kind of arguments here, they're trying to be fair and then like have some pros and cons back and forth is basically one advantage you could say about MongoDB is you can grab a single document, do one disc IO, or maybe a couple sequential IOs and get all that and send it back. And also too, so, you know, SSDs were obviously a thing back in 2011, 12 when this first was written, but maybe weren't quite as dominant, right? At that point in time, there's still probably a good amount of databases that were powered by spinning disks and SSDs were somewhat of a newer thing, right? They weren't just like nowadays, basically every computer you buy, almost every server you run on is going to be powered by SSDs. So, but in the spinning disk era, each individual disk access, if that was a random disk reader, right, was a lot slower. So that was especially important back then, still important with SSDs, the penalty is a little bit lower because technology has gotten better. But that is like a potential advantage, right? And that's something interesting to think about when you think about the design of your schemas. I mean, I'm still very much of the opinion that a relational database is still a better fit for this because there's a bunch of other downsides in this particular example with the document model. And they point some of these out, right? Like if every user, you know, let's say there's, I don't know how many employees Microsoft has, a hundred thousand employees at Microsoft. And let's just say every single one of them has a LinkedIn profile. That means the string Microsoft as an employer has to be stored 100,000 times in everybody's profile, right? In order to load it. Whereas in this one, it depends, right? Like that there could be overlap, but this employment probably then links to a company table where we don't have to store the string Microsoft in all the hundred thousand employees employment rows, but we can store that once over in the Microsoft company entry. And then basically this employment table is just like maybe just two columns basically, right? Like it's a user ID and then it's a company ID. And maybe there's other metadata in there, like the person's description of what they did at that company and all that. But there's a lot of schema advantages to like reducing redundancy and enforcing better structure. The concept of like enforcing your schema when you write data rather than figuring out your schema as you read data is a big advantage of relational databases. So I'm still very much in favor of using a relational database for this example, but I thought that was like an interesting thing to think about, right? And a lot of people that, you know, maybe aren't necessarily like experts in like databases and how the IO and all this kind of stuff works might not think about that, but like the number of IOs that need to take place to fulfill a query that has a big impact on all this stuff we were talking about before, right? Like minimizing the amount of IO you do makes a big impact on this right here. Not very many comments right now. A lot of people watching, which I love, more comments. Anyway, I like this. I thought it was a cool like discussion of trade-offs between those two things. All right, let's see. There was that that I was gonna talk about and there was one other thing that I had on my list. I think I'll skip the parallelization stuff. Yeah, okay. We will look at, so I actually can't stand too much longer. We'll look at one more thing. Go find it in the book here. What page is that? See, somebody asked, I think this is about the example from before. How would the employment table look in case of company name? Oh, in case of custom company name. Okay. So yeah, if you wanted to allow every user to say like, hey, I work at Microsoft via this employment relationship, but you would allow them to customize the name of that employment. Like maybe some people say just MSFT for Microsoft. Some people spell out Microsoft. Some say Microsoft Incorporated or whatever it is. You probably, in this case, would just add another column here. That's like custom employment string. And maybe it could be left null if they don't want to customize it, but it's applied if they do. Okay. Someone was pointing out, I mentioned earlier, oh, we have Michael Scott in the chat. Nice. The way I remember is schema on read versus schema on write. Yeah. It's basically like when, not even enforced might not be the right word, but yeah, it's like, when is the schema enforced? In a relational database, it's predefined and set. There's exceptions if you use JSON columns and all that, but that structure, while sometimes annoying, is generally actually good. Like usually you want that structure to keep things organized and neat and sensible, whereas MongoDB and other similar document databases let you play looser with that, which has its pros and cons. Okay. Last thing. This was just kind of a nice little summary, right? This chapter two did a lot of comparing and contrasting, right? Relational versus document, the older model of using hierarchical stuff. There's also the, they talk about some other more specialized things. There's graph databases, which have their use cases, right? And other different models. And they were talking about how they showed an example using a graph database. I can't remember if that's over here. Okay. Yeah. I think it was the previous page. They talked about a query, which was basically like, find all of the people in this graph database that live in the United States, but emigrated from Europe, something like that. And using, I forget exactly what it's called, because I don't think it's Sparkle. I think it was a different syntax. I think it was like the Neo4j syntax, whatever they use. But using graph syntax, you could specify that relationship or that search in like four lines of their query language, which is very convenient, right? But then they were also talking about, say you tried to model this same sort of graph system with people that can live in certain cities and cities are in certain countries and countries or states and states are in certain countries. If you tried to model that in a relational database, which you can, like there's lots of relational databases that model people and where they live and who they're related to and all that. But then if you tried to query a very broad thing, like every person who currently lives in the United States, but at some point in the past lived in Europe, that's kind of a complicated network problem, graph problem. And so they showed over here, like basically the SQL that it would take to do that using CTEs or recursive common table expressions, recursive CTEs. And even with that feature, which at least at the time, not every relational database supported, it's like 20 something lines of SQL and it goes up here as well, right? So the point that they're making basically is like, hey, you might have your favorite database or query language, whatever the case may be, right? But sometimes it is worth using a new database or a new tool if you have a particular kind of problem you need to solve, right? I love using things like MySQL and Postgres. They're very good OLTP databases and they can also serve as general purpose databases. Like pretty much any data set, graph data can be modeled with relational schemas, document, like even these databases support like JSON columns and things like that. And you can query and filter on them. They're extremely powerful, but sometimes it makes sense to, in addition to your core Postgres database, MySQL database, choose some additional tool. Like if you have a lot of data that is just by its nature, is very graph related, and a lot of the questions you want to ask about it have to do with graph traversals, and that's how you need to answer the questions. As much as it might be annoying to have to add another piece of tech to your infrastructure, sometimes that's the best thing to make your app as efficient as possible, to make your developers as efficient as possible, is to choose the right tool for the job. Similar to what we were talking about earlier with, what were we talking about? Like using something like Clickhouse when it comes to like really time series heavy data, like click events, or log events, or like streaming metric type of data, right? Sometimes, like you can, you could use a plugin or something and use it with Postgres, or you could just, you can do that in Postgres, like it works, but you might be suffering a little bit on the performance side, and need to like bump up resources when you could use a tool that's actually better for that job. So, let's see. Harsh Deep reposted. Hi, Harsh. Welcome. Thanks for posting. All right. Well, this has been fun. I need to log off soon, but I'll hang around for another minute. Are there any questions? I know some people are seeing this at a delay. I think on YouTube, it's delayed like 20 seconds or something. Here's a good question. What point do you start considering other tools beyond your normal set for what you do typically? Well, what I do typically is help other people do things typically, because I work in the dev ed space. So, what I'm often doing is either writing, you know, guides for customers, documentation for customers, making videos, or meeting with them directly, or helping them directly and, you know, through various channels and stuff. And so, often it's not necessarily what I'm doing, but it's about trying to help somebody else. And again, like my opinion, I know you will say I'm biased, but pretty much every application on the internet today needs an OLTP database, right? A database to handle user accounts and metadata and events that take place and all this kind of stuff. And either MySQL or Postgres, those are the two most popular choices, are like the place to start. But sometimes, as you start to scale up or as you start to build out more features, you do want to add an elastic search for just arbitrary text search if you have lots of documents that you need to like be able to search through, right? Or if you have big like sequential ingestion stuff, maybe you also want to add something like Clickhouse to that. So, it's like a pairing, like you need to choose the tools that pair well together. Here's an interesting one, any common misconceptions or misunderstandings you commonly see mid to senior levels get wrong? Interesting. I think my perspective is unique, because one interesting thing is when you work, this probably is the case for lots of people, but you work in the databases space. And sometimes you work with another company that has like dedicated database administrators and all this, and they're extremely knowledgeable, maybe more knowledgeable than I am about how, you know, internals of MySQL or whatever, right? But sometimes you work with companies that are a little bit smaller, maybe there's only, you know, three engineers, five engineers, something like that. And they don't even have a dedicated person to focus on databases. And that's a lot of the times why they like to have a managed database. But it's interesting, because that's kind of something where you can have an incredibly talented engineer, but just because they don't know that much about databases, you have to be able to sort of figure out like what's their level of knowledge, and then meet them where they're at and give them the resources that they need. So that's kind of a fun part of what I do. This was fun. Can we do all the chapters? Seems like this went well. So if all goes well, we'll see when I make it through chapter three, maybe we'll do another one for chapter three. Can we leave this as a rewatchable video? Like a reupload? I'll have to see. I think by default, it gets left as rewatchable, at least on like Twitter. But it probably does also make sense to chop it up. So we'll see. We'll see. Have I worked with graph databases in production? No, actually. I've never really used significantly. I like played around with them a little but I've never like used Neo4j or any of those kind of things in production. Okay, so yeah, maybe this is going back to the ORMs discussion. Yeah, good point there. Is the only engineer at my org indicated right now? Nice. Yeah. Yeah, keep chugging along. What do you think of application databases like Convex? Convex is awesome. I actually, I will say I haven't personally built an app with Convex. So I can't actually talk about the specifics of it. But from everything that I've heard from lots of very, very smart people, people that are very good at building applications, it's an incredibly powerful tool and a very, very good product, and a very good way to sort of get a more all in one back end, which maybe at a certain point, you don't want, but especially like early on, or for small engineering teams, but that want to like scale up quickly. Sounds like an amazing tool. Wondering if there's a company out there working with graph databases and prod? Yes, I'm sure there are. It just, like I said, it all depends on what kinds of things you're doing. For example, I actually don't know if they still do. I believe GitHub for a long time was running some form of graph database to power like a lot of their API requests. They have a pretty robust API that you can access for free, which is really cool. And it may not be the case anymore. But I think for a while, they were using a graph database to power a lot of those things. Okay, I'm gonna wrap up. Thank you for coming, everybody. Appreciate it. Thanks for hanging in. We got to 449. Wow, a lot of viewers. So we'll try and do this again sometime. See you later.