Instacart Elasticsearch to Postgres Search Migration

Instacart Elasticsearch to Postgres: The Migration Nobody Expected

Instacart replaced Elasticsearch with sharded Postgres for product search. The trigger was not cost or fashion. Their grocery catalog changes billions of times a day, and Elasticsearch's denormalized documents forced a full rewrite for every tiny price or stock change. Fixing bad data started taking days.

That last sentence is the whole story in miniature. A search engine where a data correction takes days to show up is not a search engine anymore, it is a liability. And it got worse in the way these things always get worse: the heavier the indexing load got, the slower reads became, so the system degraded exactly when it was busiest.

I want to walk through this properly, because the interesting part is not "Postgres can do full text search." Everyone knows that. The interesting part is the shape of the problem, why a normalized relational model turned out to be the fix for a write problem, how they split the data across shards, and how lexical and vector search ended up living inside a single SQL query.

Why Elasticsearch Broke on Grocery Data

Groceries are a genuinely hostile workload for a document search engine, and it took me a while to see why.

Think about what a product document has to contain for search to work. The item name, brand, size, category, aisle, images, tags, the price at this particular store, whether it is in stock right now, active promotions, and a pile of machine learning features used for ranking. In Elasticsearch that all lives in one denormalized document, because a document store has no joins. Denormalization is the price of admission.

Now look at how often each of those fields changes. The item name basically never changes. The price and the stock level change constantly, because it is a grocery store and things sell out. Instacart described billions of daily changes across pricing and inventory.

Here is the trap. Elasticsearch does not really update a document in place. Lucene segments are immutable, so an update marks the old document as deleted and writes a whole new one, then merges segments in the background to reclaim the space. A one byte price change costs you a full document rewrite plus the index maintenance that follows. Multiply by billions and the cluster spends its life re-indexing things that did not change.

The read path had its own problem, and this one is specific to groceries. Ankit Mittal, who worked on this at Instacart, put it plainly: items move so fast that almost everything retrieved had to be filtered out. Elasticsearch would return the top candidates, the application would check availability, discover most of them were out of stock at that store, and go back for more. That is an n plus one pattern stretched across a network boundary, and it shows up in your p99 immediately.

So Instacart had two failures compounding each other. Writes were amplified by denormalization, and reads were amplified by having the availability data in a different system than the search index. Neither was Elasticsearch's fault exactly. It was a mismatch between the data model a document store requires and the data model a live grocery catalog actually has.

The Mental Model: Stop Moving Data, Move the Compute

The insight that unlocks the whole redesign is simple to say and hard to accept: if your search results always need to be joined against other data, then your search index belongs in the same place as that data.

Every architecture where search lives in a separate engine pays two taxes. First, you have to copy data into it and keep the copy fresh, which is the sync pipeline. Second, anything the engine cannot express has to happen in your application, which means overfetching results and post filtering them.

Instacart flipped the direction. Instead of pulling data up into the application to compute on it, they pushed the computation down to where the data already lived. The catalog was already on Postgres, at scale, run by people who knew how to run it. Adding text retrieval and vector retrieval to that same database meant availability filters, ranking features, personalization data, and the search index all sat inside one query planner's reach.

The second half of the model is about writes. A document store forces denormalization, and denormalization multiplies your write volume by the size of the document. A relational model lets you split fields by how often they change. Item names go in one table, prices in another, inventory in another, machine learning features in another. A price change now updates one narrow row instead of rewriting a fat document, and the text index does not get touched at all.

That 10x number is Instacart's own figure for the write reduction from normalization, and it came with about 80 percent savings on storage and indexing costs. The end to end search path also came out roughly twice as fast, which they attribute to co-locating compute with storage rather than to any single clever query.

The Postgres Search Architecture

The rebuilt system is sharded Postgres, self hosted, on local NVMe drives rather than network attached storage. That detail matters more than it sounds. Search is a random read workload against large indexes, and network storage latency is the difference between a plan that works and a plan that falls over under load.

Each shard holds a slice of the catalog plus everything needed to score and filter it. Queries go through a routing layer that picks the right shard.

For text matching, Postgres already ships the pieces. A GIN index over a tsvector column gives you an inverted index, which is structurally the same idea Lucene uses. Instacart used GIN indexes plus a modified version of the built in ts_rank function for relevance scoring, because stock ts_rank is a fairly blunt instrument and real product search needs its own notion of what a good match is.

Here is the shape of the schema, reconstructed from what they described. This is illustrative, not their literal DDL.

-- rarely changes: the text side of search
CREATE TABLE items (
  item_id      bigint PRIMARY KEY,
  retailer_id  bigint NOT NULL,
  name         text   NOT NULL,
  brand        text,
  category_id  int,
  search_doc   tsvector GENERATED ALWAYS AS (
                 setweight(to_tsvector('english', coalesce(name, '')),  'A') ||
                 setweight(to_tsvector('english', coalesce(brand, '')), 'B')
               ) STORED
);
 
CREATE INDEX items_search_doc_gin ON items USING gin (search_doc);
 
-- changes constantly: kept out of the indexed table on purpose
CREATE TABLE item_availability (
  item_id     bigint PRIMARY KEY,
  retailer_id bigint NOT NULL,
  in_stock    boolean NOT NULL,
  price_cents int     NOT NULL,
  updated_at  timestamptz NOT NULL DEFAULT now()
);
 
-- changes on a model release cadence, not a per second cadence
CREATE TABLE item_ml_features (
  item_id      bigint PRIMARY KEY,
  popularity   real,
  conversion   real,
  embedding    vector(384)
);

The split is the point. items carries the GIN index and almost never gets written. item_availability gets hammered all day and has no expensive index on it. A price update writes one small row and never invalidates the text index. That is where the 10x comes from.

Because everything is relational, ranking features and model coefficients live in ordinary tables with ordinary join keys. You can change a model's weights with an UPDATE instead of a reindex, which is a quietly enormous quality of life difference for the machine learning team. Instacart index hundreds of gigabytes of these features alongside the documents.

One more operational detail worth stealing. Writes do not trickle in one row at a time. They batch through S3 and land as bulk merges of roughly 16,000 to 32,000 rows, with continuous table repacking to control bloat from all those updates. Batching the writes bought them close to an order of magnitude in write throughput on its own. Postgres handles a small number of large writes far better than a large number of small ones, and heavy update workloads leave dead tuples behind that autovacuum alone will not reclaim fast enough.

The Sharding Algorithm, Step by Step

This is the part people ask about most, so let me be precise about what is documented and what is inference.

What Instacart published is that text retrieval runs on sharded Postgres instances, each shard holding catalog data and search indexes, with queries routed through a service layer to the right shard. In the podcast, Ankit named PgCat as the proxy, describing it as a way to work with a sharded database as if it were not sharded. The exact shard key and shard count are not public. The mechanism, though, is fully documented in PgCat itself, and it is worth understanding because it is the same mechanism you would use.

PgCat uses the PARTITION BY HASH hashing function, the same one Postgres uses for declarative partitioning. That choice is deliberate and it is the elegant part. Postgres hash partitioning assigns a row to a partition by hashing the partition key and taking the remainder modulo the number of partitions. If your proxy uses the identical hash function, then the proxy's idea of which shard owns a key and the database's idea of which partition owns it agree exactly. You get sharding across machines that behaves like partitioning within one machine.

Concretely, on each shard you create the partition that shard owns:

-- on shard 0
CREATE TABLE items (
  item_id     bigint,
  retailer_id bigint NOT NULL,
  name        text
) PARTITION BY HASH (retailer_id);
 
CREATE TABLE items_p0 PARTITION OF items
  FOR VALUES WITH (MODULUS 3, REMAINDER 0);
 
-- shard 1 creates REMAINDER 1, shard 2 creates REMAINDER 2

And the client tells the pooler which shard it wants, either with a session command or with a comment on the query itself:

-- explicit: I know the shard
SET SHARD TO '2';
 
-- or: here is the key, you work it out
SET SHARDING KEY TO '8123';
 
-- or, lowest latency, annotate the query and skip the round trip
/* sharding_key: 8123 */
SELECT item_id, name FROM items WHERE search_doc @@ plainto_tsquery('oat milk');

The comment form is the one that matters in production. SET costs an extra round trip per query, while a comment rides along with the query you were already sending. ActiveRecord and SQLAlchemy both support query annotation, and Instacart is a Rails shop, so this fits their stack without application surgery.

Now, why is the shard key almost certainly the retailer? Because of what a query needs to touch. A search always happens in the context of one retailer, and it needs the items, the availability, the prices, and the machine learning features for that retailer's catalog. If all of those tables are hashed on retailer_id, then every row a query needs lives on one machine, and the join runs locally with no cross shard coordination. That is the entire game in sharded systems: pick the key that makes your hot queries single shard.

Choose the key badly, say by item_id, and every search becomes a scatter gather across all shards, where you pay the latency of the slowest shard on every request and you cannot join to availability without shipping rows around. The hash function is trivia. The key selection is the architecture.

Hash sharding buys you even distribution without maintaining a lookup table, and that is why people reach for it. The costs are real though, and they are worth naming:

Range queries across the key are impossible to route, because hashing destroys ordering. You cannot ask for "retailers 8000 through 9000" and hit one shard. For this workload nobody wants that query, so it costs nothing.

Resharding is painful. Changing the modulus rehashes everything and moves most of your data. Postgres gives you an escape hatch here: you can split a hash partition set at a higher modulus that is a multiple of the old one, so a modulus of 8 can become 16 by splitting each partition in two rather than reshuffling the whole catalog. Plan the modulus with headroom on day one anyway.

Skew is the failure mode hashing does not fix. Hashing distributes keys evenly, not load. A few very large retailers with enormous catalogs and enormous query volume will land on some shard, and that shard becomes your bottleneck while its neighbors idle. The honest answers are read replicas per shard, or pinning the biggest tenants to dedicated shards outside the hash scheme, which is exactly the kind of special case that makes real systems messier than diagrams.

From FAISS to pgvector: Hybrid Retrieval in One Query

Keyword search is precise and literal. It nails "pesto pasta sauce 8oz" and returns nothing useful for "healthy snacks for kids," because none of those words appear in the product names people actually want. Semantic search is the opposite: good at intent, sloppy about specifics. You need both, and the whole design question is where you combine them.

In 2021 Postgres had no approximate nearest neighbour search, so Instacart built the vector half separately. They generated embeddings with a bi-encoder based on the Hugging Face MiniLM-L3-v2 model, built HNSW indexes with Meta's FAISS library, one index per retailer, and ended up managing hundreds of them. Each search made parallel calls to Postgres for lexical results and to the FAISS service for semantic results, then merged the two lists in the application with a linear ranking model before handing the top k to reranking.

It worked, and it had exactly the problems you would predict. You cannot filter by attributes inside the ANN search, so you overfetch a large number of candidates and throw most of them away afterwards. Two systems means two copies of the data and two sync problems. Hundreds of indexes means real operational overhead.

pgvector changed the calculus. Once approximate nearest neighbour search lived inside Postgres, the merge could happen in the database, next to the filters.

Two configuration choices from their write up are worth stealing outright.

They built hybrid indexes grouped by retailer characteristics instead of a dedicated index per retailer. Hundreds of tiny indexes is worse than a smaller number of well sized ones, both for memory and for maintenance.

They stored embedding columns inline rather than letting Postgres push them out to TOAST storage. This one is subtle and it is the kind of thing that decides whether your vector search is fast or mysteriously slow. Postgres moves large column values into a separate TOAST table once a row gets too big, which means reading an embedding becomes a second lookup on a different heap. For a scan that touches thousands of vectors, that extra indirection dominates. They also raised max_parallel_workers and max_parallel_workers_per_gather to 8 so the planner would actually parallelize the scans.

Their benchmark result is refreshingly unflattering to their own decision: FAISS stayed marginally faster than pgvector for larger retailers. pgvector won on recall. They took the trade, because slightly slower with better recall inside one system beats slightly faster with worse recall across two systems.

The query that replaces all of that looks roughly like this. Again, illustrative:

WITH candidates AS (
  SELECT i.item_id
  FROM items i
  JOIN item_availability a USING (item_id)
  WHERE i.retailer_id = $1
    AND a.in_stock
),
lexical AS (
  SELECT c.item_id,
         ts_rank(i.search_doc, plainto_tsquery('english', $2)) AS score
  FROM candidates c
  JOIN items i USING (item_id)
  WHERE i.search_doc @@ plainto_tsquery('english', $2)
  ORDER BY score DESC
  LIMIT 200
),
semantic AS (
  SELECT c.item_id,
         1 - (f.embedding <=> $3::vector) AS score
  FROM candidates c
  JOIN item_ml_features f USING (item_id)
  ORDER BY f.embedding <=> $3::vector
  LIMIT 200
)
SELECT item_id, sum(w) AS blended
FROM (
  SELECT item_id, score * 0.6 AS w FROM lexical
  UNION ALL
  SELECT item_id, score * 0.4 AS w FROM semantic
) merged
GROUP BY item_id
ORDER BY blended DESC
LIMIT 50;

Notice what the candidates CTE is doing. Availability filtering happens before either retrieval path runs, which is precisely the thing FAISS could not do. That is why the zero result rate dropped: candidates that used to be discarded after retrieval never occupy a retrieval slot in the first place. In their production A/B test that showed up as a 6 percent drop in searches returning nothing, and a meaningful revenue increase behind it.

Failure Modes You Should Expect

Nobody publishes the incident reports, so this section is mostly reasoning from how these components behave. Take it as a checklist rather than a report.

GIN indexes have a pending list. Postgres buffers new entries in an unsorted pending list and folds them into the main index later, controlled by fastupdate and gin_pending_list_limit. It makes writes fast and it makes some reads slow, because a query has to scan that pending list linearly. The symptom is bimodal latency: most queries are quick, some are strangely not, and nothing in your application changed. This is a strong argument for the split schema above, where the GIN indexed table barely gets written at all.

HNSW index builds are memory hungry and slow. If the graph does not fit in maintenance_work_mem the build spills to disk and takes far longer, and a build kicked off during peak traffic will compete for the same I/O your queries need. Build them off peak, and know how long a rebuild takes before you need one at 3am.

ANN recall degrades silently. This is the worst failure mode in the whole system, because nothing errors. If hnsw.ef_search is too low for your data, you simply get slightly worse results, forever, and the only way to notice is a relevance metric you deliberately track. Zero result rate and click through position are your smoke alarms here.

Bloat from update heavy tables is a slow moving outage. Every update writes a new row version and leaves a dead one behind. Autovacuum may not keep up at billions of writes a day, tables grow, cached pages hold less useful data, and everything gets gradually slower until someone runs a repack. Instacart run continuous table repacking for exactly this reason. Treat repacking as a scheduled operation, not an emergency response.

Shard skew shows up as one hot machine. Watch per shard p99 latency separately. An average across shards will hide the one that is dying.

Note: The most valuable thing about Postgres here is boring. Instacart's own framing is that Postgres tends to fail in predictable ways and degrades gracefully instead of falling over. When a system is core to revenue, predictable mediocrity under stress beats excellent performance with a cliff.

The Tradeoffs, and When Not To Do This

I do not want this to read as "delete Elasticsearch." Instacart's situation had several specific properties, and if yours differ the answer flips.

Do not do this if your search corpus is genuinely static and huge. Elasticsearch's whole design assumes you index once and read many times. That is a good assumption for documentation, logs, and archives. It was a terrible assumption for a live grocery catalog. If write amplification is not your problem, you are giving up real capability for nothing.

Do not do this if you need what Elasticsearch is actually good at. Distributed aggregations across enormous datasets, percolators, complex analyzer chains for many languages, the whole observability ecosystem around it. Postgres full text search is a genuinely good inverted index with a fairly primitive relevance layer on top. Instacart had to modify ts_rank to make it work.

Do not do this if your search results do not need to join anything. The entire argument is that co-locating search with the data you filter and rank against removes network hops. No joins, no argument.

Be careful if you do not already run Postgres at scale. Instacart chose Postgres partly because they had deep operational expertise with it. Sharded, self hosted Postgres on NVMe with a routing proxy, bulk loaders, and continuous repacking is not a small operational surface. It is a different operational surface, not a smaller one. What they removed was a second system to keep in sync, and if you would be introducing sharding rather than removing a database, the maths is different.

The costs they accepted are worth stating plainly: slightly slower ANN performance than FAISS on their largest catalogs, a dependency on pgvector's release cadence for new vector features, and hundreds of retriever instances across shards to operate.

What This Looks Like Six Months In

The part of this story that ages best is the part with no diagram: development velocity. When search data and transactional data live in one system, nobody writes reconciliation jobs, nobody debugs why the index disagrees with the source of truth, and nobody explains to a product manager that the fix went out but search will catch up eventually. Instacart called this out as a primary win, and it is the kind of win that compounds quietly.

For anyone considering the same move, the sequence I would follow is: measure your write amplification first, because that number decides everything. Count how many index writes one business event triggers today. If a single price change rewrites a document containing forty fields, you already know the shape of your answer.

Then check your post filter rate. Measure what fraction of retrieved results your application throws away before rendering. High post filter rates mean your filters and your index are in the wrong places relative to each other, and that is the exact pain that co-location fixes.

Only then think about sharding, and think about the key before anything else. Hash function, proxy, modulus, all of that is mechanics you can look up. Which column keeps your hot query on one machine is the decision that you will not be able to undo cheaply.

This is also part of a pattern that is getting hard to ignore. JSONB took a bite out of the document database case. pgvector is taking a bite out of dedicated vector stores. PostgreSQL 19 added native graph queries, which I wrote about in PostgreSQL 19 Graph Database: SQL/PGQ Explained, and now full text search at genuine scale has a serious reference implementation. The same consolidation logic keeps showing up in the systems I build for my projects: one database that is good enough at five things usually beats five databases that are excellent at one, because the sync pipelines between them are where the bugs and the on call pages live.

The open question I keep coming back to is where the ceiling actually is. Instacart hit real limits, FAISS was faster on their biggest catalogs, and they chose the simpler system anyway. That trade is only obvious once you have run both.

References

• How Instacart Built a Modern Search Infrastructure on Postgres
• Instacart Consolidates Search Infrastructure on PostgreSQL, Phasing Out Elasticsearch
• PgCat: PostgreSQL pooler with sharding, load balancing and failover support