Oracle to PostgresSQL: Real-Time Replication Case Study
How One of the Largest Mutual Fund Houses in India Integrated Oracle with PostgresSQL for Real-Time Analytics
TO THE NEW is a digital technology company that provides end-to-end product development services. They help businesses digitally transform by leveraging the power of experience design, cutting-edge engineering, and cloud to build disruptive web and mobile apps.
TO THE NEW engaged with one of the largest mutual fund houses in India to define a bespoke digital transformation strategy. TO THE NEW helped them with a comprehensive technology implementation which included future-ready distributor and investor portals for both web and mobile (iOS and Android), and the migration of the existing website to Drupal.
The key to mutual fund houses are the investors and distributors who perform transactions on mutual fund schemes. The digital mediums comprising the investor and distributor portals offer the following features:
- View portfolio details and performance dashboard for investors
- Perform different types of transactions, create folios
- View portfolio details and performance dashboard
- Manage investor details and portfolio information
The information presented at the digital mediums being built by TO THE NEW for the mutual fund customer was comprised of schemes, transactions, folio, investor/distributor mappings among other details.
The portals used PostgreSQL DB running on AWS public instance. The total data size amounted to 1TB with 100GB added every year.
Most of the data in the portals was provided by a centralized RTA (Registrar and Transfer Agent) which are the institutions or companies that maintain detailed records of mutual fund investors. They basically handle investors’ data on behalf of the mutual fund houses.
The RTA provided some of the required information via REST APIs and for others, an Oracle Database was provided. The database was built over Oracle Database 11g, hosted in a private data center and maintained by the RTA data operations team.
To avoid depending on an external database and for performance reasons, TO THE NEW put a mechanism in place for data replication and transformation between the RTA Oracle Database and PostgreSQL Database. To avoid heavy computation and time-intensive operations, the previous replication was selective and executed in batch mode on predefined frequencies.
The table below depicts the schedule and time taken in each of the above activities.
This approach caused inconsistencies in information between our portals and the RTA portals leading to poor user experience and complaints. After reviewing the selective replication strategy we discovered several cases where our current set up was lacking.
Instead of looking at specific problem fixes, TO THE NEW re-examined the replication strategy and explored more robust and scalable ways for meeting business needs.
First, TO THE NEW tested a bespoke JVM based solution:
Replication of tables from Oracle to PostgreSQL would be based on changelog (timestamp recorded at SPHMF) irrespective of logged-in users or whether the data is for the investor or distributor. This replication would be done through a batch process on predefined intervals. No transformation will be applied while pulling the data.
On implementing this solution, TO THE NEW found the following issues:
- The solution was a pull-based mechanism, as a result, there was constant data lag between the PostgreSQL Database and Oracle Platforms.
- The integrity of data could not be guaranteed.
In parallel, TO THE NEW was exploring alternative data integration and replication solutions. A successful pilot with HVR proved that it could successfully provide real-time replication between PostgreSQL and Oracle.
HVR replicates data using log-based change data capture as well as non-intrusive direct capture of the Oracle Server transaction log. The Oracle production database is a sensitive legacy database, working under a heavy load. HVR was able to minimize latency on these very busy transaction processing systems.
Real-Time Data Replication
Replication Frequency & Execution Time
Monitoring and Alerting of Replication Processes
TO THE NEW utilized HVR’s reporting and monitoring features to create an alert when latency reached ten minutes. The alerts were integrated with the mutual fund’s communication tool, so anyone could monitor and take appropriate action as needed.
The use of HVR as part of the investor and distributors portals that TO THE NEW built, has transformed the service to be truly real-time to meet the needs of the business and the end-users.
Want to see how HVR can speed up data availability for your organization? We invite you to take a Test Drive.