Machine learning in your database

While scaling up machine learning at Instacart, Montana Low and Lev Kokotov discovered just how much you can do with the Postgres database. They are building on that work with PostgresML, an extension to the database that lets you train and deploy models to make online predictions using only SQL. This is super practical discussion that you don’t want to miss! Featuring: Montana Low – GitHub https://github.com/montanalow, LinkedIn https://www.linkedin.com/in/montanalow, X https://x.com/montanalowLev Kokotov – GitHub https://github.com/levkk, LinkedIn https://www.linkedin.com/in/levkkChris Benson – Website https://chrisbenson.com, GitHub https://github.com/chrisbenson, LinkedIn https://www.linkedin.com/in/chrisbenson, X https://x.com/chrisbensonDaniel Whitenack – Website https://www.datadan.io/, GitHub https://github.com/dwhitena, X https://x.com/dwhitenaShow Notes: PostgresML website https://postgresml.org/PostgresML on GitHub https://github.com/postgresml/postgresmlUpcoming Events: Register for upcoming webinars here https://practicalai.fm/webinars!

Speaker names marked ? are inferred from speaking time and the show’s known hosts — they are not voice-verified.

• 0:00Speaker 00

  I think there's a big disconnect between what is flashy and hype in 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, you know, gold standard. You don't even need deep learning 90% of the time.

• 0:50Speaker 01

  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 PracticalAIFM. Thank you to our partners at Fastly for shipping our pods super fast all around the world. Check them out at fastly.com.

• 1:24Daniel Whitenack?

  Well, welcome to another episode of Practical AI. This is Daniel Whitenack. I'm a data scientist with S I L 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?

• 1:39Chris Benson?

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

• 1:41Daniel Whitenack?

  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.

• 1:51Chris Benson?

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

• 1:56Daniel Whitenack?

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

• 2:02Chris Benson?

  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.

• 2:09Daniel Whitenack?

  Yeah, this is definitely something that I think fits very well within our theme of practical AI and something that 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.

• 2:35Speaker 00

  Hi, thanks for having us, Daniel. Thanks for having us.

• 2:38Daniel Whitenack?

  Yeah, definitely. So before we get into all the details about Postgres ML, 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, you know, 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?

• 3:07Speaker 00

  Yeah, I mean, it's kind of a long-winded story. It's definitely not the first time that, you know, 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. I mean, 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 a more of a distributed architecture that would be horizontally scalable.

• 3:51Speaker 00

  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, you know, 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 and distributing that. So it was it was it was cool to get some new technology.

• 4:31Speaker 00

  Went 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 and to 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 our on our laptops to actually impact the product somehow.

• 5:16Speaker 00

  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 Konda? What does a pip install actually look like? How do you get that into production? The whole nightmare of dependency management and lifecycle management of models when they're not just built once, but they have to be rebuilt continuously with with new data as it comes in. And then you have to get the feature data to the to the actual model, but it it can't be the data that's coming out of your snowflake warehouse. At the time, we were on red shift, you know, because that's too slow and latent.

• 5:58Speaker 00

  We were learning everything and building it on the fly, and it was chaotic, but fun. Actually, we we published a lot of that work in a library called LoR, which was Instacart's open source platform. Now, as as we the ecosystem evolved around us really quickly over the last, you know, five, six years. Things have changed at breakback 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, you know, 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.

• 6:42Speaker 00

  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, you know, our documents grew to several hundred fields. And many of these fields themselves were nested JSON documents. It could be tens of kilobytes of additional payload data. And so our Elasticsearch document size blossomed. And Instacart, I think, is somewhat unique in one of its constraints.

• 7:27Speaker 00

  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's 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.

• 8:11Speaker 00

  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's special that day? Is it in stock on the shelves, or did it just, did they just sell the last box? And so, you know, if you think about this from a data architecture perspective, it's a pretty classic, you have two tables.

• 8:55Speaker 00

  One is your products, one is your offerings. You join those two tables together. You denormalize that data into Elasticsearch. Easy, except, you know, 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 like 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 like our Elasticsearch capacity increase per node

• 9:40Speaker 00

  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 and 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 in very short amounts of time. So one of the things that I've heard that 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

• 10:24Speaker 00

  over to it, all traffic gets that. And then for the next 24 hours, they'll refresh their blue cluster and then they'll flip over to it. And so 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'd get in a Lucene 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.

• 11:06Speaker 00

  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? You know, 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.

• 11:49Speaker 00

  But even more recently with things like Timescale DB and Cytus Data, like 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 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 them. It's too much load on the cluster, it's already on fire.

• 12:33Speaker 00

  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 Elastic Search 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 eight 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, there was this distributed machine learning beautiful beast.

• 13:18Speaker 00

  It was not, it was not a pretty picture. It's a very complicated picture. And so we, 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. You know, we were shadow testing its search results against Elasticsearch, what we were getting back and forth. Uh, we were finding lots of data ingestion bugs, bugs that had been in our, 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.

• 14:01Speaker 00

  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, 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, you know, 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.

• 14:46Speaker 00

  When traffic would go away, we, we did all kinds of things. You know, we thought about putting up a, 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 um 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, you know, using. It's kind of dead right now, um, 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.

• 15:32Speaker 00

  Uh, but we were able to find a few missing indexes for some long-tail queries that we hadn't really optimized. Uh, and within a couple of hours, we got that cluster to a point where we could, you know, actually serve traffic again. And so that was 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 the what was the primary system and what was the secondary system. Elastic search 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

• 16:16Speaker 00

  the Postgres cluster. And it was it was vastly underscaled compared to our Elastic Search cluster. I mean, it was just tiny compared to it. 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, but you know, 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 what your database is, has the most CPU usage, pull all the data out

• 16:58Speaker 00

  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, you know, 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.

• 17:42Speaker 00

  Because at the time, there was, there is a library called MATLAB, which is an Apache Foundation project. It's, I think, ten 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 the, we weren't able to actually take a lot of our deep learning models and put them into MATLAB 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, kept building around that. But it kind of bothered me because we, we were able to clean up so much of the distributed system. I felt good about, like, the system that we ended up with

• 18:26Speaker 00

  was much better than the system that we started with. And it was kind of, it was kind of full circle for me, coming from like, you know, 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, you know, I think when we were on the Elasticsearch pipeline, we had a dedicated infrastructure team. We had dedicated catalog team just to the pipeline. We have a dedicated search team, dedicated machine learning engineering,

• 19:10Speaker 00

  all of those resources, you know. And we had, you know, 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, a few product managers sit around a 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, 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.

• 19:55Speaker 00

  And so it was, it was really, really problematic.

• 20:22Daniel Whitenack?

  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 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

• 21:07Daniel Whitenack?

  learning, all of these things?

• 21:08Speaker 04

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

• 21:16Daniel Whitenack?

  The the Elasticsearch system or the Postgres?

• 21:19Speaker 04

  The Postgres system. I came in as a, 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, how do I install Postgres on Ubuntu? Should I pick Ubuntu? What kernel version do I need? And it wasn't because, you know, we, 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're on your databases on RDS, you know that the disks are, they're network disks, right? So latency is at least like a, you know, 10 milliseconds depending on they, you know, I O two is probably a little bit better, but still not quite there.

• 22:00Daniel Whitenack?

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

• 22:04Speaker 04

  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, you know, NVMe is, you know, the cool ones that could do like four, five, six, 10 times more workload than the network drives. And that's when Postgres really came alive. And all of a sudden I could scan like a 100 gigabyte, 100 gigabyte table in like 30 seconds. And I'm like, oh wait, so this thing actually scales?

• 22:32Speaker 04

  Because before we just spent like cutting these tables, partitioning them, making them as small as possible. And I'm like, wait, but, but why? I could just read this whole thing from a disk at like two gigabytes a second. Like, what's the big deal?

• 22:44Speaker 04

  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. 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. Our databases are falling over. I'm like, all right, sounds good. I think I've heard of Postgres before. Let's see, let's see what I can do. And then I wasn't an app engineer anymore.

• 23:20Speaker 04

  I kind of learned that on the fly, 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, his new, uh, his new Elastic Search replacement. And I'm like, whoa, we're gonna sharp this ourselves? Is that, is that what's going on? I, I think I'm, might be able to do that, but okay. And, uh, yeah, we spent quite some time building that thing.

• 23:46Chris Benson?

  So as you get into Postgres ML and, and like, like this is a great story you guys are telling together. I'm, 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, in that capacity?

• 24:11Speaker 00

  Yeah, I, I think Lev should tell you about PG Cat 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 the 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. I think there's a big disconnect between what is flashy and hype in academia. Or, you know, 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, you know, across at least twenty different models

• 24:53Speaker 00

  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 gonna 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, you know, gold standard. You don't even need machine or deep learning ninety percent of the time. And so, thinking through like 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's just, it's in Postgres right there. You can just try to join and you could just do a select. And you don't even need to bring a lot of it to the application layer if you can actually

• 25:38Speaker 00

  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, uh, 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. And 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, you know, reading them into a JSON

• 26:23Speaker 00

  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, 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 gonna go on vacation, but when I get back, I'm gonna start on this project. And so, you know, on vacation on day one, I see Lev emails me and he's got

• 27:03Speaker 00

  a new commit. 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.

• 27:17Daniel Whitenack?

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

• 27:19Speaker 00

  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.

• 28:02Daniel Whitenack?

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

• 28:07Speaker 04

  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. Like, you can just, you spin up a single primary and that's all you get. You can have some partitions, you know, 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 rewrote, I took the sharding logic that we kind of invented at Instacart, but you know, it kind of already existed. You know, 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

• 28:52Speaker 04

  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 failover. They don't know anything about it. And they just get the data, whatever they want. I called it PG Cat because I'm obsessed with cats. I just said that on the internet. So about 50% of people are like, this guy's amazing. The other 50% are like, dogs are the best. I hate this guy. Unsubscribe, unsubscribe.

• 29:15Speaker 04

  That's a good number here.

• 29:18Daniel Whitenack?

  Hey, at least you get the 50%.

• 29:20Speaker 04

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

• 29:43Daniel Whitenack?

  So the two sides, you sort of have like the PG cat 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 like 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?

• 30:17Speaker 00

  Yeah, I think we're very early with Postgres ML. Like, 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 PG cat and sharding and pooling and failover and load balancing,

• 31:00Speaker 00

  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, 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

• 31:42Speaker 00

  real life obligations to get in the way of these, these fun projects that we love talking about. So quit those and we're going full time on a new venture together to put these two pieces together and really get to Postgres at scale with machine learning capabilities. That's, that's the goal for what we want to, want to build and offer and make it easy for other people.

• 32:04Chris Benson?

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

• 32:09Speaker 00

  That's correct. That's right.

• 32:10Chris Benson?

  Okay.

• 32:34Daniel Whitenack?

  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 in Postgres?

• 33:17Speaker 00

  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.

• 34:01Speaker 00

  So you would, you know, select star from postgresml dot train. And then you pass it a few arguments. You pass it, you know, 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.

• 34:44Speaker 00

  Compute is cheap enough now that you can train your data with 50 different algorithms and just pick the best. Like 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, you know, hyperparameter search. You just feed it a bunch of, in this train, you've feed it a bunch of different configurations that you want. And 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.

• 35:29Speaker 00

  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 intu to value, actually, Postgres can coalesce nulls to anything you want. It can coalesce it to an average, it can coalesce it to a min and a max, or some, you know, random value.

• 36:06Chris Benson?

  As you're telling me that, could you also walk us through a little bit of like what a simple workflow would look like? As I wasn't trying to cut you off, was wanting to see if you would add that in, just so people will kind of know what, like, 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.

• 36:38Speaker 00

  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, munch it with a bunch of Python, dump it back out into your feature table in Postgres, that's fine, too. But it is, 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

• 37:22Speaker 00

  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, you know, 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, and 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 hyperparameters I want to pass to that algorithm.

• 38:03Speaker 00

  What happens is, you know, 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 pgmlpredict 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 at the application layer with the, with online inference.

• 38:37Chris Benson?

  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, and 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?

• 39:17Speaker 00

  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, you know, 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 the, them are ranked by, you know, I've, I've gone in, I've selected, what, what is the way that you should compare the outputs of, of these algorithms? There's a key metric. For everything. And so right now, there's actually two main tasks that supervised

• 40:01Speaker 00

  learning is really good at, and that's either classification, which is, you know, 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 zero or one, or some gray area in between. And actually, regression is probably a more advanced implementation or rawer value of classification. A lot of you can build a classification on top of regression by just rounding to zero or one 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

• 40:41Speaker 00

  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. They 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 like, it's maybe it's fun for some people to learn about those things, but most people shouldn't care. They should be, you know, 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.

• 41:15Daniel Whitenack?

  So one of the things that I I was pretty excited to see in this sort of initial release, which I think you mentioned, thinking about like, how do I run deep learning or advanced things in Postgres? What I've found in a lot of the cases where I'm like applying, especially for like 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.

• 42:00Daniel Whitenack?

  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 like the future of kind of the set of models that you want to support in Postgres ML? And like how you would go about deciding like roadmap on that? Cause there's just such a, you know, amazing diversity of things out there.

• 42:27Speaker 04

  Oh yeah, for sure. I mean, I worked for about a year with like close to a 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 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 like averages, calculations, like 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 for us was never clean, right? Whatever business you're running, you're always gonna have like kind of like, you know, kind of a dirty, like unvetted, you know, data. So data scientists like yourself, you're probably gonna tell me like, I'm telling you something obvious. But like, you always have to like massage it and clean it up and like add averages, you know?

• 43:08Speaker 04

  And then the actual value itself is just garbage. You just end up throwing it away or just adding something to it, right?

• 43:13Chris Benson?

  Daniel loves data cleaning, just to warn you, okay? Just letting you know.

• 43:17Daniel Whitenack?

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

• 43:22Speaker 04

  I mean, yeah, I mean, one day deep neural nets are gonna clean their own data. But you know, today, we're still have to do it ourselves. So you gotta have some kind of like, you know, mathematical operations on your data. Like you have to be able to transform things, you know? That's the main like talking point. Somebody says like, oh, but I can't do like, I can't do my like important fancy transformations in SQL. My answer is, well, yes, you can. Of course you can. Like, we're not gonna limit you to just, you know, just pick whatever view you want. Like, 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.

• 43:57Speaker 00

  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 I think a big ask for your listeners would be like, kick the tires on Postgres ML. Tell us what sucks and what's missing so that we can cater to. Because I have experience with NLP and so that's why I think in embeddings. And so that's why I think backroom 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.

• 44:37Daniel Whitenack?

  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 like really excites you about where this is headed.

• 45:19Daniel Whitenack?

  Maybe it's something that's not implemented yet or like a reality that you want to see happen because something 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 Montana, maybe?

• 45:38Speaker 00

  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 skillsets 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 a hundred items on a decent ML deployment checklist

• 46:21Speaker 00

  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 dropping some of those things.

• 46:44Speaker 04

  Well, Montana kind of stole that line from me. I was gonna 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 HTP? 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'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, deploy everything immediately. That's like, the simplicity of it is, and the ergonomics, I think that's something that's really exciting me. Like, I'm really motivated by making people's lives easier. Like, I want like machine learning engineers to do machine learning that they actually enjoy, as opposed to figuring out

• 47:29Speaker 04

  how to like, how to load balance a service. This doesn't make any sense, right? So I, I think like the impact that's gonna have on a lot of people, hopefully that, that really, that really excites me, honestly.

• 47:40Daniel Whitenack?

  Awesome. Well, thank you both for such a, 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 Postgres ML team. Thank you both, Montana and Lev.

• 48:08Speaker 04

  Thank you.

• 48:09Speaker 00

  Yeah, thank you, guys. Appreciate it.

• 48:20Speaker 01

  All right, 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 longtime listener, please do share the show with your friends. It is the best way you can help Practical 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.