Scale By The Bay 2021 : Rob Hedgpeth, Relational Databases Don't call it a comeback!
Recording: Scale By The Bay 2021 : Rob Hedgpeth, Relational Databases Don't call it a comeback!
oh thank you yeah sorry cut out there just a little bit at the end i think i think you you handed it off to me if i'm right right yeah okay awesome yeah so hello everyone thank you for joining me on this session is we're going to be talking about specifically relational databases and a resurgence as of late we probably noticed but first things first just a little introduction to myself thank you very much for that uh the paragraph of introduction uh i say fortunately for the world as far as kind of ugly websites and since evolved really just for the sake of the world not because i write spectacular code or anything like that i am indeed the world's okaiest developer uh and i work on the developer developer relations team at marietv so basically i try to enable really good developers to do really awesome things and then i you know try to just help facilitate that if after this session if you have any questions for me uh feel free to hit me up at rob h mariadb.com you can follow me on twitter you can even judge some of my code on github that good for a laugh you know especially on a friday if you're looking for a good time but today we're going to be talking about databases and i like to make things simple starting off so that we can kind of build up and get everybody running at the same pace but as we think about databases we think about essentially just data that right that we can stick in this i've got kind of this this image here of something that that forms some kind of bucket um or some area you store that data in and the idea is that you persist it that's simple enough but how did that become so much more right over the years we've seen terms like nosql sql newsql any kind of sql we can think of right relational databases spatial databases non-relational databases and the list really goes on as far as how you can be able to maybe store and even access data and what all that means right so we're going to take a look at that and essentially as we you know dive into relational databases and really that resurgence that we're seeing recently but first we need to take a step back in time and take a brief you know brief look at the history of databases well when i say that right and i kind of describe the definition of data and being able to persistently store data certainly we've been able to do that before this timeline right so as you look at kind of starting at the 1960s well i mean we've been able to store data in a variety of different ways we've been hammering on stone tablets we can write it on scrolls and there's even these very ancient things called books that we used to use uh that we can store data in as well um but what we're specifically going to take a look at is you know this idea of the computer era right this modern computational way or digital way of being able to store and persist data and that really the story kind of starts in the 60s and really really centers around ibm small little company you might have heard of uh well apparently they are a very big deal especially in terms of the database history and we're going to dive a little bit into that in early databases right so as we kind of take a look at the very beginning in the 1960s it was all kinds of experimentation going on with persistent data storage and this was largely led by bachmann charles bachman and a group that he had at ibm that were really chartered with this idea of being able to store and persist data and then be able to access it for what would be the modern developer at that time and so they pumped out a couple of models right these you can think of them as processes or systems or organizational techniques for being able to both store and then access the data for whatever you want to use it on and there are a couple of models the network model and the hierarchical model we're not going to dive too much into what these are other than you can kind of get the gist of a network you know that makes sense we have an idea and then you look at the right-hand side of the tree but what you may not know about these is that they were nasty to implement they were you know hard to to maintain and even harder really and more confusing really to access the data in a way that developers could focus or keep the focus on whatever solution they were trying to create very similar to today instead of having to always get in and constantly maintain and you know work with the database and so that's what really introduced the relational era right this this idea of being able to use relationships which we'll get into well that was kicked off by a guy named edgar cotts or eg cod also worked for ibm in 1970s wrote a paper and really the idea was to create a simpler way of being able to store and access data and so he wrote this paper which then kind of sparked this movement of the relational system or modern relational system as we know it today and just for a quick refresher what that means is that its simplest terms we've got a collection of rows and columns which form a table in that table then you have data types right the ability to specify you've got an integer a var car a string you've got a bit or a boolean value something like that where you're actually getting to specify not only where the data lives inside of this table but what it actually consists of or what you can expect and then of course comes in the other part where these tables are entities you can think of you've got more of them and you want to be able to relate them right create relationships between the different entities storing something in a tabular manner that you can connect to a different table data that's stored in a tabular manner and this is for a couple of reasons right as i mentioned before eg cod and ibm were really looking to simplify the ability not only to store the data but how you actually go in and retrieve it and use it for real applications and in turn if you think about the 70s and the kind of limited resources that they had at the time for modern computers or you know computational engines then you really need to eliminate redundancy to make things super efficient so that it would work really well and the relational model lended itself to this as opposed to other data models that we've seen at that time really well and ultimately it was all around this idea of being able to improve integrity being able to know what the data was you know or what the data is supposed to be and knowing exactly where it is this all comes together to be able to very efficiently and very quickly be able to get up and running and use a database within your system and this became so popular that in you know throughout the mid 70s into the the mid 80s around 1986 actually is when it became a standard but this this language right used to manipulate all kinds of things specifically targeted at the time at relational databases and it's called the structured query language and the american national science standards institute um in the you know the standards institute for the world basically said hey we're going to get behind this and this is essentially what we want to use as a language well this was really kind of kicked off and you know really developed by a couple of other ibm so we're starting to see a trend here where ibm is really heavily involved in don chamberlain and ray boyce really kind of kicking that off and they came up with this idea of this standard declarative language and we'll dive a little bit more on that with the next slide but this declarative language that defines a couple of different things as you can see here now some of these came a little bit later but as you move from the left where you've got the data definition language you've got data querying language data manipulation for dml the control language and for transactions that eventually came and we don't have to dive too much into these but this is everything from being able to create some kind of construct or entity to be able to store data into uh to being able to of course query it to manipulate the data inserts updates eventually you got this idea of being able to control access to particular data and then it this whole idea of transactions right being able to create atomic type app operations that either are going to happen or not going to happen and be able to have that consistency and reliability so that whenever you build something it can maybe possibly not out of date or the wrong type or something like that and for a refresher for sql statements what this looks like in a practical standpoint is you can think of the ability to create again this entity or this table right you've got a definition here so create table and this new table called tasks with a couple of different fields id description you're of course adding you know different parts and descriptions about those fields and how you want to store that not necessarily so relevant but that that creates the entity and then you know being able then to use the dml or you know the manipulation portion to actually insert information into that entity and then of course the whole idea is that you know you want to create an application or some kind of solution where you can read that from right some kind of querying language but as you can see as you you know kind of dive a little bit deeper into what these statements are saying there's nothing about these statements that are saying specifically you know go to the you know spot on disk and store you know this information for the task in fact you don't have to worry about that and that's where the declarative nature of sql comes in where essentially you're just focused on the end result being able to create a table being able to insert data into the database or being able to select data the actual parts and the bits underneath the hood we'll get into a little bit more as we talk about database management systems and the implementation of sql and why it being declarative is so important but that's the gist of it right and so sql is taken off and you know it's it's fire at this point you know as the kids say and i'm sure kids back then said it too like this is fire uh structured queer language is awesome we're gonna build everything on this and in fact that's pretty much what happened for a couple of decades right we some of these you know smaller companies you may have heard of oracle sql server and mysql kind of cropped up in you know the 80s some of these companies existed before but their database systems cropped up really in the 80s and 90s and started to gain a lot of popularity right really starting to create applications and different types of solutions using a relational database well this was facilitated in large part by something called the database management system you may have heard of it dbms or rdbms and what this means is that as i spoke about sql before and really the relational databases that being able to connect those things in some kind of standardized or consistent way that users can use reliably involves be able to create some kind of management system and this added a bunch of parts right it added things like security or authorization authentication the ability to handle multiple sessions right so if you've got an app or you've got individual users using a client to be able to connect to it and then you've got the implementation of sql themselves which can vary slightly right as you think about the structured query language as a standard the implementation may be a little bit different for oracle as it will be for microsoft sql server or mysql but this idea of the database management system is what facilitated what we now know as in large part modern application development so much so that what started as you know smaller transactional or operational applications where you're wanting to save you know crud information or you know you're wanting to build an application to store some kind of data well that as we as we know can start small right as you're solving the problem and then as time goes on you may accumulate more data or as the application becomes more complex you may be taking in more data or maybe even you're pretty popular and you've got quite a bit of users all these things happened at once and so over time specifically in the the mid-90s into the early 2000s what happened was is that data exploded right and as you see here from from bottom to top we move from more transactional right where we're specifically just trying to work on the crud information or operationally sticking data inside of the database with our applications to being able to have analytical usages of that and that's because over time right over usage we have massive amounts of data itself that we can start to use to help us you know facilitate the application or or maybe you know help guide user input or the way they may use the application based on past behavior right a lot of things that we're seeing now within the data world and well at that time really primarily in the you know kind of mid 90s early 2000s we started to experiment around of how we are going to fix you know our issue of things like decreased throughput so as we got more data inside of our monolithic relational databases we needed that increased throughput and we really needed to have decreased latency basically the the golden ticket for creating a very snappy uh popular application right that users are accepting as their expectations grow and grow so we did what anybody would do was we maxed out this monolith you know these machines we just bought a bigger beefier machine and this this idea of being able to just buy a bigger box or more resources is known as scaling vertically right being able to allow more space basically more memory to be able to have available to your database system so they can do that well these machines cost more money and it's kind of an endless process right data is just going to keep growing and growing and so this isn't a very i guess resilient or maybe flexible way in the future of being able to to to maintain that scalability or handle that scalability and so took a look at a different approach well what if we bought a bunch of medium-sized machines or small machines or something like that instead of scaling vertically we just decided that we would just add another machine and then we could start to push the code into maybe more application logic to say hey if there's this data it goes on this machine on the left or this database on the left if there's this data it goes machine on the right and i mean that sounds like a pretty good idea right we can just scale horizontally well this whole idea of partitioning or what became known as sharding unfortunately just isn't isn't something that is easily maintained right it ends up being very complex and it pushes what normally would have been in the database layer into the application layer and so more your burden is on the database administrators application developers maybe some sysadmins in order to help facilitate these things outside of the database management system so it ends up being a very complex and ultimately brittle way of solving the problem and we limped along for a while until 2009 2011 time period we saw some papers come out on this idea of a non-relational database or a nosql database and this was this no sequel revolution it really kicked off in that 2009-2000 time period 10 time period and what the easiest way if you're not familiar with nosql and if you are i hate to disappoint we're not going to dive deep into it but if you're not necessarily so familiar with it the key takeaway here is that very similar to the 1960s where ibm was really experimenting around with different types of data models that just very weren't very useful or very feasible at the time based on the different resources that were available different hardware that was available now in 2000 or at least jumping back to 2010 a lot of this stuff was very feasible right we didn't necessarily need to worry about the horsepower of a machine or memory or storage of these things that existed or didn't exist back in the 70s and so we could take another look at this idea of using different types of data models as opposed to the relational model in order to be able to both store and access data and this is awesome because these types of data models were great right you talk about document key value cat graph column value a lot of these things which i'll get into we can relate to modern database systems that exist today and but their charter was to try to solve a variety of different problems that they were hitting now i mentioned this idea of scalability but there were several other problems at the time that we were starting to see within modern application development that relational databases just weren't great at handling you know scalability was one thing but this ability to create more flexible data right so you didn't have to get in there and do a schema update you know to tables or collection of tables every single time and then have to worry about versioning you know as mobile development and iot became more and more popular this idea of data availability solving you know being able to have data that's available not just necessarily within your locale but maybe across the world and a big one was performance being able to use very specific data models right as we saw a document and key value to do very specific things right if you think about caching or you think about being able to you know pull a document that not necessarily concerned about relationships necessarily having to build all these relationships just pull the document as a whole can be very efficient and very quick and that's the whole point right so the nosql's purpose right it's real mantra is that if you think about you know all these these people pushing an object you can think about nosql as this person out in front and this this object right is a sphere's shape and a sphere you can think of as being very good at a couple of things extremely good at it right you can push it for instance it's very good at being pushed but the downside of that is is it's not very good at holding up against a strong wind right because the whole idea is that it can move so it's not going to sit there and remain stagnant if you needed to hold up against the wind and if you wanted to stack different balls on top of it it's not really that great either on the other hand some of the more older school relational systems one of the reasons why they were so popular and so versatile even now before we get into more modern you know innovations is that they're extremely general purpose right they can do a lot of things and they don't necessarily do everything you know the best but they do almost everything extremely well and things like for instance you can push it it's not the best out of it um you could certainly have it hold up against a strong gust of wind and then you could certainly stack things on top of it right so it creates this idea of more general purpose use case and that's important because at the time no sequel is really seen as the answer right the silver bullet that was going to solve all of our data problems but unfortunately some of the things that we realize is that we still needed transactional support we still need this idea of acid compliance and this atomic operation and consistency immediate consistency to be something that we needed within our you know real operational systems you know systems of record being able to create actual applications in the enterprise world that we could rely on and so this left the door open in a good way right it turned what was the nosql revolution into this idea of the data revolution right being able to have a variety of different database options to solve a variety of different problems and we've got a spattering of nosql and new sql and regular sql and all kinds of different database management systems on here that can solve all kinds of different problems and one of the really cool things about this whole data revolution is finally getting into this idea of the relational resurgence right relational databases throughout that decade right 2010 until now just didn't sit back and say oh we're not going to innovate we're not going to do anything else in order to modernize relational systems even though a lot of companies out there for a large part still have to use relational systems but of course want some of the modern benefits that we've seen from things like sharding and horizontal scalability and flexibility from nosql and so some innovations have come throughout the last you know decade and really last five years pretty heavily around sql so again kind of keying into the structured query languages and before i mention that structured query language is a declarative language so what happens underneath the hood um is is completely up to the implementer right so the implementing system that lets that happen you're simply saying oh i want to create this or i want to insert that or i want to select this and that's very important because as we started to get into being able to take the lessons that we learned from the nosql movement relational databases started to incorporate things like semi-structured data this ability to have more flexible data storage mechanisms and access and retrieval and manipulation as i get into by combining both relational and non-relational what you could think of as you know semi-structured data so this ability to have still have your tables but then be able to store json in there and accomplishes this by keying into the structured query language right being able to have functions or you know executable chunks of declarative code that you say hey go validate this json and it validates it then it can put it into say a string field in the table and then every operation where maybe i want to update the json inside of this table it can run through a validation like yeah this is this is valid json or you know be able to sift through an array or even be able to add constraints right be able to mix some of the you know benefits of having relational databases with with semi-structured data but also that wasn't really the end of it right just getting started where something that existed for a while in this whole idea of data warehousing was being able to store the data differently so rather than on the left hand side we've got more row based or tabular based right where you've got a table that may exist in a file or multiple files instead we decided to incorporate column-based format or column-based storage into relational systems where you could split up the columns and store them into an individual file or you know multiple files and why is this important well if we take for instance a single an aggregation query of something like average where we're looking at the average of column nine and we're wanting to see it from a particular table well in a row based system what's going to have to happen if you want to average that you have to scan the whole table on the left hand side here take that take that box away now not too distracting but on the left-hand side you're gonna have to scan that whole table right and what's gonna do is theoretically if every row were 200 bytes and you really just wanted to average column while you're having to look at about 200 gigs of disk io right all that data on disk in order to be at one billion records in order to be able to have that aggregation on the flip side if you store things in a more columnar format you're still getting the advantage of using structured query language and relational systems but you only have to look at that file or multiple files for column nine and that can be significantly less if the column size is only four bytes you're looking at one billion rows that's only four gigs of disk io so immediately you get performance drastic performance improvements by simply changing the way that the data is stored in access right and still being able to use it or access it using the structured query language and then that opens up a whole other can of worms where now you can combine row-based and column-based information into a single unified query right being able to cross engine queries across you know look at row based data and column-based data so that these applications that want to do operational tasks but also want to be able to combine the benefits of maybe billions of records of historical data you can do that all within split second all under a second without having to add things like indexes to your column-based information simply store it differently and be able to incorporate that so you get this whole idea of real-time analytics through something called htap or hybrid transactional analytical processing pretty long name just think of it as like operational analytics and lastly i want to mention this whole idea of distributed scale it's kind of a subset of something called new sequel and it takes the lessons learned specifically from nosql and being able to distribute data sql data in these things called nodes right and that may be familiar if you're familiar with nosql and you have a cluster of these nodes and then each one of these nodes can be individually communicated with right so if you've got an application you can talk to node 1 or node 3 but you can retrieve all of your information and be able to do that because underneath the hood we use these things called consensus algorithms that essentially will do node to node communication in a fully distributed manner right so it's a little bit more complex than that but the idea is is that like nosql you get this ability of having to be able to scale up right this uh theoretically unlimited scalability right so you could scale up to handle your massive workloads in a relational manner so you get all the benefits of elasticity and scalability but you don't have to worry about missing out on things like modern transactions and you know asset compliance plus as opposed to no sql you can implement this in a way that you're used to rather than a lot of the goblin that comes with how having to manage acid transactions in you know a language for one nosql engine or another sql engine you can do it using sql of course you get the ability to have elasticity or kind of scale down so that then you get to very efficiently use the resources right so rather than having to buy all these machines up front you can combine this with modern innovation such as the cloud and then be able to use distributed sql in this way in the cloud that allows you to not only have data that is massively scalable and elastic but to be able to be very cost efficient as well and with that i would like to say thank you very much uh for joining me on this journey as we looked into just the modern innovations of databases in general and how we're still just learning lessons and there's great data experiments if you'd like there's a short plug here but if you'd like to know more you can head out to marietv.com developers everything there is completely free completely open source and there's a lot of different types of developer content on being able to use you know a lot of the modern innovations i went over such as json and column based storage and distributed sql if you have if you have a chance go and check it out it's all free it's all open source so thank you very much you