Postgres vs. Elasticsearch: The Unexpected Winner in High-Stakes Search for Instacart

In this episode of The Data Engineering Show, host Benjamin speaks with Ankit, former senior engineer at Instacart, about the company's innovative approach to modernizing their search infrastructure by transitioning from Elasticsearch to PostgreSQL for single-retailer search functionality.

‍

Listen on Spotify or Apple Podcasts

‍

[00:00:00] Ankit:  Now what different InstaCart was that our items was so fast moving. It's a grocery store. Things go in and out of stock very frequently. Almost everything that we got retrieved had to be filtered out.

[00:00:12] Benjamin:  Hi. This is Benjamin. Before we start with today's episode, I wanted to quickly reach out on a personal note. We've just launched FireVault core. FireVault core is the free self-hosted version of our query engine. You can run core anywhere you want, from your laptop to your on prem data center to public cloud environments. Core scales out, and you can run it in a multi node configuration. And best of all, it's free forever and has no usage limits. So you can run as many queries as you run and process as much data as you want. Core is great for running either big data ELT jobs on, for example, iceberg tables or powering high concurrency customer facing analytics on big datasets. We'd love for you to give it a spin and send us feedback. You can either join our Discord, enter our GitHub discussions, I o. We'd love to hear from you. We added a link to Fireball course GitHub repository to the show notes. And with that, let's jump straight into today's episode. Alright. Hi, everyone, and welcome back to the data engineering show. Today, we're super happy to have Ankit on. Ankit is, well, now a software engineer for RateDB, I think. But before that, I was a senior engineer at Instacart, did a lot of work on Postgres, their data stack. Excited to have you on the show. Do you quickly wanna introduce yourself? Yeah. Kind of share your background.

[00:01:27] Ankit:  Yeah. Thanks. Honored to be here. So, yeah, as of two weeks ago, I was an engineer at Instacart. Now I've just recently moved. I mainly worked on the Postgres, so storage infra search infra team, and there's a series of blog posts on how the search was modernized that will more talk in detail today. Search was modernized with hybrid retrieval, and there's also talk of how we moved from Elasticsearch to Postgres, and we'll talk about the reasons later. Before that, I had worked in a bunch of different start ups in Canada and India as well, and I've always been interested in databases. And special love for Postgres because in my experience, choice of database boosted dev productivity in general. Think about it. If there's a lot of things that you can get the database to do, then the applications become simpler. And my non Instacart experience has largely been in, like, think it's a pre PMF startups where the approach of abuse your database to its absolute limits, it works wonders. Now you can argue that it can be done at scale too, but that's for later.

[00:02:27] Benjamin:  Nice. So take us through search at Instacart. Right? And I think one thing that also is interesting to our listeners is, like and was similar for me as well for a long time. You have in your mind, like, okay. Here's my traditional relational SQL database, then there's search things. And I think traditionally, there would actually be little overlap between these things. And I think what you're seeing nowadays, and I think Gen AI is actually also driving that even harder, is like you see this kind of merging of these concepts into individual system that can do everything. So, yeah, kind of maybe take us through how do these problems actually pop up in the Instacart product.

[00:03:05] Ankit:  Yeah. That makes sense. Let's go one step back. Let's talk about retrieval in general. What is the lay of the land of the retrieval? What was the lay of the land? So there is, like, if you go to Instagart app as a consumer now, again, there's different apps for shoppers, there's consumer, and then there's ads where brands do their thing as well. So I'm sure there are retrievals over there as well. But as a consumer, the first thing that you get in touch is auto suggest retrieval. So you type b a n and you expect banana to be there and bunch of banana smoothie and whatever is there in grocery store. So that's one retrieval. Then there is we call it home search, which means you just say, I want eggs, and the app would show you a list of retailers and bunch of items in each retailer. That's one retrieval. And then there's another retrieval, which is you know which grocery store you care about because you're very passionate about I want my eggs from this specific. And that's me. Like, my eggs come from one grocery store.

[00:04:00] Unknown:  I know the chicken. I know the mother of the chicken who raised her, and she has great eggs.

[00:04:05] Ankit:  Right? So that's the single retailer search. And that's the biggest, you can say, search retrieval by volume and by server strategic importance as well. Like, Instacart is a retailer first marketplace compared to the peers. And this blog only talks about this line. We don't talk about anything anyone, auto suggest. And you can imagine bunch of other retrievals exist that I don't even know. I'm sure some teams are still using Elasticsearch for their underlying retrieval. Like, as manager, I don't I have never even seen that. So it would have some boxes for search. Someone team was also using ClickHouse via a foreign data wrapper in Postgres because it works for them, so why not? And one flag that I get after the blog was released by Elasticsearch fans is that they felt that the blog claims that we kicked Elasticsearch out of Instacart. No. That's not true. We only removed it for the third kind, which is singular tailored search, and there were good reasons for it. So let's deep dive into singular tailored search. So previously, we had an Elasticsearch that was doing the search retrieval, and the other things was application. So what are the other things? Filtering, the retrieval for some use cases, ranking, and hydrating whatever was retrieved with more attributes, and then ranking after hydrating as well. It's a bunch of different steps. And it's a standard model, if you imagine. And that that's what everyone does if you have multiple kinds of filtering and and ranking. Now what different Instagart was that our items was so fast moving. It's a grocery store. Things go in and out of stock very frequently. And we also have people who care differently about different things. Like, I might care very specific about where is this brand I care about. Someone would care. I just care about getting eggs at the point. I just care about ice cream. I just want an ice cream right now. So you can imagine the solution we were moving towards was an ensemble of models and the weights of those models being controlled by the context. The context can be the search query, understanding of search query. Now these days, that some of the context is enriched by LMs as well. Because of the fast moving nature of the grocery store like these going in and out of availability, in pathological cases, almost everything that we got retrieved had to be filtered out. So we go back to Elasticsearch again. Hey. Give us 200 more results or whatever x number of more results. You do that five times. Now this could happen because we get updates from retailer that, hey. This item is out of stock right now, or our machine learning models tell us that the item is is gone. So for an application engineer, think about it. This situation is exactly what you would see if you, by mistake, do an n plus one query. Right? You you have to fire multiple queries. So what's the solution? Well, the solution is to avoid going back and forth across the network where the network delay eats up all your latency. So we created a cluster that had everything in it. It had search index. It had ranking and boosting tables. It had availability, machine learning availability, and all other filter tables that the query may care about. And also, we could add more ranking and boosting as the team wanted. And that's what the Postgres was too. In a sense, we traded off the quality of retrieval, hardcore core retrieval, with the whole system reducing the network calls. So I guess another way is to say that we push down the compute to the data layer, closer to the data layer, which is a, I guess, an approach opposite to what you guys are more familiar about.

[00:07:32] Benjamin:  We love pushing computer to the data layer.

[00:07:34] Unknown:  By the way, the beauty about our space, it's confused. There's a lot of confusion. You know? Like, you said something now pushing down the computer, the storage. We can even go further and say, to get the speeds we need, we need to offload a lot of the compute from reading to writing. Right. And it goes into pruning, even into the codex you apply on your data. Like, what we say you push compute down to the storage is actually, in many cases, 80% of what gets you from a to b and gets you to production. And, yes, this is all hidden done by engineers. Go on. Right. So you you came to the realization where, okay, we need to do that. So what did you do?

[00:08:16] Ankit:  Yeah. So we traded off the Elasticsearch hype, like, the m 25 with the TS vector, and some modifications had to be made to how the TS vector is queried. But, essentially, this gave us avoiding of the pathological cases. And there were side benefits also. Like, you know, data stored in normalized tables was cheaper than, you know, full blown denormalized. And, also, there is operational benefits and simplification of stack. Like, the in front engineers, which is, like, sort of my team, we were not owning, operating, and managing two different systems. We're just the application would call single query that does a lot, and we'd get done with it. Also, one thing which we missed out writing in the blog is that the cluster is not just a search cluster. So search is one of the workloads it's offering right now. It is a place to go to find what item is available, in what store, what item is available, at what price, including full product taxonomy graph and product and ontology. So you could query products, filter, rank them in any sort of flexible way you want in a single query. Now does that sound like a silver bullet? Maybe.

[00:09:23] Benjamin:  Yeah. So maybe one or two questions just for kind of, like, me to also understand better the flow. So there's this pre Postgres cluster kind of running. It can do a million different things. It can also do search and ranking. Now I go into my Instacart app, and I start looking for okay. Let's stick with your x example. It's like, how does the request flow actually look here? Like, do you really send a query for every character I type or you predict what character I get how like, a single interaction with the app of, like, cert looking up a certain term, how many queries does this actually kind of cause on the back end?

[00:09:59] Ankit:  Right. Right. That that's a very good question. So the single character goes to the search auto suggest service, which is a completely independent thing. It it's still using Elasticsearch. It is always used, and we didn't change it. It was working beautifully. It does what it's supposed to do.

[00:10:15] Benjamin:  You got a lot of hate from Elastic fans. Like, whenever you're you're sprinkling a lot of love for Elastic. Like

[00:10:22] Ankit:  I mean, it it's good. It's it's very stable, and it does what it's supposed to do. So my intention was never to say that, hey. This is bad. This is good. He was like, hey. This data model and this whole system doesn't work for, say, that use case.

[00:10:35] Unknown:  So, basically, you're saying if it worked for spam checking, it shouldn't necessarily work for anything else. And it's good that it still does spam checking and searching. That aspect of searching really well, So all good, and, , no disrespect to anyone. Yeah.

[00:10:50] Ankit:  Yes. And we are doing more than that. We're doing some some, like, if it's a brand search. Because I do remember there was a time when we were running a Red Bull campaign, and, you know, someone would type red, the red bell pepper would be at the top. Because guess what? The CDR boosting, the CDR, the click through rates of red bell pepper, there are so many people who buy day to day that they were making at the top. So all that auto suggest, it still would give you the knobs.

[00:11:15] Unknown:  Benjamin, top k problem.

[00:11:17] Benjamin:  Yeah. Auto suggestion doesn't go into your service. So at what point, right, like, again, like, maybe take me just through the user flow, would my interaction start sending queries against your system?

[00:11:29] Ankit:  So as soon as you press enter, that you choose your search term, at that time, the application would construct a SQL query for Postgres, and it directly hits Postgres. There's no memcache. There's no other caching systems. It directly hits the cluster. The application would do certain things like query we call it query understanding to build more parameters for the search query to the Postgres engine. And then when we get the response from the Postgres query, it is ranked again by the application. So we have, like, different passes of ranking. The first pass ranking is done by the Postgres itself. The second one and the division is just because whatever we want to move fast, we whatever application engineers feel that, hey. This is where we want to iterate. We it's better to keep that in application and not push down to the database. And that's it. Now it sounds simple from the architecture side, but each component of it has its own, like, complexity. I don't think I'm even aware of what that complexity happens. So I can talk more from the architecture, from the data flow side.

[00:12:30] Unknown:  Hey. It's all because you're using Postgres. This is why it's complicated. You know? Yeah. Go on.

[00:12:35] Ankit:  Right. But the flow is very simple. The application prepares query, sends it to Postgres, gets the result, and then there is some hydration that final clients also do by clients. I mean, the actual mobile apps or your or the web application that also is hitting the same post release.

[00:12:54] Benjamin:  Gotcha. And so, I mean, this wasn't vanilla Postgres in the sense that you had, like, use Postgres extensions there as well. Right? Like, you use something like PG vector, kind of take us through your modding Postgres kind of journey. Like, what extensions did you use? Did you custom build any extensions?

[00:13:12] Ankit:  That's a good question. So the shape of the Postgres changed throughout the years. So the the large team here is having control over our own Postgres. So that's the only reason why this self hosted. It's a self hosted Postgres. It uses a very new proxy called PG Cat. PG Cat has moved on, and now there's a PG dog as well, the same author. It's pretty good. It's stable. It works.

[00:13:35] Benjamin:  Would you recommend PG cat or PG dog Dizdiz?

[00:13:38] Ankit:  So I have not used PG dog at scale, but I've played it on with it, and I like it. Conditions apply. Like, try it at scale. There could be bugs, but the devs are like, I know, like, Lev is is a friend, and he's very responsive. So what it allows is, , work with a sharded database in a way as if it was not sharded. So it adds the sharding layer to the PD bouncer. So coming back to the question, the grand theme of here is that we wanted more control over the cluster, how to spin it off, what kind of disks it would have. So that cluster is purely NVMe based. We don't use any network attached. , we are okay if the primary goes down for an hour. We would rebuild it back from the backup instead of failing over. Like, I didn't want to be on call for that if the Postgres had to be failover. So all the intelligence here that handles the failover in a way is in application. Like, even if a replica goes down, you need to blacklist that that replica. All that is happening on the application. So there's a you can imagine that the client for the application is a thick client. It does a lot more than just the query. It does all the failover, high availability. So that allows us to be simple on our infra side.

[00:14:50] Benjamin:  How does data flow into this Postgres database in the first place? Was it attached to Kafka? Is it just plain updates and deletes kind of being run all of the time? Take us through the entire journey, basically.

[00:15:04] Ankit:  It's a first class citizen of how this cluster operates. So all the rights are pipelined. We don't use Kafka. The interface is s three. So we tell teams who want to have their data in this cluster, create an s three home, create either a bucket or a home, whatever they want to do, and tell us that we would sync ourselves. So we'd pull s three for changes and get the data in bulk. And all the rights to the Postgres would happen like a bulk write, so, like, either 16,000 rows or 32,000 rows, whatever that is.

[00:15:35] Benjamin:  So it's append only, or there were updates and deletes as well?

[00:15:38] Ankit:  It is merge. The Postgres has merge command. So we would tell teams to tell us what are the primary keys or what are the keys that would help us identify if it is a insert or an update. And there was no real time rights or update allowed. It's that we also supported deletes through a very special mechanism. It was a, like, a you just configure that these rows are supposed to be deleted, and we would do it as at a cleanup at a later stage. A big part of the system was also PGD pack. So PGD pack is something that is like a super vacuum or something. It does it very fast, although it uses a lot of IOPS, and it uses a lot of system resources to do that. It essentially creates a copy of your table, real time syncs it, and at the opportunity it finds, it swaps hot swaps it, deletes the old. So the new table that you get is is defragged. It's slightly packed. It's clustered again. Suppose this has this cluster table. Cluster is in a overloaded time. I hate that. It's causes so much confusion. But it what we get is a nicely clustered table that has its rows tied in a way that helps query at the read time, essentially. We were running, like, tons of free pack on like, almost always, there is some repack running on. And it was hard like, it was tuned heavily because what we found is that the read throughput, we can throw more data if the tables are repacked nicely. These are fragmented and they're not clustered, the rights would slow down. The more of a right throughput optimization, and it's probably an order of magnitude more by the repack tuning. So that's how the rights are going in. And from the customization, we had in the past a custom extension for a dot product. One of the rankings we are doing in the Postgres was personalization ranking as well. So you would get your products, and at the end, the final ranking would be personalized. So this was happening in the Postgres to a very like, it's a 50 line extension in c, just to a dot product and give it back to Postgres. Now that was all deprecated because PG vector came somewhere in 2023, and we moved our retrieval to PG vector, but also PG vector could do your own default dot product.

[00:17:44] Benjamin:  Nice. Okay. Very cool. Yeah. It's always interesting to hear, like, these production stories from Postgres. Really, like, the amount of moving pieces and extensions being used is really kind of cool.

[00:17:56] Unknown:  The interesting thing is you hear about more and more workloads use cases where when engineers say simplicity, they mean, like, we want the Postgres interface. We want to be able to normalize. We want to be able to have more complex queries running. There are many moving parts. It's not just a on the metal time series, real time, single denormalized, five second query span. Like, this is something completely different up to the point where some of the data is being batch processed on the side and hot swap because you need it to be super hot. The data as it's being served to those users, it needs to be super hot. SSDs were mentioned, like, you can't break your way around it, but then you need to deal with Postgres back end, which is at a whole different place. So you need to have modes, extensions, which gives you exactly the flexibility you need as an engineer to take the concept that the kind of the strength of Postgres and evolve it over time. As you said, like, types change, search patterns change. Absolutely. I love it. Thank you for sharing with us kind of the whole thing end to end and, specifically, kind of the whole effort effort you guys are putting on the back end.

[00:19:06] Ankit:  It was almost a silver bullet, but not quite. There were certain trade offs as well. Like, eventually, what we found out was that the DevX, the developer experience in how engineers use it, especially search engineers, it was less than ideal because you realize that most engineers who want to work on search, they are more used to the Elasticsearch shape of the query. And even those who knew Postgres among those, they found the final query to be hard to work with. And scaling, provisioning, sharding, operating this self hosted Postgres in a company that relies on managed cloud for everything else. Like, everything else is on RDS. It's amazing. Right? That was, again, a hard thing. So you have this small team that does its own thing that no one understands. And we also noticed that replicating production behavior in a nonproduction, it was impossible. So if someone is making a change to really know what's the impact, it had to be tested in production. And that makes everyone hard. Like, you know, it slower is the velocity, puts more questions in the queue, like, have you made sure everything is right for this? And this sort of thing steered me to what I'm doing now, like, to sort of as of last two weeks, I joined Pro eight DBN. The promise here is that it's a transactional Elasticsearch alternative. So we ship a Postgres extension that is built on Tan TV, which is a rust fork of Lucene, and it uses Postgres storage. And that's my personal sort of observation is that if I have to rebuild the Instacart like search, it will be like a prey DB. Lot of things would be especially the Demex would be, like, much simple. And taking a stating that conversation in different ways. Yesterday, I saw a blog post by Target. They moved their search workload to AlloyDB in a sort of a Postgres compatible way, and it's not clear what were their motivations. They could be similar motivations, but their outcomes are very similar. Like, the relevance is better because they could join more things in in the database. They also saw the cost of the normalized data reduced. So that's pretty much matching with what we saw at Instacart.

[00:21:06] Benjamin:  Nice. Very, very cool. Well, Ankit, it was amazing have you on the show. We appreciate the total Postgres Instacart deep dive. Yeah. We'll follow your work at Parete d b. We're excited about all of that. Thank you for being on the show.

[00:21:20] Ankit:  It is an honor. Thank you.

[00:21:21] Unknown:  Thank you.

[00:21:23] Unknown:  The data engineering show is brought to you by Firebolt, the cloud data warehouse for AI apps and low latency analytics. Get your free credits and start your trial at firebolt.io.

‍