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.
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.