pg_replication:
  query: "SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT as lag, CASE WHEN pg_is_in_recovery() THEN 1 ELSE 0 END as is_replica"
  metrics:
    - lag:
        usage: "GAUGE"
        description: "Replication lag behind master in seconds"
    - is_replica:
        usage: "GAUGE"
        description: "Indicates if this host is a slave"

pg_postmaster:
  query: "SELECT pg_postmaster_start_time as start_time_seconds from pg_postmaster_start_time()"
  metrics:
    - start_time_seconds:
        usage: "GAUGE"
        description: "Time at which postmaster started"

pg_stat_user_tables:
  query: "SELECT current_database() datname, schemaname, relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup, n_dead_tup, n_mod_since_analyze, COALESCE(last_vacuum, '1970-01-01Z'), COALESCE(last_vacuum, '1970-01-01Z') as last_vacuum, COALESCE(last_autovacuum, '1970-01-01Z') as last_autovacuum, COALESCE(last_analyze, '1970-01-01Z') as last_analyze, COALESCE(last_autoanalyze, '1970-01-01Z') as last_autoanalyze, vacuum_count, autovacuum_count, analyze_count, autoanalyze_count FROM pg_stat_user_tables"
  metrics:
    - datname:
        usage: "LABEL"
        description: "Name of current database"
    - schemaname:
        usage: "LABEL"
        description: "Name of the schema that this table is in"
    - relname:
        usage: "LABEL"
        description: "Name of this table"
    - seq_scan:
        usage: "COUNTER"
        description: "Number of sequential scans initiated on this table"
    - seq_tup_read:
        usage: "COUNTER"
        description: "Number of live rows fetched by sequential scans"
    - idx_scan:
        usage: "COUNTER"
        description: "Number of index scans initiated on this table"
    - idx_tup_fetch:
        usage: "COUNTER"
        description: "Number of live rows fetched by index scans"
    - n_tup_ins:
        usage: "COUNTER"
        description: "Number of rows inserted"
    - n_tup_upd:
        usage: "COUNTER"
        description: "Number of rows updated"
    - n_tup_del:
        usage: "COUNTER"
        description: "Number of rows deleted"
    - n_tup_hot_upd:
        usage: "COUNTER"
        description: "Number of rows HOT updated (i.e., with no separate index update required)"
    - n_live_tup:
        usage: "GAUGE"
        description: "Estimated number of live rows"
    - n_dead_tup:
        usage: "GAUGE"
        description: "Estimated number of dead rows"
    - n_mod_since_analyze:
        usage: "GAUGE"
        description: "Estimated number of rows changed since last analyze"
    - last_vacuum:
        usage: "GAUGE"
        description: "Last time at which this table was manually vacuumed (not counting VACUUM FULL)"
    - last_autovacuum:
        usage: "GAUGE"
        description: "Last time at which this table was vacuumed by the autovacuum daemon"
    - last_analyze:
        usage: "GAUGE"
        description: "Last time at which this table was manually analyzed"
    - last_autoanalyze:
        usage: "GAUGE"
        description: "Last time at which this table was analyzed by the autovacuum daemon"
    - vacuum_count:
        usage: "COUNTER"
        description: "Number of times this table has been manually vacuumed (not counting VACUUM FULL)"
    - autovacuum_count:
        usage: "COUNTER"
        description: "Number of times this table has been vacuumed by the autovacuum daemon"
    - analyze_count:
        usage: "COUNTER"
        description: "Number of times this table has been manually analyzed"
    - autoanalyze_count:
        usage: "COUNTER"
        description: "Number of times this table has been analyzed by the autovacuum daemon"

pg_total_relation_size:
  query: |
    SELECT relnamespace::regnamespace as schemaname,
           relname as relname,
           pg_total_relation_size(oid) bytes
      FROM pg_class
     WHERE relkind = 'r';
  metrics:
    - schemaname:
        usage: "LABEL"
        description: "Name of the schema that this table is in"
    - relname:
        usage: "LABEL"
        description: "Name of this table"
    - bytes:
        usage: "GAUGE"
        description: "total disk space usage for the specified table and associated indexes"

pg_blocked:
  query: |
    SELECT
      count(blocked.transactionid) AS queries,
      '__transaction__' AS table
    FROM pg_catalog.pg_locks blocked
    WHERE NOT blocked.granted AND locktype = 'transactionid'
    GROUP BY locktype
    UNION
    SELECT
      count(blocked.relation) AS queries,
      blocked.relation::regclass::text AS table
    FROM pg_catalog.pg_locks blocked
    WHERE NOT blocked.granted AND locktype != 'transactionid'
    GROUP BY relation
  metrics:
    - queries:
        usage: "GAUGE"
        description: "The current number of blocked queries"
    - table:
        usage: "LABEL"
        description: "The table on which a query is blocked"

pg_slow:
  query: |
    SELECT COUNT(*) AS queries
    FROM pg_stat_activity
    WHERE state = 'active' AND (now() - query_start) > '1 seconds'::interval
  metrics:
    - queries:
        usage: "GAUGE"
        description: "Current number of slow queries"

pg_vacuum:
  query: |
    SELECT
      COUNT(*) AS queries,
      MAX(EXTRACT(EPOCH FROM (clock_timestamp() - query_start))) AS age_in_seconds
    FROM pg_catalog.pg_stat_activity
    WHERE state = 'active' AND trim(query) ~* '\AVACUUM (?!ANALYZE)'
  metrics:
    - queries:
        usage: "GAUGE"
        description: "The current number of VACUUM queries"
    - age_in_seconds:
        usage: "GAUGE"
        description: "The current maximum VACUUM query age in seconds"

pg_vacuum_analyze:
  query: |
    SELECT
      COUNT(*) AS queries,
      MAX(EXTRACT(EPOCH FROM (clock_timestamp() - query_start))) AS age_in_seconds
    FROM pg_catalog.pg_stat_activity
    WHERE state = 'active' AND trim(query) ~* '\AVACUUM ANALYZE'
  metrics:
    - queries:
        usage: "GAUGE"
        description: "The current number of VACUUM ANALYZE queries"
    - age_in_seconds:
        usage: "GAUGE"
        description: "The current maximum VACUUM ANALYZE query age in seconds"

pg_stuck_idle_in_transaction:
  query: |
    SELECT COUNT(*) AS queries
    FROM pg_stat_activity
    WHERE state = 'idle in transaction' AND (now() - query_start) > '10 minutes'::interval
  metrics:
    - queries:
        usage: "GAUGE"
        description: "Current number of queries that are stuck being idle in transactions"

pg_vacuum_queue:
  #master: true 
  # Until postgres_exporter is upgraded. See 
  # https://gitlab.com/gitlab-org/omnibus-gitlab/issues/4887
  query: |
    with table_opts_vs_statistic as (
      select
        pg_class.oid,
        pg_class.relname,
        coalesce(nspname, 'public') as schemaname,
        pg_class.relpages,
        pg_class.reltuples,
        case
          when array_to_string(reloptions, '') like '%autovacuum_vacuum_threshold%' then regexp_replace(array_to_string(reloptions, ''), '.*autovacuum_vacuum_threshold=([0-9.]+).*', E'\\1')::int8
          else current_setting('autovacuum_vacuum_threshold')::int8
        end as autovacuum_vacuum_threshold,
        case
          when array_to_string(reloptions, '') like '%autovacuum_vacuum_scale_factor%' then regexp_replace(array_to_string(reloptions, ''), '.*autovacuum_vacuum_scale_factor=([0-9.]+).*', E'\\1')::numeric
          else current_setting('autovacuum_vacuum_scale_factor')::numeric
        end as autovacuum_vacuum_scale_factor,
        case when array_to_string(reloptions, '') ~ 'autovacuum_enabled=(false|off)' then false else true end as autovacuum_enabled,
        n_dead_tup,
        last_autovacuum,
        last_vacuum
      from pg_class
      join pg_namespace ns on relnamespace = ns.oid 
      join pg_stat_all_tables psat  on psat.relid  = pg_class.oid
      where relkind in ('r','m') 
    ), p as (
      select pgspv.*,a.query,a.wait_event_type,a.wait_event,a.query_start
      from pg_stat_progress_vacuum pgspv
      left join pg_stat_activity a using (pid)
    ) 
    select
      table_opts_vs_statistic.schemaname as schemaname,
      table_opts_vs_statistic.relname as relname,
      round((100 * table_opts_vs_statistic.n_dead_tup::numeric / nullif(table_opts_vs_statistic.reltuples, 0))::numeric, 2) as dead_tup_pct,
      table_opts_vs_statistic.reltuples::numeric as reltuples,
      table_opts_vs_statistic.n_dead_tup,
      (relpages::bigint*8*1024) AS table_size_bytes,
      'V. Threshold:' || table_opts_vs_statistic.autovacuum_vacuum_threshold
        || ', V. Scale Factor: ' || (table_opts_vs_statistic.autovacuum_vacuum_scale_factor)::numeric *100 ||' %'
        || case when not autovacuum_enabled then ', DISABLED' else ', enabled' end as "effective_settings",
      case
        when last_autovacuum > coalesce(last_vacuum, '0001-01-01') then last_autovacuum::timestamp(0)
        when last_vacuum is not null then last_vacuum::timestamp(0)
        else null
      end as "last_vacuumed",
      case
        when last_autovacuum > coalesce(last_vacuum, '0001-01-01') then 'auto'
        when last_vacuum is not null then 'manual'
        else null
      end as "type",
      coalesce(p.phase, 'in queue') as status,
      p.pid as pid,
      coalesce  (p.query,'')as  action,
      case when p.pid is null then null else coalesce(wait_event_type ||'.'|| wait_event, 'f') end as waiting,
      round(100.0 * p.heap_blks_scanned / nullif(p.heap_blks_total, 0), 1) AS scanned_pct,
      round(100.0 * p.heap_blks_vacuumed / nullif(p.heap_blks_total, 0), 1) AS vacuumed_pct,
      extract ('epoch' from  now()-query_start) elapsed_time
    from 
    table_opts_vs_statistic
    full outer join p on p.relid = table_opts_vs_statistic.oid and p.datname = current_database()
    where
      table_opts_vs_statistic.relpages >= 8
      and  autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor::numeric * table_opts_vs_statistic.reltuples) < table_opts_vs_statistic.n_dead_tup
  metrics:
    - schemaname:
        usage: "LABEL"
        description: "Table Schema"
    - relname:
        usage: "LABEL"
        description: "Table name"
    - dead_tup_pct:
        usage: "GAUGE"
        description: "Estimated dead tuples percent"
    - reltuples:
        usage: "GAUGE"
        description: "Number of tuples in table"
    - n_dead_tup:
        usage: "GAUGE"
        description: "Estimated number of dead tuples"
    - table_size_bytes:
        usage: "GAUGE"
        description: "Estimated table size"
    - effective_settings:
        usage: "LABEL"
        description: "Autovacuums settings"
    - last_vacuumed:
        usage: "GAUGE"
        description: "Last time at which this table was vacuumed"
    - type:
        usage: "LABEL"
        description: "Last vacuum type"
    - status:
        usage: "LABEL"
        description: "Vacuum actual status"
    - pid:
        usage: "GAUGE"
        description: "Vacuum process id"
    - action:
        usage: "LABEL"
        description: "Type of vacuum executed"
    - waiting:
        usage: "LABEL"
        description: "Vacuum queue status"
    - scanned_pct:
        usage: "GAUGE"
        description: "Estimated rows scanned percent"
    - vacuumed_pct:
        usage: "GAUGE"
        description: "Estimated vacuumed rows percent"
    - elapsed_time:
        usage: "GAUGE"
        description: "Elapsed time vacuuming (in seconds)"

