Postgres vs. Elasticsearch: Instacart’s Unexpected Winner in High-Stakes Search with Ankit Mittal

Episode Overview

• Ankit Mittal, former Senior Engineer at Instacart, discusses the evolution of their product search architecture.
• The episode details the migration from a standard Elasticsearch setup to a highly customized PostgreSQL cluster for handling search queries.
• Key architectural principles are explored, such as pushing compute down to the data layer to simplify applications and reduce network latency.
• Ankit breaks down the technical trade-offs, the data ingestion pipeline, and the specific PostgreSQL extensions that made this unique architecture possible.

Key Concepts

• Hybrid Search Retrieval: Instacart's search system combined traditional text search with complex, real-time filtering and personalized ranking based on rapidly changing data like inventory and user preferences.
• Pushing Compute to the Data Layer: The core strategy was to move complex logic (filtering, ranking, joins) from the application layer into PostgreSQL. This simplified the application, reduced network round-trips, and leveraged the database's transactional capabilities.
• PostgreSQL as a Search Engine: Instacart extended PostgreSQL to function as a search engine. This was achieved by consolidating search indexes, ranking tables, availability data, and product taxonomies into a single, self-hosted cluster.
• Data Ingestion and Hot-Swapping: Data was ingested in bulk from S3 into temporary tables. The system would then use tools like pg_repack to build a new, optimized, and clustered primary table, which was then "hot-swapped" to serve live traffic with minimal disruption.
• PostgreSQL Extensions: The architecture relied heavily on PostgreSQL extensions, including a custom C extension for dot product calculations (before pg_vector was available) to handle personalized ranking models directly within the database.

Quotes

• At 0:00 - "Almost everything that we got retrieved had to be filtered out." - Ankit describes the initial problem where the search retrieval was too broad, forcing the application to do heavy filtering, which was inefficient and led to multiple network calls.
• At 2:27 - "If there's a lot of things that you can get the database to do, then the applications become simpler." - Ankit explains the core principle behind their decision to move complex filtering and ranking logic from the application layer into the PostgreSQL database.
• At 17:21 - "All the rights are pipelined. We don't use Kafka. We, uh, the interface is, is S3." - Ankit clarifies that their data pipeline for updating the search cluster was a bulk-write process originating from S3, rather than a real-time streaming approach.
• At 22:11 - "Eventually what we found out was that the DevEx, the developer experience, and how engineers use it, especially search engineers, it was less than ideal." - Ankit discusses a major trade-off of their custom PostgreSQL solution: it was operationally efficient but difficult for search engineers, who were more accustomed to Elasticsearch's query DSL, to work with.

Takeaways

• Leverage the database to simplify your application. By pushing complex filtering, joining, and ranking logic into the database, you can reduce application complexity, minimize network latency from multiple data-fetching round trips, and improve overall system performance.
• PostgreSQL can be a viable alternative to specialized search engines. For use cases that require both text search and complex, real-time transactional logic (like inventory checks or personalized filtering), a customized PostgreSQL setup with extensions can outperform a separate search system by avoiding data synchronization issues and enabling powerful single-query operations.
• Balance architectural innovation with developer experience (DevEx). While a custom solution might be technically optimal, consider the learning curve and productivity impact on your team. Moving from a widely-used tool with a familiar interface (like Elasticsearch) to a complex, bespoke system can create significant challenges for developers.