Postgres Custom Alert - Total Number of Shared Locks as percentage of max_connections * max_locks_per_transaction

If you've ever seen an out of shared memory error in PosteSQL, you may need to increase max_locks_per_transaction.

Most of the shared memory used by PostgreSQL is of a fixed size. This is true for the I/O cache (shared buffers) and for many other components as well. One of those components has to do with locking. If you touch a table inside a transaction, PostgreSQL has to track your activity to ensure that a concurrent transaction cannot drop the table you are about to touch. Tracking activity is important because you want to make sure that various DDL statements don't need to wait until all reading transactions have terminated. 

This alerts monitors the number of shared locks PostgreSQL is using via pg_locks as compared to the number of shared locks allowed based on max_connections and max_locks_per_transaction found in pg_settings.    

Alert Definition

To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Single Numeric Return. Configure the alert with values similar to these:

Alert Name: Total Number of Shared Locks as percentage of max_connections * max_locks_per_transaction

Execution Interval: 10 minutes (default) -- change this according to your database workload.  You may need to run this alert less or more frequently.  

Notification Text: Empty

SQL Statement:

SELECT ROUND((asl.total_shared_locks/nl.number_of_locks_we_can_keep) * 100, 2) shared_locks_used_pct
FROM (SELECT count(*) total_shared_locks from pg_locks WHERE mode = 'AccessShareLock') asl
,(SELECT (CAST(ps.max_connections AS NUMERIC) * CAST(ps1.max_locks_per_transaction AS NUMERIC)) number_of_locks_we_can_keep
FROM (SELECT setting as max_connections FROM pg_settings WHERE name = 'max_connections') ps, (SELECT setting as max_locks_per_transaction FROM pg_settings WHERE name = 'max_locks_per_transaction') ps1) nl
WHERE asl.total_shared_locks > 0

Execute Against: Monitored Instance

Units: %

High Threshold: Min 90, Max <blank> 

Medium Threshold: Min 85, Max 90

Low Threshold: Min 80, Max 85