Skip to content

Controle de capacidade

Peter edited this page Nov 3, 2026 · 2 revisions

estamos oferecendo no kit a funçao lib.rel_disk_usage() v3... Ela já permite acompanhar:

  • Capacidade física de tabelas, índices e partições.
  • Cardinalidade e tamanho médio dos registros.
  • Padrões de acesso sequencial e indexado.
  • Volume efetivamente retornado pelos índices.
  • Estatísticas de manutenção.
  • Organização do particionamento.

através dela o chatGPT pode nos ajudar a decidir se devemos ou não particionar tabelas gigantes, se devemos ou não cortar/adicionar indexações.

-- lib.rel_disk_usage v3
-- PostgreSQL 16+
-- Relatorio de capacidade e padrao de acesso, orientado a tabelas de grade.
--
-- Exemplos:
--   SELECT * FROM lib.rel_disk_usage();
--   SELECT * FROM lib.rel_disk_usage(ARRAY['grid_bronze','grid_silver']);
--   SELECT * FROM lib.rel_disk_usage(ARRAY['pg_catalog']); -- inclusao explicita de schema interno
--
-- Notas:
-- * Particoes fisicas aparecem individualmente; o total no pai e apenas informativo
--   (NAO some os totais agregados dos pais com os totais individuais das filhas).
-- * Estatisticas de scans sao cumulativas, desde o reset dos contadores relevantes.
-- * idx_tup_read soma entradas de indice retornadas pelos scans (pg_stat_all_indexes),
--   enquanto idx_tup_fetch mede tuplas do heap recuperadas; index-only scans podem
--   produzir idx_tup_read > 0 e idx_tup_fetch = 0.
-- * Os rotulos access_pattern e partition_advice sao heuristicas, nao um diagnostico
--   automatico de ganhos de particionamento.

DROP FUNCTION IF EXISTS lib.rel_disk_usage(text[]);

CREATE FUNCTION lib.rel_disk_usage(p_schema_list text[] DEFAULT NULL)
RETURNS TABLE (
    schema_name              text,
    relname                  text,
    reltype                  text,
    table_size               text,
    table_size_bytes         bigint,
    indexes_size             text,
    indexes_size_bytes       bigint,
    total_size               text,
    total_size_bytes         bigint,
    index_ratio              numeric,
    rows_estimate            bigint,
    live_rows                bigint,
    dead_rows                bigint,
    dead_pct                 numeric,
    avg_row_bytes            numeric,
    index_count              integer,
    largest_index_size       text,
    largest_index_bytes      bigint,
    seq_scan                 bigint,
    seq_tup_read             bigint,
    seq_rows_per_scan        numeric,
    seq_table_equivalents    numeric,
    idx_scan                 bigint,
    idx_tup_read             bigint, -- NOVO: entradas retornadas pelos indices
    idx_tup_fetch            bigint,
    idx_rows_per_scan        numeric,
    modified_since_analyze   bigint,
    last_analyze             timestamptz,
    last_autoanalyze         timestamptz,
    last_vacuum              timestamptz,
    last_autovacuum          timestamptz,
    is_partitioned           boolean,
    is_partition             boolean,
    parent_table             text,
    partition_count          integer,
    partition_key            text,
    partitions_total_size    text,   -- NOVO: tamanho fisico agregado dos descendentes
    partitions_total_bytes   bigint, -- NOVO: exclui o pai; evita dupla contagem na propria linha
    access_pattern           text,
    capacity_class           text,
    partition_advice         text,
    stats_since              timestamptz
)
LANGUAGE SQL
STABLE
BEGIN ATOMIC

WITH rel AS (
    SELECT
        c.oid,
        n.nspname::text AS schema_name,
        c.relname::text AS relname,
        c.relkind,
        CASE c.relkind
            WHEN 'r' THEN 'table'
            WHEN 'p' THEN 'partitioned table'
            WHEN 'm' THEN 'materialized view'
        END::text AS reltype,
        pg_table_size(c.oid)::bigint AS table_bytes,
        pg_indexes_size(c.oid)::bigint AS indexes_bytes,
        pg_total_relation_size(c.oid)::bigint AS total_bytes,
        c.reltuples::bigint AS rows_estimate,
        (c.relkind = 'p') AS is_partitioned,
        EXISTS (
            SELECT 1
            FROM pg_catalog.pg_inherits inh
            WHERE inh.inhrelid = c.oid
              AND inh.inhparent IN (
                  SELECT pc.oid FROM pg_catalog.pg_class pc WHERE pc.relkind = 'p'
              )
        ) AS is_partition
    FROM pg_catalog.pg_class c
    JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind IN ('r', 'p', 'm')
      AND (
          (p_schema_list IS NULL AND n.nspname NOT IN
              ('pg_catalog', 'information_schema')
              AND n.nspname NOT LIKE 'pg_toast%'
              AND n.nspname NOT LIKE 'pg_temp_%')
          OR (p_schema_list IS NOT NULL AND n.nspname = ANY(p_schema_list))
      )
),
base AS (
    SELECT
        r.*,
        coalesce(s.n_live_tup, r.rows_estimate)::bigint AS live_rows,
        coalesce(s.n_dead_tup, 0)::bigint AS dead_rows,
        coalesce(s.seq_scan, 0)::bigint AS seq_scan,
        coalesce(s.seq_tup_read, 0)::bigint AS seq_tup_read,
        coalesce(s.idx_scan, 0)::bigint AS idx_scan,
        coalesce(ist.idx_tup_read, 0)::bigint AS idx_tup_read,
        coalesce(s.idx_tup_fetch, 0)::bigint AS idx_tup_fetch,
        coalesce(s.n_mod_since_analyze, 0)::bigint AS modified_since_analyze,
        s.last_analyze,
        s.last_autoanalyze,
        s.last_vacuum,
        s.last_autovacuum,
        coalesce(im.index_count, 0)::integer AS index_count,
        coalesce(im.largest_index_bytes, 0)::bigint AS largest_index_bytes,
        parent.parent_table,
        coalesce(part.partition_count, 0)::integer AS partition_count,
        CASE WHEN r.relkind = 'p'
             THEN pg_catalog.pg_get_partkeydef(r.oid)
        END::text AS partition_key,
        coalesce(part.partitions_total_bytes, 0)::bigint AS partitions_total_bytes
    FROM rel r
    LEFT JOIN pg_catalog.pg_stat_all_tables s ON s.relid = r.oid
    LEFT JOIN LATERAL (
        SELECT count(*)::integer AS index_count,
               max(pg_relation_size(ix.indexrelid))::bigint AS largest_index_bytes
        FROM pg_catalog.pg_index ix
        WHERE ix.indrelid = r.oid
    ) im ON true
    LEFT JOIN LATERAL (
        SELECT sum(coalesce(si.idx_tup_read, 0))::bigint AS idx_tup_read
        FROM pg_catalog.pg_stat_all_indexes si
        WHERE si.relid = r.oid
    ) ist ON true
    LEFT JOIN LATERAL (
        SELECT format('%I.%I', pn.nspname, pc.relname)::text AS parent_table
        FROM pg_catalog.pg_inherits inh
        JOIN pg_catalog.pg_class pc ON pc.oid = inh.inhparent
        JOIN pg_catalog.pg_namespace pn ON pn.oid = pc.relnamespace
        WHERE inh.inhrelid = r.oid AND pc.relkind = 'p'
        LIMIT 1
    ) parent ON true
    LEFT JOIN LATERAL (
        SELECT
            (SELECT count(*)::integer
             FROM pg_catalog.pg_inherits inh
             WHERE inh.inhparent = r.oid) AS partition_count,
            CASE WHEN r.relkind = 'p' THEN
              (SELECT coalesce(sum(pg_total_relation_size(tree.relid)), 0)::bigint
               FROM pg_catalog.pg_partition_tree(r.oid) tree
               WHERE tree.isleaf AND tree.relid <> r.oid)
            ELSE 0::bigint END AS partitions_total_bytes
    ) part ON true
),
calc AS (
    SELECT
        b.*,
        round(100.0 * dead_rows / nullif(live_rows + dead_rows, 0), 2) AS dead_pct,
        round(table_bytes::numeric / nullif(live_rows, 0), 1) AS avg_row_bytes,
        round(indexes_bytes::numeric / nullif(table_bytes, 0), 2) AS index_ratio,
        round(seq_tup_read::numeric / nullif(seq_scan, 0), 1) AS seq_rows_per_scan,
        round(seq_tup_read::numeric / nullif(live_rows, 0), 2) AS seq_table_equivalents,
        -- Cuidado: idx_tup_fetch pode ser zero em index-only scans.
        round(idx_tup_read::numeric / nullif(idx_scan, 0), 1) AS idx_rows_per_scan
    FROM base b
),
classified AS (
    SELECT
        c.*,
        CASE
            WHEN c.total_bytes < 100::bigint * 1024 * 1024
                THEN 'small relation'
            WHEN c.seq_scan = 0 AND c.idx_scan = 0
                THEN 'no observed reads'
            WHEN c.idx_scan > c.seq_scan * 10
              AND coalesce(c.idx_rows_per_scan, 0) <= 100
                THEN 'highly selective indexed access'
            WHEN c.idx_scan > c.seq_scan * 10
              AND coalesce(c.idx_rows_per_scan, 0) <= 1000
                THEN 'selective indexed access'
            WHEN c.idx_scan > c.seq_scan * 10
                THEN 'broad indexed access'
            WHEN c.seq_scan > 0
              AND c.seq_rows_per_scan >= c.live_rows * 0.50
              AND c.idx_scan = 0
                THEN 'broad sequential scans'
            WHEN c.seq_scan > 0
              AND c.seq_rows_per_scan >= c.live_rows * 0.50
                THEN 'mixed, with broad sequential scans'
            WHEN c.seq_scan > c.idx_scan * 10
                THEN 'sequential-oriented access'
            ELSE 'mixed access'
        END::text AS access_pattern,
        CASE
            WHEN c.total_bytes < 10::bigint * 1024 * 1024 * 1024 THEN 'small'
            WHEN c.total_bytes < 50::bigint * 1024 * 1024 * 1024 THEN 'medium'
            WHEN c.total_bytes < 100::bigint * 1024 * 1024 * 1024 THEN 'large'
            ELSE 'very large'
        END::text AS capacity_class
    FROM calc c
)
SELECT
    c.schema_name, c.relname, c.reltype,
    pg_size_pretty(c.table_bytes), c.table_bytes,
    pg_size_pretty(c.indexes_bytes), c.indexes_bytes,
    pg_size_pretty(c.total_bytes), c.total_bytes,
    c.index_ratio,
    c.rows_estimate, c.live_rows, c.dead_rows, c.dead_pct, c.avg_row_bytes,
    c.index_count, pg_size_pretty(c.largest_index_bytes), c.largest_index_bytes,
    c.seq_scan, c.seq_tup_read, c.seq_rows_per_scan, c.seq_table_equivalents,
    c.idx_scan, c.idx_tup_read, c.idx_tup_fetch, c.idx_rows_per_scan,
    c.modified_since_analyze,
    c.last_analyze, c.last_autoanalyze, c.last_vacuum, c.last_autovacuum,
    c.is_partitioned, c.is_partition, c.parent_table,
    c.partition_count, c.partition_key,
    CASE WHEN c.is_partitioned THEN pg_size_pretty(c.partitions_total_bytes)
         ELSE NULL::text END AS partitions_total_size,
    CASE WHEN c.is_partitioned THEN c.partitions_total_bytes
         ELSE NULL::bigint END AS partitions_total_bytes,
    c.access_pattern, c.capacity_class,
    CASE
        WHEN c.is_partitioned THEN 'already partitioned; inspect pruning and child sizes'
        WHEN c.is_partition THEN 'partition leaf; review parent pruning before repartitioning'
        WHEN c.total_bytes < 10::bigint * 1024 * 1024 * 1024
            THEN 'keep monolithic; size does not justify partitioning'
        WHEN c.idx_scan > c.seq_scan * 10 AND coalesce(c.idx_rows_per_scan, 0) <= 100
            THEN 'selective indexed workload; partitioning probably unnecessary'
        WHEN c.seq_scan > 0 AND c.seq_rows_per_scan >= c.live_rows * 0.50
            THEN 'broad scans: partition only if common filters align with partition key'
        WHEN c.total_bytes >= 50::bigint * 1024 * 1024 * 1024
             AND c.index_ratio >= 1.0
            THEN 'review partitioning: large relation and heavy index footprint'
        WHEN c.total_bytes >= 50::bigint * 1024 * 1024 * 1024
            THEN 'review partitioning if a natural pruning key exists'
        ELSE 'partition only if workload benefits from pruning or bulk maintenance'
    END::text AS partition_advice,
    db.stats_reset AS stats_since
FROM classified c
CROSS JOIN (
    SELECT stats_reset
    FROM pg_catalog.pg_stat_database
    WHERE datname = current_database()
) db
ORDER BY c.total_bytes DESC;

END;

Clone this wiki locally