pgse_vacuum_user:
  query: "select count(query) as user FROM pg_stat_activity where query ~* '^vacuum'"
  metrics:
    - user:
        usage: "GAUGE"
        description: "Autovacuum user"

pgse_vacuum:
  query: "select count(query) as regular FROM pg_stat_activity where query ~* '^autovacuum*(?:(?!.*\bwraparound\b))'"
  metrics:
    - regular:
        usage: "GAUGE"
        description: "Autovacuum regular"

pgse_vacuum_for_each_table:
  query: "select schemaname, relname, vacuum_count, autovacuum_count from pg_stat_user_tables"
  metrics:
    - schemaname:
        usage: "LABEL"
        description: "Name of the schema"
    - relname:
        usage: "LABEL"
        description: "Relation name"
    - vacuum_count:
        usage: "GAUGE"
        description: "Vacuum count for each user table"
    - autovacuum_count:
        usage: "GAUGE"
        description: "Autovacuum count for each user table"
  
pgse_autovacuum_limit:
  query: "SELECT
            datname,
            2147483647 - age(datfrozenxid) AS to_limit
           FROM pg_database ORDER BY age(datfrozenxid) DESC"
  metrics:
    - datname:
        usage: "LABEL"
        description: "pgse_autovacuum_limit_datname"
    - to_limit:
        usage: "GAUGE"
        description: "pgse_autovacuum_limit"

pgse_dirtypages:
  query: "select t2.rolname,t3.datname,queryid,(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"
  metrics:
    - rolname:
        usage: "LABEL"
        description: "Username"
    - datname:
        usage: "LABEL"
        description: "Database name"
    - queryid:
        usage: "LABEL"
        description: "Query id"
    - dirty:
        usage: "GAUGE"
        description: "dirty pages count"

pgse_bgwriter_lru_maxpages:
  query: "select current_setting('bgwriter_lru_maxpages')"
  metrics:
    - current_setting:
        usage: "GAUGE"
        description: "pgse_bgwriter_lru_maxpages"

pgse_replication:
  query: "select client_addr, pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn)::bigint as total_lag_bytes, extract(epoch from replay_lag) as replay_lag_seconds, extract(epoch from write_lag) as write_lag_seconds, extract(epoch from flush_lag) as flush_lag_seconds,pg_wal_lsn_diff(pg_current_wal_lsn(),sent_lsn)::bigint as pending_bytes,pg_wal_lsn_diff(sent_lsn,write_lsn)::bigint as write_bytes,pg_wal_lsn_diff(write_lsn,flush_lsn)::bigint as flush_bytes,pg_wal_lsn_diff(flush_lsn,replay_lsn)::bigint as replay_bytes from pg_stat_replication"
  metrics:
    - client_addr:
        usage: "LABEL"
        description: "client_addr"
    - total_lag_bytes:
        usage: "GAUGE"
        description: "total_lag"
    - replay_lag_seconds:
        usage: "GAUGE"
        description: "replay_lag or total lag"
    - write_lag_seconds:
        usage: "GAUGE"
        description: "write_lag"
    - flush_lag_seconds:
        usage: "GAUGE"
        description: "flush_lag"
    - pending_bytes:
        usage: "GAUGE"
        description: "pending lag bytes"
    - write_bytes:
        usage: "GAUGE"
        description: "write lag bytes"
    - flush_bytes:
        usage: "GAUGE"
        description: "flush lag bytes"
    - replay_bytes:
        usage: "GAUGE"
        description: "replay lag bytes"

pgse_replication_slots_retained_bytes:
  query: "select slot_name,slot_type,case active when 't' then 1 when 'f' then 0 END as active,pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn) as restart_lsn_diff,pg_wal_lsn_diff(pg_current_wal_lsn(),confirmed_flush_lsn) as not_received from pg_replication_slots"
  metrics:
    - slot_name:
        usage: "LABEL"
        description: "slot_name"
    - slot_type:
        usage: "LABEL"
        description: "slot_type"
    - active:
        usage: "GAUGE"
        description: "slot activity"
    - restart_lsn_diff:
        usage: "GAUGE"
        description: "remaining bytes"
    - not_received:
        usage: "GAUGE"
        description: "not received by logical replication"

#pgse_hit_buffers_database:
#  query: "select datname,blks_hit*100::numeric/(blks_hit + blks_read) as hit_ratio from pg_stat_database where blks_read>0"
#  metrics:
#    - datname:
#        usage: "LABEL"
#        description: "datname"
#    - hit_ratio:
#        usage: "GAUGE"
#        description: "hit ratio per database"

pgse_statio_user_tables:
  query: "select relname,heap_blks_hit,heap_blks_read,idx_blks_hit,idx_blks_read from pg_statio_user_tables"
  metrics:
    - relname:
        usage: "LABEL"
        description: "relname"
    - heap_blks_hit:
        usage: "GAUGE"
        description: "Number of disk blocks read from this table"
    - heap_blks_read:
        usage: "GAUGE"
        description: "Number of buffer hits in this table"
    - idx_blks_hit:
        usage: "GAUGE"
        description: "Number of buffer hits in all indexes on this table"
    - idx_blks_read:
        usage: "GAUGE"
        description: "Number of disk blocks read from all indexes on this table"

pg_stat_statements:
  query: "SELECT t2.rolname, t3.datname, queryid, calls, total_time / 1000 as total_time_seconds, min_time / 1000 as min_time_seconds, max_time / 1000 as max_time_seconds, mean_time / 1000 as mean_time_seconds, stddev_time / 1000 as stddev_time_seconds, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written, blk_read_time / 1000 as blk_read_time_seconds, blk_write_time / 1000 as blk_write_time_seconds FROM pg_stat_statements t1 JOIN pg_roles t2 ON (t1.userid=t2.oid) JOIN pg_database t3 ON (t1.dbid=t3.oid)"
  master: true
  metrics:
    - rolname:
        usage: "LABEL"
        description: "Name of user"
    - datname:
        usage: "LABEL"
        description: "Name of database"
    - queryid:
        usage: "LABEL"
        description: "Query ID"
    - calls:
        usage: "COUNTER"
        description: "Number of times executed"
    - total_time_seconds:
        usage: "COUNTER"
        description: "Total time spent in the statement, in milliseconds"
    - min_time_seconds:
        usage: "GAUGE"
        description: "Minimum time spent in the statement, in milliseconds"
    - max_time_seconds:
        usage: "GAUGE"
        description: "Maximum time spent in the statement, in milliseconds"
    - mean_time_seconds:
        usage: "GAUGE"
        description: "Mean time spent in the statement, in milliseconds"
    - stddev_time_seconds:
        usage: "GAUGE"
        description: "Population standard deviation of time spent in the statement, in milliseconds"
    - rows:
        usage: "COUNTER"
        description: "Total number of rows retrieved or affected by the statement"
    - shared_blks_hit:
        usage: "COUNTER"
        description: "Total number of shared block cache hits by the statement"
    - shared_blks_read:
        usage: "COUNTER"
        description: "Total number of shared blocks read by the statement"
    - shared_blks_dirtied:
        usage: "COUNTER"
        description: "Total number of shared blocks dirtied by the statement"
    - shared_blks_written:
        usage: "COUNTER"
        description: "Total number of shared blocks written by the statement"
    - local_blks_hit:
        usage: "COUNTER"
        description: "Total number of local block cache hits by the statement"
    - local_blks_read:
        usage: "COUNTER"
        description: "Total number of local blocks read by the statement"
    - local_blks_dirtied:
        usage: "COUNTER"
        description: "Total number of local blocks dirtied by the statement"
    - local_blks_written:
        usage: "COUNTER"
        description: "Total number of local blocks written by the statement"
    - temp_blks_read:
        usage: "COUNTER"
        description: "Total number of temp blocks read by the statement"
    - temp_blks_written:
        usage: "COUNTER"
        description: "Total number of temp blocks written by the statement"
    - blk_read_time_seconds:
        usage: "COUNTER"
        description: "Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)"
    - blk_write_time_seconds:
        usage: "COUNTER"
        description: "Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)"

pgse_stat_statements_total_time:
  query: "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"
  metrics:
    - rolname:
        usage: "LABEL"
        description: "Name of user"
    - datname:
        usage: "LABEL"
        description: "Name of database"
    - total_time_sec:
        usage: "GAUGE"
        description: "Total time for query in seconds"
    - query:
        usage: "LABEL"
        description: "Query text"
    - queryid:
        usage: "LABEL"
        description: "Query ID"

pgse_stat_statements_avg:
  query: "select t2.rolname,t3.datname,mean_time/1000 as mean_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 mean_time DESC LIMIT 5"
  metrics:
    - rolname:
        usage: "LABEL"
        description: "Name of user"
    - datname:
        usage: "LABEL"
        description: "Name of database"
    - mean_time_sec:
        usage: "GAUGE"
        description: "Mean time for query in seconds"
    - query:
        usage: "LABEL"
        description: "Query text"
    - queryid:
        usage: "LABEL"
        description: "Query ID"

pgse_stat_statements_calls:
  query: "select t2.rolname,t3.datname,calls,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 calls DESC LIMIT 5"
  metrics:
    - rolname:
        usage: "LABEL"
        description: "Name of user"
    - datname:
        usage: "LABEL"
        description: "Name of database"
    - calls:
        usage: "COUNTER"
        description: "Number of times executed"
    - query:
        usage: "LABEL"
        description: "Query text"
    - queryid:
        usage: "LABEL"
        description: "Query ID"

pgse_stat_statements_cpu:
  query: "select t2.rolname,t3.datname,(total_time-blk_read_time-blk_write_time)/calls::numeric(20, 2) as time,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 time DESC LIMIT 5"
  metrics:
    - rolname:
        usage: "LABEL"
        description: "Name of user"
    - datname:
        usage: "LABEL"
        description: "Name of database"
    - time:
        usage: "GAUGE"
        description: "avg cpu time"
    - query:
        usage: "LABEL"
        description: "Query text"
    - queryid:
        usage: "LABEL"
        description: "Query ID"

pgse_stat_statements_io:
  query: "select t2.rolname,t3.datname, ((blk_read_time+blk_write_time)/calls)::numeric(20, 2) as time,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 time DESC LIMIT 5"
  metrics:
    - rolname:
        usage: "LABEL"
        description: "Name of user"
    - datname:
        usage: "LABEL"
        description: "Name of database"
    - time:
        usage: "GAUGE"
        description: "avg io time"
    - query:
        usage: "LABEL"
        description: "Query text"
    - queryid:
        usage: "LABEL"
        description: "Query ID"

pgse_stat_statements_bandwidth:
  query: "select t2.rolname,t3.datname, (shared_blks_hit+shared_blks_read+shared_blks_dirtied+shared_blks_written+temp_blks_read+temp_blks_written) as band,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 band DESC LIMIT 5"
  metrics:
    - rolname:
        usage: "LABEL"
        description: "Name of user"
    - datname:
        usage: "LABEL"
        description: "Name of database"
    - band:
        usage: "GAUGE"
        description: "avg bandwidth"
    - query:
        usage: "LABEL"
        description: "Query text"
    - queryid:
        usage: "LABEL"
        description: "Query ID"

pgse_stat_statements_rows:
  query: "select t2.rolname,t3.datname,rows,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 rows DESC LIMIT 5"
  metrics:
    - rolname:
        usage: "LABEL"
        description: "Name of user"
    - datname:
        usage: "LABEL"
        description: "Name of database"
    - rows:
        usage: "GAUGE"
        description: "count rows"
    - query:
        usage: "LABEL"
        description: "Query text"
    - queryid:
        usage: "LABEL"
        description: "Query ID"

pgse_stat_statements_avg_rows:
  query: "select t2.rolname,t3.datname,rows/calls as avg_rows,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 avg_rows DESC LIMIT 5"
  metrics:
    - rolname:
        usage: "LABEL"
        description: "Name of user"
    - datname:
        usage: "LABEL"
        description: "Name of database"
    - avg_rows:
        usage: "GAUGE"
        description: "count average rows"
    - query:
        usage: "LABEL"
        description: "Query text"
    - queryid:
        usage: "LABEL"
        description: "Query ID"

pgse_stat_activity:
  query: "SELECT left(query,32) as query, pid::text, coalesce(usename, 'NULL') AS usename, coalesce(datname, 'NULL') AS datname, wait_event_type, wait_event, 1 as check FROM pg_stat_activity where length(wait_event) > 0"
  metrics:
    - query:
        usage: "LABEL"
        description: "Query text"
    - pid:
        usage: "LABEL"
        description: "PID"
    - usename:
        usage: "LABEL"
        description: "User name"
    - datname:
        usage: "LABEL"
        description: "Database name"
    - wait_event_type:
        usage: "LABEL"
        description: "field from pg_stat_activity"
    - wait_event:
        usage: "LABEL"
        description: "field from pg_stat_activity"
    - check:
        usage: "GAUGE"
        description: "for view"
