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!

Can we have EXPLAIN (ANALYZE, BUFFERS) output for a fast ans a slow insert? You can use auto_explain to get that. — Laurenz Albe
– Laurenz Albe, Commented Mar 14, 2020 at 4:22
It gets slower with more rows, but doesn't get faster with less rows? — jjanes
– jjanes, Commented Mar 14, 2020 at 14:43
@LaurenzAlbe - here's explain output : Insert on xxxx (cost=0.00..0.01 rows=1 width=1502) (actual time=0.122..0.122 rows=0 loops=1) Buffers: shared hit=12 -> Result (cost=0.00..0.01 rows=1 width=1502) (actual time=0.001..0.001 rows=1 loops=1) Planning time: 0.038 ms Execution time: 0.183 ms — Suresh Naik
– Suresh Naik, Commented Mar 14, 2020 at 17:33

Suresh Naik · Accepted Answer · 2020-03-16 12:03:38Z

3

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

answered Mar 16, 2020 at 12:03

Suresh Naik

3051 gold badge4 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql table with BigInt primary key column : slow insert

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related