I would like to add one more info. We are using database replication and this issue is happening only on primary server. If we try select on our stand by copy (exact replica of our database) the query doesn't get stuck and runs quickly.
Bug reference: 17755 Logged by: Pavol Sekeres Email address: pavol.sekeres8@gmail.com PostgreSQL version: Unsupported/Unknown Operating system: CentOS Linux 7 (Core) Description:
Hello,
we are using PostgreSQL version 9.6.8 on our production environment which we operate ourselves. We are facing issue with the PostgreSQL database. When we use select/insert query on a specific table it runs fine 99.99% of the time. But we are doing many inserts per day from our app (around 100.000) and it happens to us almost every week that some query to this table gets stuck. Then you can list it using pg_stat_activity and see there that it is running long(hours, days, even weeks). From this point the only option we have is to kill this process in which the query runs. Unless we do this, the database gets busy by this process infinitely running query and the cpu usage rises dramatically causing database drop-outs. The problem is just with this table and we are using many in our database. Some further information is: this problematic table is called request_datas and has just two columns : 1. request_id - is unlimited varchar containing the string representation of uuid, 2. request_data - is again unlimited varchar containing string representation of JSON object We noticed that the insert/select queries which get stuck and run infinitely have specific request_id - starting with 'a6bda' for example 'a6bda748-0e31-4da6-8fed-331c6c4a7cdb'. Also we are not always querying the database directly but we are using java app with JDBC connections. However the select query gets stuck even when triggered from the psql console.
From our point of view we are using the database normally and there is no reason why simple select query where id is equal to some value should get stuck and run infinitely. Do you know what could be the cause for our problems and possibly suggest any solution?