набор джентельмена для мониторинга траблаков слона

postgres_analyze.sql

-- Index usage
SELECT t.schemaname,
       t.tablename,
       c.reltuples::bigint                            AS num_rows, pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
       psai.indexrelname                              AS index_name,
       pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
       CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END  AS "unique",
       psai.idx_scan                                  AS number_of_scans,
       psai.idx_tup_read                              AS tuples_read,
       psai.idx_tup_fetch                             AS tuples_fetched
FROM pg_tables t
         LEFT JOIN pg_class c ON t.tablename = c.relname
         LEFT JOIN pg_index i ON c.oid = i.indrelid
         LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid
WHERE t.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;
​
-- Index sum
SELECT pg_class.relname,
       pg_size_pretty(pg_class.reltuples::bigint) AS rows_in_bytes,
       pg_class.reltuples                         AS num_rows,
       COUNT(*)                                   AS total_indexes,
       COUNT(*)                                      FILTER ( WHERE indisunique)                  AS unique_indexes, COUNT(*) FILTER ( WHERE indnatts = 1 )                AS single_column_indexes, COUNT(*) FILTER ( WHERE indnatts IS DISTINCT FROM 1 ) AS multi_column_indexes
FROM pg_namespace
         LEFT JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
         LEFT JOIN pg_index ON pg_class.oid = pg_index.indrelid
WHERE pg_namespace.nspname in ('public', )
  AND pg_class.relkind = 'r'
GROUP BY pg_class.relname, pg_class.reltuples
ORDER BY pg_class.reltuples DESC;
​
-- Disk usage
WITH RECURSIVE
    pg_inherit(inhrelid, inhparent) AS
        (select inhrelid, inhparent
         FROM pg_inherits
         UNION
         SELECT child.inhrelid, parent.inhparent
         FROM pg_inherit child,
              pg_inherits parent
         WHERE child.inhparent = parent.inhrelid),
    pg_inherit_short AS (SELECT * FROM pg_inherit WHERE inhparent NOT IN (SELECT inhrelid FROM pg_inherit))
SELECT table_schema
     , TABLE_NAME
     , row_estimate
     , pg_size_pretty(total_bytes) AS total
     , pg_size_pretty(index_bytes) AS INDEX
     , pg_size_pretty(toast_bytes) AS toast
     , pg_size_pretty(table_bytes) AS TABLE
FROM (
    SELECT *, total_bytes-index_bytes- COALESCE (toast_bytes, 0) AS table_bytes
    FROM (
    SELECT c.oid
        , nspname AS table_schema
        , relname AS TABLE_NAME
        , SUM (c.reltuples) AS row_estimate
        , SUM (pg_total_relation_size(c.oid)) AS total_bytes
        , SUM (pg_indexes_size(c.oid)) AS index_bytes
        , SUM (pg_total_relation_size(reltoastrelid)) AS toast_bytes
    FROM (
    SELECT pg_class.oid
        , reltuples
        , relname
        , relnamespace
        , pg_class.reltoastrelid
    FROM pg_class
    LEFT JOIN pg_inherit_short ON inhrelid = oid
    WHERE relkind IN ('r', 'p')
    ) c
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
    ) a
    ) a
ORDER BY total_bytes DESC;
​
-- Check query lock
​
select coalesce(bgl.relation::regclass::text, bgl.locktype) AS locked_item,
       now() - bda.query_start                              AS waiting_duration,
       bda.pid                                              AS blocked_pid,
       bda.query                                            AS blocked_query,
       bdl.mode                                             AS blocked_mode,
       bga.pid                                              AS blocking_pid,
       bga.query                                            AS blocking_query,
       bgl.mode                                             AS blocking_mode
from pg_catalog.pg_locks bdl
         join pg_stat_activity bda
              on bda.pid = bdl.pid
         join pg_catalog.pg_locks bgl
              on bgl.pid != bdl.pid
    and (bgl.transactionid = bdl.transactionid
      or bgl.relation = bdl.relation and bgl.locktype = bdl.locktype)
  join pg_stat_activity bga
on bga.pid = bgl.pid
    and bga.datid = bda.datid
where not bdl.granted;
​
​
-- Connection
SELECT pid
     , datname
     , usename
     , application_name
     , client_hostname
     , client_port
     , backend_start
     , query_start
     , query
     , state
FROM pg_stat_activity;
​
-- Vacuum and other
SELECT *
FROM pg_stat_user_tables;
​
​
SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
         LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC;

-- Slow Query stats
SELECT query,
       calls,
       total_time,
       mean_time,
    rows,
    100.0 * shared_blks_hit / NULLIF (shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY mean_time, calls DESC LIMIT 20;
​
-- Global index cache usage
SELECT sum(idx_blks_read)                                           AS idx_read,
       sum(idx_blks_hit)                                            AS idx_hit,
       (sum(idx_blks_hit) - sum(idx_blks_read)):: float / sum(idx_blks_hit) AS ratio
FROM pg_statio_user_indexes;
​
-- Global data cache usage
SELECT sum(heap_blks_read)                                             AS heap_read,
       sum(heap_blks_hit)                                              AS heap_hit,
       (sum(heap_blks_hit) - sum(heap_blks_read)):: float / sum(heap_blks_hit) AS ratio
FROM pg_statio_user_tables;
​
-- Table size without partition group
SELECT table_schema
     , TABLE_NAME
     , rows_count
     , pg_size_pretty(total_bytes) AS total
     , pg_size_pretty(index_bytes) AS INDEX
     , pg_size_pretty(toast_bytes) AS toast
     , pg_size_pretty(table_bytes) AS TABLE
	 , heap_read
	 , heap_hit
	 , heap_ratio
	 , index_read
	 , index_hit
	 , idx_ratio
	 , toast_read
	 , toast_hit
	 , toast_ratio
FROM (
    SELECT *, total_bytes-index_bytes- COALESCE (toast_bytes, 0) AS table_bytes FROM (
    SELECT c.oid, nspname AS table_schema
        , c.relname AS TABLE_NAME
        , c.reltuples AS rows_count
        , pg_total_relation_size(c.oid) AS total_bytes
        , pg_indexes_size(c.oid) AS index_bytes
        , pg_total_relation_size(reltoastrelid) AS toast_bytes
        , heap_blks_read AS heap_read
        , heap_blks_hit AS heap_hit
        , (heap_blks_hit - heap_blks_read):: float / NULLIF (heap_blks_hit, 0) AS heap_ratio
        , idx_blks_read AS index_read
        , idx_blks_hit AS index_hit
        , (idx_blks_hit - idx_blks_read):: float / NULLIF (idx_blks_hit, 0) AS idx_ratio
        , toast_blks_read AS toast_read
        , toast_blks_hit AS toast_hit
        , (toast_blks_hit - toast_blks_read):: float / NULLIF (toast_blks_hit, 0) AS toast_ratio
    FROM pg_class c
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
    LEFT JOIN pg_statio_user_tables s ON s.relid = c.oid
    WHERE relkind = 'r' and nspname NOT IN ('pg_catalog', 'information_schema')
    ) a
    ) a
order by table_schema, TABLE_NAME;

adding pg_stat_statements to shared_preload_libraries in postgresql.conf
https://www.postgresql.org/docs/current/pgstatstatements.html