The previous system used OpenSearch for marketplace data and PostgreSQL for availability, coordinated by a central API layer.
The OpenSearch setup lacked relational joins, had a complex query DSL, and suffered from performance issues and high maintenance overhead.
PostgreSQL was selected for its mature relational capabilities, JSONB support with GIN indexes, lower operational complexity, and team familiarity with SQL.
Initial concerns focused on the performance of GIN indexes, which rely on bitmap heap scans instead of faster index-only scans.
OpenSearch excels at aggregations due to compressed data structures like roaring bitmaps, a feature not natively available in PostgreSQL for OLTP workloads.
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.
High-cardinality aggregation queries were cached, while the jOOQ library was used to dynamically generate complex SQL from the internal search DSL.
While AWS Aurora provided easy scaling with read replicas, it limited the use of specialized PostgreSQL extensions like roaring bitmaps for performance tuning.
Storing large JSON blobs triggered PostgreSQL's TOAST mechanism, which required tuning and led to replication conflicts on read replicas during vacuum operations.
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.
Bitpanda
Vienna, Austria
fulfillmenttools
Köln, Germany
24:22Jürgen Pilz
28:12Josip Stuhli
46:47Ruwan Xaviour Fernando
22:32George Asafev
30:34Markus Dreseler
20:50Nikita Shamgunov
21:09Tobias Petry
28:12Gregor Bauer
Jobs that call for the skills explored in this talk.
Red - The Global SAP Solutions Provider
Koblenz, Germany