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:
- create UNLOGGED table WITH (autovacuum_enabled=false)
- 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?