Improving Million‑Row Insert Performance with Spring Boot ThreadPoolTaskExecutor

Purpose : Increase the efficiency of inserting millions of rows into PostgreSQL by leveraging multithreaded batch insertion using Spring Boot's ThreadPoolTaskExecutor .

Technology Stack :

Spring Boot 2.1.1

MyBatis‑Plus 3.0.6

Swagger 2.5.0

Lombok 1.18.4

PostgreSQL

ThreadPoolTaskExecutor

Thread Pool Configuration (application‑dev.properties) :

# 异步线程配置
# 配置核心线程数
async.executor.thread.core_pool_size = 30
# 配置最大线程数
async.executor.thread.max_pool_size = 30
# 配置队列大小
async.executor.thread.queue_capacity = 99988
# 配置线程池中的线程的名称前缀
async.executor.thread.name.prefix = async-importDB-

Executor Bean Definition :

@Configuration
@EnableAsync
@Slf4j
public class ExecutorConfig {
    @Value("${async.executor.thread.core_pool_size}")
    private int corePoolSize;
    @Value("${async.executor.thread.max_pool_size}")
    private int maxPoolSize;
    @Value("${async.executor.thread.queue_capacity}")
    private int queueCapacity;
    @Value("${async.executor.thread.name.prefix}")
    private String namePrefix;

    @Bean(name = "asyncServiceExecutor")
    public Executor asyncServiceExecutor() {
        log.warn("start asyncServiceExecutor");
        ThreadPoolTaskExecutor executor = new VisiableThreadPoolTaskExecutor();
        executor.setCorePoolSize(corePoolSize);
        executor.setMaxPoolSize(maxPoolSize);
        executor.setQueueCapacity(queueCapacity);
        executor.setThreadNamePrefix(namePrefix);
        executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
        executor.initialize();
        return executor;
    }
}

Asynchronous Service Implementation :

@Service
@Slf4j
public class AsyncServiceImpl implements AsyncService {
    @Override
    @Async("asyncServiceExecutor")
    public void executeAsync(List
logOutputResults, LogOutputResultMapper logOutputResultMapper, CountDownLatch countDownLatch) {
        try {
            log.warn("start executeAsync");
            // async work: batch insert
            logOutputResultMapper.addLogOutputResultBatch(logOutputResults);
            log.warn("end executeAsync");
        } finally {
            countDownLatch.countDown(); // ensure latch release
        }
    }
}

Batch Insertion Logic :

@Override
public int testMultiThread() {
    List
logOutputResults = getTestData();
    // split every 100 records into a sub‑list
    List
> lists = ConvertHandler.splitList(logOutputResults, 100);
    CountDownLatch countDownLatch = new CountDownLatch(lists.size());
    for (List
listSub : lists) {
        asyncService.executeAsync(listSub, logOutputResultMapper, countDownLatch);
    }
    try {
        countDownLatch.await(); // wait for all threads
    } catch (Exception e) {
        log.error("阻塞异常:" + e.getMessage());
    }
    return logOutputResults.size();
}

Performance Test : Inserting 2,000,003 rows.

Multithreaded (30 threads) execution time: 1.67 minutes .

Single‑threaded execution time: 5.75 minutes .

Data integrity checks confirmed no duplicate IDs and complete insertion.

Conclusion : Multithreading reduces insertion time dramatically (≈70% faster). However, increasing thread count indiscriminately does not always improve performance; a practical rule of thumb is CPU cores × 2 + 2 threads.

Test Environment (hardware specs shown in the attached image):

For further details, refer to the original article source.