Re: Lock contention high

Not using PostGIS

On Thursday, October 14, 2021, Paul Friedman <
paul(dot)friedman(at)streetlightdata(dot)com> wrote:

> 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
>>
>>
>>