--------------------------- Размер базы
SELECT pg_size_pretty( pg_database_size( 'sample_db' ) );
---------------------------- Таким же макаром можно посмотреть и размер таблицы (с индексами)
SELECT pg_size_pretty( pg_total_relation_size( 'table' ) );
-----------------------------Если нужно без индексов, тогда запрос другой:
SELECT pg_size_pretty( pg_relation_size( 'table' ) );
-------------------------------------- все таблицы
SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC;
--------------------------------- таблицы с индексами
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20;
SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname IN ('edu_power_audit') AND C.relkind <> 'i' AND nspname !~ '^pg_toast' and relname like '%audit%'
ORDER BY pg_total_relation_size(C.oid) DESC;
--------------------------------------- c индексfvb
SELECT TABLE_NAME,
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
TABLE_NAME,
pg_table_size(TABLE_NAME) AS table_size,
pg_indexes_size(TABLE_NAME) AS indexes_size,
pg_total_relation_size(TABLE_NAME) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || TABLE_NAME || '"') AS TABLE_NAME
FROM information_schema.tables
) AS all_tables
ORDER BY total_size DESC
) AS pretty_sizes;
SELECT TABLE_NAME,
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
TABLE_NAME,
pg_table_size(TABLE_NAME) AS table_size,
pg_indexes_size(TABLE_NAME) AS indexes_size,
pg_total_relation_size(TABLE_NAME) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || TABLE_NAME || '"') AS TABLE_NAME
FROM information_schema.tables
WHERE table_schema ='edu_power_audit' and TABLE_NAME like '%audit_messages%'
) AS all_tables
ORDER BY TABLE_NAME
) AS pretty_sizes;
-------------------------------------------Размер столбца
select pg_column_size('имя стобца') from 'имя таблицы';
----------------------------------------------Отображаем 10 самых больших БД(подключаться к PostgreSQL надо без указания имени базы):
SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
ELSE 'No Access'
END AS SIZE
FROM pg_catalog.pg_database d
ORDER BY
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_database_size(d.datname)
ELSE NULL
END DESC -- nulls first
LIMIT 20
------------------------------Отображаем размер таблиц в базе(подключаться к PostgreSQL надо указав имя базы):
SELECT
relname as "Table",
pg_size_pretty(pg_total_relation_size(relid)) As "Size",
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
---------------------------------------------------
select pg_relation_size(20306, 'main') as main,
pg_relation_size(20306, 'fsm') as fsm,
pg_relation_size(20306, 'vm') as vm,
pg_relation_size(20306, 'init') as init,
pg_table_size(20306), pg_indexes_size(20306) as indexes,
pg_total_relation_size(20306) as total;