Repository navigation
Controle de capacidade
Peter edited this page Nov 3, 2026
·
2 revisions
estamos oferecendo no kit a funçao lib.rel_disk_usage() v3... ver código-fonte abaixo. 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.
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
-- Para conferir especificamente as partições:
SELECT
schema_name,
relname,
total_size,
is_partitioned,
is_partition,
parent_table,
partition_count,
partition_key,
partitions_total_size
FROM lib.rel_disk_usage()
WHERE is_partitioned OR is_partition
ORDER BY schema_name, relname;-- 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;