Dubious Database Design

Thank you very much for the introduction. So yes, hello everybody, I'm back again this year, as I always am at DjangoCon. I have this time though want to talk to you a bit more about It's not about migrations, but about databases in general. As mentioned, I Wrote South Migrations. I also wrote Django Migrations. I am a senior software engineer at Eventbrite. We have a booth just down the hall you should come and visit. Very lovely people over there. And I have a small hatred of MySQL, which is not it's somewhat relevant here, but this is actually a little less bashing onto MySQL than I used to. So if you're looking for that, look out for it. But what I want to address here really is a common thing I found, especially when I was a young

younger, more junior developer in the industry, is this idea of people just saying Just do this. We do things this way. And often there's no explanation why. People are like, well, you should you should just do this pattern. Especially like a university is often quite common. For a place of learning, it's much more dictatorial, like, okay, we just design things like this, we normalize like this, and sometimes you don't quite get the backstory. So what I'm going to try and do here today is is show you why by counterexample. I'm going to take eight or nine different Bad ideas and just run through with them until we get to a point where it actually turns out what the problem with them is rather than just saying they're bad And this is all part of what I call learning from failure.

So if you saw my talk at PyCon this year, that was about, it was called What Can Programmers Learn from Pilots? And a big point of that talk is failure is an expected thing. And in particular, um one of the things with both aviation and in some way with this is that every rule, every reason, there's usually something has happened to cause that rule. So in aviation, every law An accident has usually happened to bring that law into effect. Like every time a crash or incident happens, we look at the results and institute a new law and that goes into the books to stop happening again. And in software development, that is not quite as coherent. People have their own little specialties, but a lot of the accepted knowledge is there for a good reason because people Decades and decades before us

have been through the same stuff, they've been through the same wheels, they've reinvented it. So let's come to the first example here, which is a cycled space log. Now spacelog is a way of digitizing the audio, well sorry the written transcriptions of the audio between the Apollo and Mercury missions from NASA in the 70s and 60s and 80s. um into a sort of website format. Now the only format these were in beforehand was a scanned angled badly typewritered PDF. So like you know, they they typewrote it in the 80s, stuck it into a folder somewhere in NASA. Then about 20 years later someone comes along, scans them. Don't scan great, they're at an angle, they're hard to read, they you can't search through them, the OCR doesn't work.

So we took all that data, we cleared it up, we put it into a nice website. Now there are two key factors with spacelog that are relevant. The first one, so yeah, kind of an excuse for myself is that space log was written in a week here. This is thought uh Fort Clanc, uh on the Isle of Albany in the UK. Now Fort Clanc is a little unique. It is a Napoleonic-era seafort. This causeway here floods at high tide, isolating it from the entirety of the island. And the island's very small. The island you can walk end to end about 20 minutes. And even then, like the whole thing isolates off at high tide. It's a wonderful place. It was occupied by the Nazis in World War II as well. But the key thing is

Um it's part of a thing called uh DevFort. And DevFort is a thing where you go somewhere for a week and it's a very intense week of writing a single thing and launching at the end of the week, which is my excuse for the very bad architecture I'm about to show you So the nice thing about spacelog is that it's all very old data. This isn't going to change. Like, you know, Apollo 11 isn't still going on. And so we can be pretty sure that all this data is pretty much read-only forever because once a mission has happened, the transcripts don't change, there's no user features. I highly recommend you make a site with no user features, it's great. Because there's no logins, there's no moderation, there's no spam, it just sits there and presents to the world. And naturally, um in our sort of somewhat

locked away, panicked state, we were like, well, it's read-only. What's really good at read-only stuff? That's it. Redis is great at this. Redis is fantastically fast at reading operations because you can say, hey, here's a key, give me a value. And so we took all the data, we split it into different chapters, sort of like, oh, here's takeoff, here's entry, here's going around the moon, and we put them into Redis keys. Thought, great, done. Ship it. Well, not quite. The problem is, of course, that you need pagination. So we'll put every entry into its own key. And so we can load, we can do a multi-get on the keys, still very fast, and we could load fifty of them at a time and do pagination that way. Perfect. And you know, we we assumed that the numbers incremented and didn't nasty things like that. So, okay, cool.

So now we have Pages that do one multi-k against Redis, still pretty fast, that's fantastic. But then you have a problem like, well actually We don't have purely numerical identifiers on these, so we need to give each of them a score, which is its position in the chapter. and then query which keys we want as names by a range by score, and then take that range we get back and then put that into the multi-get and then do that. And then also we need to get the details and the pictures for each speaker. So we're going to then take all the speaker names in that result set and then make them into a list and query that list against another collection in Redis and get the details back from there. And of course what we've reinvented is select at that point.

This is a very roundabout way of doing a select with a joy and it gets on the table. And Of course it was much slower than a real database was because it can't do the optimizations in memory. And so to this day, space log still runs on this truly special um Reddit installation that we wrote. And it's it was perfect it was perfectly fine. Um and there is a varnish cache in front of it because it's not necessarily great at doing things like showing you random pages, but it does tend to work. But yeah, this is the place where A normal relational database would have been a perfect the bet much better solution and we were blind to that reasoning until we got to the aim of like, oh We obviously should have picked this, you know, four days ago, but it's now day seven. We have to leave in like three hours, we can't fix it.

So a somewhat unique situation there. And this kind of comes on to the second part of this. It's like people like to ignore join. And this was this is this is a bigger thing when MongoDB and things were much more popular. People have this impression that joins are really bad. And if you ask any person who does relational database stuff, like no no joins are a very important part of the whole thing. Like no no joins are back as slow. And to be fair, joins are slow. So let's assume that joins are really slow Cool. So let's go, well how three do stuff. So we have some documents, let's call them. I have some forum posts. By myself, and we have a few other authors. And we have some author records. So yeah, imagine a forum, you have avatars, you have real names, you have like date joined, that kind of stuff.

And what we want to do is we want to show a page in the forum. So what we want to do is we want to take all those forum posts and for each one of them we want to find the corresponding author document and put it in put it in place But of course, to do this, the naive way is to, for every single post, scan the entire author table, find one, put it in, for next post, scan it. And that is very slow. That's n times n operations. Now, you're not stupid developers, I know this because you're all here and you're wonderful people. So you would of course go, well, Andrew, it's obvious we can take all of the authors as like a collected set, like I said in the last example, and just do one query with them. I was like, that's great. So what we can do now is we can scan all the posts

We can then build a dictionary and says, okay, this well this post has this author ID and then do one big author lookup and then take the bulk result and put all those details back onto the posts. Yeah, pretty simple. You've re-invented hash join. Well done. This this this is what join does. And join is better at this because it's not having to round trip across the network. And so when I see people reinventing this, I'm like, that's that's lit for you what a join is. But of course it could be but of course some people go no but Andrew you've done it wrong. In documents you embed things inside other documents Okay, let's do that. Let's put the authors inside the forum posts. This is kind of the recommended way of doing stuff. So now you can just fetch the post Everything is everything is great. That's fine until

so let's say a common feature you see on forums is a date of say like you know this person was last seen at So let's put that into our author record. Now this means whenever I log into your site, you have to scan every single post in the entire database And on every single post I've ever written, you have to update that record to be the right thing. And so now you've made the sign-in process potentially 30 seconds to a minute long if I've been around the forum a lot. And this is why normalization exists, right? So and it's one of those things that like if you've been down this rabbit hole, you will have come across this particular problem like I have in the past. And gone, ah, that's why we do it that way. But it's a very important observation of like, you might think like, oh people change their usernames like once in a blue moon, it can't be that bad.

But there's a lot of other things on a user that can be very rapid fire change. You've made every single change on that document on that table essentially very, very slow. Another one that is definitely somewhat in my own fault. So when we were at Lany, I used to work at Lanyard before we were acquired by Eventbrite. And one of the things we had at Lanyard, and we still have at Lanyard, is a very wonderful set of uh cached Redis values. So in particular, so this is better than the first example. These are things that should be in Redis. So basically we we take our data set, we work out a few key things like, well, you know, these are speaker scores and whatever, and and this is like some clustering information. We put it into Redis for later use in the site so we can sort of get it out quickly.

But the unfortunate thing is Redis, at least in the past, and MongoDB in the past as well, when this happening They had a s you know they had a saving system and neither of them are particularly great. So Redis is one in particular. Redis, it's an in-memory data store generally. If you want to persist, it would write a brand new save file out. So write it to disk. When it finished writing, okay, okay, I finished writing it, looks good. Then it would delete the old save file and move the new one into place. Fantastic. The problem is when you run out of disk space, or rather when you have less than twice the disk space for this file, because You try and write the new file, but you've run out of space to write a whole new version of the file and Reddit just doesn't write it. And in the in the past when this happening It used to not write it, but it it kept going.

It kept accepting requests. It kept serving things from memory. And so all this data was just going into memory and not being persisted. And you had no idea. Like we didn't have monitoring at the time for this. We do now. And so, of course, come one day that server reboots. Ah, we can reboot the server, it's fine. We lose like six months of cache data because that data was only in memory, it was never persisted. Thankfully it was all rebuildable in a couple of hours from our main data store. But if you if something more valuable was in there, it would have gone away. And this happened to me again on a personal project of like a year later and I should have learnt my lesson, but I clearly didn't Um another thing I learned in the in the hallway this morning was that um MongoDB used to do this as well. MongoDB used to if it if it ran out of space just corrupt its own save files. It wouldn't even try and and f

so you know I I hope that's now being fixed as well, but there 's a lot of There's a lot of things with running out of disk space. Like give your database servers a lot of room because you do not want them stopping to serve requests or just corrupting themselves or even worse silent failure which is keeping on going and you think it's fine but it will just it will it's just eating all your data essentially so be aware of that. A small example of how that could be bad. Say we have so that event pays up money to organizers. Now our system is much more robust than this. This is not how we do it. I want to point that out. But if we were like young and naive, we could do it like this. We could do a query to find our unpaid organizers. We could write a road to say, okay, this one's being processed, because obviously if it's a cron

job. We don't want two of them to run at once and both pay the same person. So we lock the borough and market is processing. And then we call our bank as an API and say, hey, can you send account number so-and-so this many dollars And then when that call comes back and says success, we mark them as page. But of course, what happens if your database isn't is taking rights but not saving them? Well You never mark as processing and you never mark as paid. And so your cron job continuously finds all the clients who are ever not paid and continuously pays them money, which as you can imagine is not great. We see this is why we have proper locks in place of Membro and systems and things like that. But like if you were doing it naively, be very careful about money So another thing is

we come to the the other end of the scale now. So previously we've been people who were like, well, this normalization database stuff, it's a bit much. And now we come to the person who goes I really love normalization. I love tables. I love them so much. We're gonna make a table for everything. This was me three years ago. So at Lanyard, Lanyard had, when I joined, it was just Twitter authentication. And we were like, okay, we're going to add emails and passwords, we're going to add LinkedIn, we're going to add Facebook, we're going to add all of the socials. Um and just have like a you can sign in with just uh you know any number of things. And I went, that's great. Now these different backends are different things, so like for emails, have a table with email Um and that kind of stuff in it. Facebook have the Facebook tokens for LinkedIn and

each have their own sort of identifiers. And so we made a table for every single one. Sounds fine. Now the initial problem with this is When you try and show somebody their user profile page with all their things set, you have to go and query every single table and go, okay, well You have and then for every like you have zero LinkedIn accounts, you have three GitHub accounts, you have two Facebook accounts and Twitter accounts. That's not too bad. That's like six or seven queries on a given single page. Login is fine because login, you know the methods, you just hit the one table But the problem comes when um this page, so this is a speaker or directory page on Landed. And if you notice, um there are some icons below the people. These icons show that all the accounts they have assigned to Lanyard.

In particular, you know, they have at least one Twitter account, at least one LinkedIn account. That means for every row here we're doing a number of select queries and the number of like seven select queries per row. And so this quickly spirals into a Oh my goodness, we have to like do seven queries per row on this table and like we can kind of aggregate them ish, but we can't really um it quit you know it quickly makes what could be a simple single select into a much worse prospect And and this in particular took us a while to find and fix uh and clear up. And the key thing here is that database isn't magic. It can't just, if you give it more tables You're kind of promising your database, I'm gonna you're gonna use them sensibly. You're saying like, you know, I am a competent developer, I understand that tables cost space, they cost time.

They're meant for querying things that are separate. And so the correct solution to this is to have a single table with a type column in it and like, oh, type email, type LinkedIn, type Facebook. and then like an identifier column where you've like you put the one key thing you look them up by and then it's a single query. But we didn't know that at the time necessarily And following on from this, you have the person who really, really, really loves tables. So I have been developing South and migrations for too many years. I think it's about eight now. And I got a lot of emails. Now a lot of the emails are Andrew, status terrible, doesn't work. I'm like, it's okay, we'll fix it. And some emails are like, Andrew, how did I do crazy thing X? And one of the crazy things X is I want to make tables at runtime. My reaction to this is this picture.

So in particular I've had people request, so um I-18N is a very common request here. People are like Hey, I want to make a column per language. So, you know, this is like Django model translation, for example. Like you have a multi-language site, people have to insert content in multiple languages. So naturally people's first instinct is to store one per column or one per table in some cases. Like oh this is the English table, this is the French table. Sometimes I had people come to me and go Well, we're doing kind of a white label hosted solution, but we want to have like a whole set of tables per customer. At which point my action is and how many customers are you planning on having exactly? Um and then some of the even worse ones are writing a CMS and we want people

we want random users in the organization to be able to just add columns by clicking buttons in the back end CMS. So it's right. I was like just sitting there just clutching my head going, why? So let's take one of these examples, which is the language one. So say you want to call into language, and say you become a site like Facebook that has a global reach. There are approximately 300 to 400 language variants. There's you know obviously there's usually one per country, but there's often regional variants. So for example, there's British English and American English, and they're very different. Like we use spell colour differently Um in British English you say bin, in American English you say trash, and a lot of other words that you want to make sure are translated properly. And so you've not got just language changes, you've got locale changes. And imagine you have a couple of those columns per table

You're looking at a table with over a thousand, possibly two thousand columns. That's not sensible. Like your database isn't going to be happy about that. In particular, um if you're just trying to select from that table and don't do like column restriction. It's gonna try and send you back every single column every time. And that's gonna really, really hurt your network connection and slow you down a lot. So don't do that If you do want to this model, then be very wary of this. But make sure you use dot values and just pick out the columns you actually want rather than requesting everything. But even then I would really like if you didn't do this, because even if you are adding columns, if you have the wrong database, mighty cool. Or if you have Uh a default value in a column, adding

a column is very expensive. It has to rewrite the storage for every row. Some tables at Eventbrite can take days to add columns to sometimes because we just have so many, so much data in those tables And we use MySQL, much to my dismay. And so you've got to consider that like, sure, you can add a column, but if that's an interactive thing from like if you have like a web page where you click a button and it adds a column Be prepared if that website web page takes like potentially hours to return. Of course it won't because it will cut off with a with a time limit. But you've got to consider that like adding columns isn't necessarily cheap and fast. And it can there can be table locks in place, they can all this kind of stuff. And Postgres, as usual, does a lot better at this kind of stuff and we'll let you get away with it now and again, but it's still tricky. So I kind of recommend that what you do is you

either store your translator stuff as like JSON blobs, if you if you can retrieve from within JSON blob like by language. So for example in Postgres you can select just certain keys from a blob in JSON. H store is similar in in Postgres where you could say, okay, this is this this field is like title is just a h store of Engb is this, ENUS is this, FRFR is this. And you can just say you select the language from that thing. Or the sort of generic way is to have a entity attribute value style table, which is what EOV stands for here, which is a table that's just like, okay, this is column, this is language, this is value. And Joining against those things is something that databases can do pretty well. And it will save you a lot of headache in trying to look at the schema and not having it scroll off the side of a screen and like three miles down the street.

Another fun one says this didn't happen to me, thankfully, is the cold boot. So this is a fun one because it gets really good engineers. So You've written a site, the site has a very good cash hit rate. Like you're 75, 80, 90% of the page on the site are hitting the cash. And you're feeling pretty smug. And even better, you've got like you know, you've you've bought down your app servers so they're like if they're running at sort of like 70% capacity, like you know, you're not wasting money, you've got a good hash cash hit rate, you're doing you're doing fantastically well. Like, great engineering, good job. However, I'm evil. Uh I'm gonna come in and make those cache servers go away. They could have a failure, they could just stop working.

But let's say the data in them is lost permanently. So in the case I'm talking about, a power failure hit the servers and they and they're running memcache, so they lost all the data. Now you have this problem where you are running at 77 % capacity with a 90% cash hit rate. That means that without the cash, you've got ten times the traffic coming to those back-end servers What's gonna happen is your normal rate is gonna is gonna plummet when the cache goes up and when the connection drops, and then as soon as people can get back in, they're gonna just overwhelm your servers with ten times the normal traffic and you're gonna just be drowned. And even worse, as soon as you hit the cap on those servers, things start backlogging and filling up a backlog or a sort of like the number the sockets will sit there waiting open and it becomes this immense firefighting situation that's just

really hard to recover from. And it can take hours to repopulate that cache. Um one of the things we do with EventBite when we put builds out is the builds actually pre-warm a cache before they go fully live, just to prevent this a smaller version of this kind of thing happening. But it's very sort of nasty thing because it can happen to you with a really well planned, perfectly done system. Like you have to think Can your system handle like either can it scale in a few minutes if that's something you think you can do on a like a cloud server? Or do you have the spare capacity? Or can you turn off features for that recovery period or one of those things? So it's something to bear in mind. The next thing is something I call the the optimist. So the optimist is a very

Well let's say enthusiastic developer. And the optimist goes, okay, I've been through a few companies now, I know what I'm doing We have some great stuff. And we're in a brand new startup, small company, agency, whatever, and we had to build a new project. And so I'm going to take all the things I learned previously and we can use all of them at once. So we can have we can have Postgres, we're going to shard it because we might scale in the future. We can have Elasticsearch for search. We can have React for stuff that we think is inconsistent but we can do with storing. We can have Redis for a sort of cache slash data store work around. We have some flat files for like logging and big blobs and stuff and do all of these things at once. And that seem it seems very attractive. It's a very it's a very sort of tempting situation where you're like, oh well

They're all shiny. As a developer, like at least I am, I'm just drawn like a moth to a flame to like, it's a new data store and just like wandering towards it. Like we can put this into the site and install it, it'd be great. One of the things had lanyard in particular was that we weren't allowed to add a data store without removing a previous one, and you'll see why in a second. Because sure you have these let's say five or six different data stores. So of course for all those you've got redundancy, right? And you've got backups for all of them. Cool. Okay, so that's your ops workload already already much higher than it was And then what happens if any one of them dies? Likely if it's a new project, you haven't really built it so the site can handle them going away. For example, you know, what should happen if Elasticsearch goes down is that your s your search has stopped working

And just your search. But of course, what happens is Elasticsearch is quite good at a lot of things. It's good at like doing big aggregate pages So you start being like, oh, we'll build the home page of search as well, and then we'll build all the sort of the sort of intermediate pages of search as well, then just have the main pages be the main view. And so suddenly you've made Elasticsearch a key a key key part of the site that if it goes down you consider a major loss. And similarly Redis, like say, oh no, we use Redis for our sessions. So if that goes away is a crucial part of the site. And what you've done is, unless you're very careful, you've made every single one of those backends into a single point of failure. Because if that thing goes away, some crucial part of the site dies. And even if you don't think it's crucial, like some other person, like if it's their pet project or like it's a very important marketing push or whatever, it might be crucial to them.

So you have to consider this thing where sure more data stores is generally fine and in particular if what your problem is isn't shaped to an existing store very well. So say like You know, you're trying to put a graph database, a graph, so a strong graph database into like Postgres. It can do that. It's not particularly great at it, but it can do it. So if it's a really big part of your site, like hey, we want to get performance improvements here, we want to have a proper store, that's fine. But it's not free. Every store you add is extra redundancy work, extra backups, you've got to make sure it's working and not running out of disk space, like I said before. You've then got to make sure that like it never goes down. And it's just making your surface area bigger. So like if you're a small project or company, really

try and use one or two things. It's a lot easier when it's three in the morning and one of them goes down because that happens like much much less of the time because there's only two of them. And if it's Redis or Postgre or something, those things are pretty much solid. And so like you're working on a lot less than than less reliable stores. MySQL. It's fine. It's fine. It's actually quite the one thing it's good at is staying up generally. And then this comes this comes to a different kind of optimism, which I've seen quite a lot, is We all have and Jang Django kind of encourages this. Django has a primary key field that is an auto-incrementing integer. In particular, uh It has this sort of wonderful thing that as you sort of add more records, it increments.

That's what auto-incrementing means. And so people go, that's great. So we have this number that goes up. That means that the highest value primary key is the most recent one. I mean that's not true, right? So first of all, if you're running that for precise ordering, well, different pages can go through to the back end different times. So like you should put timestamps on them, for example. If you're running a cluster, that's definitely not true. And that and clustering is a problem here because this works on a single instance, but you quickly realize that auto-increment is a really nasty feature to scale. Um to the point where Twitter wrote entire software stacks to do this for them to just make sure their numbers auto-incremented roughly correctly. Because if you think about it, if you've got a cluster of like 20 database storage servers, They have to all agree on what the next number is and they have to not pick the same number.

And so you end up all these solutions like well okay we'll we'll sign each of the each of the servers in the ring a number modulo 20 and so this one only does like assuming this is is kind of a a bad thing. And the other one I've I've particularly annoyed is IDs are numbers we can do maths on. No, they are not. They are opaque things, you should never treat as numbers. I have this big thing wherever I go, and I've been doing this internally in Eventbrite for a little while, is making making IDs as strict, even if they're numbers, making them strings. Because they are they are not numeric. You can't do maths on them, right? That doesn't make any sense. I can't add no I can't add three to an ID to

go somewhere. It's not even like a pointer where that might do something useful. It just is like, you know If you try and do pagination by adding numbers to IDs, what are you doing? Um I've seen occasions like people want to um inflate their IDs to look good. So for example, they might say they might start a brand new site and go Okay, we don't want to have like this this customer's page be ID one because it looks really bad for our customer. So we'll like we'll start at number 3804. But we'll do it all in the front end, so we'll subtract that number whenever WordPress comes in, we'll add it when it goes out. You see where that ends up, right? Um so yeah, like if you want if you want IDs but don't give away how many rows you have, use UU IDs. Like they're built into databases.

They're basically guaranteed not to collide. Like you can we have native support for them in Django 1. 8. It's it's great. You know, don't don't sit there and and be that person who does this. But of course we come on to my favorite part of the presentation. So I was at PyCon Ukraine many years ago, um three or four years ago, and I saw a wonderful talk. And the crux of the talk was, well, we have this database. It's Postgres. And Postgres Can run Python. Like why why are we wasting network overhead querying to and from the database doing doing views when we can just run it right there and save the overhead? And so Postgres has functions. Now I have some problems with functions and sort of standardized queries.

A lot of enterprise software in particular will write all of their complex operations as basically functions. That you can run. So to you know to add a page, you just call like select add page and then brackets stuff, stuff, stuff, stuff, stuff. And the idea is it's meant to encode all the complex logic into the database so it happens there. The problem is that you can't version control that code, right? That code isn't in version control, it's in the database somewhere. You need to put it in a migration system to have it version controlled and even then have it overwrite properly and it becomes a whole mess. But that's not even what this is about. This is about Python functions and people who are slightly over. And I will say I've never seen this in production, but it's such a great thing I had to show you anyway. So They're Python functions. You can import stuff in them. In particular, you can do templating these Python functions.

And so if you were say particularly um enthusiastic You could go, well, we can render the template straight in the database. Why would we, you know, why we could just use Ginger in in a Python function? And at this point you're thinking, well Andrew, that's that's a silly idea. No one would ever do that. Well Here is the code that does this. If you want to go to it, if you have if you want to have internet right now, I'm going to show it to you right now on the screen. So that that SQL file I just showed you, bitly slash y not pg um contains a schema and a function that renders HTML directly from the database. In particular you can just you do select render brackets template name page name it would and it um outputs uh here we are this you get this out. You just get a

column back, which is the rendered HTML of the page. And even better, because Postgres has regular expressions, you could put the UL routing into a select query that uses the regular expression from the page. It's awful. Uh I d uh that so the code the code is not to be used in production. This is a very express thing from me Um but I just thought so I like here's the here's the example of of that text. And like you know, it's pretty simple. We import Ginger. Um PL Python will just import things from your standard system Python path. We can just execute a select query against another table in PLPython. It's pretty great. It comes back as a dictionary of like column names to values. We then just select the template string from another table and then just pass that to Ginger. Like Ginger will take the dictionary, it takes the template name and just render the HTML.

It doesn't it doesn't know it's doing it in a database And you could do more. You could do views in here, you could do like custom saving stuff, you could do all manner of things. Don't, but you could. Um so yeah, that that that that's my my th and like the the presentation ended up with this uh j PyConvice was like d wh why why is this a thing? And it didn't come with quite as strong as a warning as this one does. So It's possible, don't do this, do it in Django. Django is written by mostly sensible people, uh myself excluded. But the k so the the crux of the thing is there's a reason behind every rule, right? Like there's a reason why people generally stray away from stored procedures and functions. There's a reason why we don't generally make a table per s individual type. And all of these have exceptions, right? Like you shouldn't

nothing in programming is a is a hard and fast rule. In particular A lot of people just won't believe things or you know technology changes and gets faster and we get better CPUs. So You should every piece of information or recommendation you get you should ask why. Like if somebody tells you don't do this, just ask, be like a small child, go why And just like 'cause like often there's reasoning there and it's very sensible reasoning. Like if you if you come into a company that's been around for like uh many years, they'll have certain internal processes and design patterns and often there's a good reason why things are written like they are. But as a new engineer, your instinct is to go and just go, this is all terrible, let's rewrite it. And you'll go into that that sort of pothole of, well actually, there's all these hidden requirements that you never realize and then

it'll take you like a year to rewrite it and then you end up with the same complex code as before. So just take a moment, ask why, or just if it's if it's a simple thing, just As I've done here, take the thing you think is good and just try it, see what happens, see what the eventual result is. Sometimes that's not possible because it requires a scale to break, but you can do like thought experiments and just like work through it in your head But just don't write market contents. Don't go, pshh, they don't know what they're doing. Don't like, you know, don't dismiss 40 years of database research. Don't dismiss people who know what they're doing, like you know, there there are many, many engineers at Eventbrite who are much better than I am, who have been around the industry and programming for a lot longer and like they're full of really interesting and useful information from very esoteric parts of programming that You know, there

's very good reasoning behind everything that they say, and sometimes there's not enough time to say why the thing is like that is, but sometimes there is. So to just ask why, basically. Thank you. So I believe we have some time for questions. So so the question is basically like going into company and not f really is fine, but like how do we get the knowledge I'm talking about here in the first place? How how do we get that embedded knowledge of Don't make tables of every translation and stuff. Um I it's a difficult problem. Like part of it is doing this kind of thing and talking to people and giving explanations But I think part of it also is like, you know, you can still go into a company and and be a positive influence.

You can be that person who asks why and occasionally the why will be we don't know why. And then they can go and ask why and you'll discover the thing is t is a terrible idea. So if you just sort of if you leave something better than when you joined, like it's a cleaning up rule, right? So you should ask why and you should try and come in because like there are things that you as a developer will know better than other developers, right? Every one of us had our specialty. We gain it slowly over the years, but does happen. And so there are things that you'll be better at. And so don't be afraid to ask why and then provide like, well, I do it like this way because of this. And I think slowly but surely you can improve institutional knowledge that way to make things better. Okay, so the question is what's my feeling on columns like JSON and HDOL and like schemas data types in columns in relational databases? I really like them.

I'm a big proponent of mixing schemas. So in particular, one of my big things I like to do on new projects and things where I can is In any given table, there are certain columns that are core, they're important, like the ID column, the name column, title column, created date. And there are columns that aren't as important. There are things that could be stored in JSON. So what I quite like doing is if you're going to query on the column, make it a full column. If it's just sort of a a user-creable thing, so a CMS is a great example of this, right? You can have a CMS where Every CMS page has a URL slug, it has a created date, it has an author, it has all this stuff, but they might have like a whole bunch of configurable things Put those in the JSON blob and put the core fields as table rows. And then you've got this this good combination of if you add all the like

the complex changing lot of the time stuff is in a nice blob, doesn't need schema updates And the core fields that you're querying and doing, like ordering against, are all in proper table rows. And so I think that's a great combination. And even without a proper JSON type, you can do it like in MySQL, as long as you're not querying into it. You can still store things at JSON blobs. You just can't necessarily query into them as efficiently as as as you otherwise could. But it's still a perfectly valid schema design, I think. Cool. Thank you very much.