pg_stat_user_tables

Esta visão do sistema fornece métricas detalhadas sobre o comportamento das tabelas de usuários. As colunas incluem identificadores da tabela, nome do esquema e da tabela, contagens de varreduras sequenciais e por índice, além de operações DML. Observa-se também o número de tuplas vivas e mortas, informações sobre operações VACUUM e atualizações quentes (hot updates), que ocorrem dentro da mesma página.

Ao consultar esta visão, é possível identificar tabelas com alto uso de varreduras sequenciais em comparação a varreduras por índice, entender padrões de modificação (inserções, atualizações, exclusões) e avaliar a quantidade de dados mortos (dead tuples) presentes.

SELECT nome_tabela, varreduras_seq, leituras_seq_tuplas
FROM pg_stat_user_tables
WHERE esquema = current_schema() AND varredura_indice < varredura_seq
ORDER BY varredura_seq DESC;

-- Consulta alternativa para listar tabelas ordenadas por contagem de tuplas vivas
SELECT nome_tabela, tuplas_vivas
FROM pg_stat_user_tables
ORDER BY tuplas_vivas DESC;

Análise do Uso de Índices

No PostgreSQL, as visões do sistema pg_stat_all_indexes e pg_stat_user_indexes oferecem dados sobre o desempenho dos índices. A primeira contém estatísticas para todos os índices (incluindo os do sistema), enquanto a segunda se limita aos índices pertencentes ao usuário atual. Ambas compartilham colunas como nome do índice, nome da tabela, esquema, contagem de varreduras e operações de modificação.

• nome_indice: Identificador do índice.
• nome_tabela: Nome da tabela associada.
• nome_esquema: Esquema da tabela.
• varreduras_indice: Número de vezes que o índice foi utilizado em varreduras. Um valor baixo pode indicar um índice subutilizado, candidato a remoção.
• tuplas_lidas_indice: Quantidade de tuplas lidas através do índice.
• tuplas_obtidas_indice: Número de tuplas de dados recuperadas usando o índice.

Monitorar essas métricas ajuda a identificar índices frequentemente acessados e aqueles que são raramente usados. Remover índices ineficazes pode reduzir o consumo de espaço em disco e melhorar a performance de operações de escrita (INSERT, UPDATE, DELETE).

pg_statio_user_tables

Esta visão foca no desempenho de E/S (Entrada/Saída) para tabelas de usuários. As colunas incluem a contagem de blocos lidos do disco e acertados no cache para o heap (dados da tabela), índices, TOAST e índices TOAST.

• heap_blocos_lidos / heap_blocos_acertados: Métricas para os blocos de dados principais.
• ind_blocos_lidos / ind_blocos_acertados: Métricas para blocos de índices.
• toast_blocos_lidos / toast_blocos_acertados: Métricas para blocos TOAST (dados estendidos).
• tidx_blocos_lidos / tidx_blocos_acertados: Métricas para índices TOAST.

Valores altos em heap_blocos_lidos e ind_blocos_lidos sugerem que o parâmetro shared_buffers pode estar dimensionado de forma inusficiente, levando a leituras frequentes do disco ou do cache do sistema operacional.

pg_stat_bgwriter

A visão pg_stat_bgwriter oferece insights sobre o processo de escrita em background e a estratégia de gerenciamento de cache. As métricas incluem a contagem de checkpoints (programados e requisitados), o número de buffers escritos durante checkpoints, e operações de limpeza realizadas pelo escritor em background ou por processos de backend.

• checkpoints_programados / checkpoints_requisitados: Tipos de pontos de controle.
• buffers_checkpoint: Buffers gravados no disco durante checkpoints.
• buffers_limpos: Buffers limpos pelo procseso de escrita em background.
• buffers_backend: Buffers descarregados por processos de cliente, indicando possível sobrecarga.
• buffers_alocados: Novos buffers alocados.

Esses dados são cruciais para diagnosticar gargalos de E/S e ajustar parâmetros como checkpoint_completion_target e bgwriter_delay.

Consultas para Tamanho de Tabelas

Para determinar o espaço ocupado pelas tabelas, consultas específicas podem ser empregadas.

SELECT 
    nome_objeto, 
    pg_size_pretty(pg_relation_size(oid)) AS tamanho_formatado
FROM 
    pg_stat_user_tables
WHERE 
    esquema = 'public'
ORDER BY 
    pg_relation_size(oid) DESC;

Consultas para Tamanho de Índices

SELECT 
    nome_indice::regclass AS nome_indice,
    pg_size_pretty(pg_relation_size(oid_indice)) AS tamanho_indice
FROM 
    pg_index
ORDER BY 
    pg_relation_size(oid_indice) DESC;

Atividade de Sessões com pg_stat_activity

A visão pg_stat_activity fornece informações em tempo real sobre as sessões ativas no banco de dados, incluindo detalhes como ID da sessão, usuário, endereço do cliente e a consulta em execução. É uma ferramenta essencial para monitorar o estado atual do sistema.

Consultas de Espaço por Tabela (Dados e Índices)

Para uma análise detalhada do consumo de espaço por tabela, incluindo a separação entre dados e índices, pode-se usar a seguinte consulta:

SELECT 
    tabela,
    pg_size_pretty(pg_total_relation_size(caminho_completo)) AS tamanho_total,
    pg_size_pretty(pg_indexes_size(caminho_completo)) AS tamanho_indices,
    pg_size_pretty(pg_total_relation_size(caminho_completo) - pg_indexes_size(caminho_completo)) AS tamanho_dados
FROM 
    information_schema.tables
WHERE 
    tipo_tabela = 'BASE TABLE'
    AND nome_esquema NOT IN ('pg_catalog', 'information_schema')
ORDER BY 
    pg_total_relation_size(caminho_completo) DESC;

Nota: Na consulta acima, caminho_completo representa a concatenação do esquema e nome da tabela, como '"' || nome_esquema || '"."' || tabela || '"'.

Consultas de Espaço por Banco de Dados

SELECT 
    nome_banco AS "Nome do Banco de Dados",
    pg_size_pretty(pg_database_size(nome_banco)) AS "Tamanho"
FROM 
    pg_database;