1

I could be missing something obvious because this seems like a fairly basic question, but I haven't been able to find any explicit guidance anywhere on how to integrate Meilisearch/Elasticsearch with a "main" database (say Postgres), and what the best practices for this sort of setup are.

For context: I'm building an online video course platform (a la Udemy), and on the server side of things, I have a GraphQL API with a searchInCourses query field that takes in a user-provided search query and is supposed to return the relevant results. I've been looking into things like Meilisearch, but I'm not quite sure about the right workflow and how it would best fit into our system. Would something like this make sense, for example?:

I create a courses index on my e.g. Meilisearch DB, each document in the index would contain the ID of the course (which would be the same as the ID of the same course in the main DB) + a flat set of (denormalized) fields that are most relevant to searching (e.g. title, instructor_name, etc.). Every time a request comes in for the searchCourses query field, I first query the Meilisearch DB with the user-provided query, which sends back the IDs of the matching courses, but I'll then send another query to my main (PostgreSQL) database to retrieve the actual information about the matching courses — the SQL query would end with WHERE c.id = ANY (@ids) where @ids is the IDs of the courses returned by Meilisearch.

Is this a standard, sane way of doing things? Or am I just totally off? If so, I'll appreciate it if someone points me in the right direction.

Thank you in advance.

Improve this question
7
  • You should not tag this with elasticsearch if your real question is about meilisearch, which seems to be the case.
    – jjanes
    Sep 24 at 0:44
  • @jjanes I haven't decided on the particular search engine yet. And I think this question is generic enough to be applicable to any search-based DB, including Elasticsearch.
    – Arad Alvand
    Sep 24 at 0:47
  • But there is a lot of advice out there for syncing postgresql to elasticsearch. I haven't thoroughly evaluated it myself, as it is outside my fields of interest, but it is certainly not missing.
    – jjanes
    Sep 24 at 2:10
  • @jjanes Well, my question is more about presenting one particular workflow and asking if it has any flaws that I have missed, and whether or not it's a sound method of integration between an RDBMS (e.g. PostgreSQL) and a separate search engine. I think it's reasonable.
    – Arad Alvand
    Sep 24 at 2:32
  • 1
    @AradAlvand You would have to build two different indexes, one with pg_trgm and one with tsvector. Alternatively you could use the partial match feature of FTS, with :*, but that doesn't play well with stemming or with synonyms. I don't know enough about those other search tools to know how they handle these two conflicting goals and if there way is better than PostgreSQL's.
    – jjanes
    Sep 25 at 16:31

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.