---
topic: databases
author: Crashtech Editorial
published: Nov 17, 2026 · read: 2 min
---

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.

– –

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

How Instacart Built Product Search on PostgreSQL & pgvector


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:

  1. 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.
  2. 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

  1. Beware the Dual-Write Trap: Splitting operational state from search indexing creates inevitable synchronization lag that poisons data consistency.
  2. 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.
  3. Optimize for Operational Simplicity: Eliminating an entire distributed system (Elasticsearch) and its CDC replication pipeline frees engineering teams to focus on core product features.
Advertisement

/* Comments */