Check lock count for PostgreSQL

Question

Up to now we used check_postgres.pl to monitor our database.

We used this to check the count of the locks:

https://bucardo.org/check_postgres/check_postgres.pl.html#locks

We often see more than 150 locks.

I was told this in a different question: How to Debug check_postgresql

A single DML statement can easily obtain millions of locks. There is no relation to the number of statements and the number of locks. But why are 150 locks a bad thing? Unless they are held for a long time (minutes, hours) I don't see any reason to check this.

How can I check for the lock count and ignore all locks which are only N seconds old?

Answer 1

How can I check for the lock count and ignore all locks which are only N seconds old?

You can't... but I think you're asking the wrong question.

PostgreSQL has many different kinds of locking. The three main ones visible to users are:

• Row locks
• Heavyweight locks
• Advisory locks

A row lock is a lock taken by UPDATE, DELETE, SELECT ... FOR UPDATE, SELECT ... FOR SHARE, etc, indicating a claim on a row. You cannot see these in the pg_locks view and they don't consume resources. This is what "a single DML statement can easily obtain millions of locks" refers to.

Heavyweight locks are what people usually mean when they say "lock" without qualifying with the lock type. They're seen in pg_locks. There are many kinds: locks on relations (tables, views, etc); locks to allow one transaction to wait on another, etc. They're only "heavyweight" compared to PostgreSQL's internal lightweight locking mechanism (LWLocks); you don't have to worry unless you start exceeding max_locks_per_transaction.

Advisory locks are a utility locking mechanism for applications. They're done with function calls. They're visible in pg_locks.