PostgreSQL Activity and Locks Monitoring¶

Overview¶

PostgreSQL uses locks to manage concurrent access to database resources. When multiple transactions try to access the same data, locks ensure data integrity by controlling access order. However, locks can also cause performance issues when queries wait too long for resources held by other transactions.

Muppy provides three monitoring tools to help diagnose and resolve PostgreSQL locking and activity issues:

Tool	Purpose	When to Use
Lock Monitor	View ALL locks in the cluster	Investigating overall lock activity, finding what's holding locks
Blocking Lock Monitor	View ONLY blocking situations	Diagnosing deadlocks or blocked queries
pg_stat_activity Monitor	View all backend connections	Analyzing connection states, finding idle transactions

Decision Guide: Which Tool to Use?¶

Application seems slow? Start with pg_stat_activity Monitor to see active queries
Query is stuck waiting? Use Blocking Lock Monitor to find what's blocking it
Need to understand lock patterns? Use Lock Monitor to see all locks
Too many connections? Use pg_stat_activity Monitor to identify and terminate idle connections

Key PostgreSQL Concepts¶

Lock Types¶

PostgreSQL has several lock types depending on what resource is being locked:

Lock Type	Description
`relation`	Locks on tables
`transactionid`	Locks on transaction IDs
`virtualxid`	Locks on virtual transaction IDs
`tuple`	Locks on specific rows
`object`	Locks on database objects
`advisory`	Application-controlled locks

Lock Modes (from weakest to strongest)¶

Mode	Conflicts With	Typical Operations
`AccessShareLock`	AccessExclusiveLock	SELECT
`RowShareLock`	Exclusive, AccessExclusive	SELECT FOR UPDATE/SHARE
`RowExclusiveLock`	Share, ShareRowExclusive, Exclusive, AccessExclusive	INSERT, UPDATE, DELETE
`ShareUpdateExclusiveLock`	ShareUpdateExclusive, Share, ShareRowExclusive, Exclusive, AccessExclusive	VACUUM, ANALYZE
`ShareLock`	RowExclusive, ShareUpdateExclusive, ShareRowExclusive, Exclusive, AccessExclusive	CREATE INDEX
`ShareRowExclusiveLock`	RowExclusive, ShareUpdateExclusive, Share, ShareRowExclusive, Exclusive, AccessExclusive	-
`ExclusiveLock`	RowShare, RowExclusive, ShareUpdateExclusive, Share, ShareRowExclusive, Exclusive, AccessExclusive	-
`AccessExclusiveLock`	ALL modes	DROP TABLE, ALTER TABLE, TRUNCATE

Granted vs Awaited Locks¶

Granted = True: The process holds the lock
Granted = False: The process is waiting for the lock

Blocked vs Blocking¶

Blocked process: A query that is waiting because another transaction holds a conflicting lock
Blocking process: A query that holds a lock that another transaction is waiting for

Accessing the Monitoring Tools¶

Navigate to Databases > PostgreSQL > Database Clusters
Select a cluster to open its form view
Click the Action dropdown menu
Choose one of:
Monitor Database Locks - Opens Lock Monitor
Monitor Database Blocking Locks - Opens Blocking Lock Monitor
Monitor Cluster Activity - Opens pg_stat_activity Monitor

Lock Monitor¶

Purpose¶

The Lock Monitor displays ALL locks currently held or awaited in the PostgreSQL cluster. Use this tool when you need a comprehensive view of lock activity.

How It Works¶

The monitor queries the pg_locks system view joined with pg_stat_activity to show lock information alongside the queries holding them.

Key Fields¶

Field	Description
PID	Process ID of the backend holding/awaiting the lock
Lock Type	Type of lock (relation, transactionid, etc.)
Relation	Name of the table being locked (if applicable)
Mode	Lock mode (AccessShareLock, RowExclusiveLock, etc.)
Granted	True if lock is held, False if waiting
Query Duration	How long the current query has been running
Query	The SQL query text
State	Backend state (active, idle, idle in transaction)
Application Name	Name of the connected application
Client Address	IP address of the client

Using the Lock Monitor¶

Refresh: Click Refresh to update the lock list with current data
Filter by Database: Select a database to show only locks for that database
Group By: Use the search bar groupings to organize locks by:
PID
User
Database
Lock Type
Mode
Relation
Pause Auto-Refresh: Apply the "Don't Refresh" filter to prevent automatic updates
Custom Query: Click the Query tab to view or modify the SQL query used

SQL Query Used¶

SELECT
    pgl.pid,
    pgl.locktype,
    pgl.database AS database_oid,
    pgsa.datname AS database_name,
    pgl.relation::regclass AS relation_name,
    pgl.mode,
    pgsa.usename AS user_name,
    pgsa.application_name,
    pgsa.client_addr,
    now() - pgsa.query_start AS query_duration,
    pgsa.query,
    pgsa.state,
    pgl.granted,
    -- ... additional fields
FROM pg_locks AS pgl
    LEFT JOIN pg_stat_activity AS pgsa ON pgl.pid = pgsa.pid
ORDER BY (now() - pgsa.query_start) DESC;

Blocking Lock Monitor¶

Purpose¶

The Blocking Lock Monitor shows ONLY situations where one query is blocking another. This is the most useful tool when diagnosing performance issues caused by lock contention.

How It Works¶

The monitor identifies lock conflicts by finding locks that are NOT granted (NOT blockedl.granted) and joining with the locks table again to find which process holds the conflicting lock.

Key Fields¶

Field	Description
Locked Item	The table or resource being contested
Waiting Duration	How long the blocked query has been waiting
Blocked PID	Process ID of the waiting query
Blocked Query	SQL text of the waiting query
Blocked Mode	Lock mode requested by the blocked query
Blocking PID	Process ID of the query holding the lock
Blocking Query	SQL text of the query holding the lock
Blocking Mode	Lock mode held by the blocking query

Using the Blocking Lock Monitor¶

Refresh: Click Refresh to get current blocking situations
Analyze: Look at both the blocked and blocking queries to understand the conflict
Decide: Determine which query should be terminated (usually the blocking one)
Act: Use one of the terminate buttons

Terminate Buttons¶

Each blocking situation has two terminate options:

Button	Action	When to Use
Terminate backend of BLOCKING pid	Kills the process holding the lock	When the blocking query is stuck or less important
Terminate backend of BLOCKED pid	Kills the process waiting for the lock	When the waiting query should be cancelled

SQL Query Used¶

SELECT
    COALESCE(blockingl.relation::regclass::text, blockingl.locktype) AS locked_item,
    now() - blockeda.query_start AS waiting_duration,
    blockeda.pid AS blocked_pid,
    blockeda.query AS blocked_query,
    blockedl.mode AS blocked_mode,
    blockinga.pid AS blocking_pid,
    blockinga.query AS blocking_query,
    blockingl.mode AS blocking_mode,
    -- ... client info fields
FROM pg_catalog.pg_locks AS blockedl
    JOIN pg_stat_activity AS blockeda ON blockedl.pid = blockeda.pid
    JOIN pg_catalog.pg_locks AS blockingl ON (
        (blockingl.transactionid = blockedl.transactionid)
        OR (blockingl.relation = blockedl.relation AND blockingl.locktype = blockedl.locktype)
    ) AND blockedl.pid != blockingl.pid
    JOIN pg_stat_activity AS blockinga ON blockingl.pid = blockinga.pid
WHERE NOT blockedl.granted
ORDER BY blockeda.query_start;

pg_stat_activity Monitor¶

Purpose¶

The pg_stat_activity Monitor shows all PostgreSQL backend connections and their current state. Use this tool to: - Find long-running queries - Identify "idle in transaction" connections - Monitor overall database activity - Terminate problematic connections in bulk

Key Fields¶

Field	Description
PID	Process ID of the backend
Backend Type	Type of backend (client backend, autovacuum, etc.)
Database	Database name
Username	Connected user
Application Name	Application identifier
Client Address	IP address of the client
State	Current state (active, idle, idle in transaction, etc.)
State Age	Time elapsed since last state change
Wait Event Type	What the backend is waiting for (if any)
Query	Current or last executed query

Backend States¶

State	Description	Action
`active`	Executing a query	Monitor if running too long
`idle`	Waiting for new command	Generally safe
`idle in transaction`	In transaction but not executing	Dangerous - holds locks!
`idle in transaction (aborted)`	In failed transaction	Should be terminated

Using the pg_stat_activity Monitor¶

Ignore Idle Connections: Toggle the checkbox to hide idle connections (enabled by default)
Filter by Database: Select a specific database to narrow results
Group By: Organize by state, database, or client
Bulk Terminate: Select multiple records and use "Terminate Backends" action

SQL Query Used¶

SELECT
    pid,
    backend_type,
    datname,
    usename,
    application_name,
    client_addr,
    state,
    now() - state_change AS state_age,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
ORDER BY state_age DESC;

Terminating Processes¶

How pg_terminate_backend() Works¶

When you click a terminate button, Muppy executes:

SELECT pg_terminate_backend(<pid>)

This gracefully terminates the specified backend: - The connection is closed - Any open transaction is rolled back - The client receives an error about the terminated connection

Choosing Which Process to Terminate¶

Scenario	Recommended Action
Blocking query is stuck/hung	Terminate the blocking PID
Blocking query will finish soon	Wait, or terminate the blocked PID
"idle in transaction" holding locks	Terminate that PID
Long-running query not needed	Terminate that PID
Multiple idle connections	Use bulk terminate in pg_stat_activity

Warnings¶

Transaction Rollback: Terminating a backend rolls back its transaction. Any uncommitted changes are lost.
Application Impact: The connected application will receive an error. Ensure it can handle reconnection.
Cascading Effects: If a blocked query is part of a larger operation, terminating the blocker may allow many queries to proceed at once.

Use Cases¶

Use Case 1: Diagnosing a Slow Application¶

Symptoms: Application responses are slow, database seems unresponsive.

Steps: 1. Open pg_stat_activity Monitor 2. Uncheck "Ignore Idle Connections" to see all connections 3. Look for queries with long State Age in "active" state 4. Check Wait Event Type - if it shows "Lock", switch to Blocking Lock Monitor 5. If many connections are "idle in transaction", those may be holding locks

Use Case 2: Resolving a Table Lock Deadlock¶

Symptoms: Queries on a specific table are stuck.

Steps: 1. Open Blocking Lock Monitor 2. Click Refresh 3. Identify the blocking chain: - Note the locked_item (table being contested) - Compare blocked_query vs blocking_query 4. Decide which transaction can be safely terminated 5. Click Terminate backend of BLOCKING pid to release the lock

Use Case 3: Identifying "idle in transaction" Connections¶

Symptoms: Locks held for long periods, performance degradation.

Steps: 1. Open pg_stat_activity Monitor 2. Uncheck "Ignore Idle Connections" 3. Group by State 4. Look for "idle in transaction" entries with long State Age 5. These connections hold locks without doing work - terminate them

Use Case 4: Investigating Lock Contention on a Specific Table¶

Symptoms: Operations on a particular table are slow.

Steps: 1. Open Lock Monitor 2. Group by Relation 3. Find your table and expand to see all locks 4. Look at lock Mode - AccessExclusiveLock blocks everything 5. Check Granted - False means waiting for lock 6. Find the PID holding the blocking lock and investigate

Best Practices¶

Keep Transactions Short¶

Long transactions hold locks longer, increasing blocking potential:

-- Bad: Long transaction holding locks
BEGIN;
UPDATE large_table SET status = 'processed';
-- ... application does other work for minutes ...
COMMIT;

-- Better: Quick transactions
BEGIN;
UPDATE large_table SET status = 'processed';
COMMIT;
-- Application does other work separately

Monitor Proactively¶

Check for blocking locks regularly, not just when problems occur
Set up alerts for long-running "idle in transaction" connections
Review lock patterns during deployments and migrations

Coordinate DDL Operations¶

DDL commands (ALTER TABLE, CREATE INDEX) often require AccessExclusiveLock:

Schedule during maintenance windows
Use CONCURRENTLY when possible (e.g., CREATE INDEX CONCURRENTLY)
Warn users before running DDL on busy tables

Avoid Long-Running Idle Transactions¶

Applications should: - Commit or rollback transactions promptly - Use connection pooling with transaction-level pooling - Set idle_in_transaction_session_timeout in PostgreSQL configuration

Troubleshooting¶

Symptom	Probable Cause	Solution
No locks displayed	Cluster offline or SSH unreachable	Check cluster state and connectivity
Query shows "idle" but holds lock	Transaction not committed	Find and terminate idle in transaction
Blocking Monitor empty but queries slow	No lock conflicts - other bottleneck	Use pg_stat_activity to analyze
Terminate button doesn't respond	PID already terminated	Refresh the monitor
Monitor shows stale data	Auto-refresh disabled	Click Refresh or remove "Don't Refresh" filter
Connection refused error	Cluster not running	Start the PostgreSQL cluster

Technical Reference¶

Model References¶

Model	Description
`mpy.pg_lock_monitor_wizard`	Lock Monitor wizard
`mpy.pg_lock`	Individual lock record (transient)
`mpy.pg_blocking_lock_monitor_wizard`	Blocking Lock Monitor wizard
`mpy.pg_blocking_lock`	Blocking lock record (transient)
`mpy.pg_stat_activity_monitor_wizard`	Activity Monitor wizard
`mpy.pg_stat_activity`	Activity record (transient)

Source Files¶

project_addons/muppy_postgresql_base/wizards/pg_lock_monitor_wizard.py
project_addons/muppy_postgresql_base/wizards/pg_blocking_lock_monitor_wizard.py
project_addons/muppy_postgresql_base/wizards/pg_stat_activity_monitor_wizard.py

PostgreSQL Activity and Locks Monitoring¶

Overview¶

Decision Guide: Which Tool to Use?¶

Key PostgreSQL Concepts¶

Lock Types¶

Lock Modes (from weakest to strongest)¶

Granted vs Awaited Locks¶

Blocked vs Blocking¶

Accessing the Monitoring Tools¶

Lock Monitor¶

Purpose¶

How It Works¶

Key Fields¶

Using the Lock Monitor¶

SQL Query Used¶

Blocking Lock Monitor¶

Purpose¶

How It Works¶

Key Fields¶

Using the Blocking Lock Monitor¶

Terminate Buttons¶

SQL Query Used¶

pg_stat_activity Monitor¶

Purpose¶

Key Fields¶

Backend States¶

Using the pg_stat_activity Monitor¶

SQL Query Used¶

Terminating Processes¶

How pg_terminate_backend() Works¶

Choosing Which Process to Terminate¶

Warnings¶

Use Cases¶

Use Case 1: Diagnosing a Slow Application¶

Use Case 2: Resolving a Table Lock Deadlock¶

Use Case 3: Identifying "idle in transaction" Connections¶

Use Case 4: Investigating Lock Contention on a Specific Table¶

Best Practices¶

Keep Transactions Short¶

Monitor Proactively¶

Coordinate DDL Operations¶

Avoid Long-Running Idle Transactions¶

Troubleshooting¶

Technical Reference¶

Model References¶

Source Files¶

External Documentation¶