How do we Troubleshoot PostgreSQL Contention in MinervaDB?

In PostgreSQL, the catalog tables are system tables that store metadata information about the database objects such as tables, indexes, functions, and users. The catalog tables can be used to troubleshoot contention issues by providing information about the current state of the database and the resources being used by different sessions.

Here are some of the catalog tables that can be useful for troubleshooting contention in PostgreSQL:

1. pg_locks: This catalog table shows the current locks held by sessions in the database. It includes information such as the lock type, mode, and object being locked. By querying this table, you can identify which sessions are holding locks and which sessions are waiting for locks to be released.
2. pg_stat_activity: This catalog table shows the current activity of sessions in the database. It includes information such as the query being executed, the state of the session, and the user connected to the session. By querying this table, you can identify which sessions are causing contention by holding locks or executing long-running queries.
3. pg_stat_user_tables: This catalog table shows statistics about table access patterns, including the number of sequential and index scans, the number of tuples read and written, and the number of dead tuples. By querying this table, you can identify which tables are being accessed frequently and which tables may be causing contention due to excessive table scans or updates.
4. pg_stat_user_indexes: This catalog table shows statistics about index usage, including the number of index scans and the number of index entries read and written. By querying this table, you can identify which indexes are being used frequently and which indexes may be causing contention due to excessive index scans.
5. pg_stat_replication: This catalog table shows statistics about replication activity in a streaming replication setup. It includes information such as the current position of the standby server, the number of bytes sent and received, and the lag time between the primary and standby servers. By querying this table, you can identify replication issues that may be causing contention, such as a slow network connection or a lagging standby server.

SQL Script we use for monitoring PostgreSQL contention:

SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS
blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS
blocking_user, blocked_activity.query AS blocked_query, blocking_activity.query
AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid =
blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype =
blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM
blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM
blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM
blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM
blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM
blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM
blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM
blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM
blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM
blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid =
blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

Conclusion

These catalog tables can be queried using SQL statements to provide insights into the current state of the database and to troubleshoot contention issues. By analyzing the information in these tables, you can identify the source of contention and take appropriate actions to resolve the issue.