Oh, I Found a Security Issue
Published October 26, 2026
This video features Mathias Wedeken at DjangoCon Europe 2026 in Athens, Greece.
TALK: Advanced ORM kung-fu for on-demand filtering, sorting, and summing 40 million financial transactions by Mathias Wedeken
When the base data is a collection of 40 million postings in a table that grows longer every day, distributed across 472,000 accounts on roughly 5,000 properties; when the use case is filtering, grouping, summing, and sorting these; and when all of this has to happen on-demand for users trying to keep track of what the real-time financial situation of their property is- that's when you realise that using the ORM for mere data access and working the data in Python might not be the ticket. And, when users suddenly want to compare their property to some of the 5,000 others, your Python-first approach will expose the users to a considerable wait which, frankly, can be a bit embarrassing. You don't want to be that guy. Mathias Wedeken talked about how he pushed all this heavy lifting to Postgres using the Django ORM and (almost) no raw SQL.
More information: https://2026.djangocon.eu/speakers/NP83RK/
Follow DjangoCon Europe on social media:
*X - https://x.com/DjangoConEurope/
*LinkedIn - https://www.linkedin.com/company/djangocon-europe/
*Mastodon - https://fosstodon.org/@djangoconeurope
#django #djangocon #djangoconeurope #djangoconeurope2026
Automatically transcribed, so expect mistakes in names and technical terms.
Speaker 1: Hello, everyone. Welcome back. Please uh please come in So I hope you're all well caffeinated and ready for the last talk of the day before the lightning talks. We have uh Mathias who's going to talk about advanced RM Kung Fu. So not just RM Kung Fu but advanced one. Um Mathias, I think the most interesting detail I read is that fifty percent of of your professional life you're playing and teaching double bass.
Speaker 2: Yes Well
Speaker 1: the other hardware coding. So
Speaker 2: Well I'm talking about this a little in the beginning, but you said the most important part actually. Yes.
Speaker 1: Awesome. So welcome and thanks a lot. for for this talk.
Speaker 2: So well this is improvised actually. Um uh one thing I know from my other part of the professional life is as soon as the audience is bigger than the people on stage well in size, more people in the audience than on stage, then everything's going to be fine. So and this is really easy today. So I'm happy about this. So um yes um I was introduced by the fact that fifty percent of my professional life is playing doppass and the other part is coding. And I wouldn't name this if not for the fact that This 50% that is not coding has huge implications upon the coding part. Because first of all If I wouldn't if I hadn't been a double bass player, I wouldn't be in the band called E.
Speaker 2: G. Probow, which is the company I work for. Because The guy who hired me, he's actually a jazz musician as well. He plays drums. So and I play bass. So that somehow makes chemistry. And um I wouldn't have had the job at all And I certainly would not do the job the way I do if not I were a double bass player because well as I said we do lots of things by improvising. Improvising If you don't know what it is, it's basically winging it with some sort of knowledge of what worked before, but maybe not always well basically as soon as somebody claps in the audience we're we're happy. So that means that usually the first solution we find is a bad solution.
Speaker 2: And then we have to refine it afterwards. And this is this is what happened here. But uh without further ado, let's see what I'm talking about. I'm talking about this This is basically what it has evolved to. Property finances for a community of people living there. And it looks like this. This is a movie. Because I it's totally daunting to do this live, so I recorded this. Oh, I'm yeah, yeah, great, fine. Uh we're doing this again. So Can I pause this at some point? I don't know. I have to be really fast. This is a list of accounts that is basically by accident ordered by numbers, but it can be any order. The order of these accounts is in a JSON
Speaker 2: blob Then you have like for this account which is the uh operating account you have lots of postings coming in and you want to be able to see those postings and you want to also be able to in the right column see what every one of those postings does to your actual account balance. So and because you're a user and you don't want to scroll like this You might want to be able to sort this at a certain point by date, and then it flips around, and then you can see it in the other way And then you want to be able to see this historically, like for some years back, and how do how do we fare, how much have we paid for our washing machine in the last year or something like that
Speaker 2: We have we go back five years, we have lots more data because we're showing comparisons of data, which means that my community here um is compared to all the other communities we have in the database. that fit the bill in terms of what we're um what we're having in the sliders here and then we want to be able to see this how we're faring and so on And that's all very fine. And the problem is that we're dealing with like a platform with 300,000 users. Denmark has six million inhabitants. We're only Danish. So uh there's many people who somehow know us, even without they uh
Speaker 2: actually knowing. And then there's lots of accounts, lots of postings, everything's getting updated constantly because econom economics don't stop Everybody's paying all the time and they're getting money all the time. Everybody's paying their rent all the time and stuff like that. Users want to see this in real time and they want to see the comparisons in real time. And in this in the beginning I did things that I thought were really logical, naively. I thought, okay, I treat the database as the database, the base for my data. I get the data and then I wrangle it in Python and everything's going to be fine and it was. But things grew and grew bigger and stuff like that. And suddenly one of the higher-ups in the company came and said
Speaker 2: Well this part of the application is actually one of the most important parts that everybody's looking at and they're lady they are a little concerned because it takes so long. Can't you put a disclaimer on saying um we're preparing your data wait, please And I thought, well, 10 seconds looking at a disclaimer is maybe getting a little old as well. So I had to do something else. I'm going well just for laughs, this is the data model we're dealing with here. You can see that there's I I've written JSON B at certain parts, which means that some of the things that I'm actually using for doing all this are stored in JSON because they can be different from community to community and we have to react on that and stuff like that.
Speaker 2: And then yeah. The um that was also why I first did it in Python because it was very easy to treat JSON and take like a dictionary and and do so also all sorts of sorting with it, but at a certain point it got slow. And um something else I didn't tell you about is that I'm A little keen on functional programming, so I do a lot of Elixir as well. I don't know if you know this, but there you write pipelines of stuff. Uh and that's that you can see in my code as well. So then I'm just explaining why things look like that. There's going to be some code examples we're looking at. There's a key difference in the background. The red ones you're supposed to actually not memorize and not copy because they're stupid old code that is slow
Speaker 2: And then there's the other code that well I think is well at least it works better and it does the thing that you saw that actually one of those 300,000 users or 500 at the same time or whatever can go into the application and see what is going on for their property for property finances. So the goal was to um, because there's another jazz musician in the company, he plays organ, his name is Jeppe. And while organ has its own implications with double bass because we usually don't communicate with each other because one is stealing the job of the others. But well, never mind. He said something very smart. The database can do anything. You can do you can get Postgres to do anything.
Speaker 2: And I thought, okay, well, maybe he's right about that one. So I thought there must be ways to put this into action and by not writing SQL, but by using the ARM. uh B-O-R-M. We're saying Orm in Denmark by the way, which is pretty funny because Orm in Danish means this is improvised as well. Orm means warm. And and there's a uh there 's a children's book in Danish called Om for Nven. So um the warm gets a friend. And that's that's kind of cute in this connection. Well never mind. So uh we're we're using um F expressions, nine, f objects, q expressions, annotates, subqueries, outer
Speaker 2: refs, case wens, and funk. Um and now we're coming to the old ways. Whoa. Um This looks like an old version of the well that doesn't matter. So um there you can see that I my functional programming thing inclined me to use a library called tools Which incorporates lots of things that you might know from other functional programming languages like higher order functions and pipelines and stuff like that. I thought it was very neat, at least much neater than writing loops. It doesn't perform well very much better than loops though. So but yeah, well that's the way I basically did it before. I'm not going to talk too much about it. It's lots of code. uh four hundred and uh five hundred and fifty four lines of functions only
Speaker 2: to pre present the data in the way that we want to So and we'll be looking at what are we doing? We're looking at chain annotations and subqueries, we're looking at window functions, we're looking at JSON B and what you can do in the ORM with it. We're looking at this this one is nice. Dynamic and subquery generation. I don't know. Yeah. You'll see what this is. And we're looking at materialized views which is uh also very neat thing. So the first thing is those change annotations thing. So here we we see one of the old examples that can can you see that this is a red background? Don't copy this But you can see that actually we're having to go through all kinds of steps
Speaker 2: in memory to wrangle data and basically find out about what what kind of numbers there are. We we're just getting lots of postings and lots of accounts and lots of celdos and lots of budgets and summing them and ordering them in Python in loops in whatnot And well that takes time. And then now we're doing like this. There's just a single oop Not so fast. This there's a single query that gets annotated first by something called budget annotation, which which is basically a function generating an annotation that you can plop in Then you calculate something and then you calculate something more and something more and something more and then you annotate again because then you can relate to the annotations you have before and do something with those
Speaker 2: Like this cost coalescing means actually if if if we're having well if we're not having anything then we're if we're getting none then we're getting zero. So Because we don't we can't really calculate with none, so that's why we're doing it like this. Um and it's getting sent to the float field And this is actually the subquery that we're plopping in. And you can plop it in anywhere you want to because it's just a little thing that you can treat as a function. And then we have, well this one, this one is is uh even a little more annotates, chained again, like functional programming. Really nice to read in my personal humble opinion um nicer to read than the other things
Speaker 2: anyways and um very performant as well so What you can take away from this is that annotations are composable. You can just use them as functions and you can plug them into any query you want to. When you have an annotation, you can use it later on for every but everything else. So and well, what is this? This this is actually this is really irritating because it is a very old version of my presentation. This is I have to well I have to improvise So um okay. Uh he here we have two queries that come into uh The system and Python is uh responsible for mangling this into one result
Speaker 2: and checking that we're not dividing by zero and stuff like that. And this can also be done in a very nice annotation way where where we just use this little case when thing and again this kind of writing things um and the posting posting base queries actually is just a function that gives me a kind of query set that I operate on. This is about oh, sorry. The window functions. What is a window function? Remember this sliding celldo on the right hand? I was really Because I'm only a double bass player and not an economist and not a mathematician and not a programmer. I was thinking, but how can I do this? And I came up with this sliding window thing in tools
Speaker 2: And it's basically here where my colleagues just left the party because they didn't really know what was going on. The thing is it's it's it's actually kind of hard to um have a state and then you add something to the state and then this state changes and this roles and stuff like that and you you also want to be able to partition it Like looking into it not only from the start of the year to now, but maybe just for one month or something, and then you want to be able to turn it around, all of this is really hard In the database, it's actually this. And this does all the things. I I mean I I'm I'm I'm almost ashamed to to say that I That that's the other thing with those talks.
Speaker 2: You're talking about things that you afterwards think everybody should always do this and nobody should never do ever do anything else and this everybody does this anyway. So you're all sitting here knowing this But in case you don't, well if you do, then bear with me, we're almost done. So um this is another window function that makes it possible to accumulate these uh accumulate data by month. And this is for the for the history overview that we have. We're really running through it because we have so much more to look at. The JSON B in the ORM. So as I said, the sortings of the accounts is something that lies in a JSON blob that sometimes can be 16 elements long, sometimes it can be 64
Speaker 2: elements long. Everybody every community has their own way of basically displaying the data saying we want those here, we want uh the accounts for well our washing machine account is really important. We want this very m very high up in the list and stuff like that. So you can't really go after the numbers. after the account numbers that would be easy. You have to take the JSON and say this is the order. And yeah. That was what I did before. Again, lots of pipelines, lots of pipelines, lots of steps. And everything everything works basically on every account. So you're having this list of I don't know 200 accounts that you have to display and for every account you ask what kind of group are you in? Where are you sitting in the list?
Speaker 2: Okay, fine. Takes time. And then we're going here and then actually we're getting a little more SQL than uh well it's it is raw SQL but still in in kind of an ARMY way because we're using this little constru and uh con construction there. that actually just gives us a value called account group and then we can use this to sort things afterwards which is really um which was really neat And this is something that I just discovered. That the the ORM actually makes it makes it kind of easy to traverse. lists JSON lists in in um in uh in the ARM directly with this double dash
Speaker 2: na uh zero double dash nil meaning the first element of the list. You can do this for if you know how many elements a list has and if you know this element is always the most interesting one, then you can do it but like this as well. So that I just want to show this And you can even, which we never did, but you can wrap it in a manager and use it like this. And then everything suddenly feels feels really easy. This one is um dynamic and socray generation. The headline is more fancy than than the content probably. So um this is something that If all this data model thing before was maybe okay complex, then sometimes it happens
Speaker 2: that you lump five or six communities together into something that's called a sum community. And then you want to be able to see all the facts summed up, but especially also for everything singular, as a for for every singular community. And then you want to pair it with data that lies in a completely different place in the database, like for example, who's living there, who's paying how much in rent and stuff like that. And how does this look over time? And this solution here, which is on the red background totally by um By virtue of what it does. It is it gener it generates basically sixty queries for five community
Speaker 2: communities in twelve months So if you have five communities in 12 months, then you're generating a shitload of queries. I'm sorry about to say sorry to say this. But I thought this must be possible in a different way, and of course it is. And this takes a dictionary com a dict comprehension and another dict comprehension for the aggregates. And you say, well, okay, this is a dict of annotations and a dict of aggregates, and I'm just plotting them in. And this can be how many you want them to be. And if you just take care for that the aggregates ask after the right thing, ask for the right thing that the annotations annotate, then all is fine. This I
Speaker 2: Thought when I first did this, I thought okay, this is like really neat unpacking annotations and aggregates in in a query set. And then a week later I thought, okay, everybody should do this. So And this is another thing that is a little wild. Nesting outer refs in subqueries, which means You have something that references another thing that again references another thing and another thing. And that you can actually finely and perfectly do by just using the right outer ref in every step. And just chaining it together and then you get to what you really want to know by this way. I'm not really going too much into details because I'm um yeah, well, I'm just hurrying through the
Speaker 2: subject. This is the thing where it gets really unjangoish because as far as I know, Django doesn't really doesn't really have a native way to handle um materialized views. So and materialized materialized views as of now have to be generated by a pure raw SQL query and then you can do this. No, not this. You can do I'm jumping. You can do this You're making a model that is not managed and you're referencing the data from the the table from the gener uh materialized view. And then basically you can query it like anything else. And that is really neat for something that takes a long time, like for example comparing 5,000 communities and their key
Speaker 2: elements of interest. like how much do they pay per square meter for electricity, for housing and for water heating and for maintenance and stuff like that. And again, those those account groups where the the water heating and the where the maintenance sits and where the that's different from community to community so that lives in another in another JSON so you have to uh get this as well. And this gets hairy really fast if you want to do it in Python it takes all an awful lot of time. Um so What I did was I I built a query that does it for me, and this query gets um Well, should I say unpack? No, it gets gets well
Speaker 2: bridged into being raw SQL that I that I can import here and use for uh the creation of the materialized view. And basically the function has two things. The class has two functions, create materialized view and refresh materialized view. There's a drop function as well. But um you want to re be be able to refresh the view because otherwise the data is stale. So you want to be able to say once a day or once a week or something, whatever is whatever is feasible. You want to renew this, this takes a while, so do it when nobody's using the application at night or something, and then you should be all set and it works really, really well. Yes, this is the daily refresh by a salary task actually.
Speaker 2: So um and sometimes Sometimes you can't really do it with E URM and then you have to write SQL. So that's like um I came to my own limits and There's probably people who can explain to me how how I do this in another way. But this here is something that uh calculates averages um and it was really hard to do because you have things like uh ctes, common table expressions Which means that you basically annotate something that has its own select. And that is something that Django doesn't let you do. And um That's why I'm writing this SQL there.
Speaker 2: Don't look too much at it because it's well it yeah. I don't know if it's even abbreviated or something. But I'm writing this SQL as a string and um Putting the data out and then I can use it as I want to. And another thing that doesn't didn't work for me in the Django way is the uh is a leading window. which uh I don't really remember what the limitation was, but it just I couldn't I couldn't grapple it in in the in the ARM so I basically wrote SQL for this as well And then lateral joints of course, lateral joints is another thing that I didn't know a way to do this in Django, so I had to resort to uh pure
Speaker 2: SQL as well. Um so and what I learned from this is that uh retrieving data and iterating over it in Python is a bad idea. That's that's the biggest thing. So you always think like okay it's it's fine for 10 objects for hundred or for a hundred objects, but not if this is at scale and if it's getting bigger and bigger and all the time changing and stuff like that. And then yeah, of course the annotations are composable and you can uh use funk to drop real S real SQL in. And this this thing with the materialized views gives you pre-computed data just accessible like a mo uh like a model and the thing with the dig comprehensions that can generate as many annotations as you want to in a loop.
Speaker 2: And so I would say that no raw SQL is something that you can aspire to because everybody's going to love you for writing readable code that they don't have to learn SQL for. But it shouldn't be dogmatic. Sometimes you have to do it. But the measure before you optimize is the very important thing. But s because sometimes sometimes either way, either the raw SQL or the ARM way is surprisingly slower than the other. And then you have to choose something that well one example was that I was doing something to some sort of JSON, I don't really remember it, and and that was performing really slowly because I I hit some weak spot of of of Postgres that actually didn't do this very well.
Speaker 2: So it was actually faster to retrieve the object and do it in Python. And sometimes you have to do this even though it doesn't look as good. Or it's it's just the solution that you you basically want the other way to work And it didn't. So sometimes you have to well just do it the other way around. So and sometimes you have to go raw and this is will with like common table expressions and lead lag. If there's anybody here who tells me how to do those things in the ORM, I would be so happy to do this. Because I I yeah I don't trust my own SQL skills. So um And yeah, but at least document what it does and why and keep the raw SQL in dedicated functions so that you can find them and ditch them when somebody at DjangoCon tells you what to do instead.
Speaker 2: That's the takeaway of this. And I say thank you, and if you have any questions, I don't know if I can answer them, but I try.
Speaker 1: Thank you very much. Okay, we have one there. Right there
Speaker 3: Hey, hey great presentation. I'm just wondering with the ORM um magic and kung fu, uh how hard is it like Yes, this made everything faster than doing it in Python, but sometimes even in the ORM things can get really slow. And then uh You can see the SQL output and you can analyze why it's slow. Yes, but Putting that back into the ORM so that it generates the correct sequel without, I don't know, maybe a different join or something. Like that's the hard thing for me. Um and like sometimes I couldn't even do that, so I had to denormalize data
Speaker 3: to to do that. So do you have any hints on How to get from this kung fu to actually optimizing queries with it.
Speaker 2: Yeah, well as I said, sometimes sometimes it happens that you find out that um I don't know why this is slow or I don't know how to how to do this in the ORM as as of now, then you would then you would be perfectly fine to in a sane way to resort to raw SQL and maybe ask somebody who knows. I don't really have uh one size fits all answer to this. What I what I sometimes did is that I as you said I took the queries that were generated by the ARM thing and and put it into uh SQL directly for for for for getting an analyze of it. And then sometimes it would be easy to see and sometimes it was it was just a matter of indexes missing or something. And for me
Speaker 2: that one was was a really big thing that that solved lots of problems. Of course, it's it's not really ORM related, the thing with the indexing. Sometimes the speed problem isn't ORM related anyways. So well I would dare say that Lots of times it's not the ORM that's at fault because they're they're the ORM is actually really smart. It's it's kind of hard to outsmart the ORM with with real SQL s for the most part I guess at least for me so um what I can say is analyze it see if it's something that is not really related to how you do things but maybe how how things are stored in the database and then then well just try and fire away with an with an with an index maybe it does something that is good for this.
Speaker 2: So that 's at least what what it did for me.
Speaker 1: Um have one I can do that. I'm closer.
Speaker 4: Thank you. Thank you for the great talk. You've did The techniques you demonstrate are wonderful, but I've often wondered with the or seen that the the ERM there seems to be a big learning curve between the sort of basics and then these much more advanced techniques. How did you pick them up? How did you get over that barrier? Um
Speaker 2: yeah. I used basically the Django shell for a very big part of my paid working time. Just mocking about and trying and and and it's it's just basically While improvising my way through is and read error messages and see like okay this doesn't work. Why doesn't it do it doesn't it work But back then there was no such thing as clawed code or something that could help me out. So um when I did this We were with the AI thing, we were there where you just asked ChatGPT and you usually would get code back that didn't work. And maybe plotted you in the right direction. So maybe maybe this thing said something that, okay, this doesn't work at all, but maybe I I I take this as an inspiration and try this.
Speaker 2: And sometimes that helped. And I just tried and tried and tried. But that that is again the jazz musician's way to think is we're we're used to Grappling with things that we don't know anything about. We're always constantly practicing things that we are not able to do yet. And so I did this While I was being paid and sitting in the office and trying to like okay, is this fine and then suddenly five hours were going and I could do another annotation So and um th I I can't really say anything more and then and then sometimes you go on Stack Overflow, maybe there is something, there is surprisingly little. Because that was basically a motivation for me to say all this because Uh uh uh it at least it looks like the world is u using the database for retrieval and using Python for doing everything else.
Speaker 2: and because I couldn't really find much help there. I was I was basically on my own and was was uh uh forced to try and and fail and try and fail better and try and maybe succeed. Yeah, well that's what I did anyways.
Speaker 1: Thank you very much. I think uh this is it. Uh thanks a lot Matthias for the talk today.
April 15 - 17, 2026
Athens, Greece
Note: We understand that names change, people change, and bodies change. We respect each individual's journey and privacy. If you have any concerns about a video or need us to remove content, please don't hesitate to contact us. We will handle your request with care and promptly address any issues.
Published October 26, 2026
Published October 26, 2026
Published October 26, 2026
Published October 26, 2026
Published October 26, 2026
Published October 26, 2026