SQL-запросы получения метрик для панели мониторинга «Метрики»#

SELECT
    (SELECT setting::BIGINT FROM pg_settings WHERE name = 'autovacuum_freeze_max_age') autovacuum_freeze_max_age,
    (SELECT setting::int FROM pg_settings WHERE name = 'autovacuum_max_workers') autovacuum_max_workers

SELECT
    datname,
    CASE WHEN (SELECT typlen FROM pg_type WHERE oid=28) = 4 THEN 2147483647 - age(datfrozenxid) ELSE (9223372*10^18)-1 - age(datfrozenxid) END AS limit
FROM pg_database
ORDER BY 2 ASC
LIMIT 25

SELECT
    schemaname,
    relname,
    vacuum_count,
    autovacuum_count
FROM pg_stat_user_tables
ORDER BY vacuum_count DESC, autovacuum_count DESC
LIMIT 25

WITH queries AS (
    SELECT
        datname,
        query
    FROM pg_stat_activity act
    WHERE act.query ~* '^autovacuum*(?:(?!.*\bwraparound\b))'
)
SELECT
    dat.datname,
    COUNT(query) AS regular
FROM pg_database dat
LEFT JOIN queries q ON dat.datname = q.datname
WHERE NOT dat.datistemplate
GROUP BY dat.datname
ORDER BY regular DESC
LIMIT 25

WITH queries AS MATERIALIZED (
    SELECT
        datname,
        query
    FROM pg_stat_activity act
    WHERE act.query ~* '^autovacuum*(?:(?!.*\bwraparound\b))'
)
SELECT
    dat.datname,
    COUNT(query) AS regular
FROM pg_database dat
LEFT JOIN queries q ON dat.datname = q.datname
WHERE NOT dat.datistemplate
GROUP BY dat.datname
ORDER BY regular DESC
LIMIT 25

WITH queries AS (
    SELECT
        datname,
        query
    FROM pg_stat_activity act
    WHERE act.query ~* '^vacuum'
)
SELECT
    dat.datname,
    COUNT(query) AS user
FROM pg_database dat
LEFT JOIN queries q ON dat.datname = q.datname
WHERE NOT dat.datistemplate
GROUP BY dat.datname
ORDER BY user DESC
LIMIT 25

WITH queries AS MATERIALIZED (
    SELECT
        datname,
        query
    FROM pg_stat_activity act
    WHERE act.query ~* '^vacuum'
)
SELECT
    dat.datname,
    COUNT(query) AS user
FROM pg_database dat
LEFT JOIN queries q ON dat.datname = q.datname
WHERE NOT dat.datistemplate
GROUP BY dat.datname
ORDER BY user DESC
LIMIT 25

WITH queries AS (
    SELECT
        datname,
        query
    FROM pg_stat_activity act
    WHERE act.query ~* '^autovacuum.*to prevent wraparound'
)
SELECT
    dat.datname,
    COUNT(query) AS wraparounds
FROM pg_database dat
LEFT JOIN queries q ON dat.datname = q.datname
WHERE NOT dat.datistemplate
GROUP BY dat.datname
ORDER BY wraparounds DESC
LIMIT 25

WITH queries AS MATERIALIZED (
    SELECT
        datname,
        query
    FROM pg_stat_activity act
    WHERE act.query ~* '^autovacuum.*to prevent wraparound'
)
SELECT
    dat.datname,
    COUNT(query) AS wraparounds
FROM pg_database dat
LEFT JOIN queries q ON dat.datname = q.datname
WHERE NOT dat.datistemplate
GROUP BY dat.datname
ORDER BY wraparounds DESC
LIMIT 25

SELECT
    current_setting('block_size') AS block_size,
    checkpoints_timed,
    checkpoints_req,
    checkpoint_write_time,
    checkpoint_sync_time,
    buffers_checkpoint*current_setting('block_size')::float AS buffers_checkpoint,
    buffers_clean*current_setting('block_size')::float AS buffers_clean,
    buffers_backend*current_setting('block_size')::float AS buffers_backend
FROM pg_stat_bgwriter

SELECT
    current_setting('block_size') AS block_size,
    cp.num_timed AS checkpoints_timed,
    cp.num_requested AS checkpoints_req,
    cp.write_time AS checkpoint_write_time,
    cp.sync_time AS checkpoint_sync_time,
    cp.buffers_written * current_setting('block_size')::float AS buffers_checkpoint,
    bg.buffers_clean * current_setting('block_size')::float AS buffers_clean,
    (SELECT sum(writes * op_bytes)::float AS buffers_backend FROM pg_stat_io WHERE backend_type = 'client backend' AND context IN ('normal', 'vacuum')) AS buffers_backend
FROM pg_stat_checkpointer cp
CROSS JOIN pg_stat_bgwriter bg

SELECT
    current_setting('block_size') AS block_size,
    cp.num_timed AS checkpoints_timed,
    cp.num_requested AS checkpoints_req,
    cp.write_time AS checkpoint_write_time,
    cp.sync_time AS checkpoint_sync_time,
    cp.buffers_written * current_setting('block_size')::float AS buffers_checkpoint,
    bg.buffers_clean * current_setting('block_size')::float AS buffers_clean,
    (SELECT sum(write_bytes)::float AS buffers_backend FROM pg_stat_io WHERE backend_type = 'client backend' AND context IN ('normal', 'vacuum')) AS buffers_backend
FROM pg_stat_checkpointer cp
CROSS JOIN pg_stat_bgwriter bg

SELECT
    datname,
    deadlocks,
    COALESCE(ROUND(100*blks_hit/NULLIF((blks_hit + blks_read),0),2),0) AS hit_ratio,
    conflicts,
    xact_commit,
    xact_rollback,
    tup_returned,
    tup_fetched,
    tup_inserted,
    tup_updated,
    tup_deleted
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1')
ORDER BY deadlocks DESC, hit_ratio DESC, xact_commit+xact_rollback DESC, tup_returned+tup_fetched+tup_inserted+tup_updated+tup_deleted DESC
LIMIT 25

SELECT
    datname,
    deadlocks,
    COALESCE(ROUND(100*blks_hit/NULLIF((blks_hit + blks_read),0),2),0) AS hit_ratio,
    COALESCE(checksum_failures, 0) AS checksum_failures,
    conflicts,
    xact_commit,
    xact_rollback,
    tup_returned,
    tup_fetched,
    tup_inserted,
    tup_updated,
    tup_deleted
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1')
ORDER BY deadlocks DESC, hit_ratio DESC, checksum_failures DESC, conflicts DESC, xact_commit+xact_rollback DESC, tup_returned+tup_fetched+tup_inserted+tup_updated+tup_deleted DESC
LIMIT 25

SELECT
    t2.rolname,
    t3.datname,
    (sum(t1.shared_blks_dirtied + t1.local_blks_dirtied)) * current_setting('block_size')::bigint AS dirty
FROM pg_stat_statements t1
JOIN pg_roles t2 ON t1.userid = t2.oid
JOIN pg_database t3 ON t1.dbid = t3.oid
WHERE datname NOT IN ('template0', 'template1')
GROUP BY rolname, datname
ORDER BY dirty DESC
LIMIT 25

SELECT
    archived_count,
    failed_count
FROM pg_stat_archiver

SELECT
    relname,
    COALESCE(ROUND(100*heap_blks_hit/NULLIF((heap_blks_hit + heap_blks_read),0),2),0) AS heap_hit_ratio,
    COALESCE(ROUND(100*idx_blks_hit/NULLIF((idx_blks_hit + idx_blks_read),0),2),0) AS idx_hit_ratio
FROM pg_statio_user_tables
ORDER BY heap_hit_ratio DESC, idx_hit_ratio DESC
LIMIT 25

SELECT
    t2.rolname,
    t3.datname,
    SUM(total_time/1000)::bigint AS total_time_sec
FROM pg_stat_statements t1
JOIN pg_roles t2 ON t1.userid=t2.oid
JOIN pg_database t3 ON t1.dbid = t3.oid
WHERE datname NOT IN ('template0', 'template1')
GROUP BY rolname, datname
ORDER BY total_time_sec DESC
LIMIT 25

SELECT
    t2.rolname,
    t3.datname,
    sum((total_plan_time + total_exec_time) / 1000)::bigint as total_time_sec
FROM pg_stat_statements t1
JOIN pg_roles t2 ON t1.userid = t2.oid
JOIN pg_database t3 ON t1.dbid = t3.oid
WHERE datname NOT IN ('template0', 'template1')
GROUP BY rolname, datname
ORDER BY total_time_sec DESC
LIMIT 25

SELECT
    t2.rolname,
    t3.datname,
    SUM(t1.rows) as rows
FROM pg_stat_statements t1
JOIN pg_roles t2 ON t1.userid = t2.oid
JOIN pg_database t3 ON t1.dbid = t3.oid
WHERE datname NOT IN ('template0', 'template1')
GROUP BY rolname, datname
ORDER BY rows DESC
LIMIT 25

SELECT 1 AS available

SELECT
    spcname,
    pg_tablespace_size(spcname) AS size
FROM pg_tablespace
ORDER BY size DESC
LIMIT 25

SELECT
    CASE WHEN (state IS NULL) THEN 'backend' ELSE state END,
    count(*)
FROM pg_stat_activity
GROUP BY state

SELECT
    client_addr,
    pg_current_wal_lsn() - replay_lsn AS total_lag_bytes
FROM pg_stat_replication
ORDER BY client_addr DESC
LIMIT 25

SELECT pg_current_wal_lsn() - '0/0' wal_bytes

SELECT wal_bytes
FROM pg_stat_wal

SELECT SUM(temp_bytes) as temp_bytes
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1')