info_vacuum_queries.sql

info_vacuum_queries.sql

\pset pager off
\set ECHO all
\set ON_ERROR_STOP 0

select version();
\dx
select now();

\x off
SELECT datname,
       age(d.datfrozenxid) max_age,
       ROUND(100*(age(d.datfrozenxid)/current_setting('vacuum_freeze_table_age')::float)) as per_to_tables_getting_frozen,
       ROUND(100*(age(d.datfrozenxid)/current_setting('autovacuum_freeze_max_age')::float)) AS per_to_wraparound_autovac,
       ROUND(100*(age(d.datfrozenxid)/2^31::float)) AS per_to_emergency_autovac,
       mxid_age(d.datminmxid) mx_max_age,
       ROUND(100*(mxid_age(d.datminmxid)/current_setting('vacuum_multixact_freeze_table_age')::float)) as mx_per_to_tables_getting_frozen,
       ROUND(100*(mxid_age(d.datminmxid)/current_setting('autovacuum_multixact_freeze_max_age')::float)) AS mx_per_to_wraparound_autovac,
       ROUND(100*(mxid_age(d.datminmxid)/2^31::float)) AS mx_per_to_emergency_autovac
FROM pg_catalog.pg_database d
WHERE d.datallowconn;

\x on
select now()-backend_start since_backend_start,
       now()-query_start since_query_start,
       now()-state_change since_state_change,
       age(backend_xid) xid_age,
       age(backend_xmin) xmin_age,
       *
from pg_stat_activity
order by state;

--general vacuum queries
\x off
WITH q AS (
SELECT
  (SELECT max(age(backend_xmin))
      FROM pg_stat_activity  WHERE state != 'idle' )       AS oldest_running_xact_age,
  (SELECT max(age(transaction)) FROM pg_prepared_xacts)    AS oldest_prepared_xact_age,
  (SELECT max(age(xmin)) FROM pg_replication_slots)        AS oldest_replication_slot_age,
  (SELECT max(age(backend_xmin)) FROM pg_stat_replication) AS oldest_replica_xact_age
)
SELECT *,
       2^31 - oldest_running_xact_age AS oldest_running_xact_left,
       2^31 - oldest_prepared_xact_age AS oldest_prepared_xact_left,
       2^31 - oldest_replication_slot_age AS oldest_replication_slot_left,
       2^31 - oldest_replica_xact_age AS oldest_replica_xact_left
FROM q;

\x off
WITH agg AS (
	SELECT
	  count(*) FILTER (
        WHERE c.relfrozenxid != 0
          and age(c.relfrozenxid) > current_setting('autovacuum_freeze_max_age')::int
        ) needs_wraparound_vac,
	  count(*) FILTER (
      WHERE c.reltuples > 1000
        and c.relfrozenxid != 0
        and age(c.relfrozenxid) > current_setting('autovacuum_freeze_max_age')::int
      ) needs_wraparound_vac_big,
    count(*) total
	FROM pg_stat_all_tables stat
	LEFT JOIN pg_class c on (c.oid=stat.relid)
)
SELECT
	needs_wraparound_vac,
  needs_wraparound_vac_big,
  total,
  needs_wraparound_vac::float/total::float per_needs_wraparound_vac
from agg;

\x off
SELECT
  stat.relid::regclass,
  age(c.relfrozenxid) > current_setting('vacuum_freeze_min_age')::int
    and age(c.relfrozenxid) > current_setting('vacuum_freeze_table_age')::int autovacuum_can_freeze,
  ROUND(100*(age(c.relfrozenxid)/current_setting('vacuum_freeze_table_age')::float)) as perc_to_frozen,
  ROUND(100*(age(c.relfrozenxid)/ current_setting('autovacuum_freeze_max_age')::float))  perc_to_wrap,
  CASE WHEN c.reltuples > 0 THEN
	  ROUND(100*(stat.n_ins_since_vacuum /c.reltuples))
	  ELSE 0
  END per_to_autovac,
  age(c.relfrozenxid),
  c.relfrozenxid,
  c.reltuples,
  --stat.n_tup_ins,
  --stat.n_live_tup,
  stat.n_ins_since_vacuum,
  stat.last_vacuum,
  stat.last_autovacuum,
  --stat.vacuum_count,
  stat.autovacuum_count,
  age(c.relfrozenxid) < age(t.relfrozenxid) toast_is_worse
FROM
  pg_stat_all_tables stat
LEFT JOIN pg_class c on (c.oid=stat.relid)
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relfrozenxid != 0
ORDER BY age desc nulls last
LIMIT 1000;


--mxid queries
\x off
WITH agg AS (
	SELECT
	  count(*) FILTER (
      WHERE c.relminmxid != 0 and
      mxid_age(c.relminmxid) > current_setting('autovacuum_multixact_freeze_max_age')::int
    ) needs_wraparound_vac,
	  count(*) total
	FROM pg_stat_all_tables stat
	LEFT JOIN pg_class c on (c.oid=stat.relid)
)
SELECT
	needs_wraparound_vac,
  total,
  needs_wraparound_vac::float/total::float per_needs_wraparound_vac
from agg;

\x off
SELECT
  stat.relid::regclass,
  mxid_age(c.relminmxid) > current_setting('vacuum_multixact_freeze_min_age')::int
    and mxid_age(c.relminmxid) > current_setting('vacuum_multixact_freeze_table_age')::int autovacuum_can_freeze,
  ROUND(100*(mxid_age(c.relminmxid)/current_setting('vacuum_multixact_freeze_table_age')::float)) as perc_to_frozen,
  ROUND(100*(mxid_age(c.relminmxid)/ current_setting('autovacuum_multixact_freeze_max_age')::float))  perc_to_wrap,
  ROUND(100*(stat.n_ins_since_vacuum /c.reltuples)) per_to_autovac,
  mxid_age(c.relminmxid) age,
  c.relminmxid,
  c.reltuples,
  stat.n_ins_since_vacuum,
  stat.last_vacuum,
  stat.last_autovacuum,
  stat.autovacuum_count,
  mxid_age(c.relminmxid) < mxid_age(t.relminmxid) toast_is_worse
FROM
  pg_stat_all_tables stat
LEFT JOIN pg_class c on (c.oid=stat.relid)
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relminmxid != 0 and c.reltuples > 1000
ORDER BY age desc nulls last
LIMIT 1000;


--replication slot queries
\x on
SELECT * FROM pg_prepared_xacts;
SELECT * FROM pg_replication_slots;
SELECT * FROM pg_stat_replication;

\x on
select
  pid,
  application_name,
  --the following reports lag in bytes
  pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) sending_lag,
  pg_wal_lsn_diff(sent_lsn, flush_lsn) receiving_lag,
  pg_wal_lsn_diff(flush_lsn, replay_lsn) replaying_lag,
  pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) total_lag
from pg_stat_replication;

SHOW ALL;