164: Choosing a Database For Your Project With Kris Zyp

0:00A:Hey everybody! So Patrick and I have been doing a bunch of solo episodes or duo episodes—non-interview episodes. It's been really fun. But every now and then an interview comes across a plate that is a really spectacular opportunity for us to dive into something that's really important, especially for folks that are just getting started. You might be in college, in high school. You might not have a lot of years of experience under your belt. And one of the things that I didn't know until much, much later is the power and the usefulness of databases. I thought databases were for financial folks or for people who are really professional, people who wore shirts with buttons. I thought databases were for them. And so as a high school student, college student, I was doing a lot with data structures that really should have been in databases. So we're going to talk about how to choose a database for your project and what different databases have to offer and how they can make things a lot easier. And with me, I have the SVP of Engineering at HarperDB, Chris Zeip. So Chris, thank you so much for coming on the show.

1:38B:Thank you. I am delighted to be here. Cool.

1:41A:Cool. So before we get into the topic, why don't you tell us a little bit about yourself? So how did you get into computing? Did you go to college for it? How did you kind of learn that trade? And how did you end up following the path to HarperDB?

2:00B:Sure. Well, it started when I was about 10. I grew up in a great family of school teachers, and they were always very enthusiastic about trying to support and empower me in anything that I was interested in. And so at 10, I was interested in computers, and they bought an IBM computer. I don't remember the exact model, but, you know, 8 megahertz, 20 megabyte hard drive, and had Turbo Pascal. So I got started with Turbo Pascal when I was 10, and just dove into it and loved it, loved what I could do. I've always kind of been a do-it-yourselfer. So when I saw there was this new game called Tetris, I was like, I can play it myself. So that's what I did. I wrote Tetris in Turbo Pascal. It was probably a terrible clone, but whatever. I had fun doing it.

2:51A:That's amazing. Did you share it with anybody, or was it solo?

2:55B:No. Yeah, this was long before the open source world, I think. So, yeah, and I wrote like a check recording program for a chiropractor friend of mine when I was, I think, 13 or 14. So that was great. I had a lot of experiences as a kid growing up programming. And so even going into college, I knew I wanted to be involved in computers. So I did computer engineering at Oregon State and then computer science at University of Utah. And did interesting work on simulations with electromagnetics in the body at University of Utah.

3:33A:Oh, wow. So that—did you have a background in like magnetism and electromagnetism? Or was it more like you were the sort of engineer and you found yourself with these scientists?

3:44B:It was more—I was the engineer and I was, you know, handed a C framework for writing these simulations. And, you know, it was a great opportunity to learn more about physics and medicine and medical research. And so I really enjoyed getting to be a part of that.

4:04A:Wow, super cool. Yeah, that is amazing. One of the things—I have a very similar background to you. And, you know, because we were kind of like pre-internet, you know, we didn't really have an opportunity to share a lot of projects. And that is something that, you know, folks today, you know, really take advantage of just the amazing community out there that, you know, is there's a whole community of folks that love to see what, you know, different hobby projects and other things you're building.

4:37B:Yeah, for sure. Yeah. So, yeah, that's amazing. Yeah. And then from there, I worked with a friend doing some consulting work with a company called Documentum, the database software. And then from there, I really—that's when I actually really got involved in more open source software. And I started working at a company called SitePen. They were the kind of the main company behind the Dojo Toolkit. If you remember back in the days of the Dojo Toolkit.

5:07A:You know, I remember the name, but I forgot what it is. What is a Dojo Toolkit? It—

5:12B:was kind of like around the same time as jQuery. It actually came out a little bit before jQuery, but it was kind of in a similar vein of like, you know, a client-side web toolkit that was filling in for all the crazy discrepancies between different browsers at the time and trying to provide a, you know, client-side library. So I was a core contributor with Dojo for a while and really kind of doubled down into open source, got involved with like CommonJS Committee, went to W3C and WTC39 meeting. I was the author of the JSON Schema. The first draft of the JSON Schema specification.

5:53A:Wow. Okay, wait, let's rewind a little bit. Right. So we just talked about how, you know, it's kind of hard to get into, at least back in our day, it's hard to get into communities. Yeah, at least I definitely didn't have a Turbo Pascal community in my small town or anything like that. And so how did you break into that? Right. So, you know, W3C, you're writing the JSON Schema. Like, how did you build up kind of that network over time?

6:20B:I mean, I was, you know, just working on open source software and like, you know, throwing projects out there. And that was at the time when that was starting to really take off. And in some ways, it was actually, I think, easier at that time. Like, now there's so much saturation of projects out there. But back, I mean, that was around 2010, where like, if you created a new, like, you know, you spent a couple of days creating a new web framework or something, like people actually paid attention because like, there wasn't much else out there. So I think it was actually relatively easy to kind of like break in and start connecting with people and, you know, start emailing different people and talking about different ideas. And so, yeah, I was at a time where it was easy to get involved with what CommonJS was doing and different groups were doing standardization.

7:09A:Yeah. Right. And so while you're doing open source, were you working at companies that were very open source friendly? Like, what's the sort of, how's that symbiotic relationship work?

7:21B:Yeah. Well, again, I was at a company called SitePen and we were really focused on Dojo at the time. So I was doing a lot of work for the Dojo framework. I did a lot to write their event handling. We had like a store interface for interacting with different data stores. So that was kind of like a little bit of bridging into database from the client side and finding like what are good common uniform interfaces for interacting with different data storage mediums. So I did a lot of work with that at the time. And but that also afforded just a lot of opportunities to be involved in the broader JavaScript ecosystem at the time. Cool.

8:03A:That makes sense. Very cool. And so what?

8:07B:Oh, go ahead. Yeah. Sorry. Another thing I contributed to at the time was we also did. I wrote the original proposal for the Promises/A+ specification. So that was kind of like we had kind of put together some of the original ideas of what what should promises look like in JavaScript. And the Promises/A+ proposal is kind of actually what what promises basically are these days where when you call in a weight on a value, if it has a then method, that's kind of what defines it as a promise. And so I'm certainly not claiming to be like the originator of promises in JavaScript. But I had got an opportunity to be involved in a lot of like the original discussions about how that should work in JavaScript. That

8:52A:is really cool. I remember and I don't know if promises weren't around or I just didn't know about them. But writing JavaScript without using the sort of async/await is really painful. It's like it's every function and then and then. Oh, but any one of them could crash. And if they do, you have to roll back the parts that you did. But that means you have to keep track of it everywhere. It just like got totally out of control. And yeah, learning about that was a lifesaver.

9:21B:Yeah. Any and before promises, it was like everything was just callbacks. Right? Like right. JS applications. And it was just like callback, callback, callback, callback. And every step had to have an air handler. And yeah, it was fun.

9:37A:Yeah, that was wild. So so from CodePen, you at some point you ended up at Harper? Why don't you talk about that? Whereas were you there? Yeah. Getting or what's the Harper story like?

9:49B:Yeah, yeah. So a little bit of a transition between then. So from SitePen, I went to a company called Dr. Evidence. Kind of an opportunity to go back into medical research a little bit with programming. And we were doing a lot of work with analyzing clinical studies. And these were like super, super complex data structures that were super nested. And we were doing a lot with they were in like, you know, six normal form in a SQL database, you know, highly normalized, really well structured. But to load every study was like it took like several minutes to do the join that was required to pull a single study from this database and do analysis on it. And, you know, we were trying to like create a user interface to do these these clinical analysis on on the fly in a few seconds. And so that was kind of really where I got involved more at the lower level of like we needed to build like caching. And so we were starting to use like looking at key value stores and we were using like LevelDB. And then I started becoming more interested in using LMDB, partly because it had multi process support and it looked like it had very good performance characteristics. So I that kind of started. I became I kind of started using the existing LMDB JavaScript package. I kind of actually started taking over that that package and maintaining it and continuing to make advancements on it. And that was mainly to facilitate like this very, very like low latency interactions that we needed to do where we could like constantly be fetching different parts of these studies, do analysis on it, fetch different parts, do natural language processing retrievals, pull all this data together. But it really needed to be like these database interactions needed to happen in microseconds. And so we needed like this low level capabilities to interact with this, this data. And so I really got more and more invested

11:49B:in in this low level and optimizing these low level interactions between JavaScript and LMDB. So that package became LMDB JS and it actually came became reasonably popular, partly kind of transitively parcel and a few Gatsby Kibana, which is used by Elasticsearch. And so they started using this package. And so it actually has like over half a million downloads in NPM, which isn't like wildly popular or anything, but it's enough. That's really cool. It's a decently popular package. And then I'd also develop some serialization, deserialization libraries for MessagePack. It's used with it as well, which ultimately meant like we could get like microsecond level access to data from a data storage engine, which was really cool.

12:43A:How did you take it from like you said, you're saying multiple seconds to, you know, let's say microseconds or even milliseconds? You're talking about orders and orders of magnitude. Yeah. Is it really just kind of changing technology? Was there code just broken, or like how did you get such a dramatic speed up?

13:05B:Yeah, yeah. No, this is a fascinating part of it. But, you know, I mean, first of all, going from there are a lot of layers involved with just like the normal SQL query, right? Like you have parsing involved. You have network connection involved. You have serialization, deserialization involved. There's a huge amount of complexity. If you are just trying to retrieve a record by primary key at the actual storage engine level, those things are insanely fast. Like just doing a B-tree retrieval is an extremely fast operation. And there's a huge amount of overhead. So if you're just dealing with, like, I need to fetch this data really quickly. I need to fetch that data really quickly. First of all, doing things in process with an embedded data storage engine is radically faster because you don't have any network overhead. You don't have as much serialization, deserialization cost. So that was kind of the first step. But then as I was getting more into like optimizing this JavaScript, there are also really fascinating, just weird things that you run into. Like the simple process of having a memory pointer and then being able to access that data with a memory, with a, you know, like a C pointer—that is the bread and butter of C pointers. I mean, turning that over into JavaScript and getting a buffer that points to that same reference is an insanely expensive operation. That allocation is actually really, really expensive in a JavaScript engine. So there was a massive performance gain that could be had just by realizing that if we just use the same allocated buffer over and over and copy data into it as the mechanism for that interface between the C level and the JavaScript level, at least for small records, you get like 10 times performance. You go from, like, you know, 40 microseconds down to four microseconds or two microseconds.

15:00B:And so it was the combination of that and then employing more sophisticated deserialization techniques. It ends up that there are techniques you can use to do MessagePack deserialization that are actually faster than JSON parse. So like we can do retrievals from the database. That single fetch can actually be performed from the database, retrieved from the database, deserialized in MessagePack faster than a JSON parse can call with a prebuilt, with a prebuilt string. So it's kind of a big pile of different optimizations that came together to really achieve like, you know, this several microsecond level access to data.

15:44A:Yeah, that's amazing. Yeah, that's so satisfying with something like that, because, you know, it's just—it's just like, you know, you're marching towards something as you kind of hit that asymptote. Yeah, it's yeah, I love that feeling where things get super optimized over time. Yeah.

16:03B:Yeah. And so from there, I guess back to the journey, you know, I guess to me, this was kind of like maybe the open-source dream. In outcome is that like, make a kind of make an open-source project—like it's fun to make an open-source project. Cool to see some people use it. It's fun to see it kind of get moderately popular. And then I basically applied to HyperDB because they had been using LMDB JS. We actually call it LimeJS so that we don't have to say six syllables, but they had been using LMDB JS. Yes. Yeah.

16:40B:And so like basically make an open-source project. And I got hired at a company to work on this open-source project and build a database, build database software on top of it. So that's kind of how I ended up at HyperDB was through this through this work on the data store level.

16:59A:Wow, that's amazing. So was it so you kind of applied to HyperDB kind of as an individual? It wasn't part of a company acquisition or anything like that?

17:10B:No, no, nope. And yeah, I had a number of interactions with Kyle at HyperDB. So we knew each other. I mean, we'd had a number of interactions on GitHub issues and, you know, I solved some problems for them. And so when I applied, they were like, 'Oh, it's Chris.'

17:29A:That's awesome. Come join us. That is so cool. I'm hearing more and more of these stories, very similar to yours. And it's extremely inspiring. The one I saw—two of them I saw recently—one, and I'm not going to butcher the person's name, but the person who created FastAPI and SQLModel, I'm going to butcher it, but its last name is Jello, I think. But he actually got into some kind of incubator with Sequoia Capital, which is a VC venture capitalist fund. And basically, they just said, 'Look, you have amazing open-source projects; we're just going to pay you to keep working on them.' And it's an amazing relationship. And then the person who came up with Llama.cpp, which is a way to run these, you know, ChatGPT-like open-source large language models, a way to run them really fast on the CPU.

18:32A:That person, same thing. They started a GitHub project and started posting about it on Reddit. The GitHub project got really popular. They've just been spending an insane amount of time on it. And they have brought in Llama too. And all these things that have just come out in the past couple of weeks—this person is totally on top of it. And same kind of thing, like a group of venture capital has got together. And I think the person's in Serbia. So it's not even there's not really even like a personal connection, but they just got together and just funded this person. Like, this is amazing. And we want to be a part of it. And so, you know, in your story as well, I think if you have that penchant to create things, you know, put it on GitHub. How do you actually, you know, turn this into a question? How do you build some word of mouth? Like you create LimeJS, right? And so you create it. It's a GitHub project. You push the first version. It has zero stars, zero followers. Like, what do you do next? Like, how do you actually build it up?

19:33B:Yeah, I feel like you're asking the wrong person. I mean, I talked about it on Twitter some. And with the LimeJS project, it was actually kind of a kind of it started as Node LMDB. So there were already some existing users using that. I took over maintenance of it and then basically kind of forked it with some of the newer ideas that I wanted to implement to make things faster. Sure. So there was some natural growth there. But yeah, I mean, I've tried to talk about it on Twitter. And, you know, I think that from there, once you actually get a little bit of a foothold, like you see some other projects using something. And so I think it's kind of just organically grown from there. But I'm the last person in the world to talk about how to be effective in marketing open-source projects. Well, I

20:24A:It goes to show how good the, you know, how healthy the system is, right? That, you know, you can focus on making good content. And through the power of the Internet, you know, the collective consciousness of humanity here, we can all start to find those amazing projects.

20:44B:Yeah, for sure. I agree.

20:46A:Yeah, I think it's similar with Eternal Terminal. I had a buddy call me a few days ago saying, oh, he has some Eternal Terminal issue at work. And they were asking who knows anything about this. And he said, oh, I know the guy who wrote that. And so he called me and was asking me some questions around. It was pretty esoteric, you know, SSH type stuff. But same kind of thing. No real promotion or anything. And, you know, I've created hundreds of projects. And that's the only one that's really taken off to that degree. And it's just, you know, you can't, at least I can't really predict it. But when you do find something that sort of strikes that chord, it's really satisfying.

21:30B:Yeah. Yeah, I agree. And there's actually been projects I've had where it's been frustrating that they aren't seen by more people. And then I've had projects that it's like, please stop using this. Too many people are using it. I had developed one of the early JSON Schema implementations and didn't do a good job of maintaining it. But it became, it has a ton of NPM downloads that, I mean, they'll continue to exist and I'll keep it out there. But it's not something I continue to work on.

22:06A:Yeah, that's really difficult because you only have so many hours in a day. But it is hard to see, you know, the issues pile up. I actually, last week I went through and addressed like so many issues in Eternal Terminal. But they're just piling up way higher than I can really address. And, you know, maybe one day, I know so many folks try to talk about like we can build sort of a marketplace economy on top of GitHub. You know, so many companies have tried this. It almost is starting to become a tar pit idea. Have you heard of this term? No. A tar pit idea is something that's like so appealing, you know, feels like a warm bath. But you get in and you're stuck and your company dies. Yeah. So like personal CRM tar pit idea, like Facebook for X, Uber for X. Like these are all kind of like tar pit ideas. And I feel like, you know, yeah, like monetizing GitHub is starting to become a tar pit idea. But I do think that, you know, like Eternal Terminal is a great example. I mean, there's so many people using it. And your JSON Schema is another even better example. Somebody should be able to make a modest living making that library better. And we really just don't have the marketplace for it. But I think there's just so many moving parts. It's hard to really get that right.

23:32B:Yep. You're absolutely right. Yeah. And I agree. It's one of those things where I would love it if that could be reality. I don't know how to make it reality.

23:42A:Yeah. So many smart people have tried. I'm a little afraid to. It's like saying Voldemort or something, right? Yeah. Well, that's amazing. So how long have you been at Harper?

23:58B:I've actually been there for just a little over a year, about a year and a half now.

24:02A:Cool. Great. And we'll get more into the company after we talk about the main topic. But I'm just curious. Is it a remote thing? Are you all together? Or is it distributed?

24:13B:Yes, it is. I mean, there's a number of people. Our headquarters are in Denver. And there's a number of people that are out there. I live in Salt Lake City. And so most of the engineers are working remotely. It's nice to actually be in the same time zone. I worked for many years. Actually, this is, I think, the 15th year that I've been working remotely. So the previous companies were in California. Yeah. But so this has been just kind of a normal transition for me. COVID didn't affect work at all for me. Yeah, that's right. I just wanted to work remotely.

24:45A:Yeah, very cool. Great. Well, we'll definitely put a bookmark in that. I definitely want to talk more about Harper and that database. But we'll kind of step out here and talk about just choosing the right database. And maybe before we even do that, we should talk a little bit about what is a database, kind of in practical terms, like why would someone use a database versus using a Btree library or some JavaScript library for storing data? When should people make that decision?

25:22B:Yeah, that's a good question. I mean, there actually are probably times people can use a Btree library directly, but there certainly is a tremendous amount of functionality that is built on top of those Btree libraries that you typically use in a day-to-day work with databases. You know, databases handle the work of maintaining data in a structured format so that you actually, instead of just having raw binary data, it's in the form of actual fields or properties or columns. It handles things like secondary indexing so that you can search for records by different values and perform that efficiently. It handles things like transactions, ensuring that multiple things can be handled atomically with isolation, consistency, ensuring that it's stored on the disk drives in a durable, reliable way. You know, databases can get into the issues of management, observability, and then being able to provide higher level queries. You know, obviously, many of us use databases through SQL queries, which gives us a much easier way of thinking about querying data than having to think about interacting with individual indices and Btrees and how those are connected and related. So that's kind of broadly why we use databases, is it gives us the ability to interact with complex data using relatively simple mechanisms for querying and updating that data.

27:01A:I think it's interesting, too, like you mentioned, Chris. I think that maybe in some cases you don't need a database. I think we were having this little debate maybe in the pre-show of what makes a database a database. I feel like it's expanded a lot. So, you know, something from like a key value store, you know, can still be a database. And then you were mentioning a lot of things, which I think hits upon things that folks miss, which is how many users are you talking about? Like, is there contention for data or not contention? So in other words, does your application running in multiple places need to make updates to the same data or not? Is a big one. And then for internal tooling, it may be that each person is kind of by construction doing something slightly different. And so really, it's more of a caching transmission mechanism thing, in which case it's different. But then you mentioned schemas as well. I think that's one that we were referencing JSON earlier. But people maybe not with JSON schema, but with just JSON plain, we'll just insert a new field, right? And then stuff will break. There's no like planned way of dealing with it. And everyone says, 'Well, you don't need that stuff. I'll just figure it out.' And it's like, well, yeah, you're right. That is true. But at what cost? And indexes is another big one you mentioned that's fallen to the same bucket. If you just put opaque binary data, you know, in blob somewhere, somehow stored, could you write something to like extract and index the fields you want to dub that? Yes. And are you going to write a bunch of code that already is battle tested, robust and going to do a better job than you? Hey, I just going to reinvent a crappier version of like existing indexers. So there's not this like hard line. But I think early on, sitting down and really thinking about what you're optimizing for and targeting makes a big difference in what you select. And then also, like you mentioned, is it going to be SQL interface or not? And what are the implications of saying, 'Hey, I'm just going to shove random JSON objects. I'll keep beating on JSON. I should do something else.'

28:59A:Random, you know, JPEG pictures in these columns, right? Like, well, wait a minute. Hang on. Like SQL is not going to buy you much if all your data columns are JPEGs. I'm not. I mean, maybe it does. Maybe I'm not an expert there. It feels like it probably doesn't buy you as much, right? Could you do it? Sure. But it's not like a good choice. And so I think you end up with classically extremists on both ends, you know, no database or everything in the database. And in reality, it's probably a little bit more fluid.

29:26B:Yeah. Yeah, yeah, for sure. Yeah, you're right. Those are some great examples of where, you know, this is the reason why like Redis and Memcached and different things like that have really grown in popularity is because they are fulfilling a role of this, you know, high speed access to data that doesn't need the extra overhead of, you know, a full SQL engine. And so that does, you know, illustrate some of the different needs of databases. And, you know, one of the challenges is, I think maybe one of the primary drivers for like what database you're going to use is like, what is the hardest thing the database is going to do in terms of querying? And it's hard to figure that out ahead of time, right? Like, what is the most difficult query going to be? Is it just going to be these like by key lookups or is it going to be, you know, a three level join or something like that? So, yeah, kind of thinking ahead about that. And then the other aspect is like, what are the data structures look like? Like, you know, traditional databases have had, you know, tables with a relatively flat structure of columns that each can have a field in it. And part of the driver for like NoSQL databases, document driven databases is the idea that, you know, when we are working with data in typical programming languages, like it can be very convenient to think about data as nested structures, right? Like I have an object and inside that object is an array and inside of that array is a set of objects. And that's really convenient when I'm working in a programming language. When I translate that to a relational model, now I'm starting to get into junction tables and joins and things like that, that like, hey, I thought this was supposed to be pretty easy or felt really easy in my programming language. And now it's getting more complicated. So certainly data structures influence that as well as just how am I going to be accessing that data?

31:22A:Oh, that's interesting. I hadn't thought about this actually. That explains the rise of the ORM as well, right? So the Object-Relational Model, the sort of middleware. So if you talk about like a Ruby on Rails person would just go, 'No, this is no problem. I got you.' And they would sort of just attack it, right? By saying, 'Hey, I'm just going to basically—I don't call it what you want, middleware, ORM. I don't even know all the terms. But basically, how do I take a structured in-memory view and then sort of push it into the correct representation in a database and have that be—I don't call it a translator—back and forth between the two sides of the system or even do joins or queries on the back end, you know, appropriately?' So that you're trying to get the best of both worlds by having a description in the middle. Right. Right, right. For sure.

32:09B:Yeah. Yeah. And one of the realizations people have is like, 'Okay, if I have like an array of objects again inside of my object, and it only belongs inside of that object,' the relational, the traditional relational model for that, where you have the object, and then maybe the other table, and it's joined, and you may even have junction tables in between. I mean, that's actually pretty complicated if all I want is this single, you know, thing, this single document, right? Which could very well just be a single lookup in a B-tree. And so like part of this is, you know, do how is that data structured in terms of ownership? Are, is this hierarchy completely contained within objects themselves? Or are these arrays like references to other objects that are then shared? And that, in that sense, then the relational model starts making more sense. You know, you have these relationships between these objects and these other objects. If I can denormalize—if I can normalize them, sorry, if I can normalize them, you know, there's certainly benefits to normalization in terms of like one, one source of truth as far as where a record goes, and then the joins start making more sense. But, so there's a lot of questions just in terms of what do those data structures look like, and how do those map to a database appropriately?

33:32A:Yeah, that makes sense. I think that you touched on something really important where even without a database, you know, like kind of circular dependencies and circular references become, become really difficult to manage. Like, you know, even imagine like an email app. So you have email folders. Imagine you're trying to write this without a database, you know, and then you have a bunch of email objects. And so the folder has a list of objects. Each of the objects needs to know what folder it's in. And so if you don't do this right, you end up with this like kind of pointer nightmare, where if you want to move an email from one folder to the other, like first you have to delete it from the folder list, then you have to also tell it that it's now part of another folder. And so you end up having to change like three places. And if your app crashes, or if something happens, I have to roll that back. And it's just it becomes really difficult. And, you know, I remember SQL normalization was really popular in the late 90s, early 2000s, where people said, 'Oh, you just have to follow these rules.' And if you follow these rules, then you will always have kind of a perfectly normalized world. And so we did follow these rules. And as you said, we ended up with so many different joins. It's like, oh, a person could have at most two phone numbers. But instead of having, you know, a phone number one and a phone number two column, which would be super easy. Instead, now we're going to join to this table, as you said, junction table joins to another table of like, you know, user ID phone numbers. And so then you end up having to write this really complicated query to pull an entire object. And so there is a lot of like deceivingly complicated design decisions you have to make there.

35:22B:Yeah, yeah, yeah. And you're absolutely right. You know, we kind of grew up with normalization and cod we trust. And when he taught his first normalization goes, but, you know, the last decade or two really has been characterized more by like trying to figure out where is the appropriate place to denormalize that data. And that doesn't necessarily is not necessarily mutually exclusive with normalization. You know, there's a lot of systems out there that do have like a source of truth normalization, but caching layers that do some of this denormalization where, you know, you have a derived version of that that record where the phone numbers are in line and you can very, very quickly and easily access that. And so I think that a lot of the evolution of database has been learning to what are the appropriate ways to do this denormalization. It can go too far the other way, too, right, where you can have so much data that denormalize that it becomes inefficient to store this. And so you start looking at ways where maybe a simple key value store that just is doing this massive denormalization is a little too simplified. You want to do some denormalization. You want to have some relationships in that with that are kind of normalized to other parts. And so I think that that hybrid is really maybe the direction that we're starting to learn in terms of getting in between the two pendulum swings and having efficient data storage.

36:49A:Yeah, totally. And you know, one thing that this touches on too, and why you should use a database instead of, you know, like a B-tree or hash map that you serialize in C++ or something, you know, you want to change as your product changes. And change becomes really, really, really, really difficult, you know, changing but keeping backwards compatibility, handling migrations. You know, if you saved your data, you know, a year ago and you find, oh, I need some of those records. I need to retrieve something. And so now I need to, you know, mutate all of this year-old data so that it can work with my modern software. These things are incredibly, incredibly difficult to do yourself. And so the database that I'm most familiar with being a Python guy is SQLAlchemy with Alembic. And so what that does is Alembic is this tool where you try your best not to change the database in the database. You try to use Alembic to say, you know, create a row, you know, create a—sorry, create a column or change this type to an INT or create a new table, create a junction table. And as long as you do everything in Alembic, it's keeping track of all these changes. And then you now have this ledger. So if I have year-old data, I know exactly what my database schema was like a year ago. And I can tell Alembic, you know, take this database and bring it up to modern standards and it will execute all of these steps. And so under the hood is a ton of complexity there. I would say maybe just to tie it off, like, you know, I think that

38:49A:Databases will force you to be more disciplined. That they'll force you to do things that you can't if you're doing all sorts of pointer tricks and things like that. But from that discipline, you'll end up with a better product that you can rely on.

39:08B:Yeah. And I think where that maybe is most or at least a good example of it is when you're dealing with transactions. Transactions are one of those things where you never feel like you need it right from the get-go. Like you're like, oh, I want to update this and then I want to update that. Like why should I have to think about transactions? But it's part of the reason we do that is because once you realize, well, what do we have to do if one of these is updated and this other thing isn't updated? And you start dealing with tons of edge cases that are just incredibly difficult to think through like these types of in-between states and the race conditions that are involved. And so I think you're absolutely right when we are forced to deal with data through transactions, even though sometimes that's a little bit annoying to start with. It deals with this whole class of just really, really painful problems and makes them a lot more tackleable.

40:09A:Yeah, totally. Cool. So let's see. People are super excited now. They want to make their new game engine use a database. How should they go about picking a good database? I have a list of topics here and we'll kind of walk through them. The first one I have on my list is speed and latency. So, different databases kind of make different tradeoffs there. Why would everyone want a slower database? What are things that those databases are doing with that time? And what are the reasons for that?

40:53B:Yeah, I mean, yeah, I don't think any of your listeners are out here looking for what is the slowest database I can find? Maybe I can get advice on how to find that. So obviously that is a trade-off. There are reasons why people have ended up with slower databases. And there's a lot of applications that simply cannot sacrifice when it comes to speed. Oftentimes when you're dealing with things that are directly driving user interfaces or even more so maybe part of gaming, like, speed is something you can't sacrifice. Whereas oftentimes the things that will drive slower speeds is when you are dealing with something where there's higher levels of data consistency requirements. When you get into financial applications, there are pretty strict requirements about things not only being transactional, but making sure that you are fully coordinating any systems involved, that you have all the correct checks in place, that you have the correct ability to roll back if anything doesn't look correctly. And that is a very different scenario than, say, a database that's maintaining the positions of the players in a game, for example, or something like that where speed requires very, very low latency. Certainly, there are situations where things are slow just because it does involve complex queries. And oftentimes, that's well recognized by the people that are making the queries like, 'Hey, I'm doing this thing that is doing searching through a huge database for a very, very complex set of different conditions.' And a lot of times, there's a recognition on both sides that this is going to be difficult. It's going to take a while. So there are certainly those different aspects of it, I think.

42:51A:Yeah, that makes sense. Totally makes sense. Yeah, I mean, there's a saying kind of 'premature optimization is the root of all evil.' I think Donald Knuth who said that. But I think, again, if you're using a database, not some kind of homebrew thing, but you're using a common database, it will be relatively easy to migrate from one another. And so you can always start with whatever is the most convenient. And if you find that, like, all of a sudden, some government agency wants to use my product, and they are demanding that it's consistent, then you can switch to another database. Or if you need, if the latency is a real problem, and you're willing to be eventually consistent, then you can go the other way as well.

43:45B:Right, right. Yeah, there definitely are opportunities for that. And, like anything in programming, you want to get it right the first time because there is work involved in switching. But we do it all the time.

43:59A:Yep, yep. Yeah, totally. So, okay, the next one I have is scalability. One thing that comes to mind here is SQLite. I almost always start every project with SQLite. And maybe this is again because I'm a Python guy, and I'm using SQLAlchemy. It's very simple to switch from SQLite to something else. So I'll always start projects in SQLite, test out the project, test out the idea. Just for people who don't know, SQLite is a fully SQL database. You can write queries against it. You can just select statements, updates. You can create tables. You can do all of that. But the database is literally just a file on your computer, or maybe a folder full of files. I don't remember. But, oh no, it's literally just one file. It's a .SQLite file. And so, now that file could be enormous, right, if you're putting a lot of data in it. But it's really elegant in the sense that you don't have to worry about networking or any of that. The downside is only one process can write to the file at a time, ever. So you're not going to build Facebook on SQLite. It's just not going to happen.

45:17A:And so, invariably, you have to move to something else. But having the scalability—the reason why there are so many different databases on that spectrum is because you do get speed and latency, and you get a really smooth developer experience if you're willing to have those really constrained environments, like running everything off of a file. So SQLite is actually extraordinarily powerful, even if it's not very scalable. Yeah.

45:51B:And that actually is a great example of an embedded database. And like you're saying, yeah, there's actually big performance benefits of being able to directly access that data in process. You eliminate a lot of extra hops. But yeah, generally, as you're scaling, you are wanting to achieve a state where you can be running on multiple processes, multi-threads, even multiple servers. And that's a big part of scalability is what are the ways that we can vertically scale to make sure that we're leveraging a highly multi-core machine of modern servers? Are we going to be able to scale to larger and larger storage? And this is always kind of a classic issue with databases, is that if you are indexing data, and you're just doing full table scans, it's always actually really, really fast. All queries are really, really fast on small tables. The real challenge with any database work is not how do I query the data, but how do I query the data in a way that's guaranteed to stay fast as the data gets bigger? That's always the challenge, I think, anyway, is making sure that I can do that. And that can always be kind of deceptive when you start building an application, because again, like everything is fast when you get going. But like you always have to be thinking about, well, is this query going to be fast once the database is several gigabytes or several terabytes? And is it going to maintain that speed? And so there's that aspect of it. And yeah, like you were saying, other scalability is horizontal scaling. Can we run this database even potentially across multiple machines? What if we get too big for one machine? And then you start getting into issues of how do the databases cluster, replicate, or shard with each other?

47:47B:And so those definitely get into more complicated aspects of scaling a database. Those are all kinds of all kind of the different concerns related to it.

47:56A:Yeah, that makes sense. I was always kind of curious about this. And maybe you can help elucidate it for me. You know, there's, I mean, there's sort of single-node databases like SQLite, for example; Berkeley DB is another example. And then there's, you know, multi-node, which would be everything from like Postgres and MySQL to HBase to Dynamo to all of these other ones. And then it seemed like people were saying things like MySQL doesn't scale as well. Like I remember when NoSQL became a big thing. The thing that they were pushing was that it was just way more scalable, that you could scale something like Cassandra or HBase or one of these ones to like extraordinary degree that you couldn't scale Postgres to. But I never really understood why or if that was true or just marketing. So like, you know, once you go multi-node, is there a spectrum there, or are they all pretty much the same?

49:00B:Yeah, I mean, I think that there's definitely a spectrum there. And I think what you're hitting on is that a lot of the guarantees that you would typically get in a relational database are actually quite difficult to maintain in a distributed network. You know, you can't, for example, like just have a partial set of a table and do correct secondary indexing on it. Like the whole table has to be there to get a coherent secondary index. When you start dealing with things like foreign key constraints and cascading deletes, those are actually really, really difficult to maintain consistently across a distributed network. And so when you just take like the existing consistency guarantees of a traditional database and then just try to scale that to a distributed network, it's fairly complicated. So you eventually end up with situations where you are trying to decide, okay, what are the guarantees that we really need? And one of the advantages that NoSQL databases had in terms of distribution was kind of starting without those constraints, kind of starting with this blank slate of like, okay, we are going to think about what is the level of guarantees that we can provide, assuming that we are going to be in a distributed network and not providing any guarantees that we can't back up. And so it was kind of taking that different approach. And there's certainly ways that, you know, I mean, MySQL and a lot of these databases certainly have done a valiant job of trying to, you know, do better jobs of scaling. And sometimes like that can involve like things that are a little bit more complex, like sharding, like that involves a fair degree of like involvement in trying to understand, well, how can this data be distributed?

50:54B:So there's certainly approaches, but like carrying those guarantees of how ACID expectations worked with a single node and then trying to guarantee those same things across the distributed network is a difficult leap to make.

51:10A:Got it. Yeah. That makes sense. Yeah, I think it's PlanetScale. I want to say one of them might be Neon. And it's, I think PlanetScale actually bans foreign keys. And so you have to do the cascading deletes and all of that yourself, but what they get from that is probably much better scalability.

51:32B:Yeah. I think I remember listening to your podcast on this, and I think when he said that, I was like, yes, that is the thing that you do not want to attempt to do across the distributed network.

51:43A:Yeah. I should just dive in a little bit on that for the audience. And so, imagine you have a user account; the user has phone numbers, they have credit cards, they have transactions, and then they say, 'I want you to delete my account,' and I want it actually deleted. Not like a Google or Facebook deleted where they just keep your data forever, but like actually deleted. And so you have to do, you know, you delete that account, and then you have to also delete all those other things that are derived from that account. And that's where the cascading metaphor comes from because it cascades into the credit card table and the phone number table and all of that. And so then, to do that quickly, you need to somehow keep this. And I have no idea how this works. I mean, I'm very curious, but you have somehow keep a dependency graph really of a person to all of their dependent data so that you have that ready on hand, and that sounds incredibly difficult to do across the multiple machines.

52:44B:Yeah. And in particular, like foreign key constraints, cascading deletes have very significant locking requirements as far as ensuring that the record that's referencing this still exists while we're doing this delete. And then nothing else has come into existence that is also potentially using this. So it does, it simply requires a lot of kind of global coordination to ensure that all the requirements, the constraints that foreign key or cascading deletes provide, are actually maintained across the network. And so, you know, in the NoSQL world where you aren't necessarily guaranteeing these types of relational constraints, things get a lot simpler. And then you just simply deal with things, potentially after the fact, where if there's this record that is referencing a record that no longer exists, well, we either remove that reference on the fly or tolerate that. So there's a lot of things that can be done after the fact rather than relying on trying to maintain this consistency in real time.

53:54A:Yeah, that makes sense. Yeah, I mean, you touched on some of the extremely difficult edge cases. I mean, imagine you're deleting someone's account, but then maybe they right after they put the command, they go to another tab and they say, 'Oh, I want to delete my credit card just to make sure it's really gone.' And so now you get this; you might get it even in the wrong order where you had to request to delete a credit card while you're in the middle of trying to delete the credit card. So you get double deletes or you get even worse, as if someone may be on their phone, you know, a family member has the same account and they're adding a credit card. So you're trying to wipe the account and a credit card gets added right in the middle. And there are so many things that can happen. And if you have these foreign key constraints, these cascading deletes, like you're putting a very, very hard guarantee. And so if you're not allowing yourself even for a moment to be inconsistent, then the only way you can accomplish that is by hitting the pause button.

55:00B:Yeah. Yeah. Yeah. Which is getting back to that speed thing is the thing you don't want to do. Right. Yeah,

55:08A:Exactly. Exactly. You talked a little bit about NoSQL. We talked about it a little bit as well. What actually, so my mental model of this is, NoSQL is basically everything that isn't like a logically a table, you know, like everything that wouldn't just look like an Excel spreadsheet. Is that what is kind of a good way of explaining SQL versus NoSQL to folks out there?

55:36B:Yeah. I mean, I think that is a good starting point. And it is kind of a complicated thing because there's, there has been so much wrapped up into like the notion of SQL traditional databases. You know, I think that that has been kind of the primary conceptual idea behind NoSQL is that it's this idea of a document-driven database where, yeah, the document can be a data structure that has any structure that I want. And I can freely map that to the data structures in my application. And it may look more similar to it. And I don't have to have as much ORM magic. It's like doing this translation. But certainly like, it's also like comparing how things are querying. Like NoSQL is obviously a comparison to SQL, which is a query language. And so oftentimes NoSQL gets wrapped up with, okay, we're going to have different querying mechanisms for accessing that data. Maybe we all, it's also wrapped up into the whole relational versus non-relational. And what does that even mean? You know, part of that, part of what relational means, at least in the SQL world is like we talked about, maybe that, maybe that means or implies, like foreign key constraints. You know, one of the things that's kind of interesting about SQL is that SQL is actually like doesn't understand relationships as well as you might think. Like whenever you have a relationship between table one and table two, if you do a query on that and there's a known foreign key, you actually have to tell the SQL engine every single time how those two tables are supposed to be joined. You have to say, right on this field to this other field. I can't just say, 'Hey, give me the data that's associated from table two with table one.' It's not part of SQL, right? You have to tell it every time what that relationship is, which is kind of a funny.

57:29A:Such a good point. Such

57:31B:a good point. Associated SQL with relational, even though SQL is actually the query language itself isn't like terribly relational. We do all that with ORMs, right? Like ORMs know these relationships. They're the ones that kind of put together these joins. So there's kind of like, just like historically all of these things that have been associated with traditional databases. And so NoSQL was kind of this effort to rethink some of those things, rethink the relational aspect, rethink the querying aspect, rethink the structural aspect, how we store that data. And, so it's kind of given us a way to re-approach that stuff, I think, but it does encompass a lot. And the reality is, is that NoSQL databases, like, you know, one of the things that I've learned is that you can say that it's not relational, but and there's a lot of relational data out there. And even if you aren't doing SQL, and even if you don't have foreign key constraints, I bet your data has some relational properties to it.

58:30A:Yep. Yep. Yeah, exactly. I mean, you almost always want to reduce on part of the data. So you'll say something like, 'What is the average or the median number of phone numbers of all the users in my account? You know, is it zero? Is it one?' I mean, it makes a big difference to my product. And so as soon as you want to start reducing on parts of these objects, then you find yourself like really wishing you had SQL again.

58:56B:Yeah, yeah, yeah. Once you start normalizing more, yeah, it starts becoming more convenient.

59:02A:Yeah, that makes sense. Something you touched on that we should, we should explain in more detail are ORMs. So I talked about SQLAlchemy as an example. I'm sure there's a ton of other ones, but, you know, you can write raw SQL, or, you know, really for any database, you can write raw queries, and you will get back data. And, you know, you can definitely work that way. And there's times where you'll want to do that for certain queries. There's advantages to that, just like there are advantages to writing some of your code in C, even if most of it is in Python. But by and large, you'll use an ORM for a lot of this work. And the way that works is you can actually have the ORM generate the database. I don't really advocate for that because I feel like you can't change languages then. You're kind of like stuck, right? If your Python ORM generates a database, then, you know, you switch to JavaScript, and your JavaScript ORM also wants to generate the database. Now what do you do, right? So either you have to have some leader and everyone else follows, or just use something else, like Alembic, for example, but it could be anything, to generate the database. And then SQLAlchemy, and a lot of these ORMs, they can actually look at the database and, you know, map it in real time to, you know, your data types. So just to give a very simple example, you know, you might have a class called User. The User has, you know, an ID, a first name, a last name, a phone number. These are all just strings in your class. And with some annotations, you can now take that class and turn it into a sort of SQLAlchemy kind of a first-class citizen. And so what SQLAlchemy will do is look for a table called User. And then if you do something like, you know, 'Give me the user class where the ID is three,' SQLAlchemy will do sort of the magic to say, 'Okay, fetch this row from this table. Turn it into a Python class and then give it to the developer.' And so it generates a lot of really nice features for you.

1:01:25B:Yep. That's exactly right.

1:01:29A:Yeah, it's really fun. In the beginning, I had so much trouble with ORMs. You know, it's one of these things that's not very intuitive, especially if you have nested structures. You know, you have to kind of pull those out. But I would encourage listeners to take the time to learn something like that. Once I learned it, I was much, much more productive.

1:01:54B:Yeah. And maybe this is a segue into some of the challenges with ORMs. Yeah. ORMs are great. But one of the challenges that we often face with ORMs is that there's kind of like the classic select N plus one problem. And that problem is that oftentimes you maybe are getting data and then there's all this related data. And if you do a query and then start accessing this data, maybe each time you access that data, it has to then do another query to your database. Right? This is kind of a common problem is that it's actually kind of it can be challenging to get that initial data with the appropriate SQL query. That's going to fetch all the data that you need for your future data when you're accessing the data from the properties. Right? And that actually can be it can it can be anywhere on the spectrum from like a pretty easy change to how you do the query to like maybe it's just downright impossible to know ahead of time based upon how you're going to process this data, what you're going to end up accessing. And this isn't necessarily like a problem like ORM doesn't cause this problem. It's just kind of making it easier to access the data. And you're still kind of forced to deal with these issues of like, what is the appropriate way to query the data so that I'm reducing the amount of back and forth.

1:03:21A:Yeah, let me see if I go ahead.

1:03:24B:No, go ahead.

1:03:25A:Oh, I was going to see if I could—if I could understand the problem because I just want to see if I wrap my head around this. So the idea is, you know, let's say I just want to show someone's first name, last name, and their phone number. But they're but the user class has 30 fields in it. If I use an ORM, I'll get all 30 fields. And, you know, 27 of those are wasted. Is that the problem?

1:03:49B:Well, that can be one of the problems. But the other problem is, let's say that you're getting this list of users and they each have a relationship with their employer record, right? And so you're doing a join on it. And there's different ways this can work. It can potentially pull in—do that join ahead of time and pull in all that data ahead of time. Or maybe it's not. You just have these IDs that reference the employer table. And then as you iterate through the users, oftentimes ORM will then reactively, as you access that employer field, it will then say, 'Oh, I haven't fetched that yet. I will go do a query to fetch that employer record.' And so as I go through 30 user records, depending upon the way that you initially fetch this data, every time you access that, you are then accessing this related, doing a separate fetch to access this related table. So that's kind of the classic select in plus one problem with ORMs.

1:04:45A:Oh, now I totally get it. I totally, totally get it. Yeah, that is really painful, right? So if you're writing the SQL yourself, you would know just join the user table to the employer table and fetch all of it at once. And you just have one query.

1:05:01B:Exactly. And it's hard for ORMs because you actually kind of have to look into the future a little bit, right? You have to know ahead of time what data is going to be accessed from this, right? So it's

1:05:12A:challenging. Oh, man, that is wild. Yeah, I mean, you know, for an ORM to do this explicitly, you would have to, in my user.get, you'd have to provide a list of all the derived classes that I would want and not want.

1:05:29B:Right, right, right. Exactly. Yeah. Yeah. So and then this is maybe kind of a segue into like thinking about this problem from another approach. And that is like kind of going back to the idea of embedded databases, like, well, what if we made it so that this code that's iterating through these users is actually close enough to the database that it can efficiently retrieve these employer records on the fly, right? Like part of the reason why the selecting problem is so crippling is because we know that there's a lot of overhead to issuing each query. But if the data—if this code is executing close enough to the data, well, the internals of a SQL engine is basically doing the same thing. Like it's, I mean, there's different approaches to joins, but oftentimes when a join is executed, it's going through a table, getting a foreign key and doing a fetch from another table. It's kind of as simple as that, you know, unless you're doing like hash joins or something like that. But oftentimes it's relatively straightforward of like just iteratively getting other records.

1:06:34B:So if that—if data can access that at a relatively similar speed to the way that your internal engine is working, then you're kind of back into the realm of like the code doesn't need to think ahead. Maybe it's not even—again, maybe it's not even possible. Maybe as you're iterating through the users, like maybe there's actually like really complex logic that involves like the permissions of the user, what employer is related to another employer that dictates whether or not that employer record is actually retrieved or not retrieved. Those things may not even be expressible in SQL queries, right? And so this idea of getting code that's working close with the data kind of opens up new opportunities for doing these more complex levels of data retrieval on the fly and taking advantage of like this low latency access to data.

1:07:29A:Got it. Yeah, that makes sense. Yeah, that's a good transition to the sort of last area here, which is the database environment. Just to give an example, I built a kind of like a clone of Google Photos just for my family. So I had a little Android app and I have a database. I store all the photos on S3, which is this Amazon file system. My database kind of keeps track of the photos. But I ran into this issue where, you know, I had a—and I can't remember if I'm using Postgres or MySQL, but I had some SQL database. But then on my phone, I basically needed the database, but I can't run MySQL on my phone. So I ended up running this thing called Android Room, which I think is built on SQLite. But that's an example where, you know, on my phone, you know, it just—it's not practical in an Android app or an iPhone app to run MySQL database. And so, your environment plays a huge, huge role on what database, what set of databases you're going to be going to be looking at. So if you're on the browser, for example, or if you're running on the edge on an edge server, you know, that's going to—that's going to like, you know, change the sort of scope and the type of databases you're going to look at.

1:08:56B:Right, right, right. For sure. Yeah. Yeah. Yeah. And fundamentally, as you start like being more concerned about getting access to data quickly, fundamentally, this is a problem of getting data as close to the user as possible. And, you know, I mean, that kind of goes into the subject of like edge-based databases where, you know, we're trying to keep data as close to the user as possible. You know, we kind of have a few fundamental constraints here. And so, you know, I think this is another fundamental constraint where the speed of light kind of actually dictates like there are fundamental limits to how quickly you can get data from a very far distance around the world to a user. And the other fundamental constraint is that we know this is one of the most important things to users, right? Like there's been study after study on user interaction where like low latency is absolutely key to a high-quality user experience. And so, you know, I think this is another fundamental direction of databases is recognizing that we do need to get data close to users to if we're going to really try to achieve the optimal experience for users.

1:10:01A:Yeah, that totally makes sense. I think even in this Android app, you know, it just—it was totally untenable to wait for a database lookup. Like I just wanted to be able to scroll and see all the photos. And I mean, particularly for this app because it's meant to look at photos that, you know, your family and your friends who have agreed to share with you have created, but also photos that you had on your own phone. And so you kind of feel like, why is this taking, you know, 800 milliseconds to pull up a photo that I took two seconds ago? Right. And so, you know, and so that, you know, now also with other—you'll see this a lot with even games where there's a lot of transitions. You know, if someone clicks, I've been paying attention to a lot of the game design and game art recently. That's just the latest kind of kick I'm on. When someone clicks new game, there's always kind of like a little fade out, fade in. And I thought about what would this game have been like if they didn't do that? And the reality is, you know, it's hard to tell because they're hiding it with the fade, but it probably was going to take, let's say, at least two, three hundred milliseconds to create this game or to get from the new game splash screen to whatever's next. And if you don't have a transition, people can see how long it took to click that button. And that is kind of jarring. I was thinking about when I play really kind of low-budget indie games, that is kind of this thing where you feel like a little bit of a stutter when you click new game. And it kind of tells you that this is going to be like not a really professional experience, you know? Yeah.

1:11:53A:So it is amazingly like it's a subconscious thing. It's one of these things you don't think about until you think about it. But it has an enormous, enormous impact. Latency has an enormous impact on the user experience. And it's just phenomenal the degree to which it does.

1:12:10B:It does. Yeah, absolutely. I mean, even if you like try to use your mouse on a 30 Hertz screen, it's like, just give up. Yeah. And we're talking about, you know, a few milliseconds here, right?

1:12:25A:Yeah, that's right. Yeah, it's totally wild. It's just something about that synergy of real-time. It is a totally different experience. And you can do things to hide it. But, you know, when you're talking about databases, you could be potentially talking about multiple seconds and you really can't hide that. I mean, you have to get it faster than that. There's no other way. Yeah,

1:12:52B:absolutely. Absolutely.

1:12:53A:Yeah. Yeah. So I know for, you know, for Android, there's Room for iOS. Actually, Patrick, do you know what iOS equivalent of Android Room is like for storing data on phones? No, not sure. I'm Googling it. Okay. So that ChatGPT opened. Yeah. All right. Yeah. Ask ChatGPT. But there is something like that for iOS where it's basically a SQLite database, just like Android Room. But it's really, I'm sure it has the word framework in it. It's like Data Framework or something. Everything is a framework. But there is something like that on iOS. And so, you know, if you're on those platforms, you're almost certainly going to be using one of those. Again, you could load, you could load LevelDB, like do some C++ interop type thing on Android. It's totally possible. There's GitHub projects for it. But, but, you know, if you're just starting out, you know, use Android Room. I mean, it has the vast, vast majority of the market share.

1:13:58A:But now, you know, we, so for Android and iOS, kind of a no-brainer. What about for the web? I mean, what are kinds of things that people can do in the browser, things that people could do on the edge? What are sort of different options there?

1:14:15B:Well, in the browser, you know, there's been a few different attempts over the years to provide like native functionality. There's web SQL, and then the IndexedDB engine. Lately there's been the efforts to get SQLite running in WebAssembly, which is kind of interesting. Oh, cool. Yeah. So there's been some different things in the works for getting data to be, you know, like a database in the browser. You know, for most large-scale applications though, you typically are dealing more still with like a backend database. And so, you know, edge databases are kind of a big driver for that. As far as there's still a backend that you're going to, but it's as close as possible to the user.

1:15:08A:Yeah, that makes sense. So, you know, describe for some folks—some folks might've not listened to—we had a whole episode on Edge Computing. If you haven't heard that one, go back. It's great. But if you haven't heard it yet, kind of give folks like a little intro to what is the edge when people say the edge and what is that environment?

1:15:30B:Sure. I mean, at basic level, the edge is about distributing your cloud computing around the world so that there is a server that is close to every person that is accessing your data, your application. That obviously has a huge spectrum of how close can you get these edge compute machines to your users? Certainly if you have more money, you can have 200 server locations around the world; you're going to be able to get closer than if you have four locations around the world. But at the fundamental level, you're just simply trying to get your servers as close to your users as possible, which again is all about achieving lower latency.

1:16:15A:Got it. And so when you have a server on the edge, how is that different from renting an EC2 instance or something and installing Linux on it? Like what is that environment? Do you get just a whole machine where you can do anything you want or are there restrictions?

1:16:36B:I mean, there's a spectrum here, just like you'll have with cloud computing, as far as whether you can afford dedicated edge computing or whether you're utilizing shared resources. We do a lot with Akamai, and they have a lot of edge capabilities with like Edge Workers and things like that. But yeah, again, there's a broad spectrum of what you can afford.

1:17:07A:Got it. Yeah. I do know that Amazon relatively recently announced Lambda Edge where you can write Lambda functions for the edge. But I think it's only Node or something, or it's only some type of JavaScript run. Like you couldn't run Python or something without converting it first. Right.

1:17:31B:Yep.

1:17:33A:So what's how did that evolve? Like what's the connection between these edge nodes and JavaScript?

1:17:44B:JavaScript. I think the big driver is that JavaScript really has become probably the most advanced primary language for being able to sandbox in an effective way. Being able to take code that a user has provided and execute that on a machine has always been kind of a challenging task to deal with, right? Like, is this code going to do something malicious or take too much resources? The thing is JavaScript has been—we have been using web browsers that run; I've got a dozen tabs open that are all from different sites. This is like the most well-tested battle-tested system for taking user code and running it on a different machine in an untrusted model where different code can be malicious. It can be doing different things. And so JavaScript has really gone further than any other language in terms of this ability to host code, and do so in a safe and secure way and ensure that there's correct limitations on resources. That's why I think you're really seeing this both with Lambda, Edge Workers, and automated Edge Workers. Cloudflare has very similar capabilities where they're hosting things in JavaScript. And getting back to where I'm working with HyperDB, this is exactly the same model that we're using as well—JavaScript, hosting JavaScript as a mechanism for taking user code and being able to run that across the edge. And JavaScript just works really well because it is so battle-tested for being able to distribute and quickly run in a secure way.

1:19:37A:Yeah, that makes sense. So let's spend a little bit of time talking about HyperDB. Where does HyperDB kind of fit here in terms of? We talked about just to recap latency, consistency, scalability, language support, relational versus non-relational. What is HyperDB and how does it fit into the picture? When should folks use it?

1:20:01B:I mean, it certainly has its roots in terms of storage; it's like a NoSQL database. It uses document storage mechanisms. Basically we store object structures. We actually store it in MessagePack format because that's a lot more efficient than JSON. But it also has a lot of hybrid characteristics as well: an SQL query engine and secondary indexing, ACID compliance. So a lot of those things that really make for robust application development exist along built on top of that NoSQL engine capability. Probably one of the maybe distinctive aspects of HyperDB is the fact that it is designed to again run JavaScript application code and do it basically in process with the database engine. To achieve that very, very low latency access between the JavaScript and the database engine. When you have fundamentally a user, a client that's requesting data that can go directly to an edge server. There can be application code that handles that. It can do whatever appropriate queries into the database, fetch data as it needs to, and then respond to the user. You've had exactly one network hop. Our fundamental goal is this notion that rather than maybe going around the world to an application server that then makes another hop to a database and comes back trying to achieve basically one-hop access to data, even through the complexities of application logic and back to the database.

1:21:54A:Cool. So if you're running on the edge, my guess is it's like a full replication. So each node has a full copy of the database. Then how do you get around some of those challenges we talked about? Like if you're ACID compliant and two folks in different parts of the world try to delete the same shopping cart at the same time.

1:22:15B:Sure. Sure, sure. And the ACID compliance is at the node level. At the network level, it's eventually consistent, but that actually still means you get all the characteristics of atomic commits. You get the characteristics of durability. You get the characteristics of isolation. It just means that we aren't employing locking. So I can't lock this record across the entire database. I can atomically interact with it, but this isn't necessarily a great fit for a financial application where you need to do like a row-level lock on a record on an account where I don't want anyone else changing this while I retrieve this money out of this one account and put it into this other account. But there are a lot of applications where this idea that you still have the basic concepts of atomic, isolated durable commits, but those can be happening concurrently. We can replicate this data, resolve conflicts based on timestamps as that data comes together, and in doing so achieve very low latency replication as well as low-level, low latency access to the data.

1:23:31A:Well, that makes sense. Yeah. I mean, this is just tying a lot of things together here. I remember when World of Warcraft—I don't know if this is still an issue, but they had some issue where I guess you could be in one part of the world. I'm totally going to get this wrong because I don't play World of Warcraft, but you could be in one part of the world and like pass something to somebody who was right next to you, but like in a different part of the world because of the chunking, and it would duplicate it. So it was like, you could make a trade and then both of you cancel at the same time or something. And just because they were different nodes and they were eventually consistent and their way of reconciling was to just let you both keep the weapons. So, so yeah, like Chase Bank is not going to let you go halfway across the world and double withdraw your money. I mean, that would be nice. It'd definitely pay for the plane ticket to Singapore or what have you, but they're not going to let you get away with that. But for most situations, you know, if your shopping cart has the item twice in it because two people in different parts of the globe added the item, that's just a glitch that we're just going to have to sort out on the downstream. Right? And in exchange, what you get is all of those things that we talked about that are so important, know, that speed and that latency that definitely causes something in your brain to be really happy when you're on a product.

1:25:07B:Yes, exactly. Yep. We want people to be happy.

1:25:12A:Very cool. Yeah, I have a buddy who's a musician and he says you don't want to play kind of crazy notes. Like you kind of want everybody kind of nodding their head and feeling the rhythm, and then he'll save the crazy notes for when he's playing with other guitarists. Same kind of thing here. You know, you want people to feel like they're in this really natural environment. Right? And latency is proven over and over again to be super critical for that. Let's talk about Harper, the company. So we mentioned that you're distributed, roughly like how many people, and what's something kind of unique about Harper? It could be your mascot. It could be what you guys do for onsite. You know, it's something that makes Harper stand out from a company perspective. Sure.

1:26:05B:Yeah. I think we have about 18 people right now, and Harper is named after the CEO's dog. And so it's very much of a loving company. Yeah. I actually don't have a dog myself. I have a cat. Okay. I've considered a small miracle that they hired me despite the fact that I don't have a dog, but I think there's generally been like, there's been standup meetings with chickens on the calls. And in general, it's a very pet friendly company. So that might be a little bit of a distinctive.

1:26:40A:Oh, that is really cool. I go to this place called Civil Goat Coffee. And for the longest time there were goats right there and the goats would come up to you and nudge you and stuff like that. I think they finally got some kind of complaint or something, but they had to put the goats behind a fence. But I was a little bummed, you know, I thought the whole experience was just to watch my kids freak out when the goats got close to them. That was part of the fun.

1:27:08B:That's awesome. Yeah.

1:27:11A:Yeah. Well, that is really cool. You know, you can always go from there to DataDog; you know, it seems to be a recurring theme. Yeah, for sure.

1:27:19B:Yeah. We've definitely done plenty with DataDog. That's right. Very cool.

1:27:23A:Well, this is great. Anything else that you wanted to get out there? It could be, well, actually, one thing is, if someone's in high school or college, they might be really looking for something that's pretty low barrier of entry. They're not going to want to sign an RFP or anything like that. So for folks who are kind of really just getting started, does Harper have a product for them and how would they get started?

1:27:50B:Yeah, we have. You can go to Studio.HarperDB.io and you can sign up for a free instance of the database. And so that's one of the easiest ways to get started. You can also install it from NPM. So you can do an NPM install HarperDB and start with a local installation. And so, yeah, those are some great ways to just spin up a HarperDB instance, start creating some tables, add some data, and you can import CSV to have some sample data. And then you could get started with writing some application code as well and experience what it's like to have this fast in process access to data.

1:28:33A:Very cool. Cool. And so just so I'm clear, it's meant, you know, it really excels at the edge, but you could run Harper just on your own computer, the server part of it as well. Is that correct?

1:28:45B:Yes, that is correct. Yep. And then, you know, in general, like I think you've experienced, that's usually a great way to do development. You know, usually you want to have a local instance if you're going to be doing any significant development so that things are fast and direct and you know exactly what's going on. And you can look at things in your Task Manager and stuff like that.

1:29:07A:Yeah, totally. Really cool. Hey, Chris, thank you so much for coming on the show. It's been awesome. I really hope we've motivated folks out there to learn about using databases. You know, if you have a database class at your university, it would be great to take it. I know there's a lot of competition. There's a lot of other really exciting classes you might want to take. So if you don't take the database class, definitely take some time to get familiar with databases and how to store data, retrieve data pretty easily. Because it's an incredibly important part of pretty much everything you're going to do in your professional life. And really just thanks again for coming on the show and helping folks get started with that.

1:29:54B:Thank you so much for having me. This has been a lot of fun. I really appreciate it.

1:29:58A:Great. Thanks. And thanks to everybody out there. We've been going through a bunch of folks requests for programming languages and topics. We have differential equations, I think is the next show, which will be pretty exciting. That's a pretty heavy mathy topic that we're going to talk about. We're talking about game engines. We have a whole bunch of topics and we really couldn't do it without all of your inspiration, all of your ideas, your emails, and also without all of your practical support on Patreon. That's really the way that we kind of keep the show going, get the word out for everyone. And so we really thank everybody for your support on there and we will see you all next show. Thank you. Music by Eric Barndoller. Programming Throwdown is distributed under a Creative Commons Attribution Sharealike 2.0 license. You're free to share, copy, distribute, transmit the work, to remix, adapt the work, but you must provide an attribution to Patrick and I, and Sharealike in kind. Programming Throwdown is available on your podcast.