Coding Blocks - Picking the Right Database Type – Tougher than You Think

Episode Date: February 5, 2024

You asked, we listened! A request from one of our Slack channels was to go over the various types of databases and why you might choose one over another. Join us in another information filled episode where Joe won’t be attending the event he’s been promoting and Allen tries to keep his voice together for […]

Hey, we can actually do the intro this time unless Outlaw Edison said this is the intro. Whatever you want to do. I've been beaten down at this point, so I will go whichever route we want to go. All right. I guess we should. Yeah. You want to do it? Sure. All right. You're listening to Coding Blocks, episode 227. Subscribe to us on iTunes, Spotify, and more using your favorite podcast app and leave us a review if you can.

Visit us at codingblocks.net where you can find our show notes, examples, discussions, and more. And send your feedback, questions, and rants to comments at codingblocks.net. Follow us on X at Coding Blocks or head to www.codingblocks.net. Follow us on X at codingblocks or head to www.codingblocks.net and find all our social links there at the top of the page, including Slack, which we forgot to put in here. So you can head up

codingblocks.net slash Slack and become a part of that amazing community. And with that, I am Alan Underwood with our old intro. And I am Joe Zach participating in the intro. Beautiful I am Joe Zach participating.

All right. Beautiful. So Outlaw is going to be out this episode. He'll be joining us back here shortly. But so this particular topic came up from a chat with, oh, man, I had it in the show notes somewhere. Where did I put it? Brantley. Ah, Brantley. Yes, Brantley. Yes.

Brantley and Slack. He was like, Hey, you know, we get these questions about why should we choose this database or that database or whatever. So this episode and consequently next episode, because there's so many different types of databases, we're going to be talking about various different types of databases and why you might choose one over the other. But before we get into that, somebody's got to butcher some names here. All right. So we've got a couple of reviews here. We'll go with I... Oh, jeez. Sorry. One sec. iTuneses? Okay. For iTuneses, we've got

Ivan Kuchin and Mike W717. Thank you very much. And, oh, geez, hold on. Spotify. Got Darren Pruitt and Chutney3000. Thank you very much for the reviews. You know, we love that. We love those stars well that's so amazing you all know it's funny that ivan kuchin he was on the previous episode as well and outlaw was struggling he's like so he wrote another review it was like you know just trying to help outlaw butcher some more names so yes thank you very much for leaving those and uh you've got something coming up here soon that you won't actually be at yeah i'm gonna have to miss it so that's our land of code camp coming up at uh february 24th and i'll be living in georgia then so woo and also uh right and also uh for having to move and do all that that's yes yeah for sure not fun no but you should go it's a great I think it's free event. I think it was last time we went. I mean, it seriously is one of the better, like, CodeCamp type events that you can go to.

It was excellent. Yep. And it is free. It is free. Okay. And they provide lunch still and all that, right? Do they still do the t-shirts, handing out the t-shirts when you walk in? I believe so. I was helping out a little bit, but I had to duck out for moving stuff.

So I don't know where it landed. It depends on basically if they've got enough sponsors or not. Hopefully, free t-shirts. It would be the first year in many years that didn't have them. I bet they're there. I can't promise it. If you're within

an hour's drive of it, it's worth going to, I'd say. It's pretty good. A couple extra things here i just want to give a kudos to dell on so i have i have poo-pooed dell for some of their workstations because i've seen so many issues with them over the years and and so i want to at least give them the fair side flip of that is I bought a Dell monitor. It's a 34, 35 inch ultra wide, whatever. Right. And I've had it just about a year and I got this.

It's not, I don't know if you call it a dead pixel. It was like a constantly living pixel. Like it was just super bright and always ultra white right in the middle of my screen. And it was driving me absolutely crazy. So I went and looked up the information on the monitor. It actually came with a five year warranty, which was shocking to me. I don't know of many things that do. But here's where the the pat on the back to them goes.

I contacted tech support via chat chat gave them the service tag they asked me to do a few things did them send them in i had a replacement monitor at my door the next morning like and with instructions on how to pack mine back up and send it back to them so you know that is fantastic customer service so you know you know, big, big kudos to them. And the new monitor or the replacement monitor, don't know if it was refurb or what. Don't really care as long as it works, right? And I still have another four and a half years on my warranty. Yeah.

Just fantastic. That's like Costco levels of customer support. That's great. Right? Like it was truly, it was surprising. So, so, so anyways, really, really pleased with that. The next one is, you know, I've been talking about the past few episodes, you know, the cat eight type stuff. Well, tomorrow I will officially have everything in, I have the server rack or the network rack. I have, I have the, all the connectors, the connectors, the crimpers, all that kind of stuff.

So tomorrow I get the 1,000 feet of Cat 8 cable that I've been waiting a week and a half to be delivered. So, yeah, here pretty soon I'm going to be starting that journey. So that should be interesting and fun. Yeah. Fun. Nice. Yep. Speaking of journeys, I'm about to go on a journey i've

gotten rid of a lot of my like home office furniture i used to have like uh you know those big square uh ikea shelves look everyone's seen them right they're like the ones have like 12 inch by 12 inch yeah cubes uh i had i've had two of those forever and it's depending on the house i was in the situation it's been my clothes dresser it's been my you know bookshelf it's depending on the house I was in situation. It's been my clothes dresser. It's been my, you know, bookshelf. It's been like everything. And I got rid of my I just got tired of it because they get a lot of dust in the little cubes. And so unless you're like really, you know, in depth with your cleaning, like going behind your books on the shelves and stuff, it's just they're so open. They just catch everything and it just gets really gross.

And depending on where you have them, like you can't easily pull them out and stuff and so stuff gets behind it and i was just like i'm done with this i just want like clean and simple and so uh i got rid of them and i don't have anything else yet so i'm gonna get to a new place and then figure it out but i also have been thinking a lot about my kind of office setup and I am kind of sick of having stuff all around me. Maybe it's like from the move and having boxes and everything. Just a bunch of like half done kind of tasks like all over the entire house. You know, I'm just kind of ready for simplicity. But I've been thinking a lot about setting up two separate desks.

One for home and one for work. And no KVMs. I just have two stupid monitors. I have two stupid keyboards. I have two mice. No switching, no more weird, like, hey, why is the camera? Oh, it's still on the other one. Like, just two setups.

And for some reason, that sounds simpler to me. Maybe I'll feel differently about it. You know, I don't know. But I was kind of curious just to hear what other people do and who might have a similar situation. I'm going to have more space at the new place. So I have the room for, you know, but I was kind of curious just to hear what other people do and who might have a similar situation. I'm going to have more space at the new place. So I have the room for, you know, two setups. But I don't know if I'm actually making my life any easier by getting two of everything.

So I'm sure other people have some pretty good takes on this, but that's actually what I do. Like I have a spot where I have my work stuff set up. And when I go set it up, like it's just set up for work. It's really easy. I don't have to figure out anything. I just sit down, turn it on and it works. And the same thing down here where I do my podcast. And if I'll do some video gaming, it's set up for that.

Like I don't, there's no, there's no magical switches. I don't have to change anything. I just hit power and power and it's good. And oddly enough, even though it is two setups, it does simplify things because you don't have to figure out, oh, what did I change here? What did I do here? Because I needed to make this work for that. It's just it's there and it's done. And I love it personally.

Yeah, I'm looking very much forward to that. But the podcast, we have like kind of more advanced setups than I think a lot of people do. So like I've got these big roadcaster kind of mixture things that take a lot of room on the podcast. We've got like the boom arms and the big microphones for the podcasting and stuff. And so just having all that stuff in your face when you're trying to just like work on a ticket or something for work is just annoying. So I want to have just more space for that and and same on the flip side i don't want to have my uh my space heater of a work laptop like crowding up my stuff i'm trying to you know uh get on get on the podcast business so i'm looking forward to i don't know but i was just kind of curious whether people thought especially you know it might be different

if there was like a single button i could push like on a kvm that actually worked and switched usb devices and hdmi and everything was great and it wasn't like one thing is somehow left behind or you know whatever yeah that that is true for whatever reason kvms have like i guess since the advent of a bunch of usb devices they've never worked perfectly yeah which is bizarre but yeah you know it's dumb like even on like the input on my monitor i gave up on kvm so i just pushed a little button one down down over to the right you know i've got to memorize like you know tap tap tap tap tap tap and now i switch my input sometimes it just

doesn't work and so i gotta do it again i gotta turn it off turn on like why doesn't it like mess with that you know it's weird stuff and like sometimes i'll flip back to another computer and oh the mouse isn't working i've got to go crawl under the desk and like unplug replug in the mouse and the funny thing is like the mouse in particular i gave up on sharing mice so we've got a trackpad for work and i've got a mouse for home and sometimes the mouse just stops working like i didn't even switch you i didn't say you're plugged in the computer and they're both sitting there in the same spot like yeah i gotta like shuffle so every time i like switch computers i gotta like move these four things to the left and push this button five times and then swing the boom arm you know it's like i'd lose my mind absolutely lose my mind yeah i i think that

having two dedicated setups actually makes things easier and being that you play guitar and stuff it yeah it would make sense to because i'm sure that you hook all this stuff up via all that same if you had that in a room that was dedicated to podcasting and you know jamming or whatever that makes life a whole lot easier too yep and there's gonna be a lot of cables and whatnot in that room but they're all useful so i'm i just like the idea of having that stuff like all ready to go so i can just sit down and go. And then like that's my little like Zen area. And the other area is, you know, not as in at least it's like, you know, it's clean.

Like everything there is for that, you know, like everything is in its right place and I can just kind of focus. You know, it's funny that you said that about like the boom arms and all that stuff. I have found that for work, the less stuff in my visual peripheral view, the better. It's really bizarre, but it actually does help me. So, yeah, it's not like maybe it's just like spending, you know, like several hours in a row. Like you almost feel caged in or something. Yeah. Just like having something in your face for hours is rough. you have stuff around you all day yeah it's crazy well yeah good luck on the uh on the new office i know that you were gonna like put down carpets and all

kinds of stuff to to stop um the acoustical things really yeah i definitely am interested in that but also just like keeping things like easy to clean is a big deal. Like I like the idea of just being able to clean my room with like a leaf blower. You know, this sounds great. That's amazing. Yeah. Just get the coffee cup out. I don't know that my wife's going to go for that one. Yeah.

All right. We'll try it. We'll try it. Yeah. Let's see how it goes. You let me out. Yeah. Let me know how that goes.

I don't think it's going to work out well for you either. All right. So getting into the topic here that Brantley brought up is again, all the types of databases out there and, and you know, why would you choose this one or that one? And we're going to, so before we even get started, right? Like this is one of those topics that people are hyper passionate about, right? Like, so whatever we say here is our opinions or, you know, our experiences with things. And generally speaking, I don't know if this has come across

over the decade that we've been doing this show, I'm not hyper passionate about any one technology never have been like, I love C sharp, but does that mean that I'm going to hate something else? No, I kind of like Python. I kind of like JavaScript. I like Kotlin. I like, and the same thing when it, with databases, right? Like I'm not in love with any particular technology. So, you know, at least going into this, know that when at least my opinions on the things that we're going to be talking about are just kind of like what I've experienced and where I see things happening. So I don't know if you want to give any preamble, Jay-Z. I'm picky. So this should be fun.

I respect your viewpoint on it it and i recognize that it's probably the correct one to have but uh yeah there's some things i don't love uh so this this should be fun so yes please anybody like anybody that's listening you know i'm sure and and actually i think this will make for fantastic discussion so please by, by all means, like we, we don't usually say, Hey, come leave a comment on the episode or whatever, but I think this one would actually be great for some, some conversational type stuff. So, you know, head to coding blocks.net slash episode two 27 and drop a comment on why you think one would be better than the other. So also another, I guess, preamble to this, there's 12 database types on this website. So we're using this resource called db-engines.com

and they have a ranking page and we'll have a link in the show notes. But if you're listening db-engines.com slash en slash ranking. Now this globs everything together every different type of database together and then sorts them based on popularity now where they get their popularity from i have no idea but it looks like a reasonable ranking to me i i assume to you probably also jay-z right yeah yeah so we're going to be talking about these things. There's 12 different types. We're only going to do six this episode because this episode would be 12 hours long. So you're about to say something. I'll save it. Okay. All right. Okay. So a few things in this goes back to designing data intensive applications, which I think even to this day is probably my

favorite resource we've ever covered on this podcast i think jay-z is probably up there for you also yep yeah so one of the things that they that they sort of mention in just about every type of system there is when they're talking about data is their schema on right and their schema on read and the difference is the schema on right. You have to have a schema defined up front. So any kind of relational database you've ever looked at, you're going to have a table and all that kind of stuff. Right. That schema on right schema on read is, hey, your application has to know how to deal with whatever you get back.

Right. Like you don't know what's coming back. It could be anything and you just have to know how to deal with it so i tried to put this on every one of the types that we have here to just sort of you know mentally help you understand like when you go into this particular database type this is what you're dealing with so with them on right sucks by the way really first one yeah oh like there's times when you have to but you like you don't do that unless you have to this is gonna be fun all right let's get that out there yeah

so the very first type of database here and this should be no surprise to most anybody that's dealt with any kind of data are relational databases also known as as RDBMSs. So the very first thing on this ranking page, their most popular ones in order, and they give five for, or they, if there's five or more, they'll give like five for the popular. Now this is in the order that they're ranked on the page, but I want to point something out here. So the most popular ones are Oracle, my SQL, Microsoft SQL server, Postgres SQL, IBM DB to snowflake and Microsoft access, which I thought was weird that it was actually in the top, whatever. Yeah. Now here's the part I want to point out. Oracle, mySQL, SQL Server, and Postgres are one, two, three, and four in the overall list.

So of all the databases out there, those are the four very most popular, period. So four of your top whatever are relational. So schema on right, and now let's talk a little bit about this thing. Yeah. First thing I say, you know, I haven't used Oracle in 15 years, but I've still kind of turned about it.

You know, try and let it go for the show. You were working on Oracle to I or something. Who knows? It's like a whole nother world. You know, like I mostly mostly most of my experience have been in uh sql server at the time and like going over to oracle it's like oh yeah i

know database this is fine like what is this what is this editor why why can't i do top you know and you know obviously like uh you know well maybe it's not obvious but uh a lot of things that i the differences i was frustrated with i now have a better understanding of uh but uh it's not obvious, but a lot of things that I, the differences I was frustrated with, I now have a better understanding of. But it's at the time, it just really, really got on my nerves. And so I've never let that go. I never got used to it after like, I don't know, six months or a year of using Oracle. I never got over it. That's funny.

Yeah. I mean, Oracle, I think still to this day is one of the big ones used in enterprises, right? Like it's massive. If you're the big ones used in enterprises, right? Like it's, it's massive. If you're not a Microsoft shop, especially, right? If you're a Java shop, you're probably using Oracle. Now, a couple of commonalities between these, right? Typically speaking, your primary language is going to be SQL. Now, this is where what you were just saying comes into play, right?

Like everybody has their own flavor of SQL. So there's anti-SQL, which is like the broadly supported version of the language. And then there's, you know, I think Oracle was PL SQL, right? Or was it P SQL? I think so. I think it's PL and T SQL for SQL Server. T SQL SQL Server. And then I think Postgres is like P-SQL?

I can't remember. They all have their own flavors, right? And this is what like Jay-Z was talking about. I remember way back in the day with Oracle, like you didn't do inner joins the way that you do with SQL Server, which in my mind, the way that they did it in SQL Server made sense, right? Like this table, the way that they did it in SQL server made sense, right? Like this table enter join that table. Um, and then you'd have your, your on condition to tell it how to join this stuff in Oracle. I think at least back when I was working with it also a long time ago,

like your join sort of happened in the where clause. And it was really bizarre to, to mentally map that stuff out coming from a SQL server world. But the key parts of this thing are their schema on right. And what that means, and probably most of you listening have had some sort of experience with these, unless you've just been living in like a Mongo world. But if that's the case, in a relational database you create a table with a name then you give it columns with specific types you know like um let's say you have a person's table and and that also by the way is always a thing that will will throw people into a flame or is it a table that's a singular name or is it a plural name? Because each record is singular, but,

but it contains plurals of these. Like, do you put the S on it or not? Like I've heard so many arguments about that. Yeah. And you know, what's funny is a lot of times if you have a, you sort of like plugins or depending on your architecture,

a lot of times that plugin will create its own database or maybe create its own tables in your databases and its standards may not conform to yours. So it's like this huge, ugly, red, sore thumbs kind of sticking out if you ever have to deal with that. Yeah, and it might all caps it where yours were all, you know, what's the casing that starts with a capital? It's not.

Oh, title casing? Title casing, yeah. Yeah, so I mean, yes yes there are things that can happen when tools generate things that will just drive you crazy but so you have your table then you have your columns right and then you're gonna have to put a type on it whether it's a var car and how many characters you're gonna have in the var car or whether it's an n or a float or whatever so you have all these things that you have to define up front and this all seems kind of good, right? Ish until you start getting into things like, okay, well we need addresses in there. So, you know, as, as a newbie, you're going to be like, all right, cool. So we're going to have

a street address, a city state zip, right? Like, cool. Done. Oh, oh we need we need a second line address now oh okay so let's put that in there well that kind of stinks because almost every every record in your in your table is going to have an empty second line for the database and you're like well that's just wasting space because it actually takes up space on that on that row in the database and so yeah you start running into this thing and this is why i think jay-z is probably like uh schema on right kind of stinks right because you're stuck with no no i so wait did i say schema on right sucks yeah that's what you said oh man i'm so sorry i meant schema on read sucks no i'm sorry so you want schema on right it's great you gotta figure it out i'm. So you like the relational database model?

Oh, for sure. It's really flexible. It's got a lot of the relational databases have a query optimizer that handles kind of shuffling things around, so you don't have to know so much about your application and how it's going to grow. Yeah, and there's just a lot of nice features. Because relational databases are so popular, there's been a lot of nice features. Because relational databases are so popular, there's been a lot of evolution, a lot of money in that ecosystem that's led to really cool features. Yeah, and even things like, you know, we mentioned history already,

like ways you can kind of query relational databases in ways that would normally be awkward with, like, contexts, where CTE is, whatever I forget what it stands for. It's just some really advanced features. comment that's right it's just really nice and store procedures and functions and i don't know that world is just very comfortable to me like it works how i want and i i like that structure you know i like that we've all agreed that uh you know addresses are varchar 255 and if you exceed that well that you know that was the rule i'm sorry and you say varchARCAR, not VARCHAR.

Correct. Because you're not an insane person. That is fair. All right. Yep. All right. Although, yeah, although we don't say CAR-actor, but we also don't say CHAR-actor. So, you know, yeah.

So I'm torn on it. I am torn on it. I do like the schema on right here where you have to define the things up front, but it does lead to some frustrations and it leads to crazy database designs to normalize things to what is it like third normal form or second normal form, whatever, like it can get into some ridiculous, uh, runtime type queries that you need to do that could be inefficient as heck right like when you're having to join data across multiple tables you're incurring overhead yeah and like this one it's i'll probably butcher example here but if you

there's some things that you can say in english very easily that's hard to do in like a normalized database like if you will say like i want to see products that were bought by people who bought products by this group of people or something like that. And then like, oh, now we're joining to, you know, things like multiple times the self joins and it just gets really nasty really quickly. And it's something that like it's a concept that like makes a lot of sense. You say, well, like, well, I want to know what kind of cars people in Florida bought that. I don't know. I'm going to screw it up. But you know what I'm saying? Like there's things, there's use cases you can think of that sound like they should be easy and really aren't in relational databases if you've got it normalized in the

kind of the standard way. And we'll get to a type of database here shortly that does allow for what he's saying. So now let's talk about one of the probably the the biggest sore spots that i think anybody that's worked in relational databases for a long time will run into and that's the scalability like generally speaking without being creative if if you have a sql server an oracle whatever, and you're now, you've grown to the point to where you're getting billions of records and you're using this thing, and let's be honest, everybody who has already BMSs,

that is their hammer and everything in the world is their nail. You start running into performance problems. And the biggest issue with most relational databases just out of the box is they only vertically scale, meaning, and I say only, only is a bad word for it.

The primary way to scale them is to add more resources, more Ram, more CPU, right? Like add more horsepower to it. Yep. That if you're sorry, well, just to finish the thought. And the reason I say not only is because there are things like with SQL server, right? Like you can

create a cluster. And usually in that cluster, you're going to have read only type things, right? To where you don't write to those replicas, but you can read from them. So you have these fast read areas that are all sort of getting data synced from the primary, but that's sort of a bandaid because that's not really scaling out. Like you're just making it where some of the read performance in areas are better. And it also allows for failover. But again, the primary thing is just adding more resources to it to make it happen. All right. So sorry, go ahead. No, I was just going to mention, uh, horizontally scale, horizontal scaling, uh, is, you know, available in certain flavors of, uh, relational databases. And it has a lot to do with how you

set it up. So you have to know about the kinds of data that you're storing about your data patterns and usage patterns. And so you can partition it nicely. And, uh, that that's, um, you start to lose some of the benefits of things like i mentioned with the query optimizer that can kind of do things after the fact then like smartly arrange and query your things in an efficient way like now all of a sudden we're saying no actually the developer has to do some of that work up front because that's how we need to be able to kind of split this stuff into pieces yeah and it's i actually have a link to a pretty cool article here uh from design gurus and it's talking about ways to scale your SQL database.

And when we say SQL, SQL is always just sort of been interchangeable with RDBMS, even though that's not what it is anymore, because it seems like every database technology out there has a SQL implementation, but you end up like, like Jay-Z said, you're sharding data and you're doing all kinds of crazy things. And if it's not built into the database engine to handle that stuff for

you, man, there's a lot of code to make that happy. And it's also a pain to keep happy. As a matter of fact, I want to say that I read a, um, a tech blog from Instagram a long time ago, like there, when they first started off, that's how they did it. They had a database. I don't remember if it was my SQL or Postgres, it was one of the open source ones, if I remember right. And they realized that they couldn't, they couldn't handle it. So they had to create their own custom sharding and, and that's how they ended up scaling. And that's a lot of work. That's not, that's not, okay, let's turn on the sharding option, right? That's not how that works. So it is a big thorn in the side of a lot of relational databases

until you start getting into cloud offerings like Azure Cosmos DB or SQL Spanner and Google Cloud. I'm sure AWS has their own version of things as well. I think about that article, and it's from 2012. It's pretty funny. It's on the Instagram engineering blog, and they say, with more than 25 photos and 90 likes every second, we store a lot of data. I'm like, oh, I wonder what those numbers are now.

Oh, man. They would probably kill for those numbers, though. Oh, wow. That article was that long ago? Yeah. Jeez, man. That's a pre-facebook acquisition right yeah right around the time i think yeah that's that's crazy um like 25 images per second like i mean i could do that right now i could do that just on my cell phone right now that's publishing the story about you know going to Target. That's amazing. Stanley Cup.

Oh, man. So one other thing about SQL databases is there are a lot of tools built in to make them pretty performant, right? So you have to understand it, right? You have to know about indexing and the different types of indexes, clustered versus non-clustered

and the ordering of the keys and all that kind of stuff, right? But you can do it. You can absolutely do it. And for the most part, until you get into massive amounts of data, they're pretty good. And, and I don't know about you, Jizzy, but it was always my experiences. Everything would be just perfect until there was like, it was like, there was that tipping point. There's that one record that went in there that sent it was like there was that tipping point there's that one record that went in there that sent it over the top and now nothing worked off cliff yeah and and i don't know what that was about but but that always seemed to happen at some point with any databases it grew large so by the way i looked uh i looked up to see what it is now people speculate based on the

total number of images and yeah yeah, you got it. So this is not a great number, but they estimate it's about 1074 per second now. Wow. So 44 times higher than when they were with the article like, we deal with a lot of data. Now, now we deal with a lot of data. Man, that's absolutely crazy. One other thing I want to bring up here about rdbmss in particular i did i didn't even have it in the show notes but i was thinking about it as we were talking these databases have truly grown to be do-it-all things so

we're going to be talking about all kinds of other database types here in a minute. And the reality is the relational database, like SQL server, for sure. We worked with it for years. Like when you talk about document databases, they have things in there that work like a document database. When you talk about graph databases, they have things for that they have analysis types like they have so many things that they've baked into it but i want to at least caution you to taking advantage of all that stuff because it's almost like they bolted on right like it's almost like a frankenstein i'm not saying that they're not performant and they may not work decently well, but it's not the natural evolution of how that stuff went. And so you'll find yourself working really hard to do

things that would be simple in other database systems, even though it supports it, you know, it's just, it's not always the best idea. Yeah, for sure. And I was just reading the article that you mentioned. And one of the things they really emphasize is how they try to keep things really simple so that queries can be simple. So use cases can be really simple and everything's easy to maintain, but also easy to kind of program around and like just minimize the traffic to the database. And when you start talking about like different like normalization schemes and how you can break your data apart and to be able to query it efficiently and uh keep your you know data basically uh integratist if you want to maintain your data data integrity uh then simple is not a word that you usually hear in terms of relational databases that's simple model and relational databases did

those concepts are at odds they really are and you know the funny part about it is i think i think the reason why so many people like relational databases besides the fact that they're so ubiquitous is it's easy to mentally map what you're trying to do right yeah but like what you just said when you're trying to make it performant so that you can scale now you have to think real hard about how you're trying to make it performant so that you can scale, now you have to think real hard about how you're going to access your data, like what needs to come back when this request is made. And that requires way more thought because you're trying to solve a problem that the relational model doesn't necessarily work right with. Yeah, you really need to design all of your

applications and your kind of business around that data model if you're going to be a schema on right and so like you imagine like instagram like my relational brain what i think about instagram is like okay well i need a user id that user id has a feed that feed has uh entry ids associated with it and so by the time you imagine like the query that i would write to just populate the kind of the basic view there's probably like seven or eight tables in there yes and that's going to be normalized it's going to be great and my database teacher would have been proud and no way would that work without the scale that they're dealing with that at instagram and so what they really need is they need to be able to see like feed.get 10 yes you know and they need to get the items in that feed right then.

And everything that needs to happen to arrange that stuff and put it in there in the right order and, you know, sell all that stuff. That all needs to be handled by someone else because when you're rendering that request and you get so many of them, then you need to be able to keep it as simple as possible. Because you imagine like 1,074 images shared per second, like how many are viewed? Right. Whoa. Whoa. Yeah. It's another story. It's insane.

And you can't be querying the database every time for that. It'll crash. It just can't do it. So here's something that may be interesting, fun. Do we want to sort of rank these as to each type of database type? I don't know if that'll work. Like it, love it, neutral on it, don't like it, know if that'll work like like it love it you know neutral on it don't like it hate it do we want to do something like that or we want to skip that

um maybe what if we said like how common it is okay yeah well that make more sense yeah i think so let's do that so this one i think being that it has the top four spots yeah yeah probably the most common that you're going to see anywhere yeah i agree definitely um this next one's coming up though yeah this next one's coming up i'm trying to think if there's anything else about these uh that is worth saying i don't i don't know that there is i mean i would say uh that this is probably the default still most businesses so if someone comes to you uh at a bar or a party or something, and it says like, Hey, I got this idea for an app. You're probably going to need a relational database for it. Unless it's Instagram or unless it's kind of special case,

if you know nothing else about the application, other than you need some sort of application with the database, it's probably going to be best served by relational. Yeah, that's probably fair. And keep in mind what Jay-Z said a second ago about like, you might have nine tables just for some standard thing. Like even if you think about a shopping cart, like we've always kind of gone back to that because it's sort of easy for people to reason about you have an order, right? That's, that's going to be one table, at least maybe more, but for simplicity, you have an order, you have order details. So that's the items you ordered, right? And that's going to have the amounts on them and all that kind of stuff. Then you're

going to have the person, you're going to have the address you're going to have. Um, what else might you have on there? I don't know. That's four right there. That's, that's probably good enough. Let's say you have four to five tables just for that. Keep that in mind because when you're trying to paint the page page when you pull up that person's order page it's going to hit all five of those things to paint that page right so that's that is that is where you start running into performance things because if you have a thousand customers trying to pull up the order at the same time that's a thousand hits to the database going across five tables right so so you know that's that's a whole lot that blows out so now here we

go to the next oh no this no, this is the next one. I would say so before you go on, the nice thing about that kind of complexity of what it buys you is that somebody from marketing can come and say, hey, I need you to start showing the coupon label in there. And when the coupon expires and sort of, oh, well, okay, well, now I need to know the coupon ID. I'm going to link to the coupon table and get that information from there. Okay, so I've just added a sixth table to that query. That's not that bad and it works great. But if you start thinking about, well, if I had stored that as a whole document somewhere, I said that feed get example, cart get example, or order get example,

and we don't have that coupon information in there, now it's like, okay, well, we've got our original query and that's staying there because we don't want to do a giant migration project to kind of get that coupon information in there. So now we're going to have to have another process that's going to run in parallel to go get the coupons and then we're going to try and intersect these guys somehow because joins don't really work so well if I'm so highly optimized around a document and the data I need isn't in that document, you know, you start jumping through hoops. Yeah. So there's flexibility there, but yeah, it comes with a cost.

So, all right, man, I keep saying that when we get off of it, I mean, we spent so much time with relational databases, but one other thing to keep in mind is they come with challenges, right? Like the whole purpose of a relational database is if we go to the order and your address type thing, right? In a system, if you're using a relational database for your customer information, you have a customer and then they have their, let's just make it easy, there's their shipping address, right? Okay, cool. It's sort of a one-to-one thing. Well, if that customer places an order now, you don't want to point to that shipping address anymore. You need to keep a, an actual like stamp

of what they did because that can never ever change from that point on, right? Like when somebody places an order, that is a snapshot in time. So now you actually have to think about another storage approach to where maybe you have an order snapshot address table or something, right? So now you're duplicating data, even though the whole point of a relational database system is to not do that. So there are challenges that come along with that. And some of the things that we're going to talk about here later on in this show will sort of address that with different storage technologies. Yep.

So next type of databases that we're going to be talking about is key value stores. Most popular here, some that we've talked about several times, Redis. I should mention too, before we go on to the rest of these, a lot of these databases have overlaps. They have different types that are supported, different models. Sometimes you can query them in more than one way.

Sometimes that means that you have to kind of set up ahead of time to be more specific and kind of tune it that direction. And sometimes it just kind of works that way. So everything we hear, let's say pretty much from here on, for those to show, is going to have a little asterisk because it can kind of function in more than one way yes and that's true of relational debates databases a little bit uh but not to the same extent not different level uh so most popular here redis uh that's definitely one of popular one used for caching but also for all sorts of other things now dynamo db would be here

in that name a lot azure cosmos db, another one, kind of a Swiss Army knife. Memcached and etcd. Yeah, so these are interesting. And first, let's get to the important part. These are schema on read, meaning you basically put whatever you want into it. Whatever pulls that data out is going to have to know how to use it, how to recognize whatever it got back. But I want to, I want to hit on one that I thought was just fantastic. So Brantley was like, you know, it'd be awesome to have an episode talking about this stuff because, you know, I'll sometimes get somebody to be like, well, Redis is fast. Why don't we

just use it for everything? And this is where the devil's in the details, right? Like Redis is fast. Why don't we just use it for everything? And this is where the devil's in the details, right? Like Redis is fast. And the reason it's fast is because it does everything in memory. So the problem with that is it's not by default, it's not persisted to disk, right? So the whole point is a lot of times, like when Redis first came around, it was sort of used as a cash for everything, right? Because you put a key in there and then you put whatever you want into the value and now you have fast access to that value. Well, Redis has grown over time. Like Jay-Z was saying, all these things end up being Swiss Army knives of some sort. Redis now has like search engine type features and all kinds of things that you can enable on it, right?

Like adding plugins to it. But there is a feature in it that allows it to write its state to disk on occasion. So that if you have some sort of catastrophic outage or whatever, when it comes back up, it could actually rehydrate whatever state it was in. But that's a periodic thing that doesn't guarantee you that, you know, if you're trying to use this thing for your ordering engine, you're probably going to lose data, right? Like it's not made for that. It is made to be a fast in and out type of data access type thing. So, you know, it's not easy to just say, Oh, well we should use Redis for this or we should use, uh,

Amazon Dynamo DB or Azure Cosmos DB. Although if you were to read any documentation on Cosmos DB, they'll basically tell you it does everything on the planet. And from what I understand, it does it all pretty well. So maybe it does, but you probably going to pay for it too. So.

Yeah. I wish I'd used that more because it does sound so interesting with the multi-model approach and everything but i just haven't had an opportunity you know i was surprised not to see zookeeper on this list i did look it up and like the website says just straight up zookeeper is a database that's an in-memory database it's kind of back to file systems uh which i thought was interesting it's not not, you know, typically you'll see ZooKeeper being associated with a lot of like other type systems. And other systems will use it for like managing state or kind of tracking leaders or whatever. So a lot of times you'll see database systems having ZooKeeper as part of its ecosystem.

And it will kind of delegate parts of the work to it. It's so good at it. Which is kind of funny to kind of see that uh standalone i wonder if db engines has zookeeper on there i don't know that i saw it there yeah i think because it's a purpose-built one you know for coordinating infrastructure maybe that's why it's not on the list yeah i don't know so i unfortunately i can't use my mouse because there's a cat on it they make it a little bit more difficult so while he's looking that up one of the interesting things

about these is there's typically not a language per se around it like when we talked about rdbms is you have sequel and and that is ubiquitous right like it may be its own flavor, but it's there a lot of times with these, with these key value stores, it's usually just some sort of put get type thing. Now, like I mentioned, Redis has all kinds of plugins. Like it actually has some really cool stuff or like doing search engine type things and, and, and all kinds of neat stuff. So, so there is like a more advanced API that you can do there, but still by and large, you're thinking programmatically how you put things into and get things out of those collections. Also on the key value stores.

So it seems like, and I may be wrong, but when these things first came out, it seemed like it was like sort of a string was your key and a string was your value, right? Like you just threw those things in there or a string and a long or something like that. They have gotten way more advanced, right? Like every one of these probably supports, you know, whatever you want to put in there for a key and then complex data structures for the values, right? They can be objects. It can be nested objects. It could be arrays, collections, all kinds of stuff.

So you can kind of put whatever you want into these things a lot of them will support them zookeeper is not on here not in there yeah i think it's because it is an infrastructural type thing it's interesting yeah they do uh there's a uh an article that i'm not gonna read all of now where the actual article is why is the dupe not listed on db engines but they do. There's an article that I'm not going to read all of now where the actual article is why it's Hadoop, not listed on DB Engines, but they do mention some other things that they don't include, like Zookeeper. Dude, I've never, I don't think I'd ever scrolled to the entries on the page. Wow. Yeah. And it's hard to tell because they sort of do a tie for the last ones.

This is really bizarre. It might even be more than 401. Anyways, there's 400 plus database engines on here, which is too many. Yeah, that's a lot. But so here's the next thing. And this is where DynamoDB, like, was it one of the first, like, big boy key value stores? I think it was, like, probably one of the first ones that came around. Scalable.

Like, just ultimately scalable. Like you can basically put as much stuff as you want in there and you're good to go. Like it'll just, you just, you don't have to think about the size. You just got to think about how much you're willing to pay every month. And that's really what it is.

Yeah. It came out right around like the, the white paper, the original white paper that dealt with the cap theorem. And some of the stuff we talked on the show before, I forget the name of it now, the dynamo paper coincided with the release of dynam some of the stuff we talked on the show before, I forget the name of it now, the Dynamo paper, coincided with the release of Dynamo pretty closely

and also just the rise of cloud computing in general, like AWS. And that was one of the first things that was out there for it. And so it really just timed things really well. And so it was the first really popular one. Yeah. And I mean, horizontally scalable, right? Basically, you're not even going to know what's happening behind the scenes but if they end up running it on 10 nodes you don't know

you don't care right you query it you get your stuff back it works life is good uh oh yeah we didn't mention at the top but uh everything we're talking about uh today actually dates back to like 1968 so isn't that crazy man there are no new ideas in databases pretty much since, you know, the 60s. Yeah, yeah. You know, when we say like this, it's the first, you know, big popular, whatever we're talking like kind of socially. Yeah, the first one that was well adopted in the programming industry, probably. These things also are hyper performant. And again, when these things first started out like

they were pretty simple key value stores right like there wasn't a whole lot of functionality to them well as people started using these and and their desires for for features or whatever grew a lot of these have also added features like i said um the the Redis one has tons, tons of like plugins and add-ons you can use. And even DynamoDB has new extended, or not new, but extended functionality where you can index by more than one key. So you can have a primary and a secondary sorting key so that you can actually make filtering and searching faster within the collection. So, you know, these things have added things on as time has gone on, but their core functionality started as key value pairs. It was right as streams now too. It's kind of

Kafka type behavior. Dude, it's insane. And that, so here's a hint I did, or not a hint, maybe a tip. I didn't put this as my tip at the end, but in all honesty, all the databases that we have listed here, it's worth going to their pages. Like if you go to the Redis page and and you go to like, was it resources? Maybe it was core capabilities. If you go to their pages and you just start browsing some of the documents, my goodness, the amount of cool stuff you can find out, like we'll get to it in a little bit, but a lot of these companies, I don't know if Redis does, but they'll have like white papers and use case studies and all kinds of things. So you can sort of get an idea of what that technology works really well

for. Like one of the white papers I saw for Redis was using it as a query cache, right? So if you're querying a database that has, like we were talking about earlier, if you've got a lot of traffic to your database and Stack Overflow does this, they actually had it in their design paper that we've mentioned so many times, they would actually cache results, search results, so that the next time somebody searched for something, it wasn't hitting that search engine and it wasn't hitting SQL Server. It just hit Redis and then served it up from there, right? So they're not actually taxing their backend and they have all kinds of use cases like that. So worth going to each one of these database sites and just looking at what they have. It's pretty cool stuff. Just don't put your email in to get the PDF. It's a trap.

Oh man, it really is. That's why you use Mailinator. Right. It usually works on all of them not not every one of them all right so next one you want to grab this one yep uh so document stores uh this is a databases that basically will store an object uh and you're able to query uh and retrieve that object and you're able to make like partial updates to that object a lot of times you'll have secondary indexes and just better query ability in general than the key value stores. So some popular ones there,

MongoDB, Amazon Dynamo, again, Databricks, Azure, Cosmos DB, and Couchbase, popular here. And these are well-known for being schema on read, which is nice sometimes and not so nice on others. Like I kind of mentioned up at the top of the show, nice things about schema on read which is uh nice sometimes and not so nice on others like i kind of mentioned up at the top of the show um nice things about schema on read is that if you don't know a lot about the kind of data that you are going to be getting and you don't want to lose it it's nice to be able to kind of shove that in there and then deal with the repercussions later the downside is without

that normalized data schema you know it's easy for people to make mistakes that are hard to kind of fix like this uh service is putting in integers here and this one's putting in longs or The downside is without that normalized data schema, you know, it's easy for people to make mistakes that are hard to kind of fix. Like this service is putting in integers here and this one's putting in longs or this one's putting in strings. And this one's named a little bit different. We didn't realize the bug until way later. You know, all that sort of stuff gets really messy. And as your organization gets bigger and more services talk to that stuff, it kind of takes on a life of its own that could be frustrating to deal with. So that's why I kind of hate on it a lot. Yeah. So at the beginning, when he said that he didn't like the schema on, right, he actually meant he didn't like schema

on read, which is what all these are. And so what he was saying there, if it's not ultimately clear to you, it's the interesting and powerful thing about these, but also the thing that can really shoot yourself in the foot is we mentioned like a person's table earlier in one of the relational databases, right? And you have a first name, last name, address, whatever. You can have a person's collection because that's what they're called in object databases. You have a person's collection and you can put a car in there, right? Like it doesn't care. You put any documents in there you want. It's just a, it's just a container for your data, whatever type of data you want to put in it. So, so it's very much up to you to make sure that you're sort of keeping your house clean when

you're, when you're doing these things. Now, go ahead. It requires a lot of discipline. It does. And that can be really tough if you're not a disciplined organization. If you have a really tight API around it and everything uses that API, then great. If you don't, then it's going to drift. We've talked about cases before where it's like this service uses negative one to mean everything. This one uses null to mean everything.

This one uses 99 to mean everything. This one uses 99 to mean everything. And we've seen all three of those. Yeah, and things can get confused. And so you get a database, you get a callback, and you get a number, and you think you're just getting a number, and you don't realize that there's these weird special rules in there that you have to know in your application logic. And then you start searching around the code base for it,

and you see examples of code that's been copied in multiple different languages and different services to work around these weird rules and like if one forgets it did they really forget it do they not know about it or are they not using that for some reason and the one you based your new code on is that the right one is that the authority like how do we manage that stuff just gets really messy so as your organization grows there's a lot of pain on schema on read if you weren't like really just fastidious about it. Yeah. And that's a very good term for it.

Now, I want to back up real quick. So the top four on the entire list were all relational databases. Number five is MongoDB. So it's one of these document databases. And number six is Redis, which was from the previous, you know, key value store. So they're very popular, still behind the relational, but they're up there, right? Like we've got two inside the top 10 that belong to these document key value things. Now, ironically enough, I thought this was hilarious. So when I first started doing these, these show notes the

other night, I got down here to the document stores and, and Jay-Z and I have, have experience with MongoDB, um, with DynamoDB. And I don't, it's like you said, I haven't had a chance to play with Cosmos much. I haven't touched couch base and I haven't done data breaks, but I have a decent idea of the flavor of these things. When you're talking about the document things and, and Joe, Joe here had a situation the other day that would have been super easy to do in SQL, which was, Hey, I need to know all the records where they don't exist in this other set of data. Like in SQL, that's literally, Hey, select star from this table. We're not exist in select star from that table. And like, it's that easy. Right.

And it could be a self-referencing table. Doesn't really matter. The query is that simple. And I, I saw him chatting with, with another person for like, I don't know, 10 or 15 minutes trying to figure out how to do it in Mongo.

And yeah, way too long. And what was your, what was your final solution? To do something completely different. Yeah. Do it in code, right? Change the rules. Yeah. Yeah, way too long. And what was your final solution? To do something completely different. Yeah, do it in code, right? Change the rules. Yeah, yeah. That was basically like, I'm just going to kind of do a dumber query, get too

much data back, and filter it in code. And then ultimately we decided just to do something else entirely because it was a pain. Kind of example there was like, imagine like a table of addresses for people and you say like, I need to know, I need to find people that have a Florida address that have never had a Georgia address. Yeah, yeah, there you go. Yeah, and there's another example of something like that's easy to say and in relational database like, okay, yeah, I can do a, you know, select star from addresses where blah, blah, blah is in Florida and not exist georgia you know done uh mongo was like okay uh i know i can

do it in two queries but what i really like to do is not do that yeah so trying to figure out i was really tough and we're trying to like figure out how to aggregate and maybe we could do some sort of sum and like maybe if we give the georgia addresses a negative big number then oh that's just terrible it was really ugly yeah it's it's funny because it just points out that there you will get people on both sides of the camp right people they're like oh relational database is where it's at and then you get other people oh no mongo right like this document database is where it's at and the reality is they both have really good places right they both do some things perfectly right and then there are other things that it's like man i really should not be trying to use this

hammer to cut that piece of wood in half like it doesn't make sense right and that was one of them and it just illustrated it so perfectly because i didn't even respond to the conversation because i was like well i'm working on something else, but, but I did start looking up stuff. Right. And a Mongo has links that are sort of like joins sort of. And they also have this lookup function,

which I think was mentioned. It just didn't work the way that they wanted it to. So it's like, man, there probably is a way to do what you wanted to do. It probably wasn't going to be performant and it was probably not going to be readable by 99.9% of the population. Right? Like, yeah. Yeah. I think when we started looking at doing that produce that to kind of like,

go, okay, go get all the Florida's now go get all the Georgia's. Now we're going to put them together. And it's like, oh man, what am I doing? Like, let me just get this person's addresses. And we'll check it in code real quick. it's crazy but all right so on the flip side though the things that it is really good at is like what you talked about earlier let's let's say that you placed an order and and you're you know you checked out you bought some stuff that's real easy in something like a document database because every record basically is a snapshot, right? Like you write a document, you've got, this is where like Mongo is pretty interesting. Instead of putting my whole name in

there, like Alan Underwood, you can actually have a link to my, to my user document, right? They sort of make one hop joins easy. If you got to go past that, it's not. So you could link to that. So if my information ever changed, like, you know, Alan the Great or something, like if I changed my name, then it would be there. But then the rest of the document is filled out, right? Like you have your order information, your total, your order details.

It's all in that same and pictured as a json document because it's basically what it stores it's perfect like that's just an easy solution yeah yeah i tell you and uh it's really nice from a user perspective because that lines up really nicely with code because it's the way we tend to think about things so you say like order.get cart.get user.get you know things like that that uh that come back and it comes back is basically a whole object. So like we didn't really get into it, but relational databases, there's a big mismatch between like the way the data is stored and the database and the way you typically code. It's like there's this object to relational mapping, you know, ORM framework that usually kind of sits in the middle and helps you kind of navigate that a little bit. But that's its own whole, you know know big turd of wax to deal with so mongo is just like no you ask for

order here's your order and here's everything that comes along with that order if you need other stuff you know go get that other stuff separately yeah and and so some more things here on the document stores so we already mentioned their schema on read so you basically get in whatever right whatever they put in there you get out you have to know how to use it document stores. So we already mentioned their schema on read. So you basically get whatever, right. Whatever they put in there, you get out, you have to know how to use it. They also, it seems like a lot of them have sequel built on top of it, but it's usually for very easy use cases. Like Mongo has an amazing, in my opinion an amazing set of of like i don't know that you want to call them command line but just uh querying tools but it's more it more looks like code than it does look like sql right yeah and like it's really powerful like the ability to filter and stuff

that it's like by default uh regular expressions and and the ability to filter and stuff that it's like by default, uh, regular expressions and, and the ability to project things like here's, here's an example of something that for whatever reason is always stuck in my head. Uh, new egg. If, if you went to new egg and you want to look for motherboards, that's easy enough. Okay. Well, what if you want to look for motherboards that only had USB C or, or, or didn't have USB C on it or whatever, either way in an, in a relational database, what are you going to do? Every time a new port is added, you're going to add that to your table, right? So you have a motherboards table and now you're going to have, okay, well, as a USB a, well, how many does it have? Oh, so that be an integer column i don't know mongo gives you the ability when you query you can say hey just give me back records where it has this field or

it doesn't have this field right like that's real easy like it's sweet so you don't ever my point is you don't have to go back and back update the 10 billion motherboards that have existed from 1990 to now. You don't have to touch them again. The query engine and the fact that those documents didn't have them on it just work. Where in a SQL database, you have to go modify that table, which, whether you know it not, it's touching every single record of everything that ever existed in that table and it's updating them all. So, you know, there are things like that that make it really sweet and a really nice use case for a lot of things that you might do programmatically. Oh, yeah. That's funny. You mentioned about relational databases too.

Whenever the shape of your output can be really different from the filters. So you end up almost like writing two queries sometimes. You're like, this is about the things I want to get out. And at the end, I'm like, where ID is in. And then we do like another query. And then the query optimizer has to figure out how to kind of put all that stuff together. But it's real painful to have to almost like write your queries twice when you get complex. Because you're already dealing with a complex use case.

And now you're just adding this really verbose kind of weird model around it. Oh, and that's actually a really good point that I don't know if we made clear is when you are modeling something for a document database, you really have to think about what your end usage is going to be of those collections, right? Because the way they're performant is you just go get the document you need to fill out whatever it is you're trying to fulfill, right? In a relational database, you don't care. In a relational database, you're just trying to make the data as small as possible, more or less. In the document world, it's okay, well, if I'm going to load this page up over here, what data do I need on that page? That should all be in one document, right?

Like that's it. So there has to be a whole lot more upfront thought go into what you want to put into a collection in order to make that actually work for you in a performant and functional way. So yeah, data modeling takes more time. Yeah. Let's see what else we got here. And we're starting to say how common key value stores were by the way how how common are they how common i would say for programmers to

deal with directly uh kind of rare uncommon to rare uh how often they are involved in complex systems that you're using like other databases and stuff like oh very common yeah yeah like redis is a perfect example right like you may not be interacting with redis a lot but it probably is being used behind the scenes to catch data from your various different data stores and whatnot and etcd if you're using kubernetes you're using etcd constantly yeah don't know it yeah that's true um Uh, let's see what else we got. Uh, we already said that you can have different types of documents in there. Don't even matter. There's no correlation whatsoever. Uh, I did one other interesting thing that's worth noting is when we say document again, it's more like a json blob if you want to think

about it like that that's more or less what you can envision for all these things that means you can store nested objects nested collections nested objects of nested objects you basically put whatever you want in there right like i think your only limit at least in something like mongo is the size of the document you put in there and and i don't even remember what the max is now it might be like 50 meg i don't know max mongo sounds right size let's see what's it say um 16 megabytes the maximum bson document size is 16 megabytes so so not 50. So yeah, I mean, that's, that's still a bunch of data, but you know, there, there is a limit and you probably want to be thinking about what you're doing if you're trying to cram that much into a single document. Um, and then the last thing,

this one's kind of interesting to me. They can be very performant like extremely performant but they also are very much like a relational database in that because you just sort of have and it's a little bit weirder in things like mongodb you have to create proper indexes but these indexes require that you know something about the data and the collections right like? Like if you have a person's collection, you're going to assume they have first names and last names. So you're going to create an index on that stuff. But that means that for documents to actually be indexed properly, they need to have those properties, right?

So another thing I found interesting is you have to manage the number of connections to the database. Like it's a different beast than something just like a pure relational database. And how common are these? I would say at this point, I don't consider NoSQL to be or the document databases to be an exception to the rule. I think they're still choice B. So I would say common. If someone tells me they're like still choice b um so i would say common uh yeah someone

tells me they're working with mongo i'm not like oh wow what's that like cool you know i have a slightly different take on it i think that if you're working in an enterprise it's almost de facto a relational database i wouldn't be surprised if a lot of little startups, I don't want to sound derogatory, but smaller startups and companies that are sort of like techie-focused, I wouldn't be surprised if they go this route because it's just sort of the,

I don't know, the cool thing, but it's programmer- programmer friendly right it's what programmers think of when they think about this stuff and yeah they tend to scale better too uh like scale easier so what it means is you don't have to put as much like thought into uh like pricing uh your your your servers uh up front as much yeah that's true guess as much that's true it's the only thing and i remember we were looking at mongo years ago and the thing that sort of pushed us away from it at the time was the licensing remember it was it's a gpl type license and there was so much gray area and if you use it do you have to open source your application because you're using Mongo in your stack?

I still don't know the answer to that, but I'm assuming the answer is no. Yeah, they did that weird thing. Remember where they changed their license after AWS did the thing where they started hosting Mongo, selling it, and Mongo got annoyed with that. And so they changed their license and basically tried to kind of prevent people from hosting it and selling it because they wanted to be able to to do that and that was a whole big stink because now people are like oh it's not open source anymore uh yeah which i mean that's i don't mean to say like like you know as if that's like not a valid argument or whatever it's just it caused a stink and so it was a it was a whole big debacle there in open source community i remember yeahastic went through that with them.

Everybody did. They all changed the licenses because, hey, why is this company making money off what we're putting out there for free? Yep. Yeah. All right. So I'm going to do the bag this time because I would like more than two or three stars if you've got it in you.

If you haven't left us a review and and you like what we've been doing and you know it puts a smile on your face we make you laugh or giggle once or twice and you learn something along the way please if you get a chance uh whether it's on itunes or spotify not stitcher anymore audible audible actually allows you to do it so if you have an amazon account which i i believe that everybody listening to this probably does you know uh if you wouldn't mind drop by and leave us some kind words and uh leave a smile on our faces so uh yeah the more stars the better if you can get six in there that'd be amazing absolutely i was trying to get out creepy sorry

that was a little creepy that's fun all right so did you did you end up coming up with a game for us uh no it didn't go very well i tried to ask chat gdpt to come up with trivia questions for us and they came out just terrible it was like giving interview questions i was trying to do it around databases i did get a little bit better by saying like in the style of jeopardy uh but it still didn't didn't come out so we're great it was like it was like the acronym for uh for sequel it came up with a couple good ones i'll go ahead and give you an example one here oh i closed it it's gone i closed it but it was basically like um uh this field is used as the primary key for uh looking up documents in Mongo. Object ID.

Yeah, so object ID is the type order, but the underscore ID, I think, is what it was looking for. Oh, that's right. Yeah, underscore ID. You're right. And I asked it for Postgres, and it said this keyword is used for adding columns to a database table. And I was like, Really? I think i might know it's ad right uh was it alter no i don't know well that's alter table yeah i don't know man

okay so so we so yeah i guess it depends on how you yes that's why i was like uh they're both just kind of okay yeah so we came unprepared with mental blocks today without law out. So apparently he's the glue that binds us here. Um, yeah. So I guess we'll be skipping the game today and, we'll have something ready for next time. It's ultra table and ad column.

So I don't know which one of that was looking for. Cause I closed the window. Didn't you say the answer anyway? Didn't you say gpt has is um taking a slide here recently yeah that sounds funny like i'm gonna be exaggerating here to make your point but like i'm like hey can you write me a trivia question in the style of jeopardy it's like no i can't uh jeopardy is a legit company they've got a you know they give you like a paragraph about how they're not going to be able to uh generate that for you because of uh copyright and so you have to kind of like phrase

it in weird ways like in the style of a quiz show where you answer with a question and even like we talked about this early on i was trying to get it generate a picture of octocat from github and it just wouldn't uh it's gotten so much worse now it's like you try to ask about a recipe and it's like well i don't want to tell you you know know, you might have allergies. So I don't, I don't really want to answer your question about how to, you know, boil an egg. Like, come on, just tell me how to boil the egg. I know, you know, tell me the best way. Yeah. That's the unfortunate thing is, you know, all that's coming about because people are suing left and right for this free service that they've had access to.

Hey, how do I get rid of this cough yeah exactly it's like well you should consult your doctor it's like dang it yeah which before before it was like hey take some robitussin and whatever and then somebody died or somebody got really sick and and now everybody's suing and yeah now now the platform is falling apart yeah i missed the good old days i haven't tried any of the self-hosted options or you know there's some other ones where you can kind of run it on your own like i saw a cool device the other day i forget the name of it uh it's designed to basically be in a kind of a generative ai in your pocket uh similar to

phone but minus the internet connection oh interesting like so it's also the question yep nice yeah i'll look up what that is um but yeah but yeah i've not been having as much fun with gpt lately it's it's uh stinks like and i don't think it's me just kind of like you know bumping into the edges of it whatever like it i think it's actually gotten worse than it was a year ago a lot worse and it's probably seriously lawsuits and all that kind of stuff and they're like okay we got we've got to dial this thing back. Yeah. It's a shame. Yep. All right. So next up on our list of databases and when you'd want to actually use it, um, time series database systems. Uh, so the popular ones influx DB, which that was surprising to see me above the next one which is prometheus then kdb graphite and timescale db so i'd only heard of two of these yeah prometheus by far is

uh really kind of grown over the last couple years i don't know if it's just out today i don't know how they get the rankings but i feel like prometheus is more than caught up now and that they should be the champion of the category i'm trying to see so influx is number 28 and prometheus is number 50 i find that i find that it may not be wrong for one reason usually when you think about prometheus you think about monitoring right like so other services other servers whatever and i think influx db is more for development purposes so maybe that's why the popularity is skewed a little bit yeah that makes sense but but either which way this one was sort of hard for me i think this one's a schema on read because yeah i believe so it's more it's kind of like a key value store,

except your key is always a time. Yeah. And it makes the other key is always a time and you can do some interesting things because of that. Uh, schema on read, I believe is a big advantage to that. It's like,

you might start adding metrics at any given point and you shouldn't have to like pre-negotiate that. That's not somewhere we want to start like enforcing nulls or not nulls. Like that's a good example of of when you're getting monitoring data, you kind of want to get whatever you can get. And if some field is null or missing, whatever, that's important. And you don't want to throw away that whole data point just because some aspect of it's missing

because that in itself can be useful information. Yep. And these time series databases, even though they're, again, I think if you really want to think about them, they're sort of like specialized key value stores. The thing that makes these better than using something like a key value store, even a relational database, because technically you could do this in just about any of them is these have built in things to make them work very well for time series. So, um, one of the things is like, you can query instance in time, you can query ranges. So, Hey, I want the last 15 seconds. I want the last 30

seconds, whatever, or give me, give me five minutes of rolling data. Like those are all types of things that are built into it. And one of the really crazy things is you can join data on ranges. So, you know, let's say that you have data from your CPU and your RAM that were coming from monitoring of one of your services out there. You can actually join those and graph them together in the same instance across a graph with times because it knows how to join that stuff properly and bring those values together so that's all really cool stuff that these time series databases give you you imagine like trying to do some of the stuff that you do if they get something like a prometheus where like like I want a seven-minute rolling average over the last six hours,

and I want you to bucket by the unit that makes the most sense. Like something in a Prometheus user case, like that's no problem. Like, you know, it's going to be how you express that. It's probably going to be like four words, you know, and like most of those came from the dropdown. But trying to write like a relational query or a document database query that captures the same thing is going to be harder, especially with things like dynamic bucketing and stuff like that. And it's just kind of built into these engines because they're able to make a lot of – it's not even assumptions. They're just able to take a lot of shortcuts because of the kind of use cases that they support, which is like aggregations and bucketing and a lot of stuff around times. Everything is organized around it.

You can't choose another key. And so everything's just going to be able to work like that. Yeah. It's, it's pretty cool stuff. Now I will tell you, if you come from a document database world or a relational database world or whatever,

querying this stuff is a mind bender because it's a, it's a very specific language. I don't know. It's more like math equations. It looks like a lot of times when you put these things together. But the structure of the queries is very specific to time series type things. And you have to learn how you do it right. Like you don't write joins and things like you do in relational.

You'll do pluses and commas and so it's a very math approach to sets of data um but it's it's incredibly powerful like jay-z said you know you're trying to create some sort of crazy graph and you know you got you got a query that's not very long and and it does it all it's like magic yeah and And it's funny when you see someone or talk to someone who really knows how to do stuff and you're trying to do some weird thing that's just weird and you're trying to do all these weird gymnastics and they're like, oh, that's just asymptotic quintile

that's built in. And you're like, oh, yeah, I forgot about that one. I heard you. I heard what you were saying. Just subtract the arctan from one and you've got exactly what you were saying. Just subtract the Arctan from one. You got it. Exactly what you're looking for.

You're like, okay. That's so funny. Why didn't I think of that? I do have some links here. I did Prometheus because I'm more familiar with those. I've actually done Prometheus. I have links to some of the Korean basics well as like the functions, the special functions it has available, highly recommend going and looking at that stuff, just so you get an idea of the

types of things that these these time series databases will do. And again, these are used for monitoring, like Prometheus is almost ubiquitous now with, with, you know, any kind of service monitoring, if you're doing something like in Kubernetes or even your own on-prem stack, a lot of people will run Prometheus with Grafana because they just work really well together and they have hooks in so many programming languages for exposing metrics from applications into those. And so they're extremely useful. I mean, think about a do-it-yourself data dog is kind of what you're talking about here, right? That's more or less what you're doing. It's always been one of my dreams to set up a Grafana dashboard for my house.

Multiple dashboards, but I can flip around and see the data. It's just too easy to set it up. Dude, hey, if you ever decide to build a nas get unraid and run like it one of the top downloaded uh databases and and other functions with it are prometheus and grafana like you can actually see the stats in docker it's pretty cool yeah i set it up for um factorial once as i kind of like get in there and like see my power consumption per widgets built uh over the last hour did you for real yeah yeah i mean someone else set it up i basically just followed the instructions i ran in docker you know but yeah they just uh there was a plug-in to the game that basically just dumped out the metrics to a

file and then for me this was able to pull that in and then somebody bundled a bunch of dashboards together. It was great. That's pretty excellent. So one other thing that you mentioned, like quantiles, right, like quantiles and histograms, those are like two very special things that time series databases allow you to do in a non-completely insane way is it's bucketing more or less. So I'll give you an example. If you have something that like processes files, right? You might have this, this like quadrant type setup that says, Hey, show me files that are between zero and one megabyte and one and 10 megs and, and show me how long it takes to process in those. And so now you have this thing that can give you like a heat map and that's kind of what quantiles and histograms give you.

And that's super powerful. And I can't even imagine how difficult it was to program that behind the scenes to get it to work. But that is one of the things to where you can actually see a chart of, oh, this is how everything's behaving, and these are kind of where things are lying on my heat map. So super, super powerful stuff that's really, I don't want to say easy, but not difficult with the time series database.

You know, one thing that's kind of funny about at least with prometheus i imagine a lot of other time databases too is uh a lot of them are kind of sample based so they go out and they read the settings the time that uh you know that they take the reading but that doesn't mean that those numbers were static like those numbers might be written a thousand times for every time that data is actually scraped and pulled and so it's just kind of interesting to think about is like all these little snapshots in time and because the the data itself is so uh i don't want to say imprecise but you know i mean it's like it's you're taking these like samples from one percent of the real time you know these these actual like analog values whatever uh the whole kind of thing is really built around patterns rather than like the real numbers a lot

of times it's interesting yeah because the calculations would take a long time to eat up oh yeah more processing than what you'd probably have available yep so here's a couple interesting things like we talked about scalability with the other things so influx db itself when i was looking into it it is actually set up to scale via clusters. It said that you had to have meta and data nodes, so two separate. And they said that basically they talk back and forth. So it's set up to handle being able to scale out horizontally. Prometheus was a little more interesting. I have a link here to an article. It's sort of bizarre. Basically, you can set up what they call a federated,

and there was another one too, but the federated was basically you have multiple Prometheus instances running, and one can query another, and they sort of aggregate up through one. So it's a complex setup to where you're having to manage a decent amount of infrastructure to make it happen. But they said, one of the big problems with that is your storage bound, right? Like, so those, those leaf nodes underneath that top one, that top one's having to store all the data from those leaf nodes too. And so, so it can get really expensive and it doesn't grow well. Well,

that was another way they said you can actually scale it is you can have attached storage, right? So think probably something like GCS or, or S3 or something like that to where it can share that stuff and you can archive it because that's another thing like data grows pretty ridiculously over time. And a lot of times we've seen if your Prometheus node gets blown away, if you're running on something like Kubernetes, all your data resets. Like it's like you're starting over from nothing. And that kind of sucks when you're trying to look at it from a historical point of view. But scaling looks like it's very dependent on each individual database here. It's not more like a set rule like with RDBMSs. Yeah.

Let's see. Had some good practice type stuff in here for Prometheus, but we won't get into any of that. And great for learning too. So not just dashboards,boards pretty looks but very commonly used for monitoring yep all right so this next one's interesting i think you've played with this a little bit jay-z and and i've definitely done i've played with it more in relational databases but the next one up is graph dbs and i'll let you start this one off sure so uh most popular ones here neo4j i think this is old

uh dgraph is a popular one now that i'm not seeing in here but anyway so neo4j as as your cosmos db aerospike virtuoso orango db graph db has really come up in the last couple years as a more as a popular choice um now that's one that's kind of built around graphql as a query languages which has been a nice fit that's why i uh that's one of the ones i've looked at uh and what's really nice about it is that it makes certain things that are really hard to do in other database systems like trivially easy it's not necessarily uh you know uh gonna be fast but just, the ability to solve problems that you couldn't otherwise, like I mentioned that one where it's like, I want to know products that were purchased by customers who bought products

that were purchased by these other customers. That's pretty much the query you would write in graph database, except it would probably be arrows. You know, there would be, you know, product arrow item, arrow product,

you know, whatever. It's going to be very easy to reason them out, read, maintain, like, it kind of speaks that language. So when people are needing a query, like your your person sales or marketing, whatever, it's really easy to translate that stuff, which is nice. But a lot of times it is going to be really slow to like make updates or, you know, kind of inserts too, because this whole graph of connections has to be maintained. The data can get really big if there's like a lot of,

you know, like different nodes, edges. It's basically all the same problems that you have with graphs and just programming great graphs and trees. And so, you know, all the same stuff,

like all those kinds of algorithms come into play for like nearest neighbor, traveling salesman, shortest path, all that sort of stuff, a star. So really cool algorithms. And it's all built in.

So it's really nice to be able to have to not program that stuff explicitly in code and just be able to kind of outsource that. So if you've got a lot of use cases like that, it may make sense to bring in a graph database. And I cannot stress how easy it is to query those really hard things compared to like a relational database where you're going to have like four nest nested sets of queries and it's going to perform like crap well the the example you gave earlier right like um i don't even remember how you worded it but like show me show me all the people who have friends in florida that also have friends in Florida that also have friends in Massachusetts that took a

flight last week, right? Like that kind of thing in SQL in any kind of SQL database, you're doing one by one joins and unions and, and all kinds of things to get to that one very specific thing. Whereas in this graph database, it's like English. You basically, the query is exactly what you said. And that's one of the things that's interesting. So I have a link to their data modeling page on Neo4j. Neo4j's website has a lot of killer resources, like seriously good stuff.

But the cool part is when you're modeling their data, they say, hey, it's almost like you're drawing it on a whiteboard. You know, the examples they gave here that were actually pretty easy were, OK, Tom Hanks acted in Cloud Atlas. Hugo Weaving. Yeah, Weaving also acted in Cloud Atlas. So the acted in are your edges. Those are the arrows that point to something from a node, right? So you have nodes and edges in these graph databases and you start drawing this out, right? Like you have little bubbles with, with Tom Hanks and this Hugo weaving, and then you have another bubble that's Cloud Atlas. And then you have another bubble down at the bottom. That's like a directed, you directed you know the a director and these things all just have arrows pointed to each other

and that's ultimately how you model your data and it turns perfectly into a graph database model and then when you query this stuff it's very much like show me everybody that acted in um what was the one cloud atlas yeah cloud atlas it's like the kevin bacon game like uh you know the game where like someone says a character and you have to like figure out how to get back to kevin bacon whatever oh yeah yeah how many degrees of separation type thing yeah and like these databases are like designed for assuming tom hanks and be like okay here's the shortest path or here's three you know various paths or whatever and you can uh it's uh it's really nice because the model it maps to how you think it's so like we said it's like

i want to know movies that tom hanks uh or i want to know how many degrees of separation between tom hanks and um kevin bacon that don't include Meryl Streep, you know, something like that, or they do include or whatever. And you can just say it, and the way you query it is just like, that's like not in whatever, and that's it. And it pairs really nicely with like a relational database or something

where the way you kind of feed it is like by creating these relationships. So it's like Tom Hanks, Arrow, Cloud Atlas, Tom Hanks, Arrow, I don't know any other movies he's in. Big. Big, yeah. Yeah so it's like it just you can kind of query that stuff very easily in relational rational draw a little arrow between those two and kind of stuff it into the graph database and now you can just query this stuff in like a kind of english-like language natural language yeah i mean it's seriously when you come up with a use case that fits this,

there is no better tool. Like there really isn't. It's just so good at it. This is where I was mentioning, I play with it. And up at the top of the show, we said a lot of these relational databases have built everything on top of them. Like they've bolted on everything. SQL server has the ability to do graph database type functionality but it's very awkward in the way you do it the tooling's not made for it so much you're still doing inserts and updates and that kind of stuff and so you're not speaking the graph database language but you're creating the models in a very relational database way and it it works but it's not a great experience um whereas this is bespoke made for it and it's amazing like it's just really good stuff yeah and some of the use

cases you've got here like a fraud and detection analysis like again that's another case where it's like you can express these things really easily so like an example that we used to talk about a lot was be like uh show me computers that have had viruses on them in the last 90 days that have had users log in that have had more than three failed logins in the last year and like when i say that to like a security person like they understand what i'm going for it's like oh we're looking for kind of malicious actors or bad computers that are associated with you know things that have gone wrong so like these kind of two bad things that have happened together that's very easy to express it's very hard to query but when it comes to like filling in that data like we don't have to think about that use case when piling in the data we

just say like virus to computer person to computer and then later when the person's doing it like that threat hunting and trying to figure out you know what happened or how to prevent something whatever they can kind of pull this stuff up and query those use cases in a really cool way. And that they didn't have to think about at all when the thing first came up. Hey, would you say also, I mean, this brings up a really good point, like what you're just saying. All that data that you just mentioned a second ago with the logins and and virus in the last 90 days and all that kind of stuff your primary source of that data probably was not a graph database right right like it probably came from uh either a data lake or an elastic search or a relational database or or just logs right like it could have been any number of these things the graph database at least in my

view is usually a tool that you use to put data into when you have certain things that you're looking for these relationships you're looking for so it's it's literally a destination that you are very purposeful about when you're trying to find out certain types of information, right? Yeah. Okay. Yeah. And like, so things like, you know, we talked about like shopping carts and order systems and stuff like that, that's not going to go really well on a graph database because you're going to be saying like, well, let me get this shopping cart and it, all the products that are linked to it and all the pictures that are linked to those, all the prices that are linked to those. And it prices that are linked to those and it's going to end up doing this like these giant graphs that like travel your entire like

you know cluster that's going to be happening on every page request like not so great there it's great for discovery and you know it's it's not super fast but definitely not something that you're you're going to be wanting to do like millions of times a second on like instagram or whatever but it would be great for taking some of that order data and saying, Hey, show me, show me, um, people of this age range that did this. And they have friends that also are interested in this type thing, right? Like there's all kinds of, um, like, I don't know if AI is the thing, but, but relational type things where the suggestion type things are perfect for these types of models. Yeah, like Facebook, the people you may know, or LinkedIn has

similar type things. Those things are great. Where they can say, hey, let's find people that are related to this. Sorry, let's find people that also post pictures of things that you've posted pictures of. Right. Some people who have friends that are in common with you. Let's find people that work at the same companies. These kind of use cases that a person can just kind of like write down a list of things and say, yeah, throw it into a big bag and I'll shuffle up five at a time and say people you may know. That works really well here.

You're not going to want it to be your only database for that system because of the drawbacks that we mentioned but for those use cases it's just amazing yeah and someone can can go from thinking that idea to having that out in production is really quickly yeah and there's really not any other technology around that will do that for you in that easy of of an approach like you're talking about ridiculous types of queries in any other type of database system to make that happen if you imagine being like a marketing meeting with someone on facebook like a long time ago and then being like it may be great if we could like suggest groups that uh have things that the person likes in them and the person the developer the developer being like, yeah, sure. I just released it.

It's crazy. But that's the kind of thing that is easier to do because it can be expressed in that same kind of natural way. I'm oversimplifying, of course. But it's those use cases that sound simple in the English language. But when you start looking at other data storage types you're like oh man like i don't want to write that query first off i don't want to write the query and second off it's going to crash the server right like it's going to be a problem yeah

so um yeah there's tons by the way this is one of those sites when i mentioned going to the database sites and looking at the resources this one is is fantastic. Like they have, they have all kinds of like use cases on here. They have white papers that go along with the use cases. So like you mentioned the fraud detection one, if you go to that page, they have a white paper on detection with graph data science, money laundering prevention, read the case study. Like there's all kinds of good stuff here. So highly recommend checking their site out. Um, what else we got on here? I don't know that we do all those. I, we already said why you'd choose this, right? Like it, it just, if it's a use case, it's not going to work well anywhere else. Like that's, that's pretty much it. Um, and they even, so this was really cool. I have a link

to a video that they had, which is basically, Hey, why choose Neo4j? And it's not necessarily that it needs to be Neo4j. It's more a graph database in general. It's a minute and 20 seconds. Watch it. It's so good because they give, they give you kind of what we talked about right here, but you know, they have nice little visuals and stuff. So, so for sure,

check that out. It's really cool. And they have built in sharding. So I, I haven't messed with as much as you have Jay-Z, but like, I'm guessing if you had tons and tons of data, you probably need to shard it.

Yeah, I think I have to do more about this total amount of data that you're storing and less about the actual use cases. Okay, yeah, that makes sense. Because like you said, it's huge amounts of relationships and things that they're having to store, right? Yep. Also, my cat just bumped the fader so uh i don't know if he did it now or did it earlier oh that's actually now i think

about it so hopefully this sounds okay yeah a little baseball there inside baseball uh let's hope that zoom wasn't just boosting it and it's all gonna be crazy post we'll find out we'll see we'll find out that's uh that's We'll find out. That's Outlaw's problem there. Thanks, Outlaw. Yeah. Sorry, just kidding. Just kidding because I know you probably heard that just now. All right.

So here's your favorite one. All right. Search engines. I do really like search engines. Is it your favorite? I think so just because it's cool. Not necessarily that it's like the most general use case, something you should use, but I just think they're neat. I agree.

Hey, we also didn't say how common are the graph databases. Rare. Yeah. Unfortunately, I think they are. Yeah. But, man, if you have the use case for it, you should look into it for real. They're awesome. All right.

I've never heard of a company or organization that uses their primary database. So it's almost like by definition, you're starting out at an enterprise level, kind of like a large, evolved, mature solution. Yeah, it's seriously a tool that you need at some point, right? Like it's not going to be your primary storage, period. Like it can't be. It's just not suited for it. Yeah.'s not going to be your primary storage period. Like it can't be, it just, it's not suited for it.

Yeah. All right. So back, back to the search engines. Yeah. Search engines. So search engines, uh, really nice.

Uh, you use them all the time. Like Google is kind of like a big example, but they're, they're so weird. Like you don't really think of Google as being a database, uh, elastic search,

Splunk, solar, open search, Mark logic. Um, there's a bunch of other ones. I forget the of uh oh algolia is a is a newer kind of common one that's uh like very friendly with javascript um so you don't have to have like a back end and stuff they're just a nice kind of sass the product that you can use and it's really nice it's very similar in a lot of ways to document databases except they have extensive use of indexes that

make it really easy to search by just about anything that you have stored in your document and they have made trade-offs in order to kind of fit that use case of basically indexing everything and so there's copies of the data ingestion takes a little bit more work and a lot of there's a lot of catering done and a lot of work put into doing that sort of ingestion and doing that parsing of data up front when the data comes in so that you can retrieve things really quickly and basically by searching by all sorts of kind of interesting little tidbits of data that you may not have known about ahead of time. I think it was always like a fine tuning a little bit on document databases, but it's just really nice. Yeah. It's fun to use.

It's the, the interesting thing to me, or there's a couple of interesting things to me over, over the document databases, which again, this is schema. I'm reading. It's basically, you know,

a document database on steroids for search. But one thing that I find that search engines provide that almost nothing else does, at least not in any kind of standard way is the ability to search all your data, right? So the best example I can give is if you have a database, a relational database, and you want to search everything like a global search, like a Google search or an Amazon search. You want to search for everything that has the word blue in it, right? To do that in a relational database, you're going to have to select star from people, select star from computers, select star from whatever, right? Like whatever,

whatever tables you have, you've now got to sort of fashion this query to be able to go across all the things that you think you might want to look in. Same thing in a document database, as far as I know, right? Like you're going to have to tell it which collections you want to try and filter on for the word blue. Hold on. I just saved you guys a really rough cough. Nice mute. But in something like Elasticsearch, you can just say, hey, search across everything and find me blue. And what's really cool, this is the second part of it that I think where search engines really come into play is they can score it.

Right. Like if you search for the word blue, when it comes to people, it's probably going to probably gonna be like oh we gave it a score like five because it's really low but if it was searching through a product collection and index then it's gonna be like oh yeah we think that this is probably more where the blue targeted word would hit so we're gonna give it a rank of like 75, right? So it can sort. We got another cough. This one's not muted as well, but we'll see how it doesn't post. I didn't reach it in time. I tried.

So close. Man, hold on. All right. Well, I'm just going to talk to you a little bit. I think what Alan's kind of getting at is that there's a lot of like interesting rules and basically the optimization is kind of made on top of a document database where uh that supports these use cases and so at the end of the day you can say something like color blue and your search engine is very quickly going to be able to say uh you search for blue uh we are going to weight color higher as a parameter and maybe summary lower in case someone's having song lyrics or something into product description so we can kind of rank those and return those

let's go ahead and return the top 10 to you and we'll tell you how many are left that we know about and you can kind of cycle through those in interesting ways so it makes a lot of use of like things like bloom filters that we've talked a little bit about before and probabilistic data structures so it's really good about saying uh i forget the way there's like a clever expression for it's basically basically like we can tell you uh either no we don't have it or maybe we do and so uh some of the numbers are a little bit fuzzy and so that's you'll see a lot of times search engines they won't give you exact numbers. They'll say like we're showing you one through ten of thousands or tens of thousands or some sort of ordinal. But they won't necessarily show you the exact number. And it's because they took a shortcut in calculating that stuff.

And that shortcut was designed to save you time. So that's why they can give you that answer back in zero seconds. But maybe it would be off by five percent. And that's OK if you're, you know, like looking at Amazon or something where you're probably not going to go past page two. Yeah. I mean, and sorry about that. Goodness, man. I'm getting over a cold and it doesn't want to let go quite yet. Um, I'll try and hit that mute button if it comes up again. Yeah, you do good. Yeah. Appreciate it. So,

yeah, I mean, everything you said, and that's, what's really powerful about this stuff. I mean, on top of that, right?

Like, so it gives you those, those scores and it can even group things, right? Like if you ever go into Amazon and search at the top, I'm not sure what I was going to say there. I'm right. We're all waiting dying in uh in anticipation man i don't know why don't know why he's doing this right now

allergic to search engines something man hold all right well i'm just gonna i'm gonna keep going so some other things that uh often built in and might have been what he was going into are just common features for search engines. So things like stemming or fuzzing. So that's like, for example, if you search or alias is another good one, or you might search for the word running for shoes, running shoes. And it might also figure out that running is similar to jogging. We've seen these things together. So we'll find running shoes, jogging shoes, walking shoes, and also runners. Maybe, you know, that's the stemming where it comes into play, where it will look at like kind of cutting off the suffixes of words when you search them. And so it's going to be

able to say like, okay, we know you search for running and we're going to weight those higher, maybe with that relevant score, but then we'll also show you runners and runs and ran and all sorts of other aliases and stuff and just kind of sort those in there too in case that's what you're looking for so they're really designed at trying to meet you meet you halfway as a human, which is really cool. Did you just stop? Well, I thought you were about to talk. So I did. It wasn't for very long.

No, I was just talking about aliasing and stemming and full text search. I wasn't sure if that's what you're going with. I'm on Amazon. No. So those are also things. But no, like on Amazon, if you search for the word lens, right, in the main thing, it might say, hey, do you mean lens and books? Do you mean lens and cameras? Right.

And so it actually has the ability to bring back groups of things that it scored for you and said, hey, I think these are the things that you might be looking for. So that's built in as well. And it also makes for really nice faceting. And so like Amazon or something, you know, like maybe we'll have a, you know, you should search for blue. It'll bring back t-shirts and we'll say,

all right, t-shirts. Now here are the sizes, mediums. We have 453 largest. We have, you know, 262 price range.

Do you want one to 10, 10 to 20, you know, stuff like that. It's going to be able to kind of add that stuff up all really really easily for you and it's just designed for that it's uh the whole uh the tuning of this kind of document database is really optimized for those use use cases and so if you're building anything that's like a kind of a search like driven application it's got a lot

of these things probably in common because we've kind of grown to expect those things from those kind of websites and so you can really build a whole like e-commerce web page or there's a bunch of other types of web pages websites you can build around search engines and i really leverage the search engine for so much of the use cases and your user is going to be really happy because it's the things that uh that work really well for what they're trying to find. And also they're just really easy to implement. So I like it. Now, there is one particular case that they do not work well for. As a matter of fact, they're terrible for. So we've mentioned what the document databases, and again, this is an extension of it. You really have to think about how you're going to use that data up front because all that needs

to be in the one document, right? So this is where they completely fall apart is if you need to query relationships. So it's, you can nest data in the documents, right? So if, if you have an order thing, you could also have a nested document. This is a person who ordered it, right? Well, what if you then want to say, okay, well show me the people that have also placed ordered with these. You can't do that. Like there's, if you're going to nest a person underneath the order, and then you want to nest related orders under that, like you're not going to try and cram every piece of data that you could potentially ever query underneath this thing. It just does not traverse relationships. You can't do it. So you have to put what you can think of in that thing.

And, and hopefully your use cases will work with that. If you need to go further than what, then the layer of nesting that you're willing to do, that's probably where you need to look at something like Presto DB or whatever the new break off of it is. I can't remember. I mean, we were trying to solve this problem a couple of years ago. If we even talk to the people at elastic search, or like we went to one of the elastic conferences and they're like, yeah, you can't really do what you're trying to do there. And yeah, you got to know that, right? Like, and you have not just know it, you have to accept it and then be like, okay, well, how can we do what we're trying to do? Yeah.

And so it's another one of those databases where it's like there's a lot of companies that use them, but they're probably not going to be the primary database. And they're probably going to have a relational database that's kind of ultimately backing it. And you think of something like you've got an Amazon and they want to run a sale on all products from Fender guitars, right? That's something that's very easy to say as a human search engine, though. You've got to go in there and kind of update those prices. And so if you imagine like someone coming from marketing and saying, well, I want people in these states to have sales on Fender items, Fender guitar products, and people in this state get another sale. These people get 15%. These people get 10%.

That might be something that you kind of think of in a relational way or a graph way as being very easy to do. But when it comes to Elastic, if you want that stuff to be searchable and facetable on the left there and kind of take those sale prices into account, that's really hard to do because now you're having to, you can't link that information. So you're going to have to put that information into the document, which means updating all those products, which can be potentially a lot of products. And it's just really gross for something like a sale. That's like temporary, you know,

it can probably be done and probably is done, but it's not the best use case for it. Yeah. But so I guess our, our final question on this one is how common are they? Not as common as document. I think the industries, yeah,

it's just, you know, there's the industries that they're used in, which can be security a lot of times and e-commerce, obviously, some analytics type stuff, very common. But if you're working in, say, a banking operation or something, then there's a good chance you're not going to see one of those. You know, it's interesting. You're probably right even though like elastic search has gotten huge specifically out of the ones up here right and splunk is massive like it's used by oh yeah by enterprises everywhere but it's used for so many different cases

but i think that the reason why these probably aren't more popular is because I think people abuse their relational databases. I think that's the real answer is they cram everything into those and try and make them do everything. Yeah, that's a good point. And I was thinking that logging is a good example. If you're doing some sort of log aggregation, so you have all your computer logs kind of going into a centralized log system, then it's very easy to go in there. The log system say like, show me all the logs from windows version 4.3 or whatever. And that's great.

But then you, when you try to do something like show me, show me all the logs from operating systems that have a lot of vulnerabilities, suddenly it's like, Oh no, I mean, you got to have a list of them.

You know, that's not, that's not information that you can really relate in there easily. So if it's not stored in the log records individually, then you're going to have a tough time pulling it out. And you're going to have to do some sort of other layer there ahead of time to say, first go look that up and then drop in a list, which you can imagine could be potentially a big list.

Yeah. So we've covered six of these things now. And for me, the takeaway is I don't love any one of these more than another. I don't hate any one of these more than another. Like, I think I even put it in my 2024 resolutions or whatever is that, you know, data should be put where it's useful, right? Like, don't be afraid of duplicating data. If you have a relational database, or you have a bunch of logs, and you have some sort of need to query relationship type data, maybe a graph database is interesting. If you have a need to be able to search on that text in all kinds of curious ways, use a search engine, right? Like there is no right or wrong answer here. I think to me,

the wrong answer is when people know a technology, call it SQL server or call it Mongo or whatever, or even elastic search. And they try to bend it to do the other use cases because they don't want to duplicate that data. I think you spend ridiculous amounts of time trying to make those things work the way you want them to. And you've run into so many problems because the underlying design didn't exist for what you're trying to do. And it just doesn't make sense. It makes more sense to sync that data to another system and use it the way that it's supposed to be used. Like that's, you know, not for everything, right? Like it doesn't make sense to spin up a whole new infrastructure if,

if you just have one little thing that you're trying to do, right? But if you're trying to do some core functionality for your business, do not try and bend something to your will. It just doesn't make sense. three years of your organization be like oh you know what stinks is that uh we've had 20 20 of our staff our engineering staff working on trying to turn a relational database into a graph database for these two use cases and i really wish we had just synced that data and used the right tool for the job because now we've got this uh crappy like half-baked uh database system that's like totally you know on the verge of breaking down every time we look at it and, uh, we've got 20% of the staff working on it. So great. And now we feel

like we're invested in it. We're never going to move off it. Yeah. And that's a horrible feeling. And unfortunately that's always a 2020 thing, right? Like, Oh, hindsight, if we had known, but that's, you've got to always take those steps with a little bit of foresight, right? Like how much of our product or how important is it to our product, what we're about to do. And if it's super important, should we invest in doing this the more right way? Yeah. Is this really what we want our org to be? Yeah. So yeah, I mean, hopefully this is helpful. Uh, you know, if anything, you've been exposed to a bunch more things that maybe you hadn't even thought about before.

So, and we've got six more, like somehow we made it two hours into this, which is crazy. Like before the episode, I was like, uh, we're going to have chopped this back. And you know, I didn't even think it'd go this long, but yeah. So at any rate, we'll have all kinds of links here in the show notes for this, like seriously, some good, good resources up there. Go check those out.

And, uh, yeah. Now for, uh, my favorite part of the show is the tip of the week and I'm going to hit me. All right. Oh,

I got a hot tip for you. Uh, so, uh, there is a database we didn't talk about today that works for multiple users. It's document-oriented distributed database. It's really smart about storing diffs between changes. So it's really good at handling a lot of changes.

In fact, you could even say it's designed around the notion of updating records rather than wholesale replacements. It's very efficient on storage space. It's open source. You already know how to use it. It's really great about history, although you can't quite say it's immutable because you do have the option of rewriting history. A couple downsides before I tell you what it is now.

It's slow at writes, comparatively. It's not so great at reading either. Querying, filtering is not so great and uh it's not it doesn't use sql although i'm sure somebody has built a some sort of plug-in or add-on for that and the syntax is unfortunately kind of difficult to use so it's not really fun to work with uh but other than that it's great and that is git and so i'll have a i'll have two links i forgot one but uh there's a couple people that've kind of made a bit of a joke and uh but i also have some kind of interesting uh use cases for using git or github as a database and uh you know it's

obviously not something you're going to want to try and serve a lot of traffic and a lot of updates online over. But it's kind of interesting because it is in some ways very similar to a database in that it stores data and it does kind of tick some of those boxes. Just obviously optimized for storing code and working with those kind of changes and updates and not the normal type stuff you'd see in like a web web application that's funny it's sort of ridiculous but i mean there are creative people out there it's not on db engines i don't understand why

it'll be number 420 yeah all right so mine kind of, I kind of copped out cause I initially, like, I just, I could not think of a tip and, and I didn't go searching on Slack, which I should have done in hindsight, but I think this one's useful. So if you've ever done anything in Docker, like we have notoriously done things like builds and, and, and that kind of stuff inside Docker containers. And one of the things you can do is you can do a Docker CP to copy things in and out of your container, right? So if you have a file on your, on your local file system and you want to copy it in there, you can, or if you did something inside the Docker container that you're

like, Oh, I actually liked that. I want to keep it in Docker, CP it out of there. Well, there is a command that will allow you to do that for Kubernetes as well. And it's cube cuddle CP and have a link to the documentation here, but it works almost identically to Docker CP. So you'll, you'll basically, you know, typically you'll want to tell it the cluster, the namespace, the pod name and even the container name, because you could have multiple containers running in a pod and then do a CP out of whatever directory it has and copy it down to yours or vice versa. Right. Something from your local up to a pod in a Kubernetes cluster. I've used this so many times and it is incredibly helpful. So. You know, if you didn't know about it it's a useful tool i got a bonus all right bonus tip uh k-sync uh maybe you're familiar with the like bash tool r-sync yeah yeah and what r-sync is often used for things like

backups or if just syncing files between two systems so you can say like hey sync files bi-directionally or one direction and what it'll do is basically any time the changes folders change happens on one side it'll go ahead and remotely over ssh or it doesn't have to it could also be local it'll sync it well ksync is like that but for kubernetes so you can say hey sync data uh from this folder on this pod to this folder in this pod or from my local computer to a pod and you know that kind of stuff is like if if you're doing that sort of thing too much you got to kind of wonder about your use cases but it is a pretty cool tool for that however there's one big downside and so i've never been able to use this tool the way it works is it basically has an agent that

you have to put up in the cluster so if you are working in an environment where that's not really possible or it's too much trouble to make it worth it, then it's not going to work out really well. But it's pretty cool, though. But yeah, basically, you stick it up there in your cluster, and it's almost like telepresence in the way that you kind of will run some stuff locally, and it's going to communicate with the agent. And then ultimately, in the background,

it's just doing kubectl copies one one way or the other so it's pretty cool but it just the every time i've ever wanted to use it i wasn't able to because i didn't want to jump through the hoops to to get it set up well that's interesting i mean that would have been amazing i was about to say man this this is like the tip uh but yeah if you if you have to install an agent up in your cluster there's probably many many cases where you just can't yeah so still still very cool all right so my my next one and this has to go sorry and i want to mention too that agent is actually a daemon set on the nodes which i don't know if we really talked about daemon says but the idea there is like it's uh it's

almost like a special kind of pod except it's actually designed to run one on each node which is actual kind of virtual computer that you're on that can have multiple pods on it and so you can kind of imagine why it needs to do that but basically that's how it's kind of set up so it's not just that you have to run some pod up there that you could spin up temporarily it's a it's a little bit more involved in that and depending on your cluster setup and your organization rules yeah security wise they're going to be like nope not not running anything on my node yeah all right so this one goes back to the top of of the show where i said that i'm about to start having some fun with uh cat 8 setup and cabling and all that so interesting thing where i plan on putting my network rack or my server rack, whatever

you want to call it, it's actually in a closet, but it's sort of beside where my electrical panels are. Well, one of the frustrating things is if you ever look into code for electrical panels, they're supposed to be like three feet of space in front of the electrical panel so that people can access it, right? Like if an electrician ever needs to get over there, whatever, they need to be like three feet of space in front of the electrical panel so that people can access it right like if an electrician ever needs to get over there whatever they need to be able to get to it well unfortunately there's only like a foot between that and where my server rack was going to be well and part of that is because there's like a little closet system uh you know system beside it so i couldn't move it over any

further so what i found is i accidentally stumbled across these these network racks that are hinged to where they can swing out away from the wall and i was like oh that's cool well the problem is they were all hinged the wrong way and they're not reversible hinges so they all swung to the right which is toward my electrical panel. I was like, man. So I contacted a company. They're like, no, they all swing out this way. I was like, all right, well, and then I had this great idea.

Like, man, I bet somebody's built a hinge. Like there's gotta be a hinge. And sure enough. So this company nave point who I guess is well known in the, in the rack industry, like they make all kinds of racks and accessories. They actually have a hinge adapter. You can stick on the wall and you can have it swing either way you want left or right. Well, I have room to swing it out to the right. It just can't be there all the time because you know, there there's that whole closet thing over

there. So if anybody ever needs to get to my electrical panel, I can unhinge the thing, swing it out, they'll have access to it. And then when they're done, I have swing it back. Now, the only thing I have to remember is leave enough slack in the cables when they're coming in so that it will swing out, you know, however far it needs to. But, but yeah, I thought that was really cool. Fine. So, um, in lieu of, of better coding, uh, tips tips that that was what i had so have you ever looked at uh server racks uh with desks with server racks mounted in them i have it's pretty cool huh i have man dude yeah but i'm surprised like actually so you're getting ready to get your new office set up right yeah your work office which

should be minimal and then and then your play office which should be whatever you want it to be right yeah have you ever and i'm sure you have because you're all into the music stuff and you have all kinds of gear have you seen the desks that are set up that have like the the rack systems built into the desk so that you put in like one u eqs and whatever else like you know preamps and all kinds of stuff like yeah they're built into the desk man yeah it's so nice i really uh i really hate cables like i'm just bad at it i just can't like i'm not good at cable management i'm not good at like staying organized i get really flustered and like frustrated just anytime i even see cables uh so i love the idea of having a desk with that stuff built in. And it's even like dumb stuff like,

like power conditioners, you can get like rack mounted power conditioners and stuff. And so like you can have what's essentially, you know, a surge protector or whatever, just kind of like they're in the desk with the plugs on the other side. So everything can plug right into it. You don't have all these cables you got to deal with and stuff.

So yeah, I'm definitely looking at the desk like that. And I don't even have that much stuff, but just having my audio interface, all that all that stuff and like just trying to keep all the the the cables to the back is very uh very exciting to me yeah dude it's it's ridiculous but i feel the same way i don't like seeing cables and i mean my podcast set up we we've got so much stuff hooked up like it's almost impossible not to have it but man i even dreamed of like having my road mounted sort of not not completely vertically but a little bit vertical

to where you know you could see the stuff well and then yep and then and like you're talking about like i want plugs behind stuff that you don't see um so that dude that you turned us on to a couple episodes back his music tony anderson Tony Anderson, you mentioned the video where they did a walk-in of his house. Oh, yeah, the studio. Oh, it was so awesome, man. Yeah. Like he had that custom-made rack made that was, you know, it was basically a network rack type thing for all his audio equipment. And it was so beautiful.

Like all of it was so clean. And I was like, oh, man, I'm a little jealous. Yeah, for sure and guess what on computers like desktop computers fit really well in server racks to the same you can get the same size basically the 19 inch whatever uh until the apple studios in particular like they make specific uh racks design just for us and kind of pop that in there into the like a rack and you know the power button's exposed just perfectly and like all the cables for that computer that and the computer's not down on the floor it's up but you also don't have to see all that stuff it's all hidden behind i mean it just seems great yeah

it's funny because when you start looking at that stuff you're like man they want some money for for this this little rack thing but then you think about you're like man how long do you have your last desk right like oh Like, Oh yeah. Forever. I mean, you had that cube thing that you were talking about that you got rid of. You've had that thing for 10 years at least. I mean, yeah. And it's just, it's like, you know what?

It's worth the extra four or 500 bucks to have something that, that I'm going to be happy with for the next 10, 15 years. You know what I mean? I spent a lot of time time a lot of time around here right a whole bunch and i actually had the same thoughts when i was doing the server um or the the networking setup was the same exact thing like man i i could cheap out and get something a little bit less pretty or whatever but i don't want to like i want this to be something that doesn't drive me crazy when i go look at it you know yeah so yeah i don't know i'm looking forward to it i may do some uh some photos and do some blog posts and stuff up there just to show the

progress of this but um it'll be fun i just found somebody made a desk with 14 years 14 years oh storage in it oh man where's the link nice sure i'll post it they built a custom cabinet for it and so like all the plugs are on one side and it kind of it tucks in nicely to the side of the desk and it just looks like almost like a filing cabinet dude but uh yeah yeah good stuff yeah we should have this in the in the show notes as well because i mean this is the kind of stuff that's just so much fun to to see oh yeah it looks like that tony anderson thing too right like he just had it also it's so beautiful look at those little short patch cables it's gorgeous yeah that's beautiful yeah yeah i'm

all in man look at those switches they have down there what in the world yeah i don't even know what they are but i want them right. Right. Oh, man. Yes. So at any rate, next time on Hardware Blocks, we'll talk about some stuff. Hey, but if you haven't already and you enjoy this, subscribe to us on iTunes, Spotify. I think those are the big ones now. Does anybody else even exist? I don't even know. Yeah.

Audible, kind of. Audible. Yeah. Oh, yeah. Audible. Audible. You mentioned that last time. Yeah. Yeah. Google went away. Yeah. Google. Are they going there? Man, don't even know yeah audible kind of audible yeah oh yeah audible you mentioned that last time yeah and google went away yeah cool man don't get me started yeah and uh yeah if you haven't left a review like i said if if you wouldn't mind if you'd like to leave a smile

that'd be awesome and and this will be codingblocks.net slash episode 227 you'll see the show notes hey seriously i would love to hear you guys's comments guys gals comments on on this particular topic i mean this is usually a flame war type thing right like oh my my rdbms is greater than your document db or whatever like no just really curious to hear what you're using and what you've liked or disliked about or whatever yeah for sure and then next time uh we'll be getting into some other really interesting stuff too. So we didn't talk about any OLAP stuff

or anything, but I'm sure that's all coming up. Yep.