> Markdown version of [/videos/1647-optimizing-discovery-postgresql-s-role-in-transforming-getyourguide-s-search](https://www.wearedevelopers.com/videos/1647-optimizing-discovery-postgresql-s-role-in-transforming-getyourguide-s-search). Every page supports `.md` or `Accept: text/markdown`. Links point to the HTML versions so they work for humans too. Agent guide: [/agents.md](https://www.wearedevelopers.com/agents.md).

---

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

Can PostgreSQL outperform specialized search engines? Discover how GetYourGuide migrated from OpenSearch, deeply tuning TOAST tables and JSONB structures to slash API latency by 40%.

- **Speakers:** [Dharin Shah](https://www.wearedevelopers.com/@dharin-shah)
- **Event:** World Congress 2025
- **Published:** August 20, 2025
- **Duration:** 26:04
- **URL:** https://www.wearedevelopers.com/videos/1647-optimizing-discovery-postgresql-s-role-in-transforming-getyourguide-s-search

## Summary

GetYourGuide's engineering team faced rising infrastructure costs, vertical scaling limits, and problematic API latency spikes on their managed OpenSearch cluster, specifically when heavy write workloads choked read endpoint performance. Frustrated by a restrictive search DSL and a lack of support for relational joins—which stifled complex experimentation—the software engineering team orchestrated an architectural shift. By consolidating their marketplace search indexing into their existing AWS Aurora PostgreSQL database infrastructure, they drove operational simplicity, enabled robust data joins, and rapidly deployed read-replica scaling.

Migrating a high-volume, flexible search catalog into a row-oriented database required carefully addressing indexing and aggregation bottlenecks. OpenSearch naturally excelled at real-time analytics because it utilizes highly compressed, SIMD-friendly Roaring Bitmaps. Conversely, PostgreSQL natively struggles with high-cardinality aggregations, and its pattern of GIN indexes forces slightly less optimal bitmap heap scans. To overcome this, the team deployed a denormalized schema mapping heavily accessed metadata to array-based columns and `jsonb` data structures. They optimized API queries with a "Single Query Multiple Data" (SQMD) pattern, using Common Table Expressions (CTEs) to package multiple data retrievals directly into structured JSON outputs. Complex and difficult-to-maintain Hibernate/HHQL query builders were subsequently replaced by jOOQ, enabling the dynamic generation of read-heavy SQL with strict compile-time validation.

Scaling PostgreSQL for this workload subsequently exposed massive Write Amplification and read-replica replication conflicts originating from PostgreSQL's TOAST tables, which were chunking extremely large multi-language JSON blobs. The team successfully neutralized these database locks by increasing the TOAST tuple target to 4KB to dramatically reduce fragmentation, adopting newer LZ4 compression algorithms, and disabling automatic `truncate` during vacuum operations to prevent Access Exclusive locks on active standby replicas. Ultimately, this deep-dive PostgreSQL tuning led to a permanent 40% reduction in end-to-end API latency, eliminating volatility during traffic spikes, and laying the groundwork for migrating their remaining BM25 textual search into the PostgreSQL ecosystem.

**Keywords:** postgresql search migration, opensearch scaling bottlenecks, api latency optimization, gin index bitmap heap scans, roaring bitmaps aggregation, jsonb denormalized schemas, single query multiple data, common table expressions cte, jooq dynamic sql generation, hibernate hhql refactoring, postgresql toast table tuning, database write amplification, vacuum truncate locking conflicts, lz4 storage compression, aurora postgresql read replicas

## Chapters

1. **Overview of the GetYourGuide event-based marketplace** (00:05) — An overview of the event marketplace and the business need for a highly responsive discovery system.
1. **Reviewing the legacy search architecture using OpenSearch** (01:28) — The previous search system combined data processing pipelines, OpenSearch for document indexing, and PostgreSQL for availability handling.
1. **API flow for frontend team search queries** (03:18) — Custom domain-specific languages handle frontend filtering rules before requests reach the ranking and ML services.
1. **Technical challenges driving the OpenSearch database migration** (04:29) — A lack of relational joins, complex query structures, and frequent latency spikes limited product flexibility and required high maintenance.
1. **Advantages of migrating search infrastructure to PostgreSQL** (06:25) — Leveraging a mature relational model with flexible JSON support significantly reduces operational complexity and underlying infrastructure costs.
1. **Analyzing limitations with PostgreSQL bitmap heap scans** (07:50) — Running memory-resident TID bitmaps creates additional performance steps compared to faster index-only execution plans.
1. **Evaluating aggregations and memory usage of roaring bitmaps** (10:27) — Native PostgreSQL row-oriented storage struggles with high-cardinality data compared to OpenSearch's highly compressed memory-efficient roaring bitmaps.
1. **Implementing denormalized schemas with GIN indexes in Postgres** (13:38) — Combining array-based columns with GIN indexes perfectly maps to previously denormalized OpenSearch document structure dependencies.
1. **Retrieving custom API payloads using a single query** (14:42) — Executing common table expressions produces a unified JSON result format instead of receiving multiple tabular relational results.
1. **Performance impacts and search data cardinality evaluation** (16:01) — Evaluating search patterns confirms that query caching limits performance degradation when dealing with high-cardinality Postgres filter states.
1. **Generating dynamic database queries using the JOOQ library** (17:02) — Implementing JOOQ provides clean and compile-time validated SQL generation code for handling complex dynamical client search criteria.
1. **Managing infrastructure limitations with managed Amazon Aurora databases** (18:25) — Adopting managed database clusters simplifies replica deployment but restricts usage of specialized real-time aggregation extensions.
1. **Optimizing TOAST configurations for large JSON metadata blobs** (20:18) — Increasing the tuple target size alongside lz4 compression radically reduces row amplification when saving multi-language localization metadata.
1. **Preventing replication conflicts from database vacuum operations** (22:13) — Disabling truncation commands during vacuum runs safely prevents exclusive locks blocking access to standby read replica pages.
1. **Postgres migration performance results and future semantic search** (23:28) — The search layer transition successfully reduced overall latency and cleared a unified roadmap for robust vector-based semantic search.

## Related Moments

- [Comparing PostgreSQL and DynamoDB for read-heavy storage](https://www.wearedevelopers.com/videos/746-swapping-low-latency-data-storage-under-high-load) (from "Swapping Low Latency Data Storage Under High Load")
- [Benchmarking SearchOLAP against existing database engines](https://www.wearedevelopers.com/videos/100212-olap-for-ai-applications-and-why-you-should-care) (from "OLAP for AI Applications and why you should care")
- [Migrating existing applications from MongoDB to Postgres](https://www.wearedevelopers.com/videos/1042-postgres-in-the-age-of-ai-and-devin) (from "Postgres in the Age of AI (and Devin)")
- [Navigating enterprise adoption, search optimization, and platform scaling concerns](https://www.wearedevelopers.com/videos/100032-under-the-hood-of-building-on-lovable) (from "Under the Hood of Building on Lovable")
- [Choosing relational databases over NoSQL for most workloads](https://www.wearedevelopers.com/videos/100135-the-new-shiny-syndrome-how-to-avoid-tech-hype-traps) (from "The New Shiny Syndrome: How to Avoid Tech Hype Traps")
- [Migrating from local JSON storage to relational databases](https://www.wearedevelopers.com/videos/775-build-and-deploy-a-fullstack-app-with-open-source-tooling) (from "Build and Deploy a Fullstack App with Open Source Tooling")

## Related Articles

- [SEO in an AI world - Google vs. ChatGPT and survival tips for content creators](https://www.wearedevelopers.com/magazine/534-seo-in-an-ai-world-google-vs-chatgpt-and-survival-tips-for-content-creators)
- [Dev Digest 168: Hacking Postgres, Blocking Meta and Fixing CSS](https://www.wearedevelopers.com/magazine/588-dev-digest-168-hacking-postgres-blocking-meta-and-fixing-css)
- [Making Data Warehouses Fast: A Developer’s Story](https://www.wearedevelopers.com/magazine/107-making-data-warehouses-fast-a-developer-s-story)
- [Dev Digest 139 - Soft and hard queries](https://www.wearedevelopers.com/magazine/487-dev-digest-139-soft-and-hard-queries)

## Related Jobs

- [Principal Engineer - AI Search & Vector Infrastructure](https://www.wearedevelopers.com/jobs/ext/319507-principal-engineer-ai-search-vector-infrastructure) at **Redis**
- [Principal Engineer - AI Search & Vector Infrastructure](https://www.wearedevelopers.com/jobs/ext/353953-principal-engineer-ai-search-vector-infrastructure) at **Redis**
- [Principal Engineer - AI Search & Vector Infrastructure](https://www.wearedevelopers.com/jobs/ext/381484-principal-engineer-ai-search-vector-infrastructure) at **Redis**
- [Senior Software Engineer, Data](https://www.wearedevelopers.com/jobs/48273-senior-software-engineer-data) at **Sportradar Media Services GmbH**
- [Staff Software Engineer](https://www.wearedevelopers.com/jobs/ext/1425755-staff-software-engineer) at **GitHub**
- [Senior Backend Engineer (Java)](https://www.wearedevelopers.com/jobs/ext/19369-senior-backend-engineer-java) at **Bonial International GmbH**