We have a database in Postgresql, having candidate,job,campaign related tables with mapping of candidate-->job(job_candidate_mapping let's call it jcm table)and candidate-->campaign(campaign_candidate_mapping let's call it ccm table). Also we have candidate related tables like candidate_education_details, candidate_company_details, etc. we want to send the data to elasticsearch related to candidate-job-campaign as one document. What will be the best way to send relatable data for candidate from multiple tables to ElasticSearch?
We are planning to create a table having all the denormalised data related to that table in a row which we need during search. Every time we update any candidate related data from above tables, we need to update it on ElasticSearch. So now we have to maintain this denormalised table and need extra code to update this table, is it the right approach?
What's the standard way to update the search engine, how does big companies do this? Please help, any suggestions would be appreciated.