PostgreSQL database instance ‘log_lock_waits’ database flag should be set to ‘on’

Description

The PostgreSQL database instance flag ‘log_lock_waits’ causes a session wait to be logged if it takes longer than the ‘deadlock_timeout’ value to acquire a lock. This is useful to identify poor performance due to locking delays or if a specially-crafted SQL query is attempting to starve resources through holding locks for excessive amounts of time.

Remediation Steps

Google Cloud Console

  • Navigate to Cloud SQL instances.

  • Click on the Cloud SQL PostgreSQL database instance name to go to the Overview page.

  • Click EDIT and scroll down to the Flags section.

  • Select ADD FLAG, choose the log_lock_waits flag, and set the value to on.

  • Click DONE.

gcloud CLI

  • Set the log_lock_waits database flag to on for each Cloud SQL PostgreSQL database instance:

    • gcloud sql instances patch INSTANCE_NAME --database-flags log_lock_waits=on