Cheatsheet: PostgreSQL Monitoring
Note:
ʒʒ To query these metrics, you must be logged into PostgreSQL as a user that has read access to pg_stat_database
ʒʒ For more info on monitoring PostgreSQL performance, read http://dtdg.co/monitor-postgres
Replication and checkpoint metrics—more info Connection metrics—more info
METRIC DESCRIPTION QUERY METRIC DESCRIPTION QUERY
Replication delay in bytes* (version <10.x) SELECT abs(pg_xlog_location_diff(pg_last_xlog_receive_location(), Number of active connections SELECT COUNT(*) FROM pg_stat_activity WHERE state='active';
pg_last_xlog_replay_location())) AS replication_delay_bytes;
Percentage of max connections in use SELECT (SELECT SUM(numbackends) FROM pg_stat_database) / (SELECT
Replication delay in bytes (version 10.x+) SELECT abs(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), setting::float FROM pg_settings WHERE name = 'max_connections');
pg_last_wal_replay_lsn())) AS replication_delay_bytes;
Replication delay in seconds (version <10.x) SELECT CASE WHEN pg_last_xlog_receive_location() =
pg_last_xlog_replay_location() THEN 0 ELSE GREATEST (0, EXTRACT (EPOCH
FROM now() - pg_last_xact_replay_timestamp())) END; Disk and index usage—more info
Replication delay in seconds (version 10.x+) SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() METRIC DESCRIPTION QUERY
THEN 0 ELSE GREATEST (0, EXTRACT (EPOCH FROM now() -
Disk space used in bytes, excluding indexes (per table) SELECT relname AS "table_name", pg_size_pretty(pg_table_size(C.oid))
pg_last_xact_replay_timestamp())) END;
AS "table_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid
Number of checkpoints requested & scheduled SELECT checkpoints_req, checkpoints_timed FROM pg_stat_bgwriter; = C.relnamespace) WHERE nspname NOT IN ('pg_catalog',
'information_schema') AND nspname !~ '^pg_toast' AND relkind IN ('r')
ORDER BY pg_table_size(C.oid) DESC;
Number of index scans (per index and per table) SELECT indexrelname, relname, idx_scan FROM pg_stat_user_indexes;
Read query throughput and performance metrics—more info
METRIC DESCRIPTION QUERY
Number of index scans (per index and per table) SELECT indexrelname, relname, idx_scan FROM pg_stat_user_indexes; Useful psql commands
Number of sequential scans (per table) SELECT relname, seq_scan FROM pg_stat_user_tables;
COMMAND
Rows fetched by queries (per database) SELECT datname, tup_fetched FROM pg_stat_database;
Check version of PostgreSQL SELECT version();
Rows returned by queries (per database) SELECT datname, tup_returned FROM pg_stat_database;
Show location of postgresql.conf file1 SHOW config_file;
Bytes written temporarily to disk to execute SELECT datname, temp_bytes FROM pg_stat_database;
Show location of data directory1 SHOW data_directory;
queries (per database)*
Show location of log directory (may be a path SHOW log_directory;
relative to data directory)1
Write query throughput and performance metrics—more info List all databases \l
List all tables + table size in bytes in the \dt+
METRIC DESCRIPTION QUERY current database
Rows inserted, updated, deleted by queries SELECT datname, tup_inserted, tup_updated, tup_deleted FROM
List all roles and privileges in the current database \du
(per database) pg_stat_database;
Check if this server is a standby (in recovery mode) SELECT pg_is_in_recovery();
Rows inserted, updated, deleted by queries SELECT relname, n_tup_ins, n_tup_upd, n_tup_del FROM pg_stat_user_tables;
(per table) View connection info \conninfo
Heap-only tuple (HOT) updates (per table) SELECT relname, n_tup_hot_upd FROM pg_stat_user_tables; Show all available psql commands \?
Total commits and rollbacks across all databases SELECT SUM(xact_commit) AS total_commits, SUM(xact_rollback)
AS total_rollbacks FROM pg_stat_database; *Available in version 9.2+
1
Requires superuser privileges
Concurrent operations metrics—more info
METRIC DESCRIPTION QUERY
Locks (by table and lock mode) SELECT mode, pg_class.relname, count(*) FROM pg_locks JOIN pg_class ON
(pg_locks.relation = pg_class.oid) WHERE pg_locks.mode IS NOT NULL AND
pg_class.relname NOT LIKE 'pg_%%' GROUP BY pg_class.relname, mode;
Deadlocks (per database)* SELECT datname, deadlocks FROM pg_stat_database;
Dead rows (per table) SELECT relname, n_dead_tup FROM pg_stat_user_tables;
Cheatsheet: PostgreSQL Monitoring with Datadog
Note:
ʒʒ For metric descriptions and more info: dtdg.co/monitor-postgres
3. Write query throughput and performance metrics —more info
METRIC DESCRIPTION DATADOG METRIC NAME
Rows inserted, updated, deleted by queries (per postgresql.rows_inserted, postgresql.rows_updated,
database and per table*) postgresql.rows_deleted
Heap-only tuple (HOT) updates (per table)* postgresql.rows_hot_updated
Total commits and rollbacks across all databases postgresql.commits + postgresql.rollbacks
4. Concurrent operations metrics—more info
METRIC DESCRIPTION DATADOG METRIC NAME
Locks (by table and lock mode) postgresql.locks
Deadlocks1 postgresql.deadlocks
Dead rows (per table)* postgresql.dead_rows
5. Connection metrics—more info
METRIC DESCRIPTION DATADOG METRIC NAME
Number of active connections postgresql.connections
Percentage of max connections in use postgresql.percent_usage_connections
6. Disk and index usage—more info
Datadog's out-of-the-box screenboard for monitoring PostgreSQL. METRIC DESCRIPTION DATADOG METRIC NAME
Disk space used in bytes, excluding indexes (per table)* postgresql.table_size
Number of index scans (per index and per table)* postgresql.index_scans
1. Replication and checkpoint metrics—more info
METRIC DESCRIPTION DATADOG METRIC NAME Useful psql commands
Replication delay in bytes1 postgresql.replication_delay_bytes COMMAND
Replication delay in seconds postgresql.replication_delay Check version of PostgreSQL SELECT version();
Number of checkpoints requested & scheduled postgresql.bgwriter.checkpoints_requested & Show location of postgresql.conf file2 SHOW config_file;
postgresql.bgwriter.checkpoints_timed
Show location of data directory2 SHOW data_directory;
Show location of log directory (may be a path SHOW log_directory;
relative to data directory)2
2. Read query throughput and performance metrics—more info
List all databases \l
METRIC DESCRIPTION DATADOG METRIC NAME
List all tables + table size in bytes in the current \dt+
Number of index scans (per index and per table)* postgresql.index_scans database
Number of sequential scans (per table)* postgresql.seq_scans List all roles and privileges in the current database \du
Rows fetched by queries (per database) postgresql.rows_fetched Check if this server is a standby (in recovery mode) SELECT pg_is_in_recovery();
Rows returned by queries (per database) postgresql.rows_returned View connection info \conninfo
Bytes written temporarily to disk to execute queries postgresql.temp_bytes Show all available psql commands \?
(per database)1
*The Datadog Agent must be configured to track per-relation metrics
1
Available in version 9.2+
2
Requires superuser privileges
