Lista as tabelas para ser usada no lld do zabbix

top-tables-pgsql-lld.sql

-- Based on https://wiki.postgresql.org/wiki/Disk_Usage
---- To use on zabbix for LLD
---- Sorted to the greatest size to the lowest size

WITH table_stats AS (
    SELECT 
        pg_class.oid,
        nspname AS table_schema, 
        relname AS TABLE_NAME, 
        pg_class.reltuples AS row_estimate,
        pg_total_relation_size(pg_class.oid) AS total_bytes,
        pg_indexes_size(pg_class.oid) AS index_bytes,
        pg_total_relation_size(pg_class.reltoastrelid) AS toast_bytes
    FROM pg_class
    LEFT JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
    WHERE relkind = 'r'
), full_stats as (
SELECT
	table_stats.*,
	total_bytes - index_bytes-COALESCE(toast_bytes,0) AS table_bytes
FROM
	table_stats

WHERE
	-- ajuste o filtro conforme sua necessidade!
	table_stats.table_schema = 'public'
ORDER BY
	table_stats.total_bytes desc

), pretty_stats as (
    
    SELECT 
        full_stats.TABLE_NAME as "#TABLENAME",
        full_stats.total_bytes as "#TOTALBYTES",
        full_stats.index_bytes as "#INDEXBYTES",
        full_stats.toast_bytes as "#TOASTBYTES",
        full_stats.table_bytes as "#TABLEBYTES"
    FROM full_stats
)

SELECT json_build_object('DATA', json_agg(row_to_json(data))) FROM pretty_stats as data;

Não precisa fazer esse cálculo para descobrir o tamanho em bytes da tabela:

total_bytes - index_bytes-COALESCE(toast_bytes,0) AS table_bytes

Existe a função pg_table_size que faz isso pra vc