Essential SQL views for PostgreSQL Monitoring

Introduction

This guide provides simple SQL views for effective PostgreSQL monitoring and management, enabling administrators to have a comprehensive insight into various aspects such as activities, locks, and sizes of objects and tables. Proper usage of these views can contribute to improved database performance, reliability, and scalability.

Monitor the current activities running in the database.

This view will display various details such as database id, pid, username, application name, client address, and more, helping administrators in monitoring and managing the database activities effectively. The purpose of this view is to track ongoing database activities.

CREATE OR REPLACE VIEW monitoring.activity
AS SELECT pg_stat_activity.datid,
    pg_stat_activity.datname,
    pg_stat_activity.pid,
    pg_stat_activity.usesysid,
    pg_stat_activity.usename,
    pg_stat_activity.application_name,
    pg_stat_activity.client_addr,
    pg_stat_activity.client_hostname,
    pg_stat_activity.client_port,
    pg_stat_activity.backend_start,
    pg_stat_activity.xact_start,
    pg_stat_activity.query_start,
    pg_stat_activity.state_change,
    pg_stat_activity.wait_event_type,
    pg_stat_activity.wait_event,
    pg_stat_activity.state,
    pg_stat_activity.backend_xid,
    pg_stat_activity.backend_xmin,
    pg_stat_activity.query,
    pg_stat_activity.backend_type
   FROM pg_stat_activity;

Provide information regarding the locks in the database.

The view returns details about locked items, waiting duration, blocked and blocking query details, and more. The purpose of this view is to identify and resolve issues related to database locks, helping in avoiding potential deadlocks and ensuring smooth database operations.

CREATE OR REPLACE VIEW monitoring.locks
AS 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
   FROM pg_locks blockedl
     JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
     JOIN pg_locks 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 blockinga ON blockingl.pid = blockinga.pid AND blockinga.datid = blockeda.datid
  WHERE NOT blockedl.granted AND blockinga.datname = current_database();

Show the size of each object within the database.

The view returns details about schema name, relation name, and the size of each object in a human-readable format. The purpose of this view is to help in database capacity planning and management by providing insights into the size of each object.

CREATE OR REPLACE VIEW monitoring.size_object
AS SELECT t.schema_name,
    t.relname,
    pg_size_pretty(t.table_size) AS size,
    t.table_size
   FROM ( SELECT pg_namespace.nspname AS schema_name,
            pg_class.relname,
            pg_relation_size(pg_class.oid::regclass) AS table_size
           FROM pg_class
             JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid) t
  WHERE t.schema_name !~~ 'pg_%'::text
  ORDER BY t.table_size DESC;

Provide the size details of each table in the database.

The view displays the schema name, table name, row count, and the size of each table in pretty format as well as in GB. The purpose of this view is to manage and optimize the storage, allowing administrators to ensure that the database tables do not consume excessive storage.

CREATE OR REPLACE VIEW monitoring.size_table
AS SELECT pg_stat_all_tables.schemaname,
    pg_stat_all_tables.relname AS table_name,
    pg_stat_all_tables.n_live_tup AS row_count,
    pg_size_pretty(pg_relation_size(((quote_ident(pg_stat_all_tables.schemaname::text) || '.'::text) || quote_ident(pg_stat_all_tables.relname::text))::regclass)) AS size_pretty,
    pg_relation_size(((quote_ident(pg_stat_all_tables.schemaname::text) || '.'::text) || quote_ident(pg_stat_all_tables.relname::text))::regclass) / (1024 * 1024 * 1024) AS size_gb
   FROM pg_stat_all_tables
  WHERE pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name, 'pg_toast'::name])
  ORDER BY (pg_relation_size(((quote_ident(pg_stat_all_tables.schemaname::text) || '.'::text) || quote_ident(pg_stat_all_tables.relname::text))::regclass)) DESC;

Provide the size details of each index in the database.

This view provides comprehensive size details for each index within the database. It is designed to help administrators identify large indexes and optimize index usage. The view displays the index name, its associated table and schema, and the size of each index in a human-readable format as well as in gigabytes (GB). This information is crucial for managing and optimizing storage, particularly in environments where indexes may occupy significant space.

CREATE OR REPLACE VIEW monitoring.size_index
AS SELECT 
    i.relname AS index_name,
    pg_size_pretty(pg_relation_size(i.oid)) AS index_size,
    n.nspname AS schema_name,
    t.relname AS table_name
FROM 
    pg_class i
    JOIN pg_index ix ON i.oid = ix.indexrelid
    JOIN pg_class t ON ix.indrelid = t.oid
    JOIN pg_namespace n ON t.relnamespace = n.oid
WHERE 
    i.relkind = 'i'
ORDER BY 
    pg_relation_size(i.oid) DESC;