Before i used 9.3.5 and servers with ubuntu 12 with 32GB memory.
After upgrade to 9.4.1, with more power server 60GB memory on each in wall replication and ubuntu 14, started get db stucks when run multi update from resque/sidekiq background workers. resque/sidekiq work via pgpool 3.3
Queries can be as simple, update column where primary id = id. and complex, doesn't metter. usually average connections about less 300, but when queries stucks, count connections grow to maximum and i get
LOG: process 16121 still waiting for ShareLock on transaction 2448707428 after 1000.121 ms DETAIL: Process holding the lock: 16139. Wait queue: 16121. LOG: process 16146 still waiting for ExclusiveLock on tuple (665346,11) of relation 997395 of database 16455 after 1000.102 ms DETAIL: Process holding the lock: 16121. Wait queue: 16146. ERROR: canceling statement due to lock timeout
FATAL: sorry, too many clients already
then only restart help, when run restart got this
WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command.
Database size 190GB max_connections = 400 shared_buffers = 13GB work_mem = 19660kB maintenance_work_mem = 2GB effective_cache_size = 40GB
But i played with different, didn't help. On 9.3 all was fine. Nothing more in logs.
Also in one day i did pg_repack, on next day db was down with usual queries.
I found problem, issue was in
commit_delay = 50
After set to 0, issue was solved