"Normalize until it hurts; denormalize until it works"... by Flávio Juvenal

Thank you, thank you very much. I'm very excited to be here at DjangoCon again. So we'll talk about normalization and denormalization and how that applies to Django and that's my Twitter handle. I'll post the slides there after the talk. So to give a bit of context about me, I'm Flávio Juvenal, I'm from Recife, Brazil. So I came for this conference and I was before at Pygoton at New York. I work with Django for seven years now, uh since version 1. 3, and I'm a partner at Vinta Software. We are a team of experts from Brazil that works mostly with companies from the US and we help our clients to evolve

their products. with top-notch uh UX and development techniques. We do mostly uh Django and React web development. So we are going to talk about normalization and normalization has everything to do with anomalies. And the best way I think to think about a database is that it represents facts about data. So for example this table here we have some toppings on some pizzeria and a rating for them and the phone of the pizzeria. So it's the topping rating table. If we ask the question the fact what's the pizzeria known as phone, we have two answers for this question in this table uh

because the phone of the Pizzeria Nona appears to two times because nonas has two toppings uh evaluated revealed on that rating table That means there are redundant ways of getting Pizzeria non 's phone on this table. And nothing in the table structure prevents a conflict on those values. So the question what spizzeria known known as phone again, it's impossible to answer with this other anomalous table. And this state happens if one forgets to update both values together if we are updating pizzeria known as phone on this table.

So redundancies can cause anomalies, that can cause incorrect facts, and that can cause bugs. And proper normalization prevents data anomalies in the database. What kinds of anomalies can happen? Can happen update anomalies where we need to update all rules where the same pizzeria appears to update the phone of the pizzeria Insertion anomaly where we can't add a font to a pizzeria without adding a topping to. And deletion anomaly where we can't keep a phone to a pizzeria if all toppings are deleted. Which doesn't make sense, but that's happening because of the way we are storing the phone's data on this

table. And the solution is perhaps third normal form, boyscot normal form. No no no normal forms are boring Normal forms are like this. Every non-prime attribute of R m is non -transitively dependent, blah blah blah. So we are like that when we are reading normal forms. The solution is actually simpler. We can state the basic idea behind normal forms in a simpler way, which is if there are multiple ways to extract the same fact from the database, there is a redundancy. And to remove redundancy, we need to restructure tables and columns. We need to divide them, we need to combine them, we need to do something to remove the redundancies.

we find in on our databases. On that that example that I showed, the solution is to create another table just for pizzeria. and to store the phone there. And by doing this, we can't have repeated phones. Uh or if we have repeated phones it we like, even a third table, so it it will be handled by by the database. We have a structure that either allows or prohibits this for us and that's exactly what we want. So normalization is the process of restructuring a database to decrease redundancy and ensure integrity of data. But is this really a problem for Django? With our RM we work mostly with business objects.

So at the first place probably we'll do something like that. We would create a pizzeria model. and have the name and the phone there. So like we wouldn't have the problem of putting phone inside a topping rating table. Because we at the first place we would design with business objects like pizzeria, like rating. Yes, that specific case probably wouldn't happen in Django, but another problem would do to due to migration conservatism. As we all know, all kinds of conservatism is is bad. Just a joke. But uh this kind of migration conservative is all also bad because imagine you have a employee table with name and department and we have choices for

department. And the client asks for the system to store department address. So we had employees on departments, and now the client asks for department address. Developers are conservative about migrations, they don't want to do to create like complex migrations, move data around. So they would do this. They would just add department address in the employee table. And that's that's a problem because the fact address of department X is repeated for every employee of Department X on this table. Again, all sorts of anomalies can happen And instead we should actually create another model department, even though we didn't have

at the first place, we need to create it now and have uh the address field there. So we need to re-check the rule ever on every new feature we develop, on every new requirement. We need to check if did we just introduce it another way of extracting the same fact from the database? If so, we need to Restructure the tables. I want to talk also about historical data versus normalization. That's something quite complex Imagine you have this. We have order and the order is made by a user It has a related product and the total of the order. And suppose that total is computed from product price. When our order is created, we grab product price and add to

total. Then it looks like if the product is the same, the total will always be the same. So we have a redundancy here, right? If product is the same, total will always be the same. Really? Not if we consider time. Product price tends to change If we don't store total at order, we will lose data when a product price changes. So total is actually a fact about the product at the order moment What was the order total is a different fact than what's the current product price due to time So that's the idea behind the phrase accountants don't use erasers.

Historical data actually should not be normalized because it represents a fact about the moment it was created. It was inserted. Other common examples of historical data are an address field at shipment. If the user changes his address, you don't want to change out past user shipments because that that already happened or that already went to an integration with some shipping carrier. And a slug field at a blog post. If we published our blog post, shared it on Twitter or something, on a newsletter, we don't want to change change the slug if we change the name, otherwise we have broken links. So sometimes historical data is just data published to outside your database.

And you should be careful that data looks like redundant, but it actually isn't because of time. Okay, now let's talk about query expressions and how they help us uh to not denormalize and to keep things normalized. Imagine now we have this other example here, uh total order, and total order is really redundant here, it's not historical data, it's just the sum of all order totals for the user. So we have users that make many orders and we want to know the total ordered by the user, so we have to sum all the totals , all the order totals related to that user So this is redundant, the way it is right here.

Total order is redundant because we can extract it from order. But how we can do this with query expressions. So instead of having that field, we could annotate that at query time using query expressions, and we will do a sum uh over orders total. So don't be afraid of query expressions. You can filter, you can order with them, you can do much more without needing the normalized computed field. So instead of like adding to that field every time you Create a new order for the user, you can just annotate a sum of all the user order totals and grab that at query time. If necessary you can even create your own db level functions and custom lookups.

For example, imagine I have this problem where I want to store the name of the person and but I also want to store the name on Uni the code. I'll explain that. But Imagine that's for filtering, easier filtering, searching and things like that. Unit code is this. If you use that library, Unitcode, you can pass it uh accented name like mine and gap grab it without accents with just SC uh characters. And if you want to do that, like den norm in the normalized way you have to do that every time you change name to update name unit the code. But you can do that at d B level if you

Create all with Postgres a function, you need the code. And you can even write Python code inside it if you use the Python extension. for Postgres you can write pipe Python code inside the function and you can declare that on the Django side and annotate that that you are you want to compute name unit decode at query time by using the function unit decode you define it at your database. This works for Postgres, probably other uh other database systems have similar things. And this way we don't need to denormalize and create our own name unit the code But isn't that slow?

We are you are calling a function at query time for all rows you are returning. Okay, that can be slow. When that's slow, it's time for denormalization. And we shouldn't do denormalization blindly. There are denormalization patterns we can use. First we must normalize until it hurts and then denormalize until it works. We should not confuse denormalized with non-normalized. You cannot denormalize without normalizing. Denormalizing is fighting for consistency While not normalizing is dropping consistency altogether. As there are rules for normalization, there are patterns for denormalization.

And the denormalization patterns we will talk about are extend, aggregate, and fetch. I'll explain each of them. I didn't invent them. I got grabbed this from a blog post you can check on the references later. And it's very well known blog post. Okay again this example of name and name unit decode From name, we produce name unit code. This pattern is called extend. That means from one or more fields of an instance, we extend them into another field So from name at the same row at the same model instance, I'm producing another field named Unity Code. That's extend. That's the extend pattern And we could do that simply by ourselves

coding that logic. Yes, adding fields for implementing extend is cool, but you know what's cooler indexes. We could like remove that additional field and create an index on db level uh that uses the function we define it at db level unit the code. And if we do that The database will create an index considering that expression. Postgres will create an index considering that expression So an index is like, on this case, a denormalized table that the database keeps in sync for you. That's what's happening at db level. The index is like another table. uh where we have the names without accent and that

that other table that index is pointing to the right IDs of our real table with the accented names. And that's like Alpha free for you, you just need to declare the index. It's not completely free because we are trading insert update speed for select speed. We will have uh faster selects but But slower inserts and updates because we now the database needs to update both the the actual table and the index But the same would happen with an additional field. The same would happen because you'd have an additional field and you have also to compute it and update it every time uh the extension

uh up The dependent field is being updated. However, indexes don't work for aggregations on other table rows, like the user total order example we saw before. And computing user total order is actually another denormalization pattern called aggregate. That means on the one side of a one-to-many relationship, We aggregate data from the many into a new field. Yeah, that's that's complex, but I will explain. So we have this, okay? We have total order at user. It's an additional field, it's bad because we have to keep it updated and everything. What we are doing here is that from

many totals for that user, we are aggregating them into total order. Okay, so we are on the we are on the one side of a one-to-many relationship and we are grabbing many stuff and adding and computing that and adding to a field of our one side. To solve aggregate, uh we could like do it manually again, but we could use also materialized views If you are using PostgreSQL, you can try the Django PG views library. Materialized views have nothing to do with Django views, the views you handle requests and things like that. No, that's a DB level thing Where you can declare with Django PG views a materialized view.

And here we are declaring a user report materialized view. It's like a model, we have to uh set the fields But unfortunately we have to write the SQL code for it. But if we do that, we can use this user report class as a regular query set everywhere we want. And by doing that we are denormalizing without actually having to handle the denormalization sync code. Basically, we just use user report as a regular model and then on CINAHL with Chrome, SellerBit, Signal, whatever you want, you need to refresh your materialized view. So that's like a query where the results are stored on your database and you can even like filter

over that query and use it like a regular query set. For a more automatic solution of aggregate, we can use the library Django denorm. And this library is awesome, it's quite magic. If we we can remove total order Okay, and we can declare a total ordered function with the denormalized uh decorator and we say that we are creating a decimal field that depends on related orders. And then we write the code to grab the actual total order. And if we do that This library will execute that function only when it needs, only when related change things change, like related orders change, it will execute that code again.

For doing that, Django denorm uses database triggers, so it detects with triggers at the database level if related things are being updated, so you don't have to worry about signals, it handles that at the dB level. But it does what it does is it m marks the instances as dirty and then and then it needs run code to recompute the the actual values, actual the normalized values. And it can do that with a post request middleware or with a periodic task. But it needs to mark it automatically marks things are the as dirty uh to recompute the denormalized fields, but you have to run that somehow either per request or like with the periodic tasks if you can uh support eventual

consistency on those eventual consistency on those fields For a more lightweight but less powerful solution you can try libraries that just use signals, like those two. And finally the last pattern of denormalization. Uh here imagine if we have address, user address that's a reference to user, has the actual written address and the latitudes longitude for that address. This is redundant because from the address we can compute latitudinal. Yes, there is redundancy here, but it's quite difficult to get rid of this. Technically, if we had a geocoder on our server, we could address would be actually a foreign key to a geocoding table with latitude and longitude field

So you could like make this joint to grab the latitude and longitude. But in practice, geocoding is not a foreign key problem and it's very slow. So we need to fetch from the geocoder. We need to from the many side of a one-to-many relationship. So multiple addresses can have the same latitude and longitude. So we are on the many side now. From the many side, we need to fetch data from the one side into a new field on our many side. So we have like many addresses with the same latitude and longitude, and from those addresses we need to fetch data. uh we need to fetch the latitude and longitude. That's the fetch pattern. It's like the opposite of the aggregate one. And Using address we can fetch latitude and longitude

from a geocoder. Uh that's the idea here. And actually a lot of business logic is fetch So for solving fetch, I wouldn't recommend custom tools uh third-part libraries. Uh I think it's just better to be explicit as it's explicit as possible and hand it handle it yourself on your own code and write writing tests and everything. But how to do that? You can just like Create helper functions to update that model instances and just update them with those helper functions and of course write tests for that and everything. You could do it on a lower level and override save and use something like Django Module to use field tracker to track changes on addresses

to update the latitude and longitude every time the address changes. Or you can even try Django lifecycle hooks, and that's like a Rails -inspired idea of if that field changes, run this code. Uh you can do it quite declaratively with this library. There are other concerns we overlooked in this talk that when normalizing you should be careful with concurrency because it's quite uh It's quite ironic, but it's actually easier to handle concurrency if you keep it all into a single row. Because like you are updating a single row, so it's difficult for uh two different processes update the rule at the same time. But if you are dealing with multiple tables, you have to be more careful with locking and transactions.

And before demo denormalizing, you should actually profile what's slow. You shouldn't denormalize blindly, just trying to denormalize uh and using aggregations and that you compute manually because you think it's slow, you should profile and check if it's really slow. And you can even try caching before properly denormalizing stuff. And after denormalizing you have to profile to see if it really worked. You don't don't you can't do it blindly. There are other denormalization ideas. Uh I don't have time to go Dive into but one great idea is to separate transactional data from analytical data and this

Django con the denormalized query engine design pattern from Simon Willison and yesterday. Kate Klingman kind of talked about that where she accelerated Django admin filtering and aggregations. using Elasticsearch. So basically they are separating transactional data, the actual real data, from analytical data that is computed data and can even be eventually eventually consistent. And uh there's like a crazy idea of transaction log plus materialized views Which is the transaction log is the real truth about the data and materialized views are updated uh after the transaction log is updated. That's like a big data thing

that manages to uh to keep consistency consistency even though it's like handling a lot of data. Check this talk here for more information. Those are the references and thank you. If you have any questions, I don't think we have time, but you can ask me at the hall. Thank you very much