SBTB 2023: Gwen Shapira, Keynote: Things Databases Don't Do (but should).
Recording: SBTB 2023: Gwen Shapira, Keynote: Things Databases Don't Do (but should).
so amazing being here with all of you and I was thinking the last time I was at scale by the bay was obviously pre- pandemic and back then as Alexa said I was at confluent I Was An Architect I talked if I remember correctly about how to make kfka streams reliable on top of kubernetes so fast forward four five four I think eventful years and good memories brought me back and it honestly I forgot how beautiful the place is and how awesome the community is and it's so good to be with you here and I chose to talk about things that databases don't do and I really appreciate all of you for being here because it sounds like such a silly topic when you think about it occasionally there's so many things databases don't do right I mean I woke up this morning I manage a lot of databases these days not a single one made me coffee this morning I had to go thankfully Alexa and his team had very good coffee over here otherwise who knows what may have happened worse than being a bit silly the talk may sound like it's a bit useless okay you're going to sit here for an hour half an hour whatever listen to me talk about stuff databases don't do what do you have at the end of this half an hour you have a list of stuff databases doesn't do what are you going to do with this list are you going to go out and write a new database well if you are going to do it please come talk to me afterwards because I need some people who are crazy enough to want to write a database but if you are a normal developer who probably want to do literally anything except write his own dat database then what do you do with the list of things databases don't do well there's two very useful things you can do with this list so the talk is not as useless as it sounds the one thing you can do with this list is to know what are things that you are very likely to need to do in your project and you have to do them yourself because unlikely that your database is going to be much help on that and I'm going to talk about things that we all had to do over and over and over again so very little science fiction and I'll point out the science fiction a bit in um the other side of it is that for everything that I point out the databases don't do there is one or two databases that actually do do them and if those specific things are actually extra important for you that it can be a method of actually picking a good database for the project you are about to embark on so thank you for showing up for a talk that sounds a bit H silly and a bit useless I promise it will not be completely useless and worst case scenario it is going to be a good rant because I'm annoyed about every single one of those things separately and extra I not about all of them put together okay so there are few categories of things that databases don't do I'm going to have one or two examples in each category things that databases don't do but actually claim to do things that everyone needs and databases don't do things that software Engineers specifically need in databases don't do and then some things that may be a bit more futuristic although honestly they become less and less futuristic by the day so who knows so to start say one thing that a lot of databases claim to do and they don't is claiming to be able to do everything pick any database in the world anything that comes to mind and it's usually architected in a specific way for a specific purpose so EV it's usually databases when they're good they have good architecture good architectures are coherent and cohesive they have a lot of small decisions all aligning together to support being good at something it's everything from how you lay data on the dis the type of indexes that are available to you the way memory is laid out the way the network is optimized every decision that has been made has been made to be good at something and it's not the same thing for all databases if you have relational you have the non-relational you have all TP versus data warehouses they're all very very different and a lot of them say no I'm actually good at everything I can do oldp and data warehouse I can do um no SQL and SQL I can do relational and non- relational and documents and graphs in my experience it cannot Technic technically speaking it cannot possibly be true how is your files optimized what are your indexes like how do you allocate CPU to tasks all those things actually matter H so I tend to be extremely skeptical for database that claims to be good at everything because either they're good at one thing and kind of fudging the rest or they are not very good at any one of those things so that's my take and something that when we started a comp my company and I sat with my co-founder and kind of thought through how to design our database that was one of our Insight is that we cannot possibly be good at everything everyone wants it's very tempting to say I want this one database that will do absolutely everything for me I won I will never need anything else H but I for every we think that the same way that databases are built for a purpose that data platforms are engineered for a purpose and the way data platforms are built the purpose is usually a business goal or a specific type of business and you pick and choose usually more than one database a set of Technologies you put them together and make sure that this architecture now aligns with your business goal versus every one of the database which aligns with a more technical goal if that makes sense so don't believe databases do everything the on the other hand if you it's your job to build a data platform that does everything for your specific vertical okay so let's go to stuff that databases don't do and we all need all the time and the first one is soft deletes and I'm pretty sure that everyone in the audience have had to deal with it about a million times I've had to build it billion times in my career the idea is that a user goes to your application and clicks delete and the last thing you want to do is actually delete the data why that well because we all know I see a lot of birds even longer than m in the audience we all know that it's a matter of 10 seconds 10 minutes 10 hours 10 days until they're like oh [ __ ] I actually needed this can I get it back no no no really really needed back and database recovery is usually one hell of a pen in the so the way we normally do it is Mark it as deleted we turn a flag we put a date and then we have a lot of applic code saying if this flag is says deleted just pretend the row is not there and then you have another process 30 days later I I I don't want to go to jail I don't want to pay gdpr fine 30 days later actually delete everything that was deleted 30 days ago and nobody suddenly like oh my God I needed it so we did it a million times and we're still doing it and I just did it recently again why why do we have to do it over and over again isn't it obvious for the last 30 years people have had to do it can't databases actually have soft Elite built into them if it's so obvious anyway yeah so that was the first one the other one and this is the one that I am focusing on when my company and this is the first time I'm actually allowed to say it out loud so this is pretty cool H is tenant awareness if you think about how databases are used today especially in software as a service we usually use the same database for a lot of customers H if you are say Salesforce you have one big I don't know oracle myel whatever it is that Salesforce uses and most Salesforce customers somehow fit into this one database this is not something the databases today make easy even though there are millions of businesses probably that all have to do the same pattern so what we normally do is pick one sorry one step before that if you think about what you need in a database that has a lot of different customers in it it may sound like what do I need I just need to put them all in database but there is actually a lot of small things you need you may you need and you don't maybe you don't know that you need or if you have a lot of experience you don't know that you need you did first of all most important rule of putting a lot of dat customers in the same database is don't let them see each other's data we call it data isolation this one can be a career killer SL business killer it's really really bad customers absolutely hate it so this is important most places you also want to be careful about performance you want to make sure that if one customer suddenly has a burst of activity it doesn't actually affect everyone else and then there is a lot of things about being able to upgrade your customers independently and to recover them independently um there were I think a year and something ago at lasan had a very visible outage it actually lasted for weeks because they didn't upgrade it was harmful for some of the tenants on their multi tenant system it was atlan cloud and it took a very long time to manually recover every single one of the customers because they didn't really have a lot of tools to do maintenance on specific customers and leave the others alone so those are all things that you usually only discover that you need kind of late in the game and the hard way now in order to build those Mulan system we usually end up with one of two models so on one hand we have the idea of we'll put all of them in one database we'll have in every table the customer ID row and we'll put a lot of code in our application to filter out the stuff we don't need this is very good for being cost effective very manageable of course puts a lot of burden on the developers to make sure that if you need to do something specific for a customer writing the right scripts making sure the data access is correct making sure nobody uses more resources than they're supposed to the other model is give each customer its own database or sometimes it's its own schema which is slightly better but maybe somewhere in between and in this case you actually have pretty good isolation you have you are able to maintain every customer independently they it's easier not to leak each other's data on the other hand it's you need still need to do all the tooling and scripts to manage all those systems so you either way it feels like databases don't do a lot for you and the choice is not really easy and we've seen people who chose either pass and wish they have chosen the other which just shows that it's not very easy to make the right decision the way we're Imagining the world and by we in Nile is that it's actually a false Choice it is possible to take one database isolate tenants within it and allow to perform a lot of the operations either on the database as a whole with all the tenants or on specific tenants so back up the database versus back up a tenant restore a database restore a tenant a query a database versus qu connect to and query a specific tenant all the way to geographical distribution I want these tenants in the US those tenants in Europe the obviously different performance different regulations and so on so moving on another thing that we are not building but I wish someone would have built is an easier way to get changes out of a database and what do I mean by that basically as you know if you have a database people are doing inserts updates deletes all of that changes the data and this information that something Chang in database is obviously extremely useful information you can use it for auditing for push notifications for replication like there's so many use cases which means that pretty much everyone here in the audience is working with the system capable of getting changes out of the database and doing stuff with them and because it's so useful and because everyone is doing it there is a lot of Technologies to do it I've listed some of them just a tiny subset but those are all different Technologies outside the database right so let's say I want to use the bizum and Kafka and then Flink well I need to run the bizum and then Kafka and then Flink uh they probably have different security models from the database but they shouldn't have different security models from database if there is a person who is not allowed to see row a in table B in my database I probably also don't want him to see the changes to that row I don't want him to see ER the row in other systems I may allow them to see the row in aggregate after I do a bunch of analysis this may be okay but we need to be be have a way to align all those different systems and run them and manage them in a more unified fashion I can only wish that databases would have instead of okay I need to run the bizum and I need to connect the bizum and then I need to run Kafka and then I need to run fling like can I just tell the database please put all the changes in S3 that's all I need really can you do that so it turns out that no they really cannot okay now things that are more specific to software engineers and the way software Engineers work with data so if you happen to be data analyst or data scientist this may be less relevant but who knows because engineering best practices do find their way into um other disciplines eventually so let's say that you're a software engineer and you want to change the database schema this is not rare pretty much every time someone ask you for a feature it's like oh I need this extra column and it needs a foreign key over there and maybe an extra table this new inem type and also I need an index okay what do you do now well the way things normally start is that okay you now have a system in one version and you work on a local copy and you make local changes usually what where you end up with with something called IM migration file and then you do all your work in a branch so now you have have a branch of your code it has a migration file and you have a local database that has all your changes you tested them locally things look good fantastic you push it to your Source control if you have a functioning cicd system which hopefully you do you it will go there automatically so you will have a Dev system that has the new schema and maybe you'll have a staging system with a new schema a bunch of tests will run automatically maybe some developers will use your test system and kind of give you some feedback so when you decided that stuff is in a good shape you finally close a circle you merge your branch your magical cicd system pushes this to production and now production is on version 18 what you absolutely do not do unlike everyone on my Twitter feed when I asked about this question you do not SSH into a production database and run a bunch of alter table commands this is absolutely guaranteed to end in disaster there is several ways to run those migrations there are about you can have side cars doing it people do it in containers people integrate it with the c ICD systems all good don't be the guy who sses please anyway that was a side run because I was absolutely horrified by people on Twitter H you the data gets to production note that throughout all the system your version control and your cicd was very aware of changes going on and it triggered a bunch of checks and deployments and so on the database on the other hand was blissfully unaware for it one day a bunch of unrelated changes showed up a column maybe a new type maybe new index they just appeared one day it doesn't know that before that it was version a and after that it's version B it cannot warn you hey the changes that you were about to make are actually incompatible with something that is already running it cannot let you flip back to the old version if you kind of miss it for a bunch of reasons so a Version Control is something that would behoove databases to move to what has been a standard engineering practice for pretty much as long as I've been engineering which is like 20 plus years so I I feel like it's about time there are databases who do it by the way as I mentioned earlier for everything I'm talking about there are some databases that are a bit more advanced as there are databases out there that actually have Version Control I think we I think this is one place where we will see more and more of it as it proves very useful another thing that Engineers love and databases don't do very well is apis so if you want to query a database usually we write SQL SQL is lovely it looks good it's readable I think everyone in the audience can tell me what I'm getting over there and why fantastic now if I write the equivalent using one of I don't even remember which exactly Library I use there but most H query Builders are the same so R miss me here if I do the same thing in let's say spark then it's arguably less readable but but being code it is better structured and this is the important difference so let's say that I want to have a variation on this query somewhere else in my code I want the same thing only with a filter if I'm doing everything in squel myself either I have two copies of the same query and now I have to maintain both of them Engineers absolutely love having two copies of the same thing and maintaining both of them that's what really drives us and or I have to do string manipulation not that fun either actually a recipe for disaster either way if I wrote it in code on the other hand again I don't know how readable it is but it's composable I can store the original query and then reuse it somewhere else attach a work close to it store both of them pass them around I can now manipulate queries in different places in my code without having copies and without the risks of string manipulation this is a really really good thing and I think 90% of the reason people use orm is not because anyone loves orms anyone here loves orms oh wow okay there are people who like orms sorry about all that I actually I know I did a survey 50% of the engineers love orms 50% hate them I think this crowd may have more people I who either hate them or neutral but um regardless I think a lot of reason why people who even hate or m still use them is because of the nice ability to compose queries H but orms have downsides a lot of them don't produce the most optimal code and the fastest so you have to keep stepping outside them for a bit what if hear me out here what if databases actually had apis and allowed you to use structured languages like better structur I know SQL has like the s that supposed to say structured but I mean actual structured language and for querying them which brings me to the other point databases don't just have a slightly Antiquated programming language attached to them they also don't really do modern protocols let me walk you through my thinking here you have a database we already mentioned that it talk equal how does it talk equal well I some databases are open source but they still each one of them has its own binary protocol unlike any other database in the world so you need database drivers that can talk to that specific database that's number one you have to authenticate to it you usually use either the database built-in authentication or Lup or care Bros so slightly Antiquated authentication methods as well now if you look at the other side of the world you have your application these days 2023 a lot of times your application is running in a browser it talks very like browsers do in the HTTP protocol a nice modern protocol invented I think somewhere in the early 90s um so 30 years ago but it has some organizations on it and databases and browsers usually support those databases of course know absolutely nothing about about it if you authenticate in a browser very often you use basic o another Antiquated protocol going back 30 years or you use more modern things that are only 13 14 years old like oidc and Sul how do these two things talk to each other I have data in the database I want to show it in my browser they don't talk the same protocols what do we do well we know what we do we all do it all the time right we put a back end in between the back end takes query in HTTP authentication in oidc or SML or basic o translate it to SQL sends it over a different connection using a different protocol with different authentication to the database gets a response in database protocol converts it to an object converts the object to Json sends that back over HTTP to the browser do we add value when we write this back end sometimes sometimes it has a lot of business logic IC super valuable back end I love it but how many times did you write a backend it's like literally pure translation and we talk like yeah it's good engineering separation of concerns Etc but and sometimes it is but it's also losing value in a serious way because in all this translation we can introduce bugs easily we can lose security because we have to also translate credentials coming in for a user from the browser to credentials that go to the database this is not always a good thing and we do it over and over and over again so in the same organization right we usually have a lot of buckets sometimes even a lot of buck ends talking to the same database a lot of opportunities to mess up Access Control security permissions Etc not very good in my opinion what if databases would actually talk in HTTP that would be nice what if they would support modern authentication protocols they're not that b 2010 is not even that new um yeah okay let's talk about things that are not 15 to 30 years old for a second and maybe more modernistic it's funny I wrote this talk originally almost a year ago and it's scary how like I looked at it and like the things I'm mentioning is modern I don't know if they're still modern so a lot of Chang in here so the first one is that a year ago definitely five years ago most databases were in one place it w you needed very special requirements you needed to be a very big company pretty Advanced to have a database in the US and in Europe like this was like oh my God science fiction or you have to be like Netflix or something but I think today it's no longer true there are three forces driving us toward more Global databases actually two forces and maybe three us cases the first one is simply compliance requirements data there is a lot of data residency regulations if you're a company with customers in Europe there are locations where you're not allowed to take the data out of the country sorry about this if you want to have those customers either you make them signed release consents H they are pretty reluctant to do that H let me tell you or you actually need a database in their side of the world H so so this was one very big driver just regulation the other thing is latency in general people like when their websites is fast H but also it was never enough of a driver to put the database on the other side of the world a lot of times you did some cashing and it was good enough but until very recently it was all about serving the this customer in Japan that has really bad latency and the people in Japan are used to having bad latency I guess and they kind of deal with it what really changed recently is the rise of servess more and more backends and workloads are running on things like AWS Lambda Cloud flare and those may be closer to the edge H someone called me literally yesterday who is hey my workloads are in Asia Pacific my database is in US West too it's a bit of a long round trip and the thing about serverless is that it's now not about the grumpy customer in Japan it's actually costing you real money remember that in serverless you pay for CPU seconds or minutes every second your function is running it doesn't care if it's waiting for the database H TCP handshake to keep doing back and forth you are paying for every second of it it's amazing how many people start caring about leten see when they notic are paying for latency so this is obviously kind of became a big deal so we we see more and more databases and caches Going Global database replication is actually in a fairly good place it's not that hard to get your data from one database to another putting them all over the place h of course it the database itself doesn't know it's in Europe so it doesn't say hey I'm a European customer I will make sure the data never ever makes it all the way back to the US it doesn't say oh I'll make sure that if someone click delete the data will be gone even former backups within 30 days because I don't want you to go to jail so there is more databases can do in this new landscape and of course with keeping cashes out up to dat all up to you they're doing absolutely nothing and there's so many nice things they could do they have the change log they could push it proactively to all those caches they could give you warnings they could do some routing so if you query the cash and it's not up to date but you say it absolutely has to be up to date they route it correctly there is so many many things you could do better there you could do better you should do better but you have to do it versus the database doing it for you and last bit for futuristic this is the one that I'm like is it actually futuristic databases traditionally has not been very in intelligent about their workloads that you tune them and however you tune them that's how they're going to keep working and intelligence is ours not theirs but recently we've been seeing databases or even systems running on top of databases where they say no actually I'll be studying your workloads I think I can build better indexes and it's not just about figuring out where to put an index it's actually saying hey you have this very large index but only 5% of the data in this index is actually accessed so I'm going to index a subset of the data or this this in the structure is more it doesn't it the traditional bit Tre is not the right structure for you we can restructure the trees so you'll have shorter shorter passes to the data people use most in your database and we'll do it behind the scenes and on the Fly and we've seen systems do it these days very new very cutting ede they on one hand scared the crap out of me and other hand I mean how do you even troubleshoot stuff that changed behind your back but on the other hand it's very promising I mean in general making a database perform well at scale is a hard job there are some people in the world who can do it some people hire them and then most companies really struggle and move to a bunch of questionable no SQL Solutions because they haven't been able to solve it but what if you can democratize it what if that you can cture the intelligence for tuning a database and make it part of every database in the world some workloads have to be in no SQL no doubt about it some of them they may be no SQL mistake because someone told someone else that no SQL is faster and they they fail to tune the database I've seen a lot of it tuning a database is actually pretty hard so I'm pretty excited to see how those intelligent databases evolve and again like this is the thing I'm most skeptical of I've seen so many attempts to do it that failed you have no idea but I'm seeing the people who are working on it and it's like the top professors from the top universities who've been researching databases for 20 30 years and I'm really hopeful that this time it's going to work and like mostly because in the last year I've seen a lot of things that I would never believe going to happen actually happen ah thank you [Music] okay summary don't believe anyone who tells user databases do everything keep in mind the stuff that you have to do because databases don't do it for you and keep watch for databases that are faster and that do more for you when possible one last thing why do databases get away with doing so little it's all our fault how does technology get better technology gets better because the people who use the technology ask for better things this is how everything get better because of consumer demand for databases all of us technical people are the consumers but technical people tend to apply their own belief that things are impossible and it prevents them from asking the vendors for more so if I believe that having a database autotune itself is impossible I'm not going to go to my vendor and say why aren't you aut tuning your database which means that the vendor has no incentive to make it better I really ask everyone here please raise your bar ask for more be more demanding be more demanding of your vendors but also question your own beliefs when you say something is impossible because over and over things that I thought are impossible like Dallas 3 present creating actually usable pictures for my blog they are now possible so who knows and it wouldn't exist if if people keep imagining that things are not possible they will remain impossible and if we stand up and say it is so stupid that it's 2023 and I still need to implement my own soft delete what the hell things are actually going to stay this way so please reconsider your standards reconsider what is possible and raise your bar for what a good database really should give you thank you very much