Mix Elastic search simple text queries with normal Postgres queries

[camerojo]

Up until now when we have wanted to allow users to mix an Elastic search with other query fields we have had to copy those Postgres fields into Elastic search and then do the whole query in Elastic. Over time this has meant that almost the whole Postgres database has had to be replicated in Elastic.

Now with the new extractSQL method on SearchCandidateRequest there is a way to combine an Elastic query with a Postgres query. (See #1160)

Given a SearchCandidateRequest:

1. Execute the Elastic simple query text search (as specified in the simpleQueryString field of the request)
2. Extract the candidate ids of the returned candidates
3. Extract the Postgres SQL query from the request by calling request.extractSQL()
4. Run the following query on the Postgres database:

select * from candidate where id in (elasticid1, elasticd2 ...) and id in ([ExtractedPostgresQuery])

[camerojo]

Note

One option that has been discussed is to do all queries with Elastic Search - then pass the results on to Postgres for display to the user.

However, the problem with that approach is that the only way we have come up with to share Elastic search results with Postgres is by extracting the candidate ids from the Elastic search results and then running a query like the following on Postgres:

select c from candidate where c.id in (id1, id2, id3, ...)

The problem with that is that Postgres has a limit of around 32,000 ids that can be included in a query like the above.

So the only other alternative is to dispense with Postgres altogether. That may be the way to go but it would be a major change, involving also porting all our stats across to Elastic search.

Elasticsearch does have SQL support but see also limitations

Lastly, when considering the pros and cons of moving to just one database, we should explore the text search capabilities of Postgres that we currently don't use - see #1217

fyi @sadatmalik