Various Postgres Stats SQL Queries

db_size.sql

-- DB Size By DB
SELECT d.datname                 AS db,
       pg_database_size(d.datid) AS database_size
FROM pg_stat_database d;

-- DB Size By Table
SELECT nspname              AS "schema",
       relname              AS "table_name",
       CASE relkind
           WHEN 'r' THEN 'regular'
           WHEN 'i' THEN 'index'
           WHEN 'S' THEN 'sequence'
           WHEN 'T' THEN 'toast'
           WHEN 'v' THEN 'view'
           WHEN 'm' THEN 'materialized_view'
           WHEN 'c' THEN 'composite_type'
           WHEN 'f' THEN 'foreign_table'
           WHEN 'p' THEN 'partitioned_table'
           WHEN 'I' THEN 'partitioned_index'
           END              AS "table_type",
       pg_table_size(C.oid) AS "table_size"
FROM pg_class C
         LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast');

-- DB Checkpoint Stat
SELECT checkpoints_req, checkpoints_timed from pg_stat_bgwriter;

db_stats.sql

-- DB Stats
SELECT datname       AS db,
       numbackends   AS active_connections,
       tup_fetched   AS rows_fetched,
       tup_returned  AS rows_returned,
       tup_inserted  AS rows_inserted,
       tup_updated   AS rows_updated,
       tup_deleted   AS rows_deleted,
       temp_bytes,
       deadlocks,
       xact_commit   AS transaction_commit,
       xact_rollback AS transaction_rollback
FROM pg_stat_database;

-- Table Stats
SELECT schemaname                 AS schema,
       relname                    AS table_name,
       COALESCE(seq_scan, 0)      AS seq_scan,
       COALESCE(idx_scan, 0)      AS idx_scan,
       COALESCE(seq_tup_read, 0)  AS seq_rows_fetched,
       COALESCE(idx_tup_fetch, 0) AS idx_rows_fetched,
       COALESCE(n_tup_ins, 0)     AS rows_inserted,
       COALESCE(n_tup_upd, 0)     AS rows_updated,
       COALESCE(n_tup_hot_upd, 0) AS rows_hot_updated,
       COALESCE(n_tup_del, 0)     AS rows_deleted,
       COALESCE(n_dead_tup, 0)    AS rows_dead
FROM pg_stat_user_tables;

References

https://www.datadoghq.com/blog/postgresql-monitoring/
https://www.datadoghq.com/blog/postgresql-monitoring-tools/