Соответствие SQL-запросов записи и PromQL-запросов чтения#

SELECT (
        SELECT setting::int
            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, 2147483647 - age(datfrozenxid) AS limit FROM pg_database ORDER BY 2 ASC LIMIT 25;

SELECT schemaname, relname, vacuum_count, autovacuum_count FROM pg_stat_user_tables;

SELECT datname, COUNT(query) AS regular FROM pg_stat_activity WHERE query ~* '^autovacuum*(?:(?!.*\bwraparound\b))' GROUP BY datname ORDER BY 2 DESC LIMIT 25;

SELECT datname, COUNT(query) AS user FROM pg_stat_activity WHERE query ~* '^vacuum' GROUP BY datname ORDER BY 2 DESC LIMIT 25;

SELECT datname, COUNT(query) AS wraparound FROM pg_stat_activity WHERE query ~* '^autovacuum.*to prevent wraparound' GROUP BY datname ORDER BY 2 DESC LIMIT 25;

SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time, buffers_checkpoint, buffers_clean, buffers_backend FROM pg_stat_bgwriter;

SELECT datname, deadlocks, round(100*blks_hit/(blks_hit + blks_read),2) AS hit_ratio, temp_bytes, 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, temp_bytes DESC, xact_commit+xact_rollback DESC, tup_returned+tup_fetched+tup_inserted+tup_updated+tup_deleted DESC LIMIT 25;

SELECT datname, deadlocks, round(100*blks_hit/(blks_hit + blks_read),2) AS hit_ratio, temp_bytes, 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, temp_bytes 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,queryid::text,(t1.shared_blks_dirtied+t1.local_blks_dirtied) 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 ORDER by dirty DESC LIMIT 5;

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,total_time/1000 AS total_time_sec,left(query,32) AS query,queryid
          FROM pg_stat_statements t1 JOIN pg_roles t2 ON t1.userid=t2.oid JOIN pg_database t3 ON
            t1.dbid = t3.oid ORDER by total_time DESC LIMIT 5;

SELECT t2.rolname,t3.datname,(total_plan_time + total_exec_time)/1000 AS total_time_sec,left(query,32) AS
query,queryid FROM pg_stat_statements t1 JOIN pg_roles t2 ON t1.userid=t2.oid JOIN pg_database t3 ON
t1.dbid = t3.oid ORDER by total_exec_time DESC LIMIT 5;

SELECT t2.rolname,t3.datname,rows,left(query,32) AS query,queryid::text FROM pg_stat_statements t1 JOIN pg_roles t2 ON t1.userid=t2.oid JOIN pg_database t3 ON t1.dbid=t3.oid ORDER by rows DESC LIMIT 5;

SELECT 1 as available;

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

SELECT state,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;