Beyond the ORM: from Postgres to OpenSearch with Andrew Mshar

Speaker 1: Thank you everyone and welcome. This is Beyond the RM from Postgres to OpenSearch, and I am Andrew Mashar. I am your Lightning Talks chair, as I have been for the last two years. If you haven't joined us yet, come by tomorrow at noon. I'm a software developer with a broad range of skills. I've been doing Django for about 10 years now. I've also had other experience in Python with machine learning tools. This was before the big LLM craze, so much more binary classification than anything. I'm also a game developer. I've been doing that for about 15 years, primarily with Unity 3D, and I currently live in Richmond, Virginia. Today I want to tell you about some work I'm doing at Energy Solutions, primarily in the field of energy efficiency. This will be the business context for the problems I'm going to be talking about.

Speaker 1: And then I want to tell you about some of the technical parts of this that we're developing at Energy Solutions. Three primary projects, Iris, Greenhouse, and Cosmos. And then I want to show off OpenSearch, talk about what it looks like, show off some things with it, do some demos, and then talk about the trade-offs in moving from Postgres to OpenSearch. One thing I want to highlight first is that this is not a talk to compare and contrast OpenSearch and Postgres. This is a talk about some data that we had that we felt was a better fit for OpenSearch. We still use Postgres in almost every project we have, including the three we're going to talk about here. And Postgres is a great technology. But I feel like there's a few talks about Postgres

Speaker 1: every year at DjangoCon and not nearly as many about other data stores. And so I wanted to highlight this one that we were working on and just tell people how it worked for us and how things are going. So, energy solutions, we've been around for about 30 years, and our uh primary goal is to mitigate climate change. We do that in a few different ways. Some of the folks I work with work in policy and ratings. Other folks work in distributed energy resources, think solar, battery storage, and then the work that I'm doing right now is in energy efficiency, and that's what I want to talk to you about. So, what is energy efficiency? One definition we have is using less energy to get the same job done. And that might seem minor in the scale of climate change. But when you scale that across

Speaker 1: states and nations, you can have a big impact. We see that in the work of Arthur Rosenfeld, who was a physicist. Who moved into energy policy in the 70s, starting in California and then moving to influencing policy in the United States And what we saw in his work is while other states were seeing energy demand rise and usage rise along with it. California saw a long period of energy use stabilizing despite rising demand due to larger appliances, consumer electronics, et cetera. So you can have a big impact when you incentivize energy efficiency at scale. So how do we do this at Energy Solutions? I'm going to tell you about that in just a minute, but if what I'm telling you sounds interesting.

Speaker 1: Erin Mullaney, my colleague at Energy Solutions, did a talk about this a few years ago and went a little deeper on kind of the how and why of energy efficiency in particular. So please check her out her talk, Fighting Climate Change with Django. So how do we do this? We work directly with utilities, the power companies, to primarily to give rebates to manufacturers and distributors of energy efficient equipment. Now the reason we do that instead of giving the rebates directly to consumers, I can give you a good example of why that is. So imagine your air conditioner goes out tomorrow and you have a couple choices. You have a not so efficient choice that's maybe $5,000, price completely made up for the sake of the example, and can be installed tomorrow. Without these rebates, your other choice might be a more efficient air conditioner that is, say, $7,000, but can't be installed for weeks because it's not in stock.

Speaker 1: With these rebates, that efficient equipment is much more likely to be in stock and the price will come down, meaning you're making the decision more on something like total cost of ownership. Rather than, well, I can't afford that and it can't be installed. I'm not going to be without AC for three weeks. So that's how we incentivize these things. One thing I found very interesting in starting this work was I was wondering, well, why do utilities want to do this? I can see why it's good for the planet, but why would a utility want to do this? Don't they just want to charge for more power? Uh and there are two reasons that's not the case. One, they are monopolies and therefore regulated in the United States. Some of them are regulated specifically to do programs like this. But even when they're not, it's in their interest, and I can explain that with this graph.

Speaker 1: So this is known as the duck curve due to its shape. And what it shows on the y-axis is the system load, so how much energy is used on the grid. And that can be this we see this pattern. across the United States in any given subregion, state, etc. And then the x-axis is from midnight to midnight. So this is energy usage throughout the course of a day. So what we see in the morning is that we have pretty steady energy usage. It dips when people go to work and then it peaks when people come home from work and turn on all of their appliances and things like that. That peak is the reason that utilities want these rebates and want this high efficiency equipment because that peak, they have to deal with the load from that peak and in fact more of that load so that they don't have blackouts.

Speaker 1: And in order to do that, they have to build extra power plants. Those extra power plants are very expensive and they're very expensive to maintain. And so the more we can bring down that peak in a number of different ways, but specifically here with energy efficient equipment, the less they have to worry about extra power plants, which is a huge cash outlay. So That's why the utilities are interested in this as well. So how do we do this? Uh we do this primarily in a Django application called Iris. It houses about 50 active programs right now. It's had about 90 over the course of its lifetime, and we're continuing to add them. I'm going to talk about four main components of Iris. As any big business application, there's a lot more than that. But these are the

Speaker 1: kind of highlights that will get you to the core of what we need in order to talk about moving some data. So the first is claims. A claim is a request for a rebate. Usually a claim will have some equipment associated with it. I may use the term materials and equipment interchangeably here. That's because we're moving from calling the app materials to equipment. So that's why I'll I might mix those two up today. So equipment can cover a huge range of things from light bulbs to air conditioners to refrigerators to televisions, and I'll get into why that's important in just a moment. The other big part of iris is measures, and those are the rules that govern the rebates. So whether something is efficient enough is a measure that has

Speaker 1: a bunch of rules about uh different pieces of equipment. The last piece is locations and locations are um even if a piece of equipment meets all of the criteria for a measure. If it's outside of a utilities region, you won't get the rebate. So we have location information, which is usually where the equipment is installed. And to jump ahead a little bit, the equipment and locations tables are the two that we're going to talk about moving from Postgres to OpenSearch. But first, I want to show our tech stack here. Most of this is just kind of FYI in case people are interested. The main one I want to highlight though is AWS because it ends up informing a lot of our technical decisions

Speaker 1: or at least some important ones uh down the road. So what difficulties did we have that led us to rethinking Postgres? As with any application that grows, we have increased complexity, more data size that we have to deal with, things like that. A lot of that we were able to deal with. to work on and improve. But one problem we ran into that is pertinent here is configurability. So when we talk about the equipment and how many different types there are, we initially had uh a table for each different equipment type to describe what we refer to as attributes, you get think of those properties, things like that. So if you think of a TV You have the panel type, the uh size, things like that for a light bulb, wattage, and base type. And you can imagine different attributes for all of these different types.

Speaker 1: Well, as those grew, uh you can imagine the SQL gets a little uh hairy, uh, but actually the bigger problem we had was that uh these attributes could change at any given time. because of our data sources. So they could remove one, add one, or change the name. And if anybody is familiar with Django migrations and moving a lot of data around in different columns, you'll know that that is not a good idea to do very often. And so when we realized we had this and this was essentially a business necessity, this flexibility, we moved from having all of these tables to putting this data into JSON on the material itself. That worked great for a little while, and then we ran into some performance issues. I'll talk about those performance issues in a lot more depth in a little bit, but we also had other performance issues in both equipment and locations.

Speaker 1: um primarily initially from uh large imports. So most imports were fine, but then the larger imports that we had for both of those were just starting to get longer and longer and cause more issues for us as we had more business logic that we had to apply to each import. We were also having performance issues with related to search for both of those. Another issue we noticed as those tables grew and grew was moving the data around and having different copies of the data. So on a dev machine, we would have different or everyone would have copies. And then we have a lot of or we have several instances of our servers. So a dev environment, a staging environment, those kinds of things And so paying for all of those databases in the cloud. We actually had a different solution for that initially to reduce costs, which is we moved from

Speaker 1: individual RDS instances for each one. to a serverless database that we could share for some of our non-critical server environments. I'll talk a little bit about that later, but that was one of the ways that we addressed the cost part of this. The last difficulty we had was, as I mentioned, Iris has a lot of rebate programs, but there were other programs that we were starting to spin up and investigate. That didn't quite fit that same mold, and so it but they still needed access to this same data We actually have a project that was already created for this purpose. So we we were able to we didn't have to worry too much about this. This is something we had anticipated.

Speaker 1: Our CIO at previous companies. had had issues with inter-app communication uh kind of bubbling up as as the apps grew and grew. And so we designed this application called Greenhouse to make this a bit smoother for us. So this is this is part of the solution. The third part of the solution is Cosmos, and Cosmos is another project where we specifically pulled the data out from Iris from Postgres into OpenSearch. And so where we're at today is that we moved the locations data completely out of Postgres into OpenSearch, out of Iris, into Cosmos, and now Any requests for locations data goes from Iris through Greenhouse to Cosmos and back. The equipment data, we're in the process of doing this right now.

Speaker 1: And so I can give you a lot of information about what the process is looking like, what performance is looking like, but we're not quite finished with that one yet. Okay, so back to the problems to solve. What were we looking at here? Well, we've already talked about the data redundancy and how we were able to solve some of that with um uh serverless database, uh but the bigger issues we were having were performance related and we felt as though we couldn't quite solve those with Postgres. We we did some work I'll talk a little bit about some of the things we tried before moving to OpenSear, but we felt that we wanted a database that was a better fit for Address data in particular, and then JSON data, large JSON data, in the case of materials.

Speaker 1: So why open search? As I mentioned before, we're on AWS, all of our data and servers are in AWS, and so it made sense for us to keep everything in AWS and use OpenSearch versus Elasticsearch. I mentioned this because if you're not tied to AWS, Elasticsearch may be a good solution for you, so it's something to consider. But the other reasons for open search are listed here, and these are things that are mostly built into the database. or data store that we felt would be a much better suit for this type of data. One I want to highlight is the ranked results. So for locations in particular, people may leave out a piece of a an apartment number or they may mistype some part of an address. And having this scoring was super valuable to us pushing

Speaker 1: that analysis to the database rather than doing it in Python. So what does open search look like? Uh I before I can kind of dive into it, I want to talk about, I want to define some terms for folks that may be more familiar with a relational database. For another version of this, feel free to check out Jib's talk from yesterday. It's MongoDB, so it's a little bit different from OpenSearch, but a lot of the same concepts apply of non-relational versus relational databases. The first of which is a single piece of data in a relational database we usually refer to as a row. In a non-relational database, it is a document. So that's your core piece of data. The second is index is a very overloaded term in this context because in a relational database, an index is kind of like a lookup

Speaker 1: table to speed up your queries, but in OpenSearch, an index is simply a collection of documents. It's not analogous to a table in a database because you generally don't want all of your data for a given type to be in a single index for performance reasons. I won't get into why and how to break all that down, but suffice it to say, you you don't usually store it that way. And so in a database about movies, you may have an index that's uh just for movies in 2016. And so you would have a document, La La Land, by Damien Chiselle. You would have another document for Moonlight by Barry Jenkins And then if you wanted to search across all of your movies, you would have an alias that collects all of those indices together. So an alias is a collection of indices.

Speaker 1: An alias does not necessarily have to be all of your indices related to a specific type. So you might also have movies 2010s. uh where you would have movies 2010 movies 2011 etc etcetera so you may have decadal um indices there The last term I want to uh define is a mapping. A mapping is kind of like a schema in a relational database, but without the validation. So you can do a lot of things without a mapping, but a mapping is informing the database of what data to expect so that it can optimize your queries. Okay, so now I want to show what this looks like. And this first one, uh I'm just gonna I'm just gonna perform this operation

Speaker 1: and what you'll see on the right side is that there's no data here, so I'm just gonna build this up from scratch. What we're looking at here is the Kabbana dashboards for OpenSearch. So this is built into OpenSearch. So if you install OpenSearch, you can play around here. And this is a great spot to get started because you can kind of really iterate quickly putting your data in and then querying it. However, it's also a really good thing to use later, like as you get more familiar with this, I find myself, even though I have a full code base that other engineers have written alongside me. If I'm doing something new, like creating a new query or creating a new data type, it's much faster to kind of iterate here and then go back to my code base and finalize it. So this is something I use pretty often

Speaker 1: while using it. It's not just a beginner tool. Okay, so let's create some data. So you'll notice all of our actions here are rest actions. So that's how we're going to interact with open search. So the first thing we're doing gonna do is create some data. So we're gonna post to an index. So this is the name of the index, locations DjangoCon A. I'll just call it locate or index A in the interest of time. Um and then underscore doc is saying this is just a single document. We have the data here. So we create that and we get back our ID. As we see here, this is a unique ID for this.

Speaker 1: And so this is what a query will look like. So we do a get on that same index underscore search. And then you can see what the query looks like here. We're just gonna grab everything. I'll show a more complex query later. The other thing I want to highlight, this track total hits is optional, but I really like to add this on all of my queries because it gives you a much better sense of, okay, I have a million pieces of data, I'm expecting a hundred thousand, I got back ten. I probably did something wrong. So it's a good a good check. Okay, so we we got that ID back. Uh it's in the index that we expect, and it's the data that we just put in put in. That's great. We will create another piece of data very similar to the first in a new index. And then this is where this is the exact operation I did on the first line.

Speaker 1: And what we see here is this is just information about your indices. So this is a nice command to run when you have a lot of indices and you want to check like, oh, what is the latest index I created? Or did I actually create that index? Is it in here that I expect? That kind of thing. And what we're doing here is we can see this star is a wild card. So this is giving us information about all of the indexes that match this. This pattern. And so we can do the same thing in search to search all of our current indices that match that. And so we get both of our values back. So that's nice as well. So now we've created two pieces of data and two separate indices. So one thing to highlight here is we've created our indices just by putting data in them.

Speaker 1: So again, it's pretty fast and loose. uh in open search. Another way to create an index is to create a mapping for that index. So we'll do that here and we'll give it the same two fields we've been using. We can see that we created the index by querying the indices. And then we will create a document in that index And we'll search for it. And what I want to highlight here is what I mentioned before about the mapping. So the mapping does not have this extra data field, but we're still able to retrieve this value and see that extra data field. One big caveat here is that if you do a query on zip code for matching

Speaker 1: 90210, you'll get this data back. But if you do a query on extra data, you will not get this or any other data back because it's not in your mapping. I learned that the hard way. So keep that in mind as you start to search for things. Before I get to the bigger query to show a good example of that, I want to talk about a library that we use to interact with OpenSearch in Python called OpenSearch Pi. We find this to be really well documented. Helps us learn about OpenSearch, in fact, with the things that they have, the helpers that they have. As I mentioned, a lot of your interaction with open search is going to be restful. So you could do this with requests. And you can imagine a simple example like this. you could replace this client. search with request. get.

Speaker 1: But we find this library to be very helpful in real-world application. where um you have a more complex uh set of actions you're trying to do. You can write code that's a little bit more Pythonic like this, where on the Left side, you have all of your imports and some variable definitions, and on the right, you're operating over all of your indices uh updating all of your document or grabbing all of your documents to update and then using this helper function to update them. It's not important to follow all of this just to understand that It helps organize the code a little bit. So I'd highly recommend if you're interested in actually getting open search to play with your code base , check out this library. Okay, so this is where we're going to dive a little bit deeper into the queries we were using that were running into issues.

Speaker 1: So this example is our equipment. is an equipment query. And so this was printed out from a script that we have that pre-processes the equipment to check if it's eligible for a given measure. And so as I mentioned, a measure is essentially just checking these attributes for efficiency. So what's interesting about this is All of a sudden, about a year and a half ago, that pre-pro pre-processing script kind of exploded in time. So it took a whole lot longer, and that was primarily for two reasons. Around the same time, we had a whole lot of data put in, which is good. It was a new program that was ver perfectly valid. But we also switched from the individual RDF instance. To the serverless database. And these queries in particular, some of them

Speaker 1: take up to 10 times longer in that serverless database. There's actually a large variability, so it's not always 10 times longer. But even without that, we found that this really wasn't a great match for Postgres because We have so this was the most egregious example. It took about 20 or sorry two minutes on my laptop to complete. On a kind of mid-range RDS instance, the RDS instance that we were using, the time was about the same. We could throw some more hardware at it, but I don't think that's the best use of our time and money. Now I said this was the most egregious, but we also have uh hundreds of other queries like this that take a minute, 10, 20, 30 seconds. So they those add up pretty quickly, and I think it's pretty clear this is not a great use of Postgres

Speaker 1: This is what it looks like in SQL. I kind of just wanted to show this before I show what it looks like in OpenSearch. So I'll show this in Kibana. And you can see this search looks familiar. We have an index. We're using the same wildcard pattern to search all of the equipment. We have this query parameter like we had before. Here we have this combination of bool and must. What this tells us is that this query, we only want to return values or um documents where we're matching these attributes exactly. There are other types of queries, for example, a should query that will give you that scoring I was talking about with locations where you're getting those ranked results.

Speaker 1: For equipment, we don't want that. We only want if it matches exactly for these attributes. And so these top ones are all term queries. So basically a string. Does this attribute match the type that we're asking for here? For example, an air conditioner for the descriptor. And then there are also multi-term queries, so you can match against a list. This is only a two two item list, but you can have an arbitrarily long list here to match. Does it match any of these values? And then for numerics, you have these range queries. This is what they look like. You have less than or equal to 65,000. And then as you can see, you can combine these. So for the sear value, S E E E R, you have less than or less than 17

Speaker 1: or greater than or and greater than or equal to 15. So you can combine these as well. I wanted to show this after the sequel to kind of show I think you can argue which one you liked better or which is um yeah, I guess what your opinion is on which is better. I think it's hard to argue that this is significantly more complex than the SQL. And so I wanted to show this to show I don't think we're adding any complexity. to our solution here by switching. For locations, I mentioned we did some work ahead of time trying to improve what we were doing with Postgres before moving over to OpenSearch. Part of that was breaking the address into trigrams. I believe we were using a Postgres extension for that. That still didn't get quite the search accuracy we wanted.

Speaker 1: And so um but moving to open search has given us basically everything we wanted. So improved import speed, um improved search speed, and then the search accuracy is probably the biggest one for our our business Uh folks, that that was a huge ask. It was um something we just could not get to do, uh get this this code to do in Postgres. Um The import speed is also a huge one because whenever we have problems with that, it wasn't just, okay, this is taking a little while, like let's run it overnight or something like that. Whenever we would have errors, we would have a lot higher of a support burden around that. So that one also is pretty big as well. With equipment, I can't say finally like what everything is looking or is going to be. But what I can see

Speaker 1: say is our initial uh tests. So in terms of improved search speed, as I just, I actually I forgot to mention this, but that two-minute query from Postgres. takes milliseconds, like a couple hundred milliseconds at most. And the rest I I tested some of the other ones as well. They return about that fast. And that quick enough that we're actually considering dropping that pre-processing script. I need to look into that a little bit more because we're doing a lot more than just one query at a time, but that's the kind of speedup we're talking about. That's been pretty awesome for us. And then in terms of the equipment import, we're also seeing very big speed ups. Not quite to that level, but enough that our again our business folks are very happy with the speed-ups we're seeing so far. So that should be good.

Speaker 1: Okay, so this talk is titled Beyond the ORM. So as we talked about before, we're losing the ORM. We're moving to OpenSearch, which means which is not supported by the ORM. And so in order to get back some of the functionality that we've lost, we've adopted pedantic models for that data to pass between the three different projects that we have. And that offers us a few different things that are nice. One is validation, similar to Django models, though not the same. And then I have dot notation here. That's more just as you use the Pydantic models outside of the model class. It looks pretty similar to Django model code. So it's a nice familiarity there.

Speaker 1: You also get serialization and deserialization. This combined with the typing and the validation can be really nice to evolve your code base. So that for API endpoints or methods, you can extend the types to include the um more more specific types that you'll be moving to with the Pydentic models later and you don't have to commit to doing all that work at once. You can kind of use subclasses to evolve your code base in that way to give yourself breadcrumbs of Okay, this is what we're going to implement later, and then remove the previous looser typing that you had. Pydantic also provides nice versioning with API. And then another nice pattern that we've developed, a coworker of mine uh helped me figure this out

Speaker 1: is With these three different projects, you can imagine diving right in and coding a new feature in Iris, figuring out how to send that to greenhouse and then what the API endpoint looks like. in Cosmos. And as you're developing that, you're doing work across three code bases that can be a little tricky. What we can do with the Pydantic models is create factories for those models and then mock those endpoints so that you can develop your API and primarily develop your feature in iris. And then you kind of get the shape of your API, and that way you're not developing across all three. And usually you can finalize your API and then you just implement it in the other projects and you're done. Of course, there might be some cases where you do a little bit more work um in in the

Speaker 1: the final API in Cosmos, um, but the idea here is this way. you get to a point where everything is in the sh shape that you're you're going to ultimately do and you do less of that work across the three. As I mentioned, we lose the ORM with this data. We lose some other things as well. We don't have access to the Django admin out of the box anyway. You can um Kind of hack or pull some data into the admin from open search, but it's extra work. Or you can create a crux custom front end. As I mentioned with these three different projects. Anyone working on these projects generally knows where that the equipment data and the locations data is in Cosmos, but there's other supporting data that doesn't necessarily belong there

Speaker 1: and could be in one of three places and could be accessed in one of those three places. So that's some additional complexity that we pick up. This is actually the pattern that Zags was talking about this morning, where uh Django is acting as a multi-service architecture or a multi-u-layer architecture. And we've had a lot of success with with it. So uh you know it does work. Um but this is the complexity that you pick up there. Um and then in addition to that you get this complexity of development setup. So we kind of avoid this by ha having most of our engineers focus on one of these projects, but for me and some of the other folks that are working on this integration at this time, you pick up that complexity. The last trade-off is uh time. So we've had to spend time learning this new technology.

Speaker 1: Uh most of our engineers are not super familiar with open search and are much more familiar with uh uh Postgres. And so we've had to take that time to really learn this technology. However, all of these trade-offs have been well worth the investment. because we had we were just not able to get where we needed to go with Postgres with this data in particular. And as a reminder, that's locations data, so address data and the accuracy of finding that, of finding the right address. And then this very deeply nested JSON data in equipment. So those two pieces of data we just didn't feel were the right fit for Postgres for us. And so these trade-offs have been worth it for us in moving to open search. That's all I have.

Speaker 1: Thank you.

Speaker 2: All right, thank you, Andrew. Good job. Do we have any questions from the audience? We have a few minutes.

Speaker 3: I guess one question I have is sort of philosophical. Do you feel as if uh like this is also like an inflection point for Django to kind of recognize that because of these new use cases that are burgeoning, there are more opportunities to build more like Third party libraries to generalize the problems that you've had to solve?

Speaker 1: Definitely. I I I think I also think inflection point is tough because I think these problems have been around for a while, but I think that it's definitely work that would be valuable to a lot of people that use Django. It's difficult because you um as we've talked about you can't you can't support everything all at once. Um you know Zaggs was making good points about supporting things on the front end. We only have so much time for volunteers and fellows to do these things. But yeah, I I think it would be awesome if we had more support for for NoSQL solutions because I think there are certain problems that just fit this type of data better. Postgres is awesome and I think, you know, I default to it all in all my projects for uh most data, but there are some

Speaker 1: pieces of data that just fit a NoSQL solution a little bit better, at least to me. So I think it would be great to have more support in Django for that.

Speaker 4: Hello, thank you so much for your talk. So as a person who is not only doing Postgres but specifically doing Postgres optimization and specifically PostgreS application optimization You might think I will say oh my gosh, how you dare. But my other motto is I am all about money So uh basically what I'm always saying, if my perfect solution doesn't bring my customer money, it's not the perfect solution. So money is like the final criteria of good solution However, with all being said, I would really love, I think I posted on LinkedIn, so now I'm confirming, I would really love like Not now, but maybe a little bit later, to talk to you in depth how you manage to create two minutes long

Speaker 4: running queries because that is actually something I'm really interested in. I have several of my personal like GitHub projects which address that. And uh I believe it did not work but I'm curious

Speaker 1: Yes. Sorry, that was loud. Yes, I would love to talk about that. We so what happened two years ago is, or a year and a half ago, we found these queries. I actually dove really deep on this, but I am not a SQL expert. And so we tried several things to get those queries to run faster. We actually found some optimizations on the code side that meant we were running fewer queries. And so we were able to get it to a speed we wanted. And we had already decided on using OpenSearch for the locations data. And so we we knew we were going to move there as well, the the equipment stuff there as well. But I would love to to talk to you about that because yeah, that was something me and another person who is much better at SQL than I am on my team, we worked through for a while and just couldn't get the speed ups we we thought we needed

Speaker 1: directly working on the SQL.

Speaker 2: Awesome. Thanks, Andrew. Uh please give him a hand.