Re: SHARED LOCKS , EXCLUSIVE LOCKS, ACCESS EXCLUSIVE LOCKS

On Sun, Apr 04, 2021 at 04:12:14PM +0530, aditya desai wrote:
> Hi,
> We have few select queries during which we see SHARED LOCKS and EXCLUSIVE
> LOCKS on tables. Can these locks cause slowness? Is there any way to reduce
> the locks?
>
> What must be causing ACCESS EXCLUSIVE LOCKS when the application is running
> select queries? Is it AUTOVACUUM?

I suggest to review all the logging settings, and consider setting:
log_destination = 'stderr,csvlog'
log_checkpoints = on
log_lock_waits = on
log_min_messages = info
log_min_error_statement = notice
log_temp_files = 0
log_min_duration_statement = '9sec'
log_autovacuum_min_duration = '99sec'

You should probably set up some way to monitor logs.
We set log_destination=csvlog and import them into the DB.
Then I have nagios checks for slow queries, errors, many tempfiles, etc.
https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG
https://www.postgresql.org/message-id/20190206232110.GH29720@telsasoft.com

--
Justin