The Hidden Blog

As it turns out, I do have a hostname.

Lock Contention on Google Cloud SQL and Postgres

Published on Feb 01, 2024

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.

"Locking metrics on the Cloud SQL dashboard"

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.

Cloud SQL database flags

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.