Top 9 PostgreSQL System Views for Better Database Monitoring and Troubleshooting

PostgreSQL, as one of the most advanced and widely used relational database management systems (RDBMS), offers a range of system views that can be utilized for monitoring and diagnosing database operations.

These system views provide insights into the internal state of the database, such as resource usage, query performance, locks, connections, and system activity, making it easier for database administrators (DBAs) and developers to troubleshoot issues and optimize performance.

In this article, we will explore some of the key PostgreSQL system views and how they can be used to monitor various aspects of the database system.

SELECT *
FROM pg_catalog.pg_views
WHERE schemaname = 'pg_catalog'
ORDER BY viewname;

Key PostgreSQL System Views

PostgreSQL system views are part of the pg_catalog schema and are available by default in any PostgreSQL database.

These views give users access to a variety of internal information about the state of the database. Below are some of the most useful system views for monitoring and diagnosing database operations.

1. pg_stat_activity: Monitoring Active Connections

The pg_stat_activity view is one of the most commonly used views for monitoring the activity of active sessions in PostgreSQL. It provides information about all current database connections, including the current query being executed, the application name, the client address, and the session state.

Key Columns:

• pid: Process ID of the backend.
• usename: Username associated with the connection.
• application_name: Name of the application connected to the database.
• client_addr: IP address of the client.
• state: Current state of the session (e.g., idle, active, waiting).
• query: The SQL query currently being executed by the backend.

Example Query:

SELECT pid, usename, application_name, client_addr, state, query
FROM pg_stat_activity
WHERE state = 'active';

This query will return all active queries currently running on the database.

2. pg_stat_user_tables: Table-Level Statistics

pg_stat_user_tables provides statistics on user-defined tables, including the number of sequential scans, index scans, inserts, updates, deletes, and tuples returned or fetched by each table. This view is useful for understanding the overall usage of tables and identifying potential bottlenecks or under-utilized indexes.

Key Columns:

• relname: Name of the table.
• seq_scan: Number of sequential scans initiated.
• seq_tup_read: Number of rows read by sequential scans.
• idx_scan: Number of index scans initiated.
• idx_tup_fetch: Number of rows fetched by index scans.
• n_tup_ins: Number of rows inserted into the table.
• n_tup_upd: Number of rows updated in the table.
• n_tup_del: Number of rows deleted from the table.

Example Query:

SELECT relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables;

This query will give an overview of the table usage statistics across all user tables.

3. pg_locks: Lock Monitoring

The pg_locks view provides information about the locks currently held in the database. Locks are essential for maintaining data integrity and ensuring concurrency control, but excessive locking or contention can negatively impact performance. By querying pg_locks, you can identify locking issues such as blocked transactions.

Key Columns:

• locktype: Type of lock (e.g., relation, page, tuple).
• database: OID of the database where the lock is held.
• relation: OID of the relation (table) being locked.
• transactionid: ID of the transaction holding the lock.
• pid: Process ID of the backend holding the lock.
• mode: Lock mode (e.g., AccessShareLock, ExclusiveLock).
• granted: Indicates whether the lock has been granted (true/false).

Example Query:

SELECT pid, locktype, relation::regclass, mode, granted
FROM pg_locks
WHERE NOT granted;

This query will show all the locks that are not granted (i.e., waiting for other locks to be released), which could help identify locking contention.

4. pg_stat_database: Database-Level Statistics

pg_stat_database provides general statistics for each database in the PostgreSQL cluster. It includes information such as the number of transactions committed or rolled back, the number of deadlocks, and the amount of I/O operations for each database.

Key Columns:

• datname: Name of the database.
• xact_commit: Number of transactions committed.
• xact_rollback: Number of transactions rolled back.
• blks_read: Number of disk blocks read.
• blks_hit: Number of cache hits.
• deadlocks: Number of deadlock occurrences.
• tup_returned: Number of rows returned by queries.
• tup_fetched: Number of rows fetched by queries.

Example Query:

SELECT datname, xact_commit, xact_rollback, blks_read, blks_hit, deadlocks
FROM pg_stat_database;

This query provides an overview of activity and performance at the database level.

5. pg_stat_bgwriter: Background Writer Statistics

The pg_stat_bgwriter view provides statistics related to the background writer process, which is responsible for writing dirty buffers to disk in PostgreSQL. Monitoring these statistics can help determine whether the background writer is performing well or if it is falling behind.

Key Columns:

• buffers_written: Number of buffers written by the background writer.
• buffers_alloc: Number of buffers allocated by the background writer.
• maxwritten_clean: Maximum number of buffers that remained clean during the background writer’s run.
• buffers_backend: Number of buffers written by backends directly instead of the background writer.

Example Query:

SELECT buffers_written, buffers_alloc, maxwritten_clean
FROM pg_stat_bgwriter;

This query can help you determine if the background writer is effectively flushing dirty buffers.

6. pg_stat_user_indexes: Index Usage Statistics

pg_stat_user_indexes shows statistics about index usage for user tables. It helps identify which indexes are being used frequently and which ones are rarely accessed, potentially allowing you to optimize your indexing strategy.

Key Columns:

• relname: Name of the table.
• indexrelname: Name of the index.
• idx_scan: Number of index scans initiated using this index.
• idx_tup_read: Number of rows fetched by index scans.
• idx_tup_fetch: Number of tuples fetched by the index.

Example Query:

SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes;

This query can help you identify unused indexes that can be dropped, or underused indexes that might need optimization.

7. pg_stat_replication: Replication Monitoring

In a PostgreSQL environment configured with replication (either streaming or logical), the pg_stat_replication view provides information about the status of replication connections. It shows details such as the replication lag and the state of the replication process.

Key Columns:

• pid: Process ID of the replication connection.
• application_name: Name of the replication client.
• state: Current state of the replication connection (e.g., streaming).
• sync_state: Indicates if the replication is synchronous or asynchronous.
• write_lag: Amount of time the replication is lagging behind.

Example Query:

SELECT pid, application_name, state, write_lag, sync_state
FROM pg_stat_replication;

This query helps monitor the replication health and ensures that the standby servers are up to date.

8. pg_user: User Roles and Permissions

The pg_user view provides information about the roles (or users) in the PostgreSQL database, along with some key details about their permissions and membership in different roles. This view is a convenient way to quickly inspect user accounts and their properties, making it useful for security audits and role management.

Key Columns:

• usename: Name of the user role.
• usesysid: System ID (OID) of the user role.
• usecreatedb: Indicates whether the user can create databases (true/false).
• usesuper: Indicates whether the user has superuser privileges (true/false).
• userepl: Indicates whether the user has replication privileges (true/false).
• usebypassrls: Indicates whether the user bypasses Row Level Security (RLS) (true/false).
• passwd: The user’s password (encrypted).
• valuntil: The timestamp until which the user’s password is valid (for password expiration).
• useconfig: Configuration settings for the user role, stored as a list.

Example Query:

SELECT usename, usesysid, usecreatedb, usesuper, userepl, usebypassrls FROM pg_user;

This query returns a list of users along with information about their privileges. It’s particularly useful for auditing roles, checking who has superuser or replication rights, and ensuring that users have appropriate permissions.

Use Cases:

• User Auditing: Quickly check user privileges to ensure security compliance and that users have the right level of access.
• Role Management: Get an overview of all users in the database, their abilities, and their access levels.

9. pg_class: Relation (Table, View, Index, etc.) Metadata

The pg_class view is a crucial system catalog table that provides metadata about the relations (tables, views, indexes, sequences, etc.) in PostgreSQL. This view contains information about all the database objects that are stored in the database, allowing you to query detailed information about tables and indexes.

Key Columns:

• relname: Name of the relation (e.g., table, index, view).
• relkind: Type of the relation. It can be:
  • 'r': Regular table
  • 'i': Index
  • 'S': Sequence
  • 'v': View
  • 'm': Materialized view
  • 'c': Composite type
  • 't': TOAST table (for storing large data types)
• relnamespace: OID of the schema containing the relation (can be joined with pg_namespace to get the schema name).
• relowner: OID of the owner of the relation (can be joined with pg_user to get the owner’s name).
• relam: OID of the access method used for indexes (can be joined with pg_am to get the access method name).
• relfilenode: File node (file ID) of the relation’s data storage.
• reltuples: Estimated number of rows in the relation (this is an approximation and might not always be up-to-date).
• relpages: Number of disk pages used by the relation (pages are 8KB each).
• relchecks: Number of constraints defined on the relation.

Example Query:

SELECT relname, relkind, relnamespace::regnamespace, relowner::regrole, reltuples, relpages FROM pg_class

This query returns a list of all tables in the database, along with their schema, owner, estimated row count, and page usage. You can modify the WHERE clause to show other types of relations (e.g., views, indexes).

Use Cases:

• Database Structure Overview: pg_class is essential for getting a comprehensive list of all database objects, whether tables, views, or indexes.
• Table Size and Performance Monitoring: You can monitor the reltuples and relpages columns to check for any unusual growth in table size or changes in the number of rows.
• Identifying Indexes: By filtering relkind = 'i', you can easily identify all indexes in the database.

Using System Views for Diagnosis

In addition to simple monitoring, system views can be used to diagnose issues in PostgreSQL. Some common scenarios include:

1. Slow Queries: By examining pg_stat_activity, you can identify long-running queries and optimize them using indexes or query rewriting.
2. Locking Issues: If you notice slow performance, querying pg_locks can help identify if any transactions are blocked due to lock contention.
3. Deadlocks: Deadlocks, where two or more transactions wait for each other to release locks, can be detected by monitoring the deadlocks column in pg_stat_database.
4. Disk I/O Bottlenecks: High values in blks_read and blks_hit in pg_stat_database can indicate disk I/O bottlenecks, which can be mitigated by increasing cache size or optimizing queries.
5. Replication Delays: By querying pg_stat_replication, DBAs can monitor lag in replication and address issues in data synchronization.

Conclusion

PostgreSQL provides a rich set of system views that allow DBAs and developers to monitor various aspects of the database, from session activity to background processes, locks, and replication status. By leveraging these system views, you can gain deep insights into the internal workings of PostgreSQL, diagnose potential issues, and optimize performance for your database environment.

Regular monitoring using system views should be a part of any DBA’s toolkit to ensure that PostgreSQL is running efficiently and that potential issues are identified and resolved promptly.