#StackBounty: #insert #postgresql-performance Postgresql sudden slow down on INSERTs

Bounty: 50

I have a very odd situation with one of my Postgres databases. The server itself is quite powerful, it’s 24 SSD NAS divided into 2 LUNs of 12 drives each – a very good IOPS setup. It is connected via 10g network card to the 128-threaded server with 256 GB of RAM, running on Win 2019.

During the day, randomly, I am affected by the sudden slowdown of transaction inserts, a query like this:

INSERT INTO public.transactions(  col1,  col2,  col3, col4,  col5,  col6,  col7,  col8,  col9,  col10,  col11, col12)  
VALUES (  $1, $2, $3, $4,  $5, $6, $7, $8, $9, $10, $11, $12) 

Under normal circumstances, time is close to 0. In the period of problems, 2-3 mins, it can be between 5 and 20 secs, destroying the transaction system. Then suddenly it recovers and works super stable. CPU, RAM, disk queues are not a problem. I don’t spot in the logs any significant slow down of other queries, no vacuuming taking place. I have even installed some pg extensions to get execution plans of queries long than x ms, but in case of INSERT you can only get:

Insert on transactions(cost=0.00..0.01 rows=1 width=313)

Lock monitoring only shows that the same queries are blocking each other, but it’s quite odd as they are independent.

I don’t expect a solution to my problem, as I know it’s impossible, but maybe some more hints how I can investigate such issue? I think I tried every approach but still feel blind and lost when it happens.

Get this bounty!!!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.