0

I'have rather odd case. I have a "cache" table in Postgres and I need to insert 100K - 1M records in parallel from different sources. Sources can try to insert duplicated data (by Primary key). I'm too lazy to write complex sync code around INSERT process that is why I do it this way:

  1. create UNLOGGED table WITH (autovacuum_enabled=false)
  2. do insert into TABLE (foo,bar) values (1,2) on conflict do nothing.

I'm fine with lower perf compared to the COPY command since writing synchronisation logic is 100 times more expensive from business point of view than slow insert. We can live with relatively slow inserts.

BUT the performance is waaay to slow.

  • It takes around 12.000 ms to insert 50.000 rows. around 4-5 records per millisecond.
  • Each row has 150 columns.
  • insert batch size in 50.000
  • Table has single PK (I can't drop it)
  • The pattern is to insert 100K-1M records.
  • Table won't grow more than 10M records.
  • Postgres version is 13.2
  • I'm using JDBC driver 42.0.0
  • Table has only on PK (varchar, long)

Why is it so slow? Can I do something with it?

Improve this question
5
  • why do you think that it is slow? copared to what ? 150 columns is a lot
    – nbk
    Apr 5, 2021 at 20:04
  • 4 row per millisecond is slow. I expect at least 20-30 per millisecond. What if I merge all 150 columns (except PK columns) into single column. Would it speed up insertion?
    – Capacytron
    Apr 6, 2021 at 8:30
  • but not with 150 colums, bedises constraints can have also its toll at the performance. Every Rdms handles Bulk inserts in their documentation, see first there what you can do
    – nbk
    Apr 6, 2021 at 9:52
  • Right, Postgres has COPy command, but I can't use it since COPY doesn't handle duplicated PKs. Anyway, I'll try to merge columns
    – Capacytron
    Apr 6, 2021 at 11:59
  • 1
    a table with 160 columns is rare, maybe with normalization and subtables you can reduce the number and get so much more speed
    – nbk
    Apr 6, 2021 at 13:46

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.