I am trying to optimize the reuse of PostgreSQL connections by implementing the r2dbc Connection Pool for jOOQ. But I noticed that transaction execution speed tends to be better whenever I use just a ConnectionFactory without ConnectionPool.
My configuration of СonnectionPool to transfer them to jOOQ DSL:
// Connection Factory
val dbConnections = 145 // Max number of connections
val connectionFactory: ConnectionFactory = ConnectionFactories.get(
ConnectionFactoryOptions
.parse("r2dbc:postgresql://$dbHost:$dbPort/$dbName")
.mutate()
.option(ConnectionFactoryOptions.USER, dbUser)
.option(ConnectionFactoryOptions.PASSWORD, dbPassword)
.build()
)
// Connection Pool
val configuration = ConnectionPoolConfiguration.builder(connectionFactory)
.maxIdleTime(Duration.ofMinutes(30))
.initialSize(dbConnections)
.maxSize(dbConnections)
.maxCreateConnectionTime(Duration.ofSeconds(1))
.build()
val connectionPool = ConnectionPool(configuration)
// jOOQ DSL Context
val dslContext = DSL.using(DefaultConfiguration().set(connectionPool).set(SQLDialect.POSTGRES))
Implementation parallel chunked insertions to DB:
coroutineScope {
measureTime("Uploading ${orders.count()} orders to the database using $dbConnections of database connections") {
orders.chunked(orders.size / dbConnections).map { chunk ->
async {
orderDao.saveAll(chunk)
}
}.awaitAll()
}
}
Implementation of saving all items in OrderDao in a transaction:
suspend fun saveAll(chunk: List<Order>) {
ctx
.transactionCoroutine { trx ->
chunk.forEach { order ->
val orderRecord = insertOrder(trx, order)
insetRecordChildren(trx, order, orderRecord)
}
}
}
As a result of executing the code, I get the following result:
Total orders: 19992. Data size: 103 MB. Average row size: 5 KB
Uploading 19992 orders to the database using 145 database connections:
43.293s spent.
And when I execute the same code with the same parameters by passing the сonnectionFactory to jOOQ DSL instead of connectionPool, I get a much better result.
DSL.using(DefaultConfiguration().set(connectionFactory).set(SQLDialect.POSTGRES))
27.426s spent.
I can't figure out why using connection Pool gives a worse result. Maybe someone has an idea what I'm doing wrong. I would really appreciate it!