however, it showed that nothing was wrong. no deadlocks, no pending locks no nothing.
have to mention that this update is run in serial (no other thread/process is trying to update that balance -or any other-, only one. well not 100% true, except the topup mechanism that happens relatively rare).
also have to mention that his exact same mechanic is there some time now, life a few years, and it never had similar problem.
also the fact that if i use another usid, with great many updates, will roll normally leads me to think that it might be a corruption or something, thus the dump/restore hope :)
as a prior step to dump/restore i am thinking of deleting and re-inserting that particular row. that might share some light you think?
>> the load of the machine is also low (like 0.2).
Which means little if the update is waiting for a lock to be released by one other process; which is more likely the situation (or some other concurrency contention) especially as you said that this particular user generates significant transaction/query volume (implied by the fact the user has the most balance updates).
During slow-update executions you want to look at: pg_stat_activity pg_locks
to see what other concurrent activity is taking place.
It is doubtful that dump/restore would have any effect given that the symptoms are sporadic and we are only talking about a select statement that returns a single row; and an update that does not hit any indexed column and therefore benefits from "HOT" optimization.
