How Instacart Built a Search Engine for Billions of Items on PostgreSQL
How Instacart migrated product search from fragmented Elasticsearch clusters onto a unified PostgreSQL and pgvector stack to eliminate sync lag.
On this page
Operating search for an online grocery marketplace is an engineering nightmare of localized inventory and hyper-dynamic availability.
Unlike standard e-commerce (where a catalog item is either in a global warehouse or not), grocery inventory varies by individual grocery store location. If a user in Brooklyn searches for “organic honeycrisp apples”, the search engine must evaluate inventory across five local neighborhood stores. Prices, stock levels, and promotions change every minute.
Historically, Instacart separated its transactional data from its search infrastructure:
- Transactional Database (PostgreSQL): Stored the authoritative inventory, pricing, and merchant catalog.
- Search Engine (Elasticsearch): Ingested product records asynchronously via Kafka CDC pipelines to power lexical search.
While this dual-stack architecture scaled initially, it introduced severe production friction: replication lag. When a grocery store ran out of milk, the Postgres update took seconds or minutes to propagate to Elasticsearch. Customers ordered “ghost” out-of-stock items, resulting in failed deliveries and millions of dollars in customer service refunds.
To solve this, Instacart executed an audacious replatforming: consolidating search directly onto PostgreSQL using pgvector and hybrid indexing.
The Dual-Stack Synchronization Dilemma
Merchant Updates Stock: "Apples Out of Stock"
│
▼
[ PostgreSQL Primary ] <── Updated in 2ms
│
│ Asynchronous Kafka CDC Stream
▼ (Sync Lag: 500ms to 45 seconds during load)
[ Elasticsearch Cluster ]
│
▼
Customer searches Brooklyn Store ──► Search returns "Apples In Stock" ──► Checkout Fails
The Unified PostgreSQL Architecture
By consolidating search into the primary database engine, Instacart unified transactional consistency with search indexing:
1. Hybrid Lexical and Semantic Retrieval
Grocery search requires two distinct search disciplines:
- Lexical Precision (GIN Indexes): Users search for exact brand names or SKUs (“Chobani Greek Yogurt Vanilla 32oz”). Trigram GIN indexes (
pg_trgm) resolve exact lexical matches and typo tolerances in milliseconds. - Semantic Discovery (pgvector HNSW): Users also search for contextual recipes or cravings (“healthy snacks for kids” or “gluten-free party dip”). High-dimensional vector embeddings generated by vision-language models are indexed using Hierarchical Navigable Small World (HNSW) graphs in
pgvector.
-- Hybrid search query combining lexical and vector distance
SELECT id, title, price,
(ts_rank_cd(text_vector, query) * 0.4 +
(1 - (embedding <=> query_embedding)) * 0.6) AS composite_score
FROM store_products
WHERE store_id = 941
AND in_stock = true
ORDER BY composite_score DESC
LIMIT 50;
2. Immediate Transactional Consistency
Because inventory updates and search indexes live in the same database table, updating stock is an atomic ACID transaction. When a merchant updates an item’s availability, the search index reflects the change instantly. Zero ghost inventory.
3. Operational Overhead Reduction
Managing hundreds of Elasticsearch nodes requires deep JVM tuning, garbage collection monitoring, shard rebalancing, and custom snapshot scripting. Consolidating onto PostgreSQL reduced search infrastructure footprint by over 70%, allowing engineers to leverage existing automated backups, read replica pooling, and failover infrastructure.
Key Lessons for Systems Architects
- Beware the Dual-Write Trap: Splitting operational state from search indexing creates inevitable synchronization lag that poisons data consistency.
- Postgres is More Capable Than You Think: With extensions like
pgvector,pg_trgm, and declarative partitioning, modern PostgreSQL can replace secondary search clusters for millions of operational workloads. - Optimize for Operational Simplicity: Eliminating an entire distributed system (Elasticsearch) and its CDC replication pipeline frees engineering teams to focus on core product features.
/* Comments */
Comments are offline right now — we reconnect automatically, nothing is lost.