Analytics Layer Transformation with AWS DMS and RDS PostgreSQL
About the Client
A 2Safe is a company specializing in debt collection solutions, with a strong presence in the credit recovery sector. Its operations rely on intensive analysis of data extracted from its main system, hosted on an Oracle database, to feed Power BI dashboards and guide operational decisions.
With growing data volume and increasing query complexity, 2Safe faced performance bottlenecks and rising Oracle licensing costs. To address this, the company sought a efficient way to replicate analytical data to an optimized and scalable environment, maintaining the integrity and currency of the information.
Business Challenge
2Safe needed to enable a safe environment quick and economical consultation to feed reports and dashboards in Power BI, without affecting the performance of the Oracle transactional environment.
Problems faced:
- High load on the source database due to analytical queries.
- Elevated licensing for Oracle, even for secondary operations.
- Difficulty scaling reports with satisfactory performance.
- Complexity of maintaining external pipelines for ETL.
2Safe's objectives with the new solution:
- Continuously replicate data from Oracle to PostgreSQL.
- Guarantee low latency between data refresh at the source and availability in Power BI.
- Improve analytical query performance.
- Reduce operating costs and licensing.
- Implement a scalable and maintainable architecture.
Implemented Solution
Flexa Cloud proposed and implemented a solution based on AWS managed services, focusing on operational simplicity, scalability and cost reduction.
Architecture Used:
AWS Database Migration Service (DMS)
- Replication mode: Full Load + CDC (Change Data Capture).
- Main task: teste-cdc, created to run in production with continuous replication.
- Applied transformations: schema renaming (DRCOB_ADM → lnd), lowercase names, precise control of replicated tables (32 in total).
- Logs and failures tracked via Amazon Cloud Watch.
AWS Schema Conversion Tool (SCT)
- Used for converting types and structures between Oracle and PostgreSQL.
Amazon RDS for PostgreSQL
- Target bank optimized for reading, with automatic backup, high availability (Multi-AZ) and adequate performance for analytical loads.
Amazon VPC and Subnet Group
- Isolated and secure infrastructure to ensure connectivity between Oracle and PostgreSQL, using subnet groups in DMS.
Technical Challenges and Solutions
| Challenge | Applied Solution |
| Enable CDC in Oracle (Supplemental Logging Not Enabled) | Manual configuration of global and per-table supplemental logging (including ALL columns for tables without PK) |
| Intermittent failures on Sundays | Complete task reset and complete cleanup of the lnd schema, with scheduled restart. Monitoring became the responsibility of support. |
| Tables with a large number of LOBs | Limited replication configuration of LOBs up to 32KB, ensuring performance without requiring Full LOB mode |
| Type compatibility between Oracle and PostgreSQL | Treated via SCT and manual mapping with technical monitoring |
Obtained Results
Efficiency
- Replication task with parallelization in 8 simultaneous subtasks, optimizing Full Load.
- CDC with low latency, synchronizing changes in near real time.
- Centralized and traceable logs via CloudWatch.
Analytical
- Power BI dashboards now operate with replicated data safely, without generating load on Oracle.
- Significant improvement in performance of queries.
Financial
- Reduction in the use of Oracle licenses, since analytical queries were directed to PostgreSQL.
- Costs under control with use of optimized DMS instance (~US$65/month N. Virginia, ~US$130/month São Paulo).
Results in Numbers
- (I.e. 32 replicated tables with ~200MB of data.
- ️ 2h initial load (Full Load).
- 💡 Updated data in near real time via CDC.
- (I.e. Zero impact in the Oracle database after migrating analytical consumption.
Benefits of AWS Architecture for SMBs
- Operational simplicity with managed services
- Scalability without code rewriting
- Predictable and on-demand cost
- Security with full network control (VPC)
- Integrated monitoring via CloudWatch
- Support for future evolution with decoupled architecture
About Flexa Cloud
A Flexa Cloud is an AWS partner specializing in solutions for SMEs, focusing on high-efficiency projects, automation, data analysis, and cloud migration. We work with a certified technical team and methodologies that ensure agile, well-documented deliveries.
