I'm working on a querying tool that would allow to make complex relational queries along to fulltext search on quite big datasets. My data is stored in a Postgres database with a elasticsearch engine for convenient and efficient fulltext search. However, some queries require complex join, cardinality tests, filters on joined data ...
My dilemma is that I cannot use only ElasticSearch or only Postgres. I need both to answer specific needs. But combining them seems to be a really difficult task.
My approach
... was to perform the ES query first and then use the results' id as a filter for the SQL query. The problem is that ES has a max_result_window that prevents to get all the matching data at once. Even worse, ES may return the first 10K results matching the fulltext search, but the subsequent SQL query may narrow those results number to something ridiculously small, while there's actually much more matching items but the 10K limit acted as bottleneck.
Taking the other way around is no better since if we use the result of the SQL query as the document ids as filters for the ElasticSearch query, the max_clause_count limit will be easily reached and the ES query wouldn't be able to be performed on more than (default) 1024 items.
Maybe my logic isn't good. Is there any other approach to combine both ES and Postgres queries simultaneously ? Thanks.