<commit_before>--Vacuum: Current Activity

-- Based on: https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/vacuum_activity.sql
with data as (
  select
    p.pid as pid,
    (select spcname from pg_tablespace where oid = reltablespace) as tblspace,
    p.datname as database,
    nspname as schema_name,
    relname as table_name,
    (now() - a.xact_start) as duration,
    coalesce(wait_event_type ||'.'|| wait_event, null) as waiting,
    case
      when a.query ~* '^autovacuum.*to prevent wraparound' then 'wraparound'
      when a.query ~* '^vacuum' then 'user'
      else 'auto'
    end as mode,
    p.phase,
    pg_size_pretty(p.heap_blks_total * current_setting('block_size')::int) as heap_size,
    pg_size_pretty(pg_total_relation_size(relid)) as total_size,
    pg_size_pretty(p.heap_blks_scanned * current_setting('block_size')::int) as scanned,
    pg_size_pretty(p.heap_blks_vacuumed * current_setting('block_size')::int) as vacuumed,
    round(100.0 * p.heap_blks_scanned / p.heap_blks_total, 2) as scanned_pct,
    round(100.0 * p.heap_blks_vacuumed / p.heap_blks_total, 2) as vacuumed_pct,
    p.index_vacuum_count,
    round(100.0 * p.num_dead_tuples / p.max_dead_tuples, 2) as dead_pct,
    p.max_dead_tuples
  from pg_stat_progress_vacuum p
  left join pg_stat_activity a using (pid)
  left join pg_class c on c.oid = p.relid
  left join pg_namespace n on n.oid = c.relnamespace
)
select
  pid as "PID",
  duration::interval(0)::text as "Duration",
  mode as "Mode",
  database || coalesce(
    e'\n' || coalesce(nullif(schema_name, 'public') || '.', '') || table_name || coalesce(' [' || tblspace || ']', ''),
    ''
  ) as "DB & Table",
  heap_size as "Heap",
  total_size as "Total",
  waiting as "Wait",
  phase as "Phase",
  scanned || ' (' || scanned_pct || '%)' as "Heap Scanned",
  vacuumed || ' (' || vacuumed_pct || '%)' as "Heap Vacuumed",
  index_vacuum_count || ' completed cycles,'
    || e'\n' || dead_pct || e'% dead tuples\nof max ~'
    || case<commit_msg>v1: better wording for Heap & Index Vacuuming columns<commit_after>--Vacuum: Current Activity

-- Based on: https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/vacuum_activity.sql
with data as (
  select
    p.pid as pid,
    (select spcname from pg_tablespace where oid = reltablespace) as tblspace,
    p.datname as database,
    nspname as schema_name,
    relname as table_name,
    (now() - a.xact_start) as duration,
    coalesce(wait_event_type ||'.'|| wait_event, null) as waiting,
    case
      when a.query ~* '^autovacuum.*to prevent wraparound' then 'wraparound'
      when a.query ~* '^vacuum' then 'user'
      else 'auto'
    end as mode,
    p.phase,
    pg_size_pretty(p.heap_blks_total * current_setting('block_size')::int) as heap_size,
    pg_size_pretty(pg_total_relation_size(relid)) as total_size,
    pg_size_pretty(p.heap_blks_scanned * current_setting('block_size')::int) as scanned,
    pg_size_pretty(p.heap_blks_vacuumed * current_setting('block_size')::int) as vacuumed,
    round(100.0 * p.heap_blks_scanned / p.heap_blks_total, 2) as scanned_pct,
    round(100.0 * p.heap_blks_vacuumed / p.heap_blks_total, 2) as vacuumed_pct,
    p.index_vacuum_count,
    round(100.0 * p.num_dead_tuples / p.max_dead_tuples, 2) as dead_pct,
    p.num_dead_tuples,
    p.max_dead_tuples
  from pg_stat_progress_vacuum p
  left join pg_stat_activity a using (pid)
  left join pg_class c on c.oid = p.relid
  left join pg_namespace n on n.oid = c.relnamespace
)
select
  pid as "PID",
  duration::interval(0)::text as "Duration",
  mode as "Mode",
  database || coalesce(
    e'\n' || coalesce(nullif(schema_name, 'public') || '.', '') || table_name || coalesce(' [' || tblspace || ']', ''),
    ''
  ) as "DB & Table",
  heap_size as "Heap",
  total_size as "Total",
  waiting as "Wait",
  phase as "Phase",
  scanned || ' (' || scanned_pct || '%)' || e' scanned\n'
    || vacuumed || ' (' || vacuumed_pct || '%) vacuumed' as "Heap Vacuuming",
  index_vacuum_count || ' completed cycles,'
    || e'\n'
    || case
      when num_dead_tuples > 10^12 then round(num_dead_tuples::numeric / 10^12::numeric, 0)::text || 'T'
      when num_dead_tuples > 10^9 then round(num_dead_tuples::numeric / 10^9::numeric, 0)::text || 'B'
      when num_dead_tuples > 10^6 then round(num_dead_tuples::numeric / 10^6::numeric, 0)::text || 'M'
      when num_dead_tuples > 10^3 then round(num_dead_tuples::numeric / 10^3::numeric, 0)::text || 'k'
      else num_dead_tuples::text
    end
    || ' (' || dead_pct || e'%) dead tuples\nof max ~'
    || case
{'fraction_non_alphanumeric': 0.14154652686762778, 'fraction_numerical': 0.013761467889908258, 'mean_word_length': 3.8098739495798317, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 7, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5752588', 'n_tokens_mistral': 1783, 'n_tokens_neox': 1705, 'n_words': 383}