I recently looked into some performance issues on a large PostgreSQL 13.12 instance. One part of that was to make sure we don’t have too much locking going on in busy tables.

The first step was to enable log_lock_waits, one of the runtime flags. In Cloud SQL that’s just one of the default database flags you can flip to true. It doesn’t require a restart.

After keeping that in production for a while you can query the instance through the Log Explorer with something like the following:

resource.type="cloudsql_database"
      resource.labels.database_id="db"
      log_name="projects/company/logs/cloudsql.googleapis.com%2Fpostgres.log"
textPayload:"ExclusiveLock"

If your locks are hitting the (default) 1 second threshold they will log something like:

db=db,user=db_write LOG:  process 2026853 acquired ShareRowExclusiveLock on relation 19062 of database 16448 after 3267.563 ms

Then you can look up the relation id like select 19062::regclass; and it’ll tell you which table is affected. This should give you a good start for your investigation.

A great article about the topic can be found as always on the pganalyze blog.