Postgresql table with BigInt primary key column : slow insert

Question

I have a table with "BigInt"(int8) primaryKey column, insert on this table is extremely slow, and it's keep degrading as the number of rows increases.

Some more details:

• Postgres version: 10.7
• No separate index on table
• Number of inserts per min ~2000, it degrades with more rows in data
• We have around 25k records, we tried by truncating data and inserting from zero row, but no improvement
• We use Spring-data-jpa to insert the data (.save()) method. SpringBoot: 2.2.4.RELEASE/Hoxton.SR1
• Hikari datasource with max-pool-size: 50.
• column is mapped to java.math.BigInteger in entity
• single row inserts (not batch)

The issue seems with BiGInt column itself, when we change the column type from BigInt to varchar, slowness disappeared, 25k records inserted in one minute.

am I doing anything wrong with BigInt column ? why BigInt type makes insert so slow ?

any insights and help is appreciated.

Thanks!

Answer 1

It was caused by wrong mapping:

Postgresql:BingInteger(int8) --> Java.math.BigInteger ❌

Correct Mapping

Postgresql:BingInteger(int8) --> Java.lang.Long ✅

Got hint from this post: mailing-list