Tables

General Size

SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS index
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS table
  FROM (
  SELECT *, total_bytes-index_bytes-coalesce(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS table_name
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a;

Check for Bloat

SELECT  objectname, pg_size_pretty(size_bytes) as object_size,     
        pg_size_pretty(free_space_bytes) as reusable_space, 
        pg_size_pretty(dead_tuple_size_bytes) dead_tuple_space, 
        free_percent
FROM bloat_stats;

Column Stats

select * from pg_stats where tablename='mytable';

Size (unpartitioned)

WITH RECURSIVE pg_inherit(inhrelid, inhparent) AS
    (select inhrelid, inhparent
    FROM pg_inherits
    UNION
    SELECT child.inhrelid, parent.inhparent
    FROM pg_inherit child, pg_inherits parent
    WHERE child.inhparent = parent.inhrelid),
pg_inherit_short AS (SELECT * FROM pg_inherit WHERE inhparent NOT IN (SELECT inhrelid FROM pg_inherit))
SELECT table_schema
    , TABLE_NAME
    , row_estimate
    , pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
    , round(total_bytes::float8 / sum(total_bytes) OVER ()) AS total_size_share
  FROM (
    SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes
    FROM (
         SELECT c.oid
              , nspname AS table_schema
              , relname AS TABLE_NAME
              , SUM(c.reltuples) OVER (partition BY parent) AS row_estimate
              , SUM(pg_total_relation_size(c.oid)) OVER (partition BY parent) AS total_bytes
              , SUM(pg_indexes_size(c.oid)) OVER (partition BY parent) AS index_bytes
              , SUM(pg_total_relation_size(reltoastrelid)) OVER (partition BY parent) AS toast_bytes
              , parent
          FROM (
                SELECT pg_class.oid
                    , reltuples
                    , relname
                    , relnamespace
                    , pg_class.reltoastrelid
                    , COALESCE(inhparent, pg_class.oid) parent
                FROM pg_class
                    LEFT JOIN pg_inherit_short ON inhrelid = oid
                WHERE relkind IN ('r', 'p')
             ) c
             LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  ) a
  WHERE oid = parent
) a
ORDER BY total_bytes DESC;

Size (partitioned)

WITH RECURSIVEpg_inherit(inhrelid, inhparent) AS
    (selectinhrelid, inhparent
    FROMpg_inherits
    UNION
    SELECTchild.inhrelid, parent.inhparent
    FROMpg_inherit child, pg_inherits parent
    WHEREchild.inhparent = parent.inhrelid),
pg_inherit_short AS(SELECT* FROMpg_inherit WHEREinhparent NOTIN(SELECTinhrelid FROMpg_inherit))
SELECTparent::regclass
    , coalesce(spcname, 'default') pg_tablespace_name
    , row_estimate
    , pg_size_pretty(total_bytes) AStotal
    , pg_size_pretty(index_bytes) ASINDEX
    , pg_size_pretty(toast_bytes) AStoast
    , pg_size_pretty(table_bytes) ASTABLE
    , round(100* total_bytes::float8/ sum(total_bytes) OVER()) ASPERCENT
  FROM(
    SELECT*, total_bytes-index_bytes-COALESCE(toast_bytes,0) AStable_bytes
    FROM(
         SELECTparent
              , reltablespace
              , SUM(c.reltuples) ASrow_estimate
              , SUM(pg_total_relation_size(c.oid)) AStotal_bytes
              , SUM(pg_indexes_size(c.oid)) ASindex_bytes
              , SUM(pg_total_relation_size(reltoastrelid)) AStoast_bytes
          FROM(
                SELECTpg_class.oid
                    , reltuples
                    , relname
                    , relnamespace
                    , reltablespace reltablespace
                    , pg_class.reltoastrelid
                    , COALESCE(inhparent, pg_class.oid) parent
                FROMpg_class
                    LEFTJOINpg_inherit_short ONinhrelid = oid
                WHERErelkind IN('r', 'p')
             ) c
            GROUPBYparent, reltablespace
  ) a
) a LEFTJOINpg_tablespace ON(pg_tablespace.oid= reltablespace)
ORDERBYtotal_bytes DESC;

Table Size

-- pgMonitor Exporter (ccp_table_size)
SELECT 
    current_database() as dbname, n.nspname as schemaname, c.relname, 
    pg_total_relation_size(c.oid) as size_bytes 
FROM 
    pg_catalog.pg_class c 
    JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 
WHERE 
    NOT pg_is_other_temp_schema(n.oid) AND relkind IN ('r', 'm', 'f')

Table Stats

-- pgMonitor Exporter (ccp_stat_user_tables)
SELECT 
    current_database() as dbname, 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, 
    vacuum_count, autovacuum_count, analyze_count, autoanalyze_count 
FROM 
    pg_catalog.pg_stat_user_tables

Percent to Emergency Vacuum & Wraparound

WITH table_option (oid, val) AS (
	SELECT oid, unnest(reloptions)
	FROM pg_class WHERE reloptions::text ~ 'autovacuum_freeze_max_age'
), autovac_option AS (
	SELECT oid, val
	FROM table_option WHERE val LIKE 'autovacuum_freeze_max_age%'
)
SELECT n.nspname, c.relname, c.relpages,
       CASE WHEN c.reloptions::text ~ 'autovacuum_enabled=false' THEN true ELSE false END AS autovacuum_disabled,
       coalesce(CASE WHEN ao.val IS NOT NULL THEN substring(ao.val,position('=' in ao.val)+1) 
                     ELSE null 
                END, current_setting('autovacuum_freeze_max_age'))::numeric autovaccum_freeze_max_age,
       round(((greatest(age(c.relfrozenxid), age(t.relfrozenxid))::numeric) / 
            coalesce(CASE WHEN ao.val IS NOT NULL THEN  substring(ao.val,position('=' in ao.val)+1) 
                          ELSE null 
                     END, current_setting('autovacuum_freeze_max_age'))::numeric)*100) pct_emergency_vac,      
       round(((greatest(age(c.relfrozenxid), age(t.relfrozenxid))::numeric) / 2146483647)*100)  pct_wraparound,
       c.relfrozenxid table_relfrozenxid, 
       age(c.relfrozenxid) table_relfrozenxid_age,
       t.relfrozenxid toast_relfrozenxid,       
       age(t.relfrozenxid) toast_relfrozenxid_age       
FROM pg_class c 
     JOIN pg_namespace n ON n.oid = c.relnamespace
     LEFT OUTER JOIN pg_class t ON c.reltoastrelid = t.oid
     LEFT OUTER JOIN autovac_option ao ON ao.oid = c.oid
WHERE c.relkind in ('r', 'm', 'f')
ORDER BY pct_emergency_vac DESC;


SELECT c.oid::regclass
    , age(c.relfrozenxid)
    , pg_size_pretty(pg_total_relation_size(c.oid))
    , current_setting('autovacuum_freeze_max_age')
    , round((age(c.relfrozenxid)::numeric / current_setting('autovacuum_freeze_max_age')::numeric)*100) pct_to_emer_vacuum
    , round((age(c.relfrozenxid)::numeric / 2146483647 )*100) pct_to_wraparound
FROM pg_class c
JOIN pg_namespace n on c.relnamespace = n.oid
WHERE relkind IN ('r', 't', 'm')
and c.oid in (76759, 76756)
--AND n.nspname NOT IN ('pg_toast')
ORDER BY 2 DESC LIMIT 100;

Vacuum Progress

select a.pid, pc.datname, relid, heap_blks_scanned, heap_blks_total, phase, round((heap_blks_scanned::numeric/heap_blks_total::numeric)*100) pct_done,
       extract(epoch from current_timestamp - xact_start) elasped_time_seconds,
       round(heap_blks_scanned / extract(epoch from current_timestamp - xact_start)) blks_per_second,
       round(((heap_blks_total - heap_blks_scanned) / (heap_blks_scanned / extract(epoch from current_timestamp - xact_start)))) est_time_remaining 
from pg_catalog.pg_stat_progress_vacuum pc
     join pg_stat_activity a on (a.pid=pc.pid)

Percent Toward Exceeding Maximum for Auto Incrementing Columns

SELECT
    seqs.relname AS sequence,
    format_type(s.seqtypid, NULL::integer) sequence_datatype,
    CONCAT(tbls.relname, '.', attrs.attname) AS owned_by,
    format_type(attrs.atttypid, atttypmod) AS column_datatype,
    pg_sequence_last_value(seqs.oid::regclass) AS last_sequence_value,
    TO_CHAR((CASE WHEN format_type(s.seqtypid, NULL::integer) = 'smallint' THEN(pg_sequence_last_value(seqs.relname::regclass) / 32767::float)WHEN format_type(s.seqtypid, NULL::integer) = 'integer' THEN(pg_sequence_last_value(seqs.relname::regclass) / 2147483647::float)WHEN format_type(s.seqtypid, NULL::integer) = 'bigint' THEN(pg_sequence_last_value(seqs.relname::regclass) / 9223372036854775807::float)END) * 100, 'fm9999999999999999999990D00%') AS sequence_percent,
    TO_CHAR((CASE WHEN format_type(attrs.atttypid, NULL::integer) = 'smallint' THEN(pg_sequence_last_value(seqs.relname::regclass) / 32767::float)WHEN format_type(attrs.atttypid, NULL::integer) = 'integer' THEN(pg_sequence_last_value(seqs.relname::regclass) / 2147483647::float)WHEN format_type(attrs.atttypid, NULL::integer) = 'bigint' THEN(pg_sequence_last_value(seqs.relname::regclass) / 9223372036854775807::float)END) * 100, 'fm9999999999999999999990D00%') AS column_percent
FROM
    pg_depend d
    JOIN pg_class AS seqs ON seqs.relkind = 'S'AND seqs.oid = d.objid
    JOIN pg_class AS tbls ON tbls.relkind = 'r'AND tbls.oid = d.refobjid
    JOIN pg_attribute AS attrs ON attrs.attrelid = d.refobjid
        AND attrs.attnum = d.refobjsubid
    JOIN pg_sequence s ON s.seqrelid = seqs.oid
WHERE
    d.deptype = 'a'AND d.classid = 1259;