Increase data insert speed of PostgreSQL

Question

I am stuck in a problem that PostgreSQL data writes are very slow.

I developed my application in Java (using JDBC) to insert data into a PostgreSQL DB. It works well on our remote development server. However, after I deploy it to the production server, it causes a problem.

The insert speed of PostgreSQL on the production server is only ~150 records/s for 200000K records, while it is ~1000 records/s for the same data set on the development server.

Firstly, I tried to change the configuration in postgresql.conf as follows:

effective_cache_size = 4GB
max_wal_size = 2GB
work_mem = 128MB
shared buffers = 512MB

After I changed the configuration and restarted, it only affects the query speed, while the insert speed does not change (~150 records/s).

I have checked my server memory info, there is a lot of free memory ~4GB. The inserter only uses 0.5% of 8GB (~40MB).

So my questions are:

Is this a problem of a storage disk, such as SSD and HDD or virtual and physical etc.? Why is the insert speed still very slow, although I have changed the configuration? Is there any way for increasing the insert speed?

Note: the problem does not relate to the insert query structure. I have used the same query in the same condition elsewhere (I set up an environment in 2 servers in the same way). I do not know why the DEVELOPMENT server (4GB) works better than the PRODUCTION server (8GB).

Do you insert one row at a time in a loop in your frontend ? — joop
– joop, Commented Sep 28, 2018 at 8:53
Yes, I did, I am considering 1000 data rows with 1000 insert expression compared to 1000 data rows with only 1 insert expression which one is better. However, It looks like using an expression separately easy to control a duplicate inserted data by using ON CONFLICT rather than the other. Do you have any idea? — Duy Huynh
– Duy Huynh, Commented Sep 30, 2018 at 23:51

Laurenz Albe · Accepted Answer · 2018-09-28 05:07:48Z

8

The only one of your parameters that has an influence on INSERT performance is max_wal_size. High values prevent frequent checkpoints.

Use iostat -x 1 on the database server to see how busy your disks are. If they are quite busy, you are probably I/O bottlenecked. Maybe the I/O subsystem on your test server is better?

If you are running the INSERTs in many small transactions, you may be bottlenecked by fsync to the WAL. The symptom is a busy disk with not much I/O being performed.

In that case batch the INSERTs in larger transactions. The difference you observe could then be due to different configuration: Maybe you set synchronous_commit or (horribile dictu!) fsync to off on the test server.

answered Sep 28, 2018 at 5:07

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

4 Comments

Duy Huynh Over a year ago

I haved used iostat command to check. The result is: PRODUCTION: %user: ~10 %iowait: ~40 %idle: ~40% DEVELOPMENT: %user: ~10 %iowait: ~16 %idle: ~75%

Duy Huynh Over a year ago

wkB/s is totally different: ~1000 wkB/s for PRODUCTIOn and ~5000wkB for DEVELOMENT

Laurenz Albe Over a year ago

Yes, you are clearly I/O bound. So my answer should help.

Duy Huynh Over a year ago

Thank you so much. Now I know what is the problem

Collectives™ on Stack Overflow

Increase data insert speed of PostgreSQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related