In a recent project, I had the task of safely and quickly migrating large volumes of data coming from external systems into the internal master table of the system. In this process, I encountered technical limitations and how I resolved them through PostgreSQL's Stored Procedure and optimization techniques. I would like to share this based on detailed architecture and implementation code.
-
Limitations of Existing Application (Java/Spring) Level Batch Processing
In the initial design phase, we first examined how to implement the batch process in the familiar Java and Spring Boot environment. This is because the method of processing data in chunks using tools like Spring Batch is popular. However, as we reviewed the architecture, clear limitations such as the following became evident.
1-1. Serious Network Overhead (Network Overhead)
During the process of retrieving tens of thousands or even millions of data points coming from external systems and comparing them with existing data, frequent network communication (Round-Trip) occurred between the WAS (Web Application Server) and the DB. This not only consumed network bandwidth but also caused delays in the overall operation time due to latency.
1-2. WAS memory burden and OOM (Out Of Memory) risk
Loading a large number of entities or DTOs into Java memory (Heap) at once can significantly increase the load on garbage collection (GC), and there is a risk that a java.lang.OutOfMemoryError may occur momentarily, causing the entire application to crash.
1-3. Limits of Application Operations
Due to performing complex business logic (comparison, validation, mapping) for each row of data within the application loop, the migration performance has significantly decreased as the internal indexes and operational engines of the database could not be maximally utilized.
[Selected Solution: PostgreSQL Stored Procedure]
To overcome these limitations, we have concluded that "let's process the logic directly within the space where the data exists (DB)." This has been supported since version 11 of PostgreSQL.Stored Procedureunlike the existing functionExplicit transaction control (COMMIT, ROLLBACK) is possible internallyThis has been adopted as the optimal solution to ensure data integrity by minimizing the pressure on DB resources (Undo, Lock, etc.) by executing commits in chunks during large batch operations.
|
Comparison Items |
WAS Level Processing (Java/Spring) |
DB Level Processing (PostgreSQL Procedure) |
|---|---|---|
|
Data Movement |
DB → WAS → DB (Bulk Movement) |
Completion Inside DB (Minimizing Movement) |
|
Network Load |
Very High (Row-wise Queries or Bulk Transfer) |
Very Low (Procedure Call and Result Reception) |
|
Memory Management |
WAS Heap Memory Pressure (OOM Risk) |
Utilization of DB Buffer Pool and Shared Memory Optimization |
|
Transaction Control |
Spring @Transactional or Batch Transaction |
Explicit COMMIT Control at Each Step in Procedure |
-
System architecture and data flow
The overall data pipeline is designed with a two-stage structure.
2-1. Staging phase
Bulk loading raw data from external systems into a temporary staging table.
2-2. Migration Stage
When a Java application calls a PostgreSQL procedure with parameters, it compares and analyzes the staging_table and target_table within the database to process and reflect (Upsert/Update/Insert) in the final master table.
-
Application Process
The implementation processes the data and reflects it.PostgreSQL procedureWow, call this and receive the result count.Java applicationIt consists of code. To aid understanding, complex business columns have been replaced with simplified example data.
3-1. Implementing PostgreSQL Procedure
This is the logic for updating the target table (target_table) based on the data from the staging table (staging_table). Using the WITH clause (CTE) and the IS DISTINCT FROM clause, it selects only the data with changes to perform a Bulk Update.
3-2. Implementation of Java (Spring) Call Code
I used a JdbcTemplate-based CallableStatement to call the procedure. I passed the IN parameters and safely returned the results (number of records processed) calculated inside the procedure as OUT parameters, converting them to a Map object.
-
Problem-solving experience
4-1. Preventing unnecessary updates: Utilizing IS DISTINCT FROM
-
Problem situation: When large amounts of data are pushed in every time with an UPDATE query unconditionally, write operations occur for records with no changes, leading to unnecessary accumulation of WAL (Write-Ahead Log) and degraded index performance.
-
Solution: I applied PostgreSQL's ROW() IS DISTINCT FROM ROW() syntax. This approach prevents exceptional situations that occur when comparing NULL values (e.g., NULL = NULL is False) and filters so that updates are performed only when at least one of the target fields differs, improving performance.
4-2. Explicit COMMIT control within the procedure
-
Problem situation: During large-scale migration, if an error occurred, the recovery risk was high, and bundling into one giant transaction put a lot of pressure on DB resources.
-
Solution: This is the key reason for adopting Procedure instead of PostgreSQL Function. By explicitly stating the COMMIT; command inside the procedure at the end of each business stage, I properly released system resources and ensured step-by-step data integrity. Additionally, in Java, I maintained control over commit within the procedure by setting conn.setAutoCommit(true).
-
Results and conclusions
As a result of switching to a Stored Procedure-centric architecture, we achieved remarkable improvements in migration performance metrics.
5-1. Improvement in Processing Speed
The overall migration execution time has been reduced compared to the previous Java application running in a `Loop + Bulk Insert` manner.
5-2. Resource Optimization
In terms of infrastructure costs, the CPU/Memory utilization of the WAS has stabilized, and due to efficient index scanning and minimized tuple management within the DB, disk I/O has shown a stable downward trend.
PYS