Re: Lock contention high

Are you using PostGIS?

If so, there is an issue with TOAST table locking having these symptoms.

---Paul

On Wed, Oct 13, 2021 at 11:15 AM MichaelDBA <MichaelDBA(at)sqlexec(dot)com> wrote:

> 1.Is there a way to tune the lock contention ?
> 2.Is any recommendations to tune/reduce the lock contention via postgres.conf
>
> I think you'd want to find *which* LW locks are being waited on, to see if it's
> something that can be easily tuned.
>
> You can check pg_stat_activity, or maybe create a cronjob to record its content
> for later analysis.
>
>
> Hello,
>
> Also turn on log_lock_waits so you can evaluate the actual SQL causing
> the problems in the PG log files. Thinking ahead, you may want to consider
> if using advisory locks from the application side of things might be
> helpful to manage locks in a more pessimistic way. Also, join with
> pg_locks table to find out the specific resources that are in contention.
>
> Regards,
> Michael Vitale
>
>
>