https://kotlinlang.org logo
#server
Title
# server
m

Mod

11/02/2023, 5:59 AM
Hello everyone I have a ktor application, and I'm connecting to postgres DB and using pgJDBC and HikariDataSource, and using exposed ORM. what I'm trying to do is batch inserts for preformance, but I couldn't know what is the problem, it keeps running a separate insert statement. as per documenation in exposed https://github.com/JetBrains/Exposed/wiki/DSL#batch-insert > NOTE: The batchInsert function will still create multiple INSERT statements when interacting with your database. You most likely want to couple this with the rewriteBatchedInserts=true (or rewriteBatchedStatements=true) option of your relevant JDBC driver, which will convert those into a single bulkInsert. You can find the documentation for this option for MySQL here and PostgreSQL here. I checked postgres documentation https://jdbc.postgresql.org/documentation/use/ and I added this flag like this
Copy code
private fun createHikariDataSource(
        url: String,
        driver: String,
        user: String,
        password: String,
        maxPoolSize: Int,
        autoCommit: Boolean
    ) = HikariDataSource(HikariConfig().apply {
        addDataSourceProperty("reWriteBatchedInserts", true)
        driverClassName = driver
        jdbcUrl = url
        username = user
        this.password = password
        maximumPoolSize = maxPoolSize
        isAutoCommit = autoCommit
        isReadOnly = false
        transactionIsolation = "TRANSACTION_REPEATABLE_READ"
        addDataSourceProperty("reWriteBatchedInserts", true)
        validate()
    })
and tried to pass the flag in the url like
Copy code
val connectionPool = createHikariDataSource(
            url = "$jdbcURL/$defaultDatabase?reWriteBatchedInserts=true",
            driver = driverClassName,
            user = username,
            password = password,
            maxPoolSize.toInt(),
            autoCommit.toBoolean()
        )
and still the batch statement won't work
d

Dominik Sandjaja

11/02/2023, 5:05 PM
Did you verify with Postgres' logging that the statements do not work? For me it works with those properties set, but I only see the differences in the Postgres log, NOT in the Exposed logs. The Exposed logging still shows two separate
INSERT INTO ...
m

Mod

11/02/2023, 5:07 PM
yes I believe the exposed logs is the problem
d

Dominik Sandjaja

11/02/2023, 5:07 PM
This is specifically mentioned in the documentation:
If you want to check if the
rewriteBatchedInserts
+
batchInsert
is working correctly, check how to enable JDBC logging for your driver because Exposed will always show the non-rewritten multiple inserts. You can find the documentation for how to enable logging in PostgreSQL here.
m

Mod

11/02/2023, 5:08 PM
thanks
d

Dominik Sandjaja

11/02/2023, 5:16 PM
In case you want to validate that it works with testcontainers, I found this blog page helpful: https://maciejwalkowiak.com/blog/postgresql-testcontainers-sql-query-logs/
m

Mod

11/02/2023, 7:30 PM
thanks, that is helpful