Machine learning in your database

I think there's a big disconnect between what is flashy and hype and academia or, you know, coming out of OpenAI. Like, there's a lot of reinforcement learning or unsupervised applications, but in the business context that we saw, you know, across at least 20 different models and machine learning applications at Instacart, they all basically boil down to you've got some relational data and you're trying to predict a single column and you're going to do some joins and that's it. First thing is you pump it through linear regression, you work your way up through the SciKit Learn algorithm, you hit XGBoost and your predictions are gold standard. You don't even need deep learning 90% of the time.

Welcome to Practical AI, a weekly podcast making artificial intelligence practical, productive and accessible to everyone. This is where conversations around AI, machine learning and data science happen. Join us at PracticalAI FM community and follow the show on Twitter. We're at PracticalAI FM. Thank you to our partners at Fastly for shipping our pods super fast all around the world. Check them out@fassy.com.

Well, welcome to another episode of Practical AI. This is Daniel Whitenack. I'm a data scientist with SIL International and I'm joined as always by my co host, Chris Benson, who is a tech strategist at Lockheed Martin. How you doing, Chris?

Doing very well, Daniel. How's it going today?

It's going great. I missed you last week. I had a good conversation about various interesting topics, but it's good to. Good to have you back with me.

Yeah, sorry I missed that one. The day job got in the way on that one, so had to travel.

Yeah. The actual practical AI of your life got in the way?

The practical AI of my life definitely got in the way on that one. Sorry I missed it. I'm glad to be here today, though. I think we're gonna have a great conversation.

Yeah, this is definitely something that I think fits very well within our theme of Practical AI and something that I. I'm really excited to talk about because I think it might solve some of my own struggles in my own development life. So today we have with us Montana and Lev, who are the founders, creators of Postgres ML. Welcome, Montana and Lev.

Hi. Thanks for having us, Daniel.

Thanks for having us.

Yeah, definitely. So before we get into all the details about PostgreSQL, what that means and what it is, do you want to give us just maybe a little bit of a backstory about how two people sort of find their way together into connecting machine learning things into a popular database. Maybe we'll start with you, Montana. You want to give your sort of side of that?

Yeah, I mean, it's kind of a long winded story. It's definitely not the first time that I've taken a stab at machine learning infrastructure and trying to make things simpler. I joined Instacart about seven years ago. I had been a, you know, chief data scientist prior to that and mostly it was all self taught. I didn't really deserve the title, but at small startups you get to pick your own and that's what I wanted to be when I grew up, so to speak. Anyway, when I joined Instacart, it was a really exciting time. There were a couple dozen engineers in the company. We were getting large enough that we needed to move out of a monolithic Rails app into more of a distributed architecture that would be horizontally scalable. One of the first projects that I did when I started there was pulling all of the product catalog data out of the single postgres database that we had, moving it into a new postgres database, but then fronting that with elasticsearch so that we would have this horizontally scalable catalog system that could power the whole e commerce website as we added thousands and thousands of stores to Instacart's platform. And that, that was really exciting. That was really fun. I learned a ton. I had, I'd worked with natural language processing and search prior to this. I had some experience with Lucene in distributing that. So it was, it was, it was cool to get some new technology and to really leverage that and to start unlocking data scientists and how they could impact the product in a more direct way. But data science was very nascent at Instacart at that point. You know, fast forward a couple of years. I got to sort of lead several SWAT team initiatives around the company to pull out more systems into more distributed architectures, help stitch these things together. And as our team grew, we brought on a VP of engineering, Jeremy Stanley, who's a brilliant data scientist, one of the best people that I've ever worked with. And he sort of put out a call for help of, hey, if anybody can help us get some of these models that we're building in R on our laptops to actually impact the product somehow, we'd love to talk. And so, you know, I got to work very closely with him to help figure out how we would productionize a lot of these systems and to help build a lot of the tooling that Data scientists need, you know, they're going to use Python. Should they be using conda? What does a PIP install actually look like? How do you get that into production? The whole nightmare of dependency management and lifecycle cycle management of models when they're not just build once, but they have to be rebuilt continuously with new data as it comes in. And then you have to get the feature data to the actual model, but it can't be the data that's coming out of your snowflake warehouse. At the time we were on Redshift because that's too slow and latent. We were learning everything and building it on the fly and it was chaotic, but fun. Actually. We published a lot of that work in a library called Lore, which was Instacart's open source platform. As the ecosystem evolved around us really quickly over the last five, six years, things have changed at breakneck speed. There's a new platform library company coming out every day that's doing something really cool. And so we grew that internally, but it didn't really make sense to keep a lot of the stuff that we had built because original libraries built better embedded solutions. They actually built the bridges. We could take some of our tape and glue out of the middle and things got simpler. But you know, fast forward another couple of years. At Instacart, the original system that we had built with elasticsearch as the heart of our data architecture, our data infrastructure, it really became like the beating heart. If anybody had any data, and this included all of our machine learning feature data, you would just shove it into the elasticsearch document and then anybody who needed data would just get it right out of the elasticsearch document. And so our documents grew to several hundred fields, and many of these fields themselves were nested JSON documents. They could be tens of kilobytes of additional payload data. And so our Elastic document size blossomed. And Instacart, I think, is somewhat unique in one of its constraints, in a couple of its constraints. One is the real time nature of the business. Instacart's not like Amazon, where if there's, you know, if it's not in stock, Amazon tells you it's not in stock, it'll be two days late. Instacart can't do that. If we don't, you know, if we say, sorry, we're not going to be there in 45 minutes, we don't have your entree that you're planning to cook for your family, your family is going to go hungry. And that's Going to be a really, really bad customer experience. And so everything at Instacart is built from the product, from the machine learning. It has to be rapid and online and responsive. It can't be, you know, an offline 24 hour batch job that we get around to eventually. And so I think that that is a really challenging technology problem. It's a really challenging business and product problem as well. At the same time, Instacart is a platform for hundreds of different grocers throughout the country that have tens of thousands of stores, all that have different inventory. So it's true that we have, you know, one product, one box of Captain Crunch cereal. You know, it has an image, it has nutrition facts, it has this universally true data about it. But then it also has little facets of data that are specific to every single store. Like, what is it actually being sold for in that store? Is there a manager special that day? Is it in stock on the shelves? Or did it just. Did they just sell the last box? And so if you think about this from a data architecture perspective, it's a pretty classic. You have two tables. One is your products, one is your offerings. You join those two tables together, you denormalize that data into elasticsearch. Easy, except we actually have a million products. We have 10,000 stores. You multiply that together, you get 10 billion. And so all of a sudden, this is an incredibly large elasticsearch cluster. And it's growing very, very rapidly because Instacart was at the time expanding into whole new verticals beyond grocery. It was basically all of retail. And it's like, oh, now we have this whole other dimension and we want to join whole other things and how are we going to scale the cluster? And I remember seeing a graph plotted of our elasticsearch capacity increase per node added to the cluster. There were some diminishing returns there. You don't get perfectly linear scaling when you add nodes to a cluster. At the same time, there's. Well, that curve is asymptoting and flattening. There's another curve that's coming up exponentially, which is Instacart's growth rate, both in terms of rights to elasticsearch in times of timely ingestion to the system. And this is another thing that Instacart had contractually agreed to providing updates to the website on behalf of retailers and in very short amounts of time. So one of the things that I've heard that, you know, Walmart, for instance, does is they have a green Blue deployment for Elasticsearch. Right. And they will spend 24 hours filling up their green cluster with updates. They'll flop over to it all traffic hits that, and then for the next 24 hours they'll, you know, refresh their blue cluster and then they'll flip over to it. And so you can, you can just rebuild your cluster every night, flip back and forth between the two. And that way you avoid like a lot of the incremental update penalties you get in a lucine index in this inverted index world. That's not a strategy, for example, that Instacart can employ because of the tight time constraints. And so we were all sitting around kind of pulling our hair out, trying to figure out how we were going to outscale the business with our technology and getting a little desperate, honestly. Like, I think postgres was not the first idea, but eventually we did decide that fundamentally this is a join problem. If we could do the join at read time rather than index time, then that would potentially eliminate a huge amount of work because many of the documents that we were joining and indexing were actually never read before they were re indexed again. And so we could actually, by not doing those useless amounts of work, we could reduce the amount of work in the system substantially. And so we built a prototype for this system of what would it look like? People have been sharding postgres for decades, something that people know how to do. It's a little finicky. You have to get it right. But even more recently with things like timescaledb and Citus data, they make sharding a lot more manageable, a lot more tolerable. And so, you know, we started looking at some of those options and we started to look at. The Postgres also has these, you know, full text search capabilities built into it. They're not, they don't nearly have the bells and whistles of Elastic, but they, the basics are there. And so, you know, I started talking with our NLP guys and our search engineering team and saying like, what are we actually using in Elastic? What machine learning functionality in Elastic do you have? And like, oh, we tried, but it fell over. So we couldn't do it. We couldn't actually use a lot of. It's too much load on the cluster. It's already on fire. And so what we learned is that most things happen at the application layer and most things are like joins between various microsystem data stores, feature stores that had gotten kicked out of elasticsearch because they were creating too much load on the heart of the company. And then we would join those all at request time at the application layer. Sometimes that would take eight seconds for our P90, it could be quite slow. And sometimes what we would find is like we would do this 8 seconds of work and then at the last step we would find everything. All of our high candidate, high quality results were out of stock. So we would have nothing to show because we had to implement several constraint layers upstream. When we really got into the nature of the system that we had built, that was this distributed machine learning, beautiful beast. It was not a pretty picture, it's a very complicated picture. And so we just said, well, we don't really have any other options. We're going to try to do this in Postgres. We stood up our prototype, we had it running, we were shadow testing its search results against elasticsearch. What we were getting back and forth, we were finding lots of data ingestion bugs, bugs that had been in our data pipeline going into elasticsearch for years. We discovered several of those because we had to rebuild the pipeline in parallel. For postgres, it was a totally different pipeline. Obviously we found several bugs in our postgres implementation as well. When you're doing a second system rewrite, that's never a fun thing. I don't typically recommend people go that route, but things were looking okay until the pandemic started. You know, we had plotted out the intersection of those two curves looking something like a year out that we would have to figure this thing out and to kind of experiment and prototype. And we went through that year of growth in about a week, the first week of the pandemic. And I remember getting paged the next Sunday. Everybody does their grocery shopping on Sunday morning, so if there's a new load issue, it's going to be Sunday morning when we get paged. And so I remember getting paged and Elasticsearch was timing out 30 second requests. We were, you know, we had stopped indexing, so we were, you know, in, in danger of not meeting SLAs unless we could get indexing going again and traffic would go away. We, we, we did all kinds of things. You know, we thought about putting up a stop sign on the website and saying, sorry, Instacart is full, you have to come back another time. Luckily we never had to actually deploy that. Luckily we were able to scale our way out of the pandemic, but it was a lot of work. And so while we were in the middle of this incident, we said, well, we've got this other cluster over here. We think that the results are about the same as the cluster that we're using that's kind of dead right now because it's just timing out at 100% load. So we just flipped the switch, put all the load against postgres and started using it. Of course, it immediately went to 100% CPU utilization and also caught on fire. But we were able to find a few missing indexes for some long tail queries that we hadn't really optimized. And within a couple of hours we got that cluster to a point where we could actually serve traffic again. And so that was really exciting to really get the system bolted down. Took a couple of months after that, but for the most part we had sort of shifted what was the primary system and what was the secondary system. Elasticsearch from that point going forward was really the backup to this new system that we had. And we had a couple incidents with the new system as we started throwing more and more data into it, because what, after we did the original optimizations, we got down to like 4% CPU usage or something in the Postgres cluster. And it was, it was vastly underscaled compared to our elasticsearch cluster. I mean, it was just tiny compared to it. So it was really amazing. But at the same time I mentioned all these other feature stores, model stores, everything else that we had in this, all of those, whether they were Redis or whether they're postgres or Cassandra, those were all melting down as well. Those were not horizontally scalable systems. We learned a lot about scaling every system we had, whether that was rabbitmq or Redis or if you can name a database, we probably tried it at some point at Instacart. So we had lots of fun. But our solution in this case was basically like figure out which database has the most CPU usage, pull all the data out of it, and dump it into this new horizontally scalable postgres cluster that we have. And so we just did that over and over again and we barely kept ahead of, you know, our doubling week over week growth curve for the next eight weeks. And so like I, like I mentioned, sometimes we missed optimizations and we didn't really have the time to vet and test the system that we were building like we should have or could have. But I think that we did the best that we could with the resources that we had. And we spent at least a year after that iterating, adding more, really unlocking some new machine learning for our search team that we could now do. And we didn't get as far as I really wanted though, because at the time there was, there is a library called Madlib which is an Apache foundation project. It's I think 10 years old. It's been going for a while. But there were some constraints at the time. They were locked to a specific older version of TensorFlow. I think my memory is fuzzy. I didn't get a lot of sleep back then. But we weren't able to actually take a lot of our deep learning models and put them into Madlib and run them inside the database to eliminate some of the microservices. So we actually kept quite a bit of the microservice architecture and kept building around that. But it kind of bothered me because we were able to clean up so much of the distributed system. I felt good about it. The system that we ended up with was much better than the system that we started with. And it was kind of full circle for me coming from like I joined Instacart and I was all about distributing everything, but by the end I was all about pulling everything back into one, not one fairly monolithic system. And so that I think was kind of eye opening for me about the complexity, both organizational but also technological, that these systems can develop and how powerful it can be if you can simplify the system. For example, I think when we were on the elasticsearch pipeline, we had a dedicated infrastructure team, we had a dedicated catalog team just to the pipeline. We have a dedicated search team, dedicated machine learning, engineering, all of those resources. And we had upstream of that catalog data acquisition specialists that would get new kinds of data to do new kinds of products and services or add new features to the website. But it took multiple quarters of planning and execution from the like you can set. A few product managers sit around the room, they conceptualize like, hey, we want to add this feature to the website. They're like, okay, we'll go source the data, we'll get the data into the pipeline, we'll ingest it into elasticsearch, the search team will start using it and then they'll display it and you know, they'll start work this quarter. The search team will get to the next quarter. Oh, wait, we don't have the data in the right format. Let's circle back for another quarter of this whole process. And so it was, it was really, really problematic.

So Montana, fascinating to hear about this sort of like progression at Instacart, the sort of scale up and the issues, especially around the pandemic and having to respond in that way and how you sort of, like, that path, that journey led you into Postgres before we sort of, like, continue into, like, the Postgres ML story. I'm curious, like, Lev, were you experiencing this also with Montana? Or were you coming from, like, a different. A different side of things? Or how, like, how did you experience your sort of, like, journey into, like, thinking more deeply about Postgres and where it intersects with data science, machine learning, all of these things.

I'm laughing because, you know that system that Montana is talking about, I might have been the guy who built it.

The Elasticsearch system or the Post PostgreSQL system.

I came in as a true believer. I was told that Elastic was wrong and Postgres was right. And I'm like, sounds good, let's build Postgres. And it's not like we built it on RDS or anything. We actually built it straight up on EC2. So I had to learn things like, ooh, how do I install Postgres on Ubuntu? Should I pick Ubuntu? What kernel version do I need? And it wasn't because we kind of like, oh, yeah, self hosting is the way. It's because RDS was too slow to power our workloads. If you ever run on. If you run your databases on rds, you know that the disks are. They're network disks, right? So the latency is at least like a, you know, 10 milliseconds depending on day. You know, iO2 is probably a little bit better, but still not quite there.

And rds, for those that don't know, is a managed database service, right?

Yeah, that's the one. Yeah. The AWS relational database service, they have Postgres, MySQL, all the fun ones. Anyway, so instead we picked SSDs, like the raw NVMEs, the cool ones that could do like 4, 5, 6, 10 times more workload than the network drives. And that's when Postgres really came alive. And all of a sudden, I could scan 100 gigabyte table in 30 seconds. And I'm like, oh, wait, so this thing actually scales? Because before we just spent cutting these tables, partitioning them, making them as small as possible, and I'm like, wait, but why? I could just read this whole thing from a disk at like 2 gigabytes a second. What's the big deal? You know, technology came a long way. Yeah. So you know those Sundays that Montana talked about, he was getting paged, I was also getting paged. You know, I worked at Instacart infrastructure for about Three years, cumulatively. Well, I started as an app engineer. I came in and I was supposed to build, like, widgets and like, the checkout page that we might have used at some point. But, you know, like a week later they told me, like, hey, we need to fix our postgres setup or our databases are falling over. I'm like, all right, sounds good. I think I've heard of Postgres before. Let's see what I can do. And then I wasn't an app engineer anymore. I kind of learned that on the fi, started, like, memorizing the documentation and everything. And then a year later, you know, I got sat down in a room and Montana was presenting his new elasticsearch replacement. And I'm like, whoa, we're going to sharp this ourselves? Is that what's going on? I think I might be able to do that, but okay. And, yeah, we spent quite some time building the thing.

So as you get into Postgres ML and like, like, this a great story you guys are telling together, I'm really into it. But, like, as you're looking at this, how does that lead down to that, you know, without. Without losing the thread where. Where are you going that's going to arrive at that moment where you're starting to really think about, you know, what you need to be doing next in that capacity?

Yeah. I think Lev should tell you about pgcat and I'll tell you about Postgres ML, because I think there are two different pieces of the puzzle. But to answer your question, you know, like I mentioned, we weren't able to get a lot of deep learning models into postgres. And so because we couldn't get that, we didn't even really start with all of the XGBOOST models or even simpler models that we were running. And I think there's a big disconnect between what is flashy and hype and academia or coming out of OpenAI. Like, there's a lot of reinforcement learning or unsupervised applications or whatever, but in the business context that we saw across at least 20 different models and machine learning applications at Instacart, they all basically boil down to you've got some relational data and you're trying to predict a single column, and you're going to do some joins, and that's it. First thing is you pump it through linear regression, you work your way up through the SciKit, learn algorithms, you hit XGBoost and your predictions are gold standard. You don't even need machine learning. Deep learning, 90% of the time. And so thinking through the convoluted data architectures and data engineering and everything else that we had to do to get features to models, I was like, well, the data is just, it's in postgres right there. You can just write a join and you can just do a select and you don't even need to bring a lot of it to the application layer. If you can actually do your ranking or whatever it is that you're trying to do. You know, ranking is obviously a big application of machine learning. You need to know what your popular things are, your trending things are, your relevant things are, your possible alternatives to this thing are like there's all these different way that things are associated to things in this high dimensional space that data scientists love. So being able to pull things only the most relevant of any of those dimensions out of the database, the application layer, which is actually a really expensive operation compared to things like deep learning. People think deep learning inference is expensive. It's really not relative to like taking thousands of rows out of a database, serializing them, sending them over a copper wire that's multiple feet long, reading them into a JSON blob on the other side in some dynamic language that's allocating a ton of memory to do all of these operations so that you can operate on this in Python or Ruby or whatever it is. That's where actually most of the latency in the system comes from. It's not from the models themselves. The models themselves are like highly optimized C code that sure, it may have millions or even billions of parameters, but it's relatively fast and optimal. And so I was just thinking like, what if we could cut all of that complexity, all of that latency out and keep things in the data layer. And I was talking to Lev about this and I was like, I'm going to go on vacation, but when I get back, I'm going to start on this project. And so, you know, on vacation on day one, I see Lev emails me and he's got a new commit and he's like, I've got deep learning in postgres and Lev is really phenomenal this way and he's very competitive. If you tell him about something, he'll try to beat you at it.

We all have a Lev in our life, you know.

No, it's fantastic to be challenged by somebody that way. I really appreciate it. It's a lot of fun working with Lev, but I think that, you know, that was sort of my itch, which I would consider you know, my thesis defense at Instacart was like, can we do this with Postgres? Can we actually go all the way and can we get to a data architecture that doesn't really involve any ETL or elt, whichever you prefer. It's just the database and the data just sits there until you know that you want it. And that was what really drove the creation of Postgres ML. But I think Lev had a different itch with the system that we built, and so he actually went off and built another solution that he should tell you about.

Shame on you for going on vacation and he beat you to it.

Yeah, I guess it's a good segue into sharding and load balancing and running postgres at scale. It's funny because Postgres itself doesn't have any sharding capabilities. You spin up a single primary and that's all you get. You can have some partitions, you can send some foreign tables or FTWs, like foreign data wrappers, if you ever heard about that. But by the end of that sentence, you're like, I don't know, what are you talking about? Please just do this for me, okay? And I'm like, yeah, sure, I can do this for you. So I took the sharding logic that we kind of invented it, instacart, but it kind of already existed. You always reinvent the same thing over and over. You put it into like the proxy, like a pooler, essentially, and you put that in front of your database and then your clients, they're just connecting to postgres. They don't know about sharding, they don't know about replicas, they don't know about load balancing, they don't know any of failover, they don't know anything about it, and they just get the data whatever they want. I called it pgcat because I'm obsessed with cats. I just said that on the Internet. So about 50% of people are like, this guy is amazing. The other 50% are like, dogs are the best. I hate this guy. Unsubscribe, Unsubscribe. That's a good number right here.

Hey, at least you get the 50%.

Yeah, I mean, it's a fun project that we kind of wrote at the application layer, like all the try catch, Ruby Python logic to talk to five different replicas we just implemented at the end for layer. And now the database is magically sharded, magically load balanced, magically highly available. It's everything that we wanted but couldn't have. Yeah, I don't know. I like that.

So the two sides, you sort of have like the pgcat stuff and then you have like, oh, the idea, can we run some of this sort of like pull thousands of rows out, do some sort of ranking or search or ML operation on them. There's sort of like these ideas floating around. Is Postgres ML? Is it sort of leveraging both of those things and that sort of like is, is some of what makes it what it is or how did those things influence how you think about like Postgres ML and like what it is?

Yeah, I think we're very early with PostgreSQL. I think we only released, released it a couple of months ago. We started working on it maybe 10 weeks ago or something. So it's, it's still what we would consider, you know, public alpha. Basically, like the point was like, does anybody care about doing machine learning in postgres? Is anybody interested in this idea at all? Or are we crazy? Because I have a lot of qualms about putting more load on the primary data store. That is something that I think anytime you can avoid doing that, that's probably a good thing. At least that's my naive take on it. But when I start seeing Lev's work with PGCAD and sharding and pooling and failover and load balancing, then I think, well, actually maybe the simplicity that you can get from having a single data store instead of every database technology in the world and the expertise that you can build and the muscle that you can build around that single technology will lead you to a much better place in the end. So if we. Postgres ML doesn't combine any of the pgcat stuff right now. These are two separate pieces, but we're actually working to put them together in an online service offering that we've started a company together so that we could go full time. I know Chris was mentioning he's got those real life obligations that sometimes get in the way of all the fun stuff that we're doing. And so Lev and I also have real life obligations that get in the way of these fun projects that we love talking about. So quit those and we're going full time on a new venture together. Just put these two pieces together and really get to postgres at scale with machine learning capabilities. That's the goal for what we want to want to build and offer and make it easy for other people.

So to clarify, you've moved on from Instacart, if I'm understanding that correctly.

That's Correct.

That's right. Okay.

I'm sort of curious, I think about, like, when someone comes to Postgres ML, like, I know it's sort of new, it's beta. What is the experience? Could you just sort of describe, like, what is the experience like of doing ML in Postgres? Maybe like running through. Maybe you could give an example of sort of like a training type of thing and then maybe like a deployment or inferencing type of workflow just to give people a sense of like, hey, I might know how to run SQL against Postgres, but what does it mean to, quote, do machine learning and postgres?

Yeah. So, you know, Python is really, I think, the dominant language in the machine learning ecosystem these days. And so what Postgres ML is right now in this sort of alpha public release is a wrapper around all of your favorite Python libraries. We just define a little SQL or PL Python function that calls out to scikit learn, it calls out to XGBoost or whatever your favorite Python library is. And when we define these Postgres functions to take in all of the parameters that you could possibly pass to scikit Learn and just forward them on so that you get all of the scikit functionality for training these models. And so in Postgres ML training is a single function call. So you would select star from PostgreSQL train and then you pass it a few arguments. You pass it the name of the algorithm that you want to use and that, you know, that's either linear regression or that's XGBoost or anything on the menu. I think this is actually a really interesting rabbit trail to go down is why I think that's the right approach. I think most business uses of ML should, can safely treat ML as a black box that they put inputs in and they get inputs out. Now, you need to be very careful about the outputs of that black box and you need to watch it closely and make sure that it's doing the correct thing for your business. But you don't need to understand the math behind how these algorithms actually work. Compute is cheap enough now that you can train your data with 50 different algorithms and just pick the best. You don't need to have this. I've seen a lot of theorizing from a lot of people about why a model is doing what it's doing and how they're going to tweak something. And, you know, it's pretty much a crapshoot of whether that actually makes it any better or not. It's always Better to just test a bunch of different stuff. And so that's another, that's another feature of Postgres ML is that we have hyperparameter search. You just feed it a bunch of, in this train, you feed it a bunch of different configurations that you want, then it will build all of the models for you and then you can just compare which one is the best and that will be the one that's automatically deployed in your database. So people always focus on the math behind the algorithms, because I think those are intellectually interesting. But what they don't focus on, but which is actually a lot of data science work, is the curation of the data. And so that data cleaning, that data curation, the feature engineering work, the data scientists do day to day Postgres is fantastic at that. You have SQL, you can manipulate your data in just about any way you want. Now, it may not have all of the typical functions that data scientists might be used to for treating data in particular ways. Like if you want to impute a value, actually postgres can coalesce nulls to anything you want. It can coalesce it to an average, it can coalesce it to a min, a max, or some random value.

As you're telling me that, could you also walk us through a little bit of what a simple workflow would look like? I wasn't trying to cut you off, was wanting to see if you would add that in just so people know what, I start here and I go bam, bam, bam. And I end up here with that output. And just to give me a sense, because as someone who hasn't used it yet, I'm really curious about this because so many of us in the, in the development world, aside from just the data science world, are using postgres every day. So I'm pretty excited about that. If you could just kind of fit that into what you're telling me there.

Yeah, absolutely. So you start with getting your data into a relation and that's either a table or a view. And this is going to be your training data. So however you want to create that table or create that view, whether you want it to be a view with a bunch of joins out to your application tables, that's fine. If you want to, you know, suck the data up into the application, munge it with a bunch of python, dump it back out into your feature table. In postgres, that's fine too. But it is that, that is the, you know, I think the bread and butter of a lot of data science is that kind of feature engineering, creating that table. I think it's really magical if you can create it with a view, because if you can create the view of your training data, you should be able to reuse the view for your inference calls and you can get the same features out. You have to be very careful in an application database where the application is updating rows. And so you have to make sure that you're not training with a false view of the past, but you actually have the true append only log somewhere that you can train from what the values were at the time. But if you can get that, if you can build your OLTP database in such a way that it handles that, then you can get this very magical. I have this view, I pass that view to my training function along with an algorithm name, whatever hyper parameters I want to pass to that algorithm. What happens is Postgres ML calls out to Python, runs the whole pipeline. You end up with a trained model. It serializes that model back into the database, and so it's just stored in a Postgres ML models table. And then later on you can call the PGML predict function and you pass it the model name and you pass it the parameters that you want to make an inference on. It loads that model from the model store, makes your prediction. And it's very similar to what you would do with the application layer with the, with online inference.

So let me ask you this with, if you are coming at this as someone who has been in Postgres for a while, like so many of us have been, but you aren't necessarily really strong on the ML side, you know, the, the idea of models is kind of new to you. You're not the person necessarily that was not like naturally jumping into TensorFlow or PyTorch or one of the other options out there is what is the delta between what you know in the postgres world and what it takes to be productive with Postgres ML so that you're getting model output and you're like, you're making that leap. What's that delta of, of learning or leveling up that the practitioner needs?

Yeah, absolutely. Again, we're, we're at alpha level of functionality, so there's a pretty big gap of where I want to take it and where it is now. But we've started work on what we call the dashboard. And the dashboard actually has a click button wizard that you can go through and you can select your algorithm from a dropdown list, you can select your source table data from a dropdown list. You can hit the train button, it will do it for you. And you can do this with as many different algorithms as you want. And you just compare the out and all of them are ranked by. I've gone in, I've selected. What is the way that you should compare the outputs of these algorithms. There's a key metric for everything. And so right now there's actually two main tasks that supervised learning is really good at. And that's either classification, which is you have some fixed numbers of examples. You want to know if it's hot dog or not hot dog, whatever it is, or it's a regression where you're predicting some floating point value of whether it's 0 or 1 or some gray area in between. And actually regression is probably a more advanced implementation or raw value of classification. A lot of you can build classification on top of regression by just rounding to 0 or 1. In a lot of cases it's not completely true. Some algorithms are not amenable to that. But generally I think that's a useful way for us to think about it is I'm either trying to predict some number or I'm trying to predict some class of thing. In most business cases. And so you can do that through a ui, we will tell you how good your predictions are. For every different algorithm, you just pick the best one. You don't really need to know what the difference is between a support vector machine and a gradient boosted tree model is. It's maybe fun for some people to learn about those things, but most people shouldn't care. They should be three letter acronyms, but they should have a score next to them. You pick the one with the best score and you move on with whatever your business is.

So one of the things that I was pretty excited to see in this sort of initial release, which I think you mentioned, thinking about how do I run deep learning or advanced things in postgres, what I've found a lot of the cases where I'm applying, especially for NLP type of things, I'm applying a sort of sophisticated model. But really what I'm doing is a bunch of operations on embeddings like word embeddings or sentence embeddings. I'm doing similarity calculations and all of those things. And I see that there's this element within postgres ML of vector operations, which is I think like really important for like so much of my own work. Lev, I'm wondering if you could comment on sort of like why that was important to include in terms of like when you were thinking through initial features and also maybe the future of the set of models that you want to support in postgresml and how you would go about deciding roadmap on that because there's just such an amazing diversity of things out there.

Oh, yeah, for sure. I mean, I worked for about a year close to machine learning engineers and data scientists, and I look at their Python code, I used to look at their Python code every day. And it was never just a call to a train and never just a call to predict. It wasn't just like, just load a CSV file, magically train and then predict something. There was so many transformations, so many different averages calculations. It was never straightforward. It was never like, oh, there's one SQL query and you get all the data magically you want. The data, first of all, was never clean. Right. Whatever business you're running, you're always going to have kind of a dirty, unvetted data. So data scientists like yourself, you're probably going to tell me, I'm telling you something obvious. But you always have to massage it and clean it up and at averages, and then the actual value itself is just garbage. You just end up throwing it away or just adding something to it. Right?

Daniel loves data cleaning.

Just to warn you, okay, Just letting.

You know, if you don't, I don't see what the point is of being a data scientist, because that's all you do.

I mean, yeah, I mean, one day deep neural nets are going to clean their own data, but today we still have to do it ourselves. So you got to have some kind of mathematical operations on your data. You have to be able to transform things. That's the main talking point. Somebody says, oh, but I can't do my important fancy transformation in SQL. My answer is, well, yes, you can, of course you can. We're not going to limit you to just pick whatever view you want. Pick a view, you can transform and clean the data and then pass it to the models and then get the results back. So that's why that's important.

I think there's an important thing there, Daniel, is that me and Lev are not data scientists by trade. We work closely with them. But, you know, I think a big ask for your listeners would be like, kick the tires on PostgreSQL, tell us what sucks and what's missing so that we can cater. Because, you know, I have experience with ML nlp, and so that's why I think, you know, in embeddings. And so that's why I think vector operations are important, but there's a ton of other feature engineering that needs to be done in the world. I'm sure there are huge holes in the functionality right now and so just filing GitHub issues of I would rather have this function available. That would be super helpful feedback for us.

Well, yeah, I mean the call has gone out. Many listeners will hear this, and I'm sure at least a good portion of those listeners have Postgres running in their company's infrastructure or they're working with it in some way. So yeah, I know that I'm definitely going to jump in and we'll make sure to include links in the show notes so listeners go and find those links. Kick the tires with Postgres ML, see how it works out for you. As we kind of wrap up on this discussion, I wonder if maybe you could both just briefly mention something that really excites you about where this is headed. Maybe it's something that's not implemented yet or a reality that you want to see happen. Because something like, like this exists. What is it that like really kind of excites you and keeps you motivated to make sure that something like this exists and grows. Let's start out with, with Montana maybe.

Yeah, for me I think it's about the simplicity that we can bring back to workflows and we can get to the parts that really matter and are really valuable. So that we used to believe a lot in end to end machine learning at Instacart in the early days where a data scientist would need to become Python proficient and production proficient and be able to maintain and monitor their models in production. And it's really unrealistic at scale to expect one person to have all of the skill sets necessary across data engineering, data science, machine learning engineering, infra operations and just good software engineering and expect them to go through the checklist of, you know, there's probably 100 items on a decent ML deployment checklist that you need to make sure that you're covered on. It really requires a team of people right now and so being able to simplify a lot of that work, abstract a lot of that work so that smaller teams that like were at Instacart at the time like we started out with can reasonably get back into production at a very high level of quality at the same time without. Without dropping some of those things.

Well, Montana kind of stole that line for me. I was going to say simplicity because I spent like so much time reading like complicated Python code and then literally like PhD level, like mathematicians were talking to me like, hey, what's HTTP like what's like how am I supposed to launch what's the service like, how am I supposed to launch my model into production? And I'm like, I'm sorry. Like I don't know. I don't know. I'll just do it for you. Don't worry about it. Just give me your code, I'll rewrite it and I'll launch it. You know, that was the my main sticking point. Like I wish I could. You could just like you just run a query and deploy everything immediately. That's like the simplicity of it is and the ergonomics. I think that's something that's really exciting me. I'm really motivated by making people's lives easier. I want machine learning engineers to do machine learning that they actually enjoy as opposed to figuring out how to load balance a service that doesn't make any sense. Right. So I think the impact that's going to have on a lot of people, hopefully that really excites me, honestly. Awesome.

Well, thank you both for such a great description and a story kind of behind postgres ML. I know I'm really excited to see this materialize and excited to get hands on with it. Like I say, we'll include show notes and or links in our show notes so that our listeners can find their way and make sure and engage with the team. Open some issues, open some discussions with with the PostgreSQL team. Thank you both. Montana love.

Thank you.

Yeah thank you guys. Appreciate it.

Foreign.

That is Practical AI for this week. If this is your first time listening, subscribe now at PracticalAI FM or just search for Practical AI in your favorite podcast app. We're in there. And if you're a long time listener, please do share the show with your friends. It is the best way you can help Practical AI Magical AI succeed. Thanks again to Fastly for shipping our shows super fast all around the world to break master cylinder for the beats and to you for listening. We appreciate you. That's all for this week. We'll talk to you again next time.