Crafting Code Podcast

[00:00:16] Allan Stewart: Welcome to the Crafting Code Podcast, where we discuss the importance of doing the right thing at the right time with the right tools. I'm Allan Stewart, a software architect, and lately I've been thinking about how easily I can get sucked back into playing certain games and lose all my free time.

[00:00:33] Dave Adsit: I'm Dave Adsit, an engineering leader, and recently I've been thinking about the intersection and overlap between company strategy and technology strategy.

[00:00:41] Allan Stewart: Our topic for this episode is databases. Databases are a key technology for most software these days. Almost every app uses one in some way or another. Websites all use them. Even mobile apps sometimes have local embedded databases. So let's discuss how we use databases, how they've changed over time, and some of the things that we've learned about using databases over our years of experience.

[00:01:08] Dave Adsit: Yeah, I would say the first thing that comes to mind in terms of changes over the last 25, 30 years is the commodification of databases when I first started in the industry the database was a very special server it was probably the biggest most expensive server in your rack only the database administrator could touch it which you know that's a critical thing there was a database administrator who did all the hardware and software and even helped you with schema design and all of those aspects of of interacting with the database, which was his baby. He was the ultimate decider on everything that was done on or with that server.

[00:01:56] Allan Stewart: My father-in-law was a database administrator and he could definitely see the writing on the walls in the last, I don't know, five to 10 years of his career that things were changing and that a specialized position like his, was becoming less and less useful in the industry. But he was lucky in two ways, or unlucky in some ways, depending on how you look at it. They had a really large and monstrous database that had a ton of coupling to many different apps. And so they were unable to easily move it. And so technical debt kept his job alive for the rest of the time until he was ready to retire, retire, which is probably the other good thing for him is that he didn't need to retool his career because he was at the point where he was ready to retire. Right. I would say, you know, most of us

[00:02:55] Dave Adsit: don't have database administrators anymore. We don't have anybody managing the hardware and software or rather we don't have someone from our team managing the hardware and software for the database. All of our databases are in the cloud and you can provision one at a moment's notice and you can decide how big it is you need it to be. And you can change how big it is without buying more hardware and waiting six to eight weeks and then taking it down time to install the new hardware and all of those things that we used to have to do and plan around.

[00:03:29] Allan Stewart: Yeah. I feel like things like the DevOps movement have made a lot of changes there. where it used to be something very specialized. And you might still have some operations people on larger teams. Like at larger companies, there'll be like a team that helps support these kinds of things because they have specialized skills and they can help with that. But more and more, you can just do it. Or the expectation is that a developer can just do it. And probably you need to be running them locally. and just pull down a Docker image and start running the database and off to the races. That's right.

[00:04:10] Dave Adsit: And, you know, the edit, edit, edit. So in addition to not having a database administrator, all of the transactions costs around having a database have gone way, way down. You don't have to spend the time or the money. You don't have to do all of the planning that you used to have to do on exactly how big this machine needs to be. And basically what we would do is we just buy the biggest one we could afford because you could never really predict how big it was going to get. And you knew it was going to be bigger than you predicted. So one of the other things, speaking of costs, actually, one of the other things that's changed a lot is storage. One of my first, my first home computer that had a hard disk had an 80 megabyte hard disk. And that was pretty impressive for the era. The database servers that I ran, we were very, very, very storage constrained, not just in terms of memory, but also in terms of the stateful media that we would use to write data to. And so it was very important that you store things in a database in an extremely efficient way. And so that pushed us to do things like follow high normal forms, make sure you're not repeating any data that you don't need. You don't have any unnecessary repetitions of the data on disk because you couldn't afford to have wasted data on disk. It also meant that we paid a lot of attention to record sizes. It was important that you knew that the customer record was 565 bytes. Because if it went over 590 bytes, then we might have to buy a new disk and that was going to cost a lot of money.

[00:06:07] Allan Stewart: Right. And keeping track of all the Varchar lengths for your fields.

[00:06:13] Dave Adsit: Well, and that's it, is that we used a lot of fixed length fields. And so you'd have to figure out ways to jam data into a field. Sometimes you just truncate it and lose it. Like, so there were a lot of things that happened because storage was at a premium that we just don't even think about now. I think the last time I bought a drive for a computer, it was a two terabyte M2 drive. Just, you know, throw it in there. No big deal. Like you don't even think about it.

[00:06:43] Allan Stewart: Yeah. And one of those changes that I've observed is also how we think about the database and how we place it in sort of the pantheon of designing software. When I was first writing code, the database was a first, a first level concern, right? a top tier thing that you had to think about. And you designed how the software was going to work based on the database. And even if it wasn't because of your hardware constraints, oftentimes the first level of planning would be to figure out, okay, what are all the entities going to be in our system? Which that's good still, but immediately designing them out in a table form, probably like a third normal form and say, this is how the software is going to be designed. And you're looking at tables. And I remember, it must have been somewhere around 2010 when I first encountered Uncle Bob saying that the database is an implementation detail or that it should be an implementation detail. And he would talk about how you can swap it out. He would talk about how when he was working on the fitness nest project they knew they were going to need a database but they didn't know which one and it's kind of hard to run back then and so well we'll just use an in-memory database and when we outgrew that then we used a file system thing and then eventually they said well the file system thing is just working it's fine and then they never they never changed it because it was they had set things up such that the database actually was an implementation detail and it was changeable

[00:08:28] Dave Adsit: Yeah, I was introduced to persistence, ignorant domain models early on in my career. I think through the domain driven design book, or at least that community. And so the idea that you could swap your database was always intriguing to me. And early in my career, nobody ever did that. Because once you were committed to a database technology, you were committed to that technology. Yeah. But the, the database really should be an implementation detail. And I remember early, early along in my career as well, that designing software was a matter of designing the database model. Like what are all the tables and all the, and all the fields for each of the tables and what are the data types for each of those fields? And that was what we thought of when we thought of designing software is like, what do we need to store so that it works? and now I would probably start from somewhere far different. What does it need to do and how does the user need to interact with it and what are they trying to accomplish?

[00:09:29] Allan Stewart: And we've seen microservices come along and kind of push that even further, right? As we've seen these databases become more commoditized, right? Where you can just ask your cloud provider, hey, can I have a database? And they say, sure, here you go. Instead of waiting the six to eight weeks for requisition. Right? Yeah. And so with microservices, we started looking at other practices like, well, you shouldn't share your databases and that's going to force you to have more duplication. You should avoid the data coupling that happens when you're sharing databases as far as like the shape of the data, because this part of the application needs it in a different shape than this other part of the application. even though it's the same data, we want to store it differently. Or you get also schema coupling where now I can't upgrade a table because some other team in some other division of the company is using that table.

[00:10:31] Dave Adsit: Well, and that's part of the definition. If we look at microservices as an evolution of service-oriented architectures, in service-oriented architectures, you could have different data stores for different parts of the system, but it wasn't necessary or even expected. You might have all of your services running on top of one Oracle database, and that was okay. But when you go to microservices, part of the definition is that they don't share data stores. And the reason for that is you want everybody to interact with you through your publicly published interface, not by deep coupling to your schema. And that allows you to quickly evolve inside of your microservice as your understanding of the problem set changes without trying to coordinate with other teams running other services. And so, yeah, that is enabled by, or at least facilitated by the ease with which we can get new databases. Just spin one up, go to whatever your cloud provider is, and just, you could have 10 databases in 15 minutes if you wanted. And they could all be running different technology stacks, right? You could have a SQL server on Windows and a SQL server on Linux and then a Postgres and a MySQL and you'd have every database you want. And you could use them all in different applications or different parts of your system. And like you mentioned, the same is true for running them locally. You just add them to your Docker Compose file. And when you do a Docker Compose up, now you have databases running.

[00:12:12] Allan Stewart: Yeah, exactly. You need a Redis? Well, here you go. Here's a Redis. You want a Postgres? Ask and you shall receive. So speaking of these different types of databases, let's drill into that. One of the first ones we want to talk about is relational databases. This is kind of the classic or traditional kind of database that most people are familiar with. It's one of the longest standing, long running ones and kind of the origin of SQL or SQL.

[00:12:42] Dave Adsit: Yeah. So the, you know, SQL, which is the structured query language is intended to query relational data. And there's, you know, common dialects, but then there's, is obviously every database vendor is going to have a specific things that they've done. But if you think about it like that, that is a domain specific language for interacting with data of a specific type. And so the key things about the relational database is tables with records and relationships between those tables. So the concept of keys becomes very important. Primary keys and foreign keys and all of those things. And all of that is in order to support, what is it, the ACID transactions, right? We want things to be, basically, you want to be able to have atomic interactions with your database that are then repeatable. And there's a bunch of characteristics to relational databases that we probably won't get into all the details of. But one of the things that it enables is data normalization. So you have a table with your customers and a separate table with your customers' addresses. And the customer address is linked back to the customer. So if you know the customer, you can find their address and all those things.

[00:13:57] Allan Stewart: Right. And that's how for a long time, I just, it was a given for me, but that's how software is designed. And you're going to have these relations and you're going to be joining between tables. And that's just, that's just what you do. There's going to be a report and the report is going to join on like seven tables and, and there you go.

[00:14:19] Dave Adsit: Yeah, you're right. That was, that was databases for a long time for me. In fact, I took a database class in college, and one of the things that they had us do is build an in-memory relational database. It wasn't even considered that we might do a different type of database. It was going to be an in-memory relational database.

[00:14:37] Allan Stewart: Yeah. And one of the things you mentioned a minute ago made me think, because you said, you know, the customer's address is over in the address table. But one of the issues that I've found with relational databases, and especially when we use normal forms to push out the data to different places, is that you can very often run into this problem of accidentally changing history because your data was too normalized and you edited something. So the customer's address, they want to change it. Great. And then you look at their order history and say, okay, where, what address did they ship their, their stuff to? It was like, oh yeah, they edited that. So we don't actually know anymore what the address was at the time that we shipped it, unless we, you know, wrote it out to a PDF or something like that. And, you know, it got printed onto a packing slip and there's a, you know, carbon copy or something ridiculous. And so that's one of these dangers that I have seen in a lot of projects where you can lose that data because you were thinking so much about the relations and the normalization of the data that you didn't, that you didn't consider. Well, what happens? We need, We need the address at the time of the order being placed or shipped or whatever.

[00:16:07] Dave Adsit: Yeah. And then you start doing a bunch of specific practices or techniques to cache data and things like that at the time of certain records being created. And then you're denormalizing. And you're denormalizing for specific reasons. I think that this is one of the areas where the concept of dry was really hammered into me. Do not repeat yourself. Don't have, don't repeat the data in multiple places because then you'll have to update it in multiple places. And then when you do an update, you've got to lock all those tables or whatever. Right. And so that was one of the things that I was, what I was told. In addition, normal forms are a storage optimization. We already talked about how storage was at a premium in the early days of computing. And so we would normalize data because we couldn't afford to store more than one copy of it. And so we came up with some techniques and there are other benefits as well. But one of the big drivers was financial. Don't have multiple copies because you can't afford to store them. And now we don't have that problem. And so we can have different practices around data and different things that we do with data when we have other constraints that may become more important, like access time, read time and write time may become more important. And so we may design our tables in a different way. Maybe retaining all of the history forever is more important than just storing the current state of a record and losing that history. Now, there's an idea that it's somewhat orthogonal to relational databases, but often comes up in context of them is event sourcing. And in that model, instead of storing the current state of the data, you store every single change as the data is mutated. And then when you want to know what the current state is, you just read and replay all of those events, and then you construct the current state from the series of events. And that is only possible when you are not worried about compute and not worried about storage, or rather when you are more worried about replayability and retaining history than you are worried about compute and storage. which has been something that has come to the industry over time as some of our other

[00:18:39] Allan Stewart: constraints have changed. So as those things have changed, for me, it was probably around the same time as I was learning that databases can be an implementation detail. It was also the rise of the no SQL movement. And they called it no SQL because it weren't relational databases, Although so many of them eventually adopted SQL as their language for interaction. But these are like document databases, wide column stores. And they brought some very different things to the mix. There are very different query patterns in particular. killer. I remember we worked at the same company when I learned about Cassandra and how to use Cassandra. And that really changed how I worked with databases from then on out, because in Cassandra, there are no joins, or at least there weren't. I don't know, maybe today it's changed. So denormalization and duplication were just a normal part of how you use it. It was clustered and you had to learn how to optimize your queries for clusters. But more than anything, you had to design your table based on what do you plan to query? Because if you didn't design your table well, you couldn't just have an arbitrary query. You couldn't just throw an index up like you can with relational databases and add a new query pattern. You had to think about it up front.

[00:20:18] Dave Adsit: Yeah, the document databases and wide column databases like Cassandra, they looked at relational databases and they said, you know what's really important is reading and writing data. What's the fastest write pattern? And the fastest write pattern is append, append, really append to the table or overwrite everything in that record. And the fastest read pattern is look up by, by ID. Now, like, let's just do those. We don't need any of the other stuff. Let's just look up by ID. And so you have to store everything by ID. Like, what am I looking up? Oh, well, I want to know what is the, what is the, I don't know, the customer address as of this date. And so I will have that as an ID and then it'll pull back a single document that is their address on that date. And so it, it very much changes you from a thinking about things in terms of entities and tables to thinking about things in terms of query patterns.

[00:21:21] Allan Stewart: Yeah.

[00:21:22] Dave Adsit: And it becomes very, very, very fast. And you can handle an extreme amount of data, very, very high volume. One of the use cases for Cassandra is capturing all of the readings off of sensors, very high velocity sensors, or all of your web metrics that are just streaming at the server very, very fast. And you can capture all those things. You could never capture them in a relational database because it has all of these constraints and all of these things that you have to do when you're writing data. But with Cassandra, you can just write it really fast or probably any document store. You can write data really, really, really fast. You can read it really, really, really fast. You can't do some of the nice things like joining and reporting and aggregating and all of that. You have to pull that out of the database into a separate layer.

[00:22:13] Allan Stewart: And I think for me, there were some very valuable lessons there. And so I don't use a lot of these kinds of databases as often anymore, but still sometimes I still sometimes use them. And yet it has also changed how I look at relational databases and looking at some of those advantages and saying, hey, how can I get some of that goodness in a database that I'm already using, you know, that we already have for whatever other reasons that we had a relational database, learning about the document and column databases can really help you think through some of those problems. And you can use a relational database to do the same thing, or at least to get some of those same advantages or kind of a hybrid between the two.

[00:23:03] Dave Adsit: Yeah. One of the lessons that I learned using Cassandra is that you don't have to have all of your data in a single record. You can have an entity that is abstracted or stored as a collection of records. You read all the records and then do something with them in memory to create the entity. And that was an interesting learning that I have brought back to some of the relational database designs that I've built since. So the next database type that we want to talk about is the graph database and in a graph database, you have edges and nodes and nodes are connected by edges and they're optimized for finding a path or finding the distance between items. So the query pattern is different. You're like, okay, so I've got a bunch of things that are all related, but they're not related the way they would be in a relational database. It's more like points on, I mean, a graph database is like a map, right? You think about, you could represent a map of all of the cities in your country and all the roads between them in a graph database very easily. I've never put a graph database into production. I've built a lot of prototypes and spikes that used graph databases. And then every time we've decided, we're not sure we know how to operationalize this for the scale that we want it for. And then we just drop the project.

[00:24:29] Allan Stewart: I tangentially did because I was working on a team that started building out a graph database implementation or a solution on top of graph database for authorization. And I was on that team. And then I moved into an architecture role and I was no longer on the team, but I was still advising that team. So I got some of that experience. And that was an interesting way to handle that kind of problem because you're taking those edges and nodes and using it to trace a path to does somebody have permission for a thing? There's obviously relational ways that you can do the same thing, but it was definitely an interesting take that we learned a lot from. It reminds me a lot of when I was in college. One of the algorithms that we would discuss is the traveling salesperson. And you're thinking about how do you, you've got all these cities and those are your nodes and you've got all of these routes, you know, airplane flights or, you know, roads or whatever, and those are your edges and each one has a cost and how do you optimize? And so we've, we've gotten very clever to come up with things like graph databases to, to help us answer those kinds of problems or at least represent them maybe a little bit clearer. Right. Find me a path between

[00:25:57] Dave Adsit: A and B. Okay. Here's the path. That's, I didn't expect that path. Find me the shortest path between A and B, right? Those types of things. So the next database that we wanted to talk about, and one that has become very, very important recently is the vector database, which is a way of representing concepts in multi-hyper-dimensional space.

[00:26:22] Allan Stewart: Yeah, it's interesting because a vector database is kind of designed to store unstructured data. And so we're getting away from like all the schema kind of stuff that we were used to in a relational database. It's almost the exact opposite it because you're searching based on meaning or, you know, or at least a statistical representation of, of meaning or, you know, similarity, right. You know, so you take text or images or whatever else, you know, how many of our modes that you operate on and create a vector embedding to say, oh, that represents this other things that are close to this vector are also close to it. And so So yeah, very different way of thinking about data, but it has some really useful properties.

[00:27:16] Dave Adsit: Yeah. And one of the things is that the query patterns become very different from a relational database. A relational database where you could say in SQL, I want something that equals this or is greater than this or is between these two things. Then now, you know, you can constrain the data that you get back. In a vector database, you take, for example, a string of text and you create a vector from it. And that vector could be, it is going to be some number of numbers, right? Because it's going to be a hyperdimensional database, right? Your data store, right? And so you might have 512 numbers that represent the concept from that sentence or that block of text. This block of text becomes these 512 numbers. And now we throw that into the data store and now we can query for things around it in a way that we couldn't really do with a relational database. And that allows us to find similar concepts based on our encoding algorithm and whatever, you know, like you started with a raw thing and then created a vector. But computers are really good at doing vector math very fast. And so you can find the similar things and then, you know, now you can decode them and say, this is a string of text that is represented by this concept. And so that's one that's become really important because the vector database is a critical component of the LLM space.

[00:28:53] Allan Stewart: Yeah, just recently, I've been exploring for my current job, using a vector database and embeddings to look at historical data that we have. And then when somebody comes along and enters some text, we can find things that are like that, and then offer up relevant suggestions to them. not like a recommendation engine, but more like, hey, I have another piece of work that's like X and we can recommend like, oh yeah, this is very similar. You probably want this cost code. You want this division, et cetera, which is really cool because like those things could be built before, but now we have a whole new way of doing it that is more efficient in certain dimensions. Right. Another kind of database is the time series database, which I kind of fell in love with after there was a there was a blog post that was pretty influential for a while. I want to say it was Etsy posted it about their philosophy of just log everything. And so they had a philosophy to make it easy for their developers to just say, hey, are you curious about how often this happens? Just track it. And so these new kinds of databases came around to help us handle this problem to say, hey, over time, how often does this happen? How many requests did our API get today on which endpoints, et cetera? Like you can find out, you know, how many times were there 500 error? And give you a new way to look at those problems in your code.

[00:30:42] Dave Adsit: Well, and for me, I fell in love with time series data stores when I was building my own observability into a startup that I was working on. I grabbed an influx database, threw some Grafana on top of it, and then suddenly I had visibility into a part of the system that was previously inscrutable. How many times a day does a customer access our website? How long does it take for each customer to request this data? What parts of the system are slow? What parts of the system are fast enough that they don't need to be optimized anymore right now? And I got a whole new level of visibility into our production system by using Grafana on top of InfluxDB. And it was just eye-opening. Like I had used similar tools in the past, but they kind of felt like magic. You know, you hook up one of these observability toolkits and pay a whole bunch of money because it's obviously a nearly impossible task. And then you get automated visibility into your system. But you could actually build it yourself using some fairly straightforward, simple tools. And again, one of the things that happens is you learn different query patterns. Because in your time series database, your queries are aggregations. Like, okay, show me the number of requests for the last 24 hours at a five-minute granularity. And so then you get a result set that has however many five-minute intervals there are in a 24-hour period. So it's just a very cool way to get access to different things. But again, it's a different type of database. It's not the kind of database that you would want to store your data entities in. You know, your customers and their addresses don't belong in that type of data store.

[00:32:32] Allan Stewart: But it may be the most beautiful database as far as you throw some graphs on top of it. And it's amazing how quickly, like with other databases, you build products and things that look nice on top of it. But the time series database, plus a little bit of graphing on top, makes something really neat, really quick.

[00:32:57] Dave Adsit: Well, and if you think about it, it has some other really cool characteristics, like data compaction. You tell the time series database, you have this much storage. And it says, okay, well, based on that much storage, I'm going to store every single data point until I start running out. And then I'm going to start compacting the old ones into time blocks that get more bigger and bigger and bigger the farther back you go. So maybe for the last month, you have every single data point, every single page request has a tracking associated with it. And for the two to three months before that, you only have them at the one minute interval. and then past that, it's the one hour interval. And you can retain data for a very long time, but it becomes less and less and less granular as it goes through compaction. So the last one that we had in our list, the last type of database that we wanted to talk about is the in-memory database. This one is kind of exemplified by Redis, which does a lot of things besides store data, But if you think about the, a lot of those are enabled by the fact that it is in memory, it is very, very, very fast because it's in memory. It doesn't have any disk latency or any of those other issues. It's, you know, that's your, your very fastest high volume data storage is RAM. It's not persistent, you know, it's volatile. So if your database server loses power, unlike your relational database, which will come back online and it'll be like, oh, we may have lost a few transactions, but we got most of it. Your in-memory database, if it goes offline, if you lose power for even a microsecond, like, ah, we got nothing. We're starting from scratch. And it's empty again. It's empty again, but because it's so fast, it's useful for things like data caches. I loaded this data from the relational database. It's not very volatile. I know it's going to stay the same for 10 minutes. I'm going to just store it here in memory. And then if I need it again in the next 10 minutes, I don't have to pay the cost of going to disk because it's right there in memory.

[00:35:16] Allan Stewart: Yeah. And that was especially useful when we were using older hard drives technology, right? That were slower that sometimes we call them the spinning rust because you really actually had a metal platter that was rotating at thousands of rotations per second. And you had to wait for the head to get to the right place. And the magnetic reading was hit and miss, you could say. So with SSDs, some of these other databases have gotten some significant speed boosts. But even so, Redis is still pretty amazing.

[00:35:58] Dave Adsit: And it has other constraints, right? Because it's in memory, you have to have enough RAM to hold the entire data set. Because if you run out of RAM, the database is done. Right. So in addition to the types of databases, use cases for databases, it used to be that every database you would buy a server, mount it in your rack, and then add it to your network. And then you would run these locally hosted. At this point, we don't do that very much anymore. I am sure that some companies do have their own on-prem servers and things like that. But a lot of us, we're either doing local databases on our other server, or we've talked about different types of databases that you could run locally, like an access database, a JET database, or even SQLite. we often use SQLite for local development. We don't want to reach out to another server when we're developing locally because it's faster and easier and I don't have to share schema with other developers if I just run it locally. There's also the typical staging and production environment which is cloud hosted. A cloud provider manages the database for us. That one's phenomenal because that was, that's actually what killed the DBA is someone else is managing both the hardware and the software of my database. And I don't have to think about it anymore. That was the primary thing that the database administrator had to do is administer that physical server and the related software packages on top of it. As well as, you know, helping with all of the other things like backups and optimization and whatever, because they knew more about it than you did, most likely as a developer.

[00:37:43] Allan Stewart: And we've forgotten some of those things or we've outsourced them to other people. There are pros and cons to that. But in a lot of cases, we can create simpler, smaller things. Instead of having one massive, giant database, we can have a bunch of smaller ones in which we don't have to worry quite as much because we have kind of moved the problem around to where some of those details just aren't as important anymore. more like, you know, how long it took to write to disk or things like that when we can use other techniques to change that optimization strategy. So let's talk a little bit about some coding or implementation things that we have learned as far as using databases, since we're coming much more from the developer's perspective, not the administrative perspective. What are some of the things that we have learned? Don't use triggers. Number one, triggers seem awesome.

[00:38:47] Dave Adsit: And they probably are awesome. Just that every system I've ever worked on that made heavy use of triggers has been impossible to debug or even reason about has, has eventually become impossible.

[00:38:59] Allan Stewart: I hear that. And, and partly I think it's because it removes it from where you're normally thinking, Or at least where I'm normally thinking about is that logic happens in the code and that the events are happening up there. But triggers propagate changes in ways that are often mysterious.

[00:39:21] Dave Adsit: That's the key, right? Is that triggers can propagate. You can have an insert, update, or delete trigger. And if you have a delete trigger, or say you have an update trigger. When I update this record, update this other record, which could also have a trigger that updates another thing, which could have a trigger that deletes a thing, which could have a trigger that inserts a thing. And all of that could happen while you're looking over here at the table where you just did the update.

[00:39:47] Allan Stewart: And if you're very lucky, there's not a cycle.

[00:39:49] Dave Adsit: And if you're very, yeah, if you're lucky, there's not a cycle. So yeah, that one to me, that's the first thing to do is when in doubt, don't use triggers. it's like regex that way sometimes it's the exact right solution but probably not and try to do it

[00:40:06] Allan Stewart: some other way first along the same lines i have found that i usually want to avoid views and specifically like relational database views where it is built on a query and the problem is is that the view hides the fact that it is querying other parts, other tables for its data. It's hiding what those query patterns are like. And it's adding this layer of coupling that, although it's nice for reporting, like there are definitely reasons to use views. I typically avoid them to get away from those kinds of problems where I don't understand, because they just, they masquerade as a table. And then somebody joins a table to a view and who knows what's going to happen.

[00:40:56] Dave Adsit: Right. And there is, there is a layer of abstraction that is both beneficial and problematic. Right. Right. The, like, that's, that's what abstraction does for us is it, it makes some things easier at the expense of other things. The next one is probably the most controversial one on our list. Don't use foreign keys. people will tell you that's the whole reason to have a relational database so that you can relate tables if you're not using foreign keys what are you even doing why do you not use foreign keys

[00:41:29] Allan Stewart: alan oh man there are several reasons that i don't probably the biggest thing that annoys me with foreign keys is that i like to do testing on individual entities like integration tests in my code on individual entities so if i have a user object i want to be able to create a user verify some things about them create and delete them but if there are foreign keys then all of a sudden now i have to look at other things so if the user belongs to some other kind of tenant account well what tenant do i create them with and now i'm creating like standardized like like integration test tenants that I have to keep track of just so I can test my user stuff and make sure that I can save and load a user. The other reason that I avoid them is that oftentimes I will run into situations where they've just become obsolete for various reasons. One of the code bases that I work in now makes heavy use of soft deletes in the database. So there's just a marker that says, yeah, this was deleted by this person at this time. And then it, there are some query patterns to prevent those from surfacing up normally. And so it's like they're deleted except, except they don't have to follow the foreign key rules. As far as orphaning other data, I can go in and soft delete something and the foreign keys. Don't care because the actual record is still there, but now I've lost all of the the guarantees and all the benefits that the foreign key was supposed to give me.

[00:43:12] Dave Adsit: Yeah. Related to foreign keys. One of the other things that I highly recommend that everyone avoid is natural keys. We used to talk about how great these were until we actually started using them and natural keys being like, if I were going to create a record for you in my person table, I would have the key be Allan Stewart. So your first name and your last name as a composite key, that is a natural key that represents you. Fantastic. Now, every time I want to look you up, I just look you up by, I can easily divine what the, what the key is to look you up. Unless of course you change your name and then everything is broken. Whoever changes their name or their email address. No one does. Has anyone ever changed their email address? No one's ever changed their email address. No one's ever moved. They've never gotten a new phone number. All of these become really challenging to work with over time. And so I always just use a generated key. Right now I'm using some form of UUID or ULID for everything. I want things to be ordered, but I also want to be able to generate an entity from any of my application servers, not just one database server. When I first started working on databases, the relational database that I was using had a concept of a sequence, and you could query the next item from the sequence, and it was guaranteed to be monotomically increasing. You would never get the same number twice because the sequence was locked to a single server and it used all kinds of mutexes to ensure that no, it wasn't reentrant. There weren't multiple threads getting one at the same time. And now we just use UUIDs or some form of them to get that instead.

[00:45:06] Allan Stewart: instead. Which is real nice when you can track things and create relationships, even if the data hasn't hit the database yet, rather than, well, I've got to create them in a certain order so that I can get this ID so I can put it onto this other object so that I can save it so that I can get its ID and put it onto the third object. You can just set them all up as UUIDs in the first place, create the hierarchy, and then save them all together.

[00:45:36] Dave Adsit: So the next thing that I generally avoid is stored procedures. And the reason for that is not because it isn't a powerful concept or construct inside of your relational database. The reason I tend to avoid stored procedures is that I think the business logic of the application should be in the application, not in the database layer. I don't want the data to be smart. I want I want the data storage to be dumb. It goes to that, you know, I want dumb pipes. I want dumb data storage. So I don't want to put any business logic in stored procedures. I want to put all that business logic in entities and repositories and services inside of my application code.

[00:46:20] Allan Stewart: The other reason that I avoid stored procedures is changeability. You can change stored procedures. There are ways to do it. but generally it's harder to change database stuff, the schemas and tables and things, and they need to be backwards compatible, et cetera. But even if you do change it, the rate of change is much slower. If I put that stuff in code, I can change it all the time. It's easy to change. And that's, that's probably the biggest reason why I avoid the stored procedure is that I just, I'd rather have it somewhere that I can quickly and easily and safely change it. So in addition to these things we avoid, we've also learned there's some things to be careful with. The first one on my list there is transactions. We often need transactions. They're good and they're useful, but they also cause contention and deadlocks and other problems that you just have to be careful with. And I think that you can get a lot of mileage out of stepping back and thinking, does it really matter for certain combinations of things? Does it really, really matter that this record never gets orphaned or that if this happened, this other table also got updated? Because it turns out that in a lot of cases, actually, it doesn't matter as much as you might think based on, you know, kind of the classical training of, well, put everything in a transaction.

[00:47:50] Dave Adsit: Well, and I would say that there are often ways to design around transactions rather than representing a database change as a mutation of the data or the records in multiple tables. You can often have a better design by capturing the concept as it is in a single table or as an appending data to a table versus this change or this update is actually a mutation of the data in these five or six tables. I'm just going to capture the actual thing in its own table, and then I don't need a transaction for it. I mean, technically there's a transaction. If you're using a relational database, there's just a single record update. And so the transaction opens and closes really quickly. But the other type, the types of transactions that people really think about when they're like, oh, I'm doing a transaction because they're mutating multiple tables. And there's the possibility that all the tables have to be locked in order to get the update complete. Right. And those are the ones that you want to be very careful with. Don't, it's not that you should necessarily avoid them. Just be aware of what you're doing and be careful.

[00:49:06] Allan Stewart: Another area that I recommend people be very careful with is joining tables and especially joining tables in ways that cause data level coupling. So for example, I like to write my entities thinking about domain driven designs, aggregate routes. And so if I have something like an order and line items on order, it might make sense for me to join these together in the table. And I'll have a single repository that knows how to deal with both tables. But as soon as I have multiple repositories that are all reaching over to join into something else, for example, the user object, because lots of things might relate to that. And we say, hey, well, you know, the user, we need to join the user table because we're going, I mean, we need to know which user created this thing and that thing and the other thing. And pretty soon you have a very tight coupling around the schema of that table and it becomes difficult. And now we want to change something about the user. It's hard to do. We want to upgrade to a user's version two table. Well, good luck because there's all of these other places that we have to fix. And so I try to look and see where do I actually need the database join and why? And can I just do it in code instead and make two queries and let the code match up the things that need to be, that would have otherwise been joined?

[00:50:41] Dave Adsit: Yeah. The way I think about that is from that concept of the persistence ignorant data model. I don't, like if I were to store this in a relational database versus a document database, database, where would that matter? Right? So I start from what is my domain entity? My entity is an order and the order happens to have a collection of order lines. Okay. So that's how I've modeled it in my domain. Okay. If I were going to store that in a document database, I hand that entire thing to the document database repository, and then I would serialize it into to the document database as a single document. I want to take that same concept to my relational database. So I'll have a relational database repository that takes the entire order with its line items and serializes it, of course, into two tables, because that's the characteristic of a relational database is I need to, I need to store different types of data and different types in different tables. And then when I query it back, I get it back as one thing. and so all of that detail, all of the complexity of the database is in that repository, not scattered across my code base, right? Which is one of the reasons why I don't like to use ORMs because ORMs make it easy to do the wrong thing with your data. And if you are using an ORM, I want that abstracted behind a repository layer. I want to control the query patterns. I don't want arbitrary access to the database base happening anywhere in the application. So I agree with what you're saying. And these are some of the ways that I think about it in order to help myself understand and make decisions around where to put access for data, right? I want to load the user and all the orders for the user. Well, that's going to be a user query and an orders query, not a user orders report query, because while that may be more efficient overall, I'm taking the still quite expensive database server that is often my bottleneck for my entire system and I'm throwing something complex at it to do instead of giving it two simple things to do and then putting the complexity of joining those together on my application server, which I can scale horizontally on commodity hardware. And some of these constraints come into play like around costs, et cetera. You can only scale your database so big before you have to

[00:53:12] Allan Stewart: start having multiple of them. Yeah. And so there are always the trade-offs that you have to consider with those because there's not, there's not a single right answer. And that's why we're saying be careful with these things, right? Be careful how you join. Yeah. Another one is indexes. Be careful with your indexes. If you don't have an index that you need, you're going to have a bad time. If you have too many indexes or it's too expensive, or you tried to cover the whole table with a covering index, you're going to have a different bad time. And I'm reminded of a time when we had an outage of about an hour because we added an index to a table that was a heavily heavily utilized, very busy table. And the index required some locking, I think. And it took a very long time. It was also a rather large table, if I remember correctly.

[00:54:12] Dave Adsit: Yeah. And I think we added the index in the staging environment and it was like milliseconds. We're like, great. So then we added it to the production data and the production data was substantially larger than the staging data. and the index calculation was substantially longer and harder. And that's the problem with not enough indexes, your database is going to be slow. Too many indexes, your database is going to be slow because every time you insert or update data or delete data, probably, you have to update all the associated indexes with that table. And if you have one or two, then that's fine. And if you have 17, you're probably going to have a bad time. You should start looking at what are the query patterns hitting this data? And is this data, is the schema appropriate to the queries that I'm trying to do? And that's something that we didn't get into and probably don't have time to get into is like there are schema designs specific to different purposes. Your relational database schema for your online transaction processing, your OLTP server is is going to be very different than the schema for the reporting server that is the same data, right? You're going to replicate data to your reporting server, denormalize it, flat, you know, create tables specific to the queries you want. And when you do that, you're going to end up with, you know, just completely different use cases and completely different designs based on the constraints and the outcomes that you're trying to accomplish. Even if it is the same data in both of those databases.

[00:55:49] Allan Stewart: Which just kind of brings me to this idea of keep the database simple. Going back to the idea that it's the implementation detail and you might change it, you might move to a completely different kind of database. The more you've kept it simple, the more opportunity you'll have to be able to change. And then speaking of change, a few other things that I recommend, automatic schema updates. Yep. Very, very nice. Add them to your CI pipeline so that when you deploy to an environment that any associated database changes happen as part of it um because it really sucks to forget it's like oh i deployed to production and i forgot that the database change hadn't been deployed and And now everybody's mad because there are 500 server errors.

[00:56:45] Dave Adsit: There's an outage with a rollback. Right. Treating the database like any other part of your system is critical. And that is one of the things that the CICD pipeline is going to do, right? It's going to continuously deploy updates to that system so that it's always clean and in a good, healthy state. date. One of the things that I learned, we learned working together on a project is that you can do zero downtime database migrations and table migrations. You can say, Hey, here's version one, here's version two of either the table or the whole database. We migrated data from one data store to another from, and it wasn't even the same type. You know, we migrated data from from a relational data store to a document data store, or actually a wide table data store. We also migrated data from one document database to another. And there's a lot of patterns that you can use. We've talked about some of them in the past where there's the dual read, single write, or dual write, dual read, compare. I'm going to return the data from the original database while I'm tracking the data into the new database. And then I'm going to return the data from the new database. If it Matt and compare it to the data from the old database. And then eventually you just stop using the old database. Right. Or at least for that, that table. And that is facilitated by having that abstraction layer in your, in your application code, where you say all the data access goes through these repositories. Nobody is doing ad hoc data access anywhere in the code base. It all goes through these repositories. So I'm going to use these repositories as the choke point for transitioning from database one to database two. And we've talked about this before. We use the decorator pattern. We have three repositories. There's the repository for database one, repository for database two. They have the same interface. And there's the migrating repository that uses both. And again, same interface. And so from the point of consumption, you don't know or care which one you're using. All of that is handled inside of that tool. And so those are some of the things that become when you have a persistence ignorant domain model, when you treat the database as a data store instead of as the heart of your application system, you can start to do some of these types of things that make your life a lot easier. Make it easier for you to do the right thing at the right time with the right tool. Sometimes the right tool is a relational database. Sometimes the right tool is a graph database. Sometimes the right tool is a document database. And if you've set yourself up so that you can migrate between them as needed, and you're following patterns that allow you to abstract them, then you can use the right one, even when that changes over the life cycle of your application.