[Feature Request] Integrate Pluggable Storage Engine Support

[laminelam]

Is your feature request related to a problem? Please describe

Currently, integrating OpenSearch with traditional databases requires duplicating data: storing operational data (source of truth) in a database/data store and searchable content in OpenSearch. This leads to increased operational overhead and higher costs.

Describe the solution you'd like

Introduce pluggable datastore support that allows administrators to configure the underlying storage mechanism. This could include traditional database features alongside the existing Lucene-based search capabilities. Such a feature would allow OpenSearch to handle both search queries and traditional data management tasks within a single platform, eliminating the need to duplicate data across separate systems.

Related component

Storage

Describe alternatives you've considered

The current alternative involves using OpenSearch in conjunction with a separate database/data store, which as mentioned, increases complexity and cost.

Additional context

By allowing OpenSearch to be configured with various backends (e.g., Lucene for search-optimized storage, SQL databases for transactional data), we can streamline data management processes, reduce infrastructure costs, and decrease the operational complexity. This would make OpenSearch a more versatile and cost-effective solution for managing and searching large datasets.

[andrross]

Thanks @laminelam! #12948 is a related proposal, though not quite the same. Please take a look if you haven't seen that and see how that lines up with your thinking, if at all.

[msfroh]

This also connects nicely with our query engine project, where we're trying to move more query logic into the coordinator nodes, and let them process streams of columnar data.

For now, those streams of columnar data are coming from (Lucene-based) OpenSearch data nodes, but in the long run, we would like to support streaming results from "anything" as long as it can be encoded as an Apache Arrow stream.

Tagging a few people who are interested:

• @Vikasht34 -- who is interested in data source federation (which seems to be what this is proposing)
• @rishabhmaurya -- who is driving the Apache Arrow integration between coordinator and data nodes
• @mch2 and @harshavamsi -- who are working on the Arrow stream processing stuff
• @backslasht -- who is interested in alternate storage backends for OpenSearch
• @getsaurabh02 -- who leads the query engine project