Rails Sync Postgresql and Elasticsearch Database of a Model

Hi, This is Shubham Mishra from India,  this is the part of Ruby on Rails exploration journey.  In this post we will discuss about how can we keep Postgresql (a SQLDB) and Elasticsearch (a nonSQL DB) with the help of a background job processor and I am sure you will get excited to make your hands dirty with those code, so why to wait let's get started...

Most of the time due to increase in DB record size we try to split our DB into two DB based on the requirement like one is Transaction oriented (SQL DB) and another Search oriented (Non SQL DB) with limited data. In my case we have Postgresql for storing all the records and used for entire business operation and for Search support we have elasticsearch with minimum indexes (tables) and their entire documents. For better understanding lets consider in PG DB we have Employee table with id, name, dob, address etc and some more other tables, whereas in Elasticsearch we have only Employee index (table) with all its documents duplicated from PG DB row's. Now when customer use search feature to search by employee's name we make request to elasticsearch and rest of all business operation on PG DB.

Problem:

Now with this approach we may face DATA inconsistency as record (emp's name) updated in PG DB but its still old record in elasticsearch DB. So to avoid it we need to make sure of keeping elasticsearch also in sync with PG DB, but how lets see below approach,

Solution:

To achieve this, you can use a combination of ActiveRecord callbacks and the Shoryuken gem to create a background job that updates the corresponding Elasticsearch document when an employee's name is updated in Postgres.

Here are the general steps you can follow to implement this:

1. The Employee model in your Rails application that maps to both your PostgreSQL database and your Elasticsearch index, Make sure to configure the searchkick gem to use your Elasticsearch index. Why we are using searchkick gem is to make elasticsearch query too easy to operate.


2. Use the after_commit callback provided by ActiveRecord to trigger a background job that updates the corresponding Elasticsearch document. This can be done by creating a job class that takes in the employee_id and the updated name value as arguments, and updates the Elasticsearch document using the update method provided by the searchkick gem.
   For example, you could define a job class like this:
   
   
   In this example, the update_document method is a custom method you would define in your Employee model that uses the update method provided by searchkick to update the Elasticsearch document.


3. In your Employee model, define an after_commit callback that triggers the background job when the employee's name is updated in the PostgreSQL database.
   
   
   In this example, the update_elasticsearch_document method is triggered by the after_commit callback, and it creates a new instance of the UpdateEmployeeDocumentJob class with the id and updated name values as arguments. The update_document method is a custom method that updates the name column in the PostgreSQL database and reindexes the Elasticsearch document using the reindex method provided by searchkick.


4. Use the shoryuken gem to configure a worker process that processes the background jobs created by the UpdateEmployeeDocumentJob class. This can be done by defining a worker class that includes the Shoryuken::Worker module and specifying the queue that the worker should listen on. For example, you could define a worker class like this:
   
   
   In this example, the worker class listens on the default queue, and it calls the perform method of the UpdateEmployeeDocumentJob class to perform the background job.


5. Start the Shoryuken worker process using the shoryuken command provided by the shoryuken gem. You can run this command in a separate terminal window or as a background process using a process manager like systemd.

With these changes in place, any updates to an employee's name in the PostgreSQL database will trigger a background job that updates the corresponding Elasticsearch document using the searchkick gem. The job is processed by the Shoryuken worker process, which ensures that the update is performed asynchronously in the background

You can explore my previous blog: Rails Postgresql Query timeout set and skip during migration

Or you can explore my other blogs too here,

Get to know answers for common search on Google : A blog for posts which can help you for daily life problems, such as where to get free images, Topic suggestion for the blog.

Computer Science algorithms and other knowledge share : A blog for posts such as best search algorithm, Top interview questions for diffrent technologies, knowledge share for some frameworks or programming languages for the interview or in general terms.

My ideas to solve real world problems : A blog where me shared and presented my ideas to solve a real world problems, this will be interesting for me.

Ruby on Rails Web development Blog : As the name suggest, it is the blog for sharing few knowledge about RoR web development framework.

Liked my blogs, wanna to connect:

LinkedIn  |  GitHub  |  HackerEarth  |  ResearchGate  |  Twitter  |  Facebook  |  StackOverflow

Thanks for reading this post, Have a good day :)