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.
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;
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();
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;
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;
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;