Allow multiple tables per index

[abdullah2993]

It would be nice to have the ability to index multiple tables to a single index, essentially making nodes a list instead of a dict. e.g.

[
  {
    "database": "postgres",
    "index": "global_search",
    "nodes": [
      {
        "table": "estimates"
        ...
      },
      {
        "table": "invoices"
        ...
      },
      {
        "table": "bills"
        ...
      }
    ]
  }
]

[abdullah2993]

for time being anyone else facing the similar issue can leverage the index aliases in elasticsearch

[toluaina]

An Elasticsearch/opensearch index maps to a Postgres database. Not sure how you want the data to be represented at the table granularity

[abdullah2993]

you can think of an index called global_serach that has data from multiple tables so that you can search over them. The alias does the same thing, you can combine multiple indexes into one so that you can do pagination and sorting on them as a whole.

[toluaina]

• I'm still finding it hard to visualize the resulting data.
• Is there supposed to be a relationship between these tables?
• we are trying to map relational data to a tree like data structure with pgsync

[abdullah2993]

not really, there isn't any relationship between them. you can think of having products, invoices and estimates inside your application and a search feature that allows you to search over all of them.

[toluaina]

There is no conceptual 1:1 representation of a Postgres table in Elasticsearch/Opensearch. A document is composed of one or more tables with some relationship that ties them together. The closest you can you can do is have multiple indices per table in the same schema e.g

[
  {
    "database": "postgres",
    "index": "global_search_1",
    "nodes": [
      {
        "table": "estimates"
        ...
      }
  },
  {
    "database": "postgres",
    "index": "global_search_2",
    "nodes": [
      {
        "table": "invoices"
        ...
      }
    ]
  },
  {
    "database": "postgres",
    "index": "global_search_3",
    "nodes": [
      {
        "table": "bills"
        ...
      }
    ]
  }
]