[META] Native Join support in OpenSearch

[harshavamsi]

Please describe the end goal of this project

The goal of this project is to support joins across indices natively in OpenSearch.

Supporting References

#15185

Issues

• [Joins] Join Query DSL #15450
• [Joins] Source Reader implementation for Joins #15874

Related component

Search:Query Capabilities

[dblock]

[Catch All Triage - 1, 2, 3, 4, 5]

[dblock]

You can do joins via SQL plugin, but not natively in OpenSearch.

[msfroh]

You can do joins via SQL plugin, but not natively in OpenSearch.

Well, not yet, which is why we have this issue! 😁

[rendel]

Hello,

I wanted to share some insights from our work on Siren Federate, which may be helpful for the design and implementation of distributed joins in OpenSearch. Our team has spent nearly a decade developing and refining Siren Federate, a plugin specifically focused on high-performance joins over Elasticsearch and OpenSearch, with an emphasis on minimizing response time in large-scale, distributed environments. This system is actively deployed across clusters of various sizes, from smaller setups to those with hundreds of nodes and terabytes of data, supporting large numbers of concurrent users.

For reference, you can find our original open-source implementation, siren-join (licensed under AGPL). Since then, we've developed a more advanced version, Siren Federate, which is a closed-source plugin, but with several accessible resources to explore its architecture and functionalities, that could be useful references in designing such a feature:

• documentation for Siren Federate: https://docs.siren.io/siren-federate-user-guide/36/siren-federate/introduction.html
• a recent scientific paper detailing its architecture, presented at IDEAS 2024: https://renaud.delbru.fr/doc/pub/ideas2024-federate.pdf

Implementing native join operations that work at scale is challenging, and we hope this information could provide useful insights as you design a similar feature for OpenSearch.