Postgresql performance issues when issuing many small inserts and updates one at a time

Question

I have a production web server with postgresql database.

My server gets data from another server every hour on the hour. The other server wakes up and sends many requests to my server, each result in an insert/update to the postgresql database.

In order to avoid overloading my server, the requests are queued and handled one at a time.

So typically my server is doing (not many) reads from the database most of the time, and every hour on the hour it runs many inserts and update, one after the other.

The problem I'm experiencing is that once the updates/inserts start, the performance of the database gets very bad for a couple of minutes and then, even though the updates/inserts continue, the performance gets better and stay at a good level until the updates/inserts are finished.

I asked around and got some good directions that may explain the degradation in the performance of the webserver itself, but since I'm seeing that the database's performance also degrades, there might be more to this.

What can explain this behaviour? How can I fix this?

Answer 1

In order to avoid overloading my server, the requests are queued and handled one at a time.

That's the problem right there. You are not avoiding but causing overloading this way. Single row INSERT / UPDATE is dramatically more expensive than doing the same en bloc. Each statement has to be planned and executed separately. Depending on missing details there may be transaction overhead and even connection overhead, too.

Try to run multi-row INSERT / UPDATE commands instead. Or use COPY, or one of the fastest options would be to define a foreign table and fetch all data from a remote DB directly in a single SQL command (if that's at all possible).

Or depending on details, some other replication method may fit your needs.

This is how you update multiple rows at once:

• Update multiple rows in a table from another table when condition exists

And here is how to do that from a CSV file:

• How to update selected rows with values from a CSV file in Postgres?