Debian Question posted in
The oficial documentation can be found here.

Postgresql 11 row insert low performance using jdbc – Debian

I am trying to improve the performance of row insertion in a postgresql 11 db using standard java prepared statements.
It is standing on top of debian 11 in a VM environnement.
I am using pentaho data integration and a simple table output step.
I want to make my transformation database transactional so using batch update is not an option.
I have created tables without index, nor constraints and made then unlogged. They have a few columns.
I am currently getting row insert top speed of 40 r/s per connection.
I can increase this speed by using parallelism and each connection tends to reach this speed.

The server is running smoothly using 3% cpu and about 10% RAM.

How can I increase the "per connection" row insert performance ?

Thanks for you help

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found out that it came from my connection to the server. I was using a ssh tunnel (I should have stated it, sorry). Using a direct connection, I get decent insert times. I did not suspect it as I was having good performance using batches.

    Thanks for your answers


  2. 0

    It sounds like you are bottlenecked by synchronous write speed. That makes the solution easy. Buy storage hardware that supports faster fsync speed and put your pg_wal directory on that storage.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search