Optimizing Discovery: PostgreSQL's Role in Transforming GetYourGuide's Search

Can a relational database replace your search engine? GetYourGuide migrated from OpenSearch to PostgreSQL, cutting latency by 40% and simplifying their architecture.

#1about 4 minutes

Understanding the original search architecture at GetYourGuide

The previous system used OpenSearch for marketplace data and PostgreSQL for availability, coordinated by a central API layer.

#2about 2 minutes

Identifying the limitations of the OpenSearch implementation

The OpenSearch setup lacked relational joins, had a complex query DSL, and suffered from performance issues and high maintenance overhead.

#3about 2 minutes

Why PostgreSQL was chosen for the migration

PostgreSQL was selected for its mature relational capabilities, JSONB support with GIN indexes, lower operational complexity, and team familiarity with SQL.

#4about 3 minutes

Analyzing PostgreSQL performance concerns for search queries

Initial concerns focused on the performance of GIN indexes, which rely on bitmap heap scans instead of faster index-only scans.

#5about 3 minutes

The challenge of real-time aggregations in PostgreSQL

OpenSearch excels at aggregations due to compressed data structures like roaring bitmaps, a feature not natively available in PostgreSQL for OLTP workloads.

#6about 2 minutes

Designing a hybrid schema and query pattern

The solution involved a denormalized schema using array columns with GIN and BRIN indexes, queried via a "single query, multiple data" (SQMD) pattern using CTEs.

#7about 2 minutes

Implementing dynamic queries and caching strategies

High-cardinality aggregation queries were cached, while the jOOQ library was used to dynamically generate complex SQL from the internal search DSL.

#8about 2 minutes

Operational benefits and limitations of using AWS Aurora

While AWS Aurora provided easy scaling with read replicas, it limited the use of specialized PostgreSQL extensions like roaring bitmaps for performance tuning.

#9about 3 minutes

Handling large objects and replication conflicts

Storing large JSON blobs triggered PostgreSQL's TOAST mechanism, which required tuning and led to replication conflicts on read replicas during vacuum operations.

#10about 3 minutes

Migration results and future roadmap

The migration resulted in a 40% end-to-end latency improvement, and future plans include consolidating text search into PostgreSQL and removing the caching layer.