Re: The Curious Case of the Table-Locking UPDATE Query

On Mon, Jul 05, 2021 at 08:22:39PM -0300, Emiliano Saenz wrote:
> We have a huge POSTGRES 9.4 database in the production environment (several
> tables have more than 100.000.00 registers). Last two months we have had
> problems with CPU utilization. Debugging the locks (on pg_locks) we notice
> that sometimes simple UPDATE (by primary key) operation takes out
> ACCESS_EXCLUSIVE_LOCK mode over these huge tables so POSTGRES DB collapses
> and it generates excessive CPU consumption. My question is, How is it
> possible that UPDATE operation takes out ACCESS_EXCLUSIVE_LOCK mode?
> More information, this system never manifests this behavior before and we
> don't make software changes on last 2 years

To be able to help we will need pg_stat_activity data for the for
backend that has this lock, and pg_locks information for it too.

And, please, send text, and not screenshot.

Best regards,

depesz