PostgreSQL Monitoring

Monitoring Areas

SELECT * FROM pg_stat_activity;

SELECT * FROM pg_stat_statements;

SELECT pg_database_size('db');

SELECT * FROM pg_locks;

🔹 Monitoring Active Connections

Track current database connections to identify active users and running queries. Connection monitoring helps detect connection leaks, identify long-running queries, and manage connection pool usage. Use this information to optimize application connection handling.

-- View all active connections
SELECT pid, usename, application_name, client_addr, state, query
FROM pg_stat_activity
WHERE state = 'active';

-- Count connections per database
SELECT datname, count(*) 
FROM pg_stat_activity 
GROUP BY datname;

-- Find idle connections
SELECT pid, usename, state, state_change
FROM pg_stat_activity
WHERE state = 'idle'
AND state_change < now() - interval '1 hour';

 pid  | usename  | state  | query
------+----------+--------+------------------
 1234 | app_user | active | SELECT * FROM...

🔹 Query Performance Monitoring

Analyze query execution statistics to identify slow queries and optimization opportunities. The pg_stat_statements extension tracks query performance metrics including execution time, call frequency, and resource usage. Use this data to prioritize optimization efforts.

-- Enable pg_stat_statements extension
CREATE EXTENSION pg_stat_statements;

-- View slowest queries
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Find most frequently executed queries
SELECT query, calls, total_exec_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

 query              | calls | mean_exec_time
--------------------+-------+----------------
 SELECT * FROM...  | 1500  | 245.32 ms

🔹 Database Size Monitoring

Track database and table sizes to manage storage capacity and plan growth. Regular size monitoring prevents disk space issues and identifies tables needing maintenance. Monitor growth trends to forecast future storage requirements accurately.

-- Database size
SELECT pg_size_pretty(pg_database_size('mydb')) AS size;

-- All databases sizes
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- Largest tables
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;

 tablename  | size
------------+--------
 orders     | 1.2 GB
 customers  | 450 MB

🔹 Lock Monitoring

Identify blocking queries and deadlocks that prevent concurrent operations. Lock monitoring reveals contention issues affecting application performance. Understanding lock patterns helps optimize transaction design and reduce blocking scenarios.

-- View current locks
SELECT locktype, database, relation::regclass, mode, granted
FROM pg_locks
WHERE NOT granted;

-- Find blocking queries
SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query,
       blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted AND blocking_locks.granted;

 blocked_pid | blocking_pid | blocked_query
-------------+--------------+---------------
 5678        | 1234         | UPDATE orders...

🔹 Table Statistics

Monitor table access patterns and maintenance needs through statistics views. Table statistics reveal which tables receive most activity and require optimization. Use this information to schedule maintenance and identify indexing opportunities.

-- Table access statistics
SELECT schemaname, tablename, seq_scan, idx_scan, 
       n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

-- Tables needing VACUUM
SELECT schemaname, tablename, n_dead_tup, n_live_tup,
       round(n_dead_tup * 100.0 / NULLIF(n_live_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

 tablename | seq_scan | idx_scan | dead_pct
-----------+----------+----------+----------
 products  | 1500     | 45000    | 15.5

🔹 Index Usage Monitoring

Evaluate index effectiveness to ensure queries use optimal access paths. Index monitoring identifies unused indexes wasting space and missing indexes causing slow queries. Regular index analysis maintains query performance as data grows.

-- Index usage statistics
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- Unused indexes
SELECT schemaname, tablename, indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexname NOT LIKE '%_pkey';

-- Index size
SELECT schemaname, tablename, indexname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

 indexname        | idx_scan | size
------------------+----------+--------
 idx_orders_date  | 0        | 125 MB

🔹 Replication Monitoring

Track replication lag and status for high availability setups. Replication monitoring ensures standby servers remain synchronized with primary. Detecting replication issues early prevents data loss and maintains failover readiness.

-- Replication status (on primary)
SELECT client_addr, state, sync_state, 
       pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS pending_bytes
FROM pg_stat_replication;

-- Replication lag (on standby)
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

-- WAL status
SELECT pg_current_wal_lsn() AS current_wal;

 client_addr    | state     | sync_state
----------------+-----------+------------
 192.168.1.100  | streaming | async