To get the physical size of the database files (storage), use the following query:
select pg_database_size(current_database());The result will be represented as a number of the form 41809016.
current_database()
— a function that returns the name of the current database.
Instead, you can enter the name explicitly:
select pg_database_size('my_database');In order to get information in human-readable form, we use the function pg_size_pretty():
select pg_size_pretty(pg_database_size(current_database()));As a result, we get information like 40 Mb.
select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) as size from pg_database;Иногда требуется получить перечень таблиц базы данных. Для этого используем следующий запрос:
SELECT table_name FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','pg_catalog');information_schema — стандартная схема базы данных, которая содержит коллекции представлений (views), таких как таблицы, поля и т.д.
Представления таблиц содержат информацию обо всех таблицах баз данных.
Запрос, описанный ниже, выберет все таблицы из указанной схемы текущей базы данных:
SELECT table_name FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
AND table_schema IN('public', 'myschema');В последнем условии IN можно указать имя определенной схемы.
select table_name,
pg_size_pretty(total_size) as total_size,
pg_size_pretty(table_size) as table_size,
pg_size_pretty(indexes_size) as indexes_size,
pg_size_pretty(toast_size) as toast_size
from (
select c.oid::regclass as table_name,
pg_total_relation_size(c.oid) as total_size,
pg_table_size(c.oid) as table_size,
pg_indexes_size(c.oid) as indexes_size,
coalesce(pg_total_relation_size(c.reltoastrelid), 0) as toast_size
from pg_class c
left join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'r'
and n.nspname = 'public'::text
order by c.relname::text
) as tables;
Функция pg_relation_size() возвращает объём, который занимает на диске указанный слой заданной таблицы или индекса.
select pg_size_pretty(pg_total_relation_size('public.order_item'));Для того, чтобы вывести список таблиц текущей базы данных, отсортированный по размеру таблицы, выполним следующий запрос:
select
coalesce(t.spcname, 'pg_default') as tablespace,
n.nspname ||'.'||c.relname as table,
(select count(*) from pg_index i where i.indrelid=c.oid) as index_count,
pg_size_pretty(pg_relation_size(c.oid)) as t_size,
pg_size_pretty(pg_indexes_size(c.oid)) as i_size
from pg_class c
join pg_namespace n on c.relnamespace = n.oid
left join pg_tablespace t on c.reltablespace = t.oid
where c.reltype != 0 and n.nspname = 'public'
order by (pg_relation_size(c.oid),pg_indexes_size(c.oid)) desc;Для того, чтобы вывести информацию о самой большой таблице, ограничим запрос с помощью LIMIT:
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC LIMIT 1;- relname — имя таблицы, индекса, представления и т.п.
- relpages — размер представления этой таблицы на диске в количествах страниц (по умолчанию одна страницы равна 8 Кб).
- pg_class — системная таблица, которая содержит информацию о связях таблиц базы данных.
select relname as objectname, pg_stat_get_live_tuples(c.oid) as livetuples, pg_stat_get_dead_tuples(c.oid) as deadtuples
from pg_class c where relname = 'order_item';select * from pg_stat_all_tables where relname='order_item';select table_name,
c.column_name, c.data_type, coalesce(c.numeric_precision, c.character_maximum_length) as maximum_length, c.numeric_scale
from pg_catalog.pg_statio_all_tables as st
inner join pg_catalog.pg_description pgd on (pgd.objoid=st.relid)
right outer join information_schema.columns c on (pgd.objsubid=c.ordinal_position and c.table_schema=st.schemaname and c.table_name=st.relname)
where table_schema = 'public';select psat.relid::regclass::text as table_name,
psat.schemaname as schema_name
from pg_catalog.pg_stat_all_tables psat
where
(obj_description(psat.relid) is null or length(trim(obj_description(psat.relid))) = 0)
and position('flyway_schema_history' in psat.relid::regclass::text) <= 0
and psat.schemaname not in ('information_schema', 'pg_catalog', 'pg_toast')
order by 1;select t.oid::regclass::text as table_name,
col.attname::text as column_name
from pg_catalog.pg_class t
join pg_catalog.pg_namespace nsp on nsp.oid = t.relnamespace
join pg_catalog.pg_attribute col on (col.attrelid = t.oid)
where t.relkind = 'r' and
col.attnum > 0 and /* to filter out system columns such as oid, ctid, xmin, xmax, etc.*/
--nsp.nspname = :schema_name_param::text and
position('flyway_schema_history' in t.oid::regclass::text) <= 0 and
nsp.nspname not in ('information_schema', 'pg_catalog', 'pg_toast') and
col_description(t.oid, col.attnum) is null
order by 1, 2;select
x.indrelid::regclass as table_name,
x.indexrelid::regclass as index_name,
x.indisunique as is_unique,
x.indisvalid as is_valid,
x.indnatts as columns_count,
pg_get_indexdef(x.indexrelid) as index_definition
from
pg_catalog.pg_index x
join pg_catalog.pg_stat_all_indexes psai on x.indexrelid = psai.indexrelid
where
psai.schemaname = 'public'::text
and x.indexrelid::regclass::text = 'target_index_name'::text;select
d.classid::regclass as owning_object_type,
d.objid::regclass as owning_object,
d.refobjid::regclass as dependent_object,
a.attname as dependent_column,
d.deptype -- see https://www.postgresql.org/docs/current/catalog-pg-depend.html
from pg_catalog.pg_depend d
left join pg_catalog.pg_attribute a on d.refobjid = a.attrelid and d.refobjsubid = a.attnum
where
refobjid = 'target_table_name'::regclass and
a.attname = 'target_column_name';select
r.id as row_id,
pg_size_pretty(sum(pg_column_size(r.*))) as row_size
from <table_name> as r
group by r.id
order by sum(pg_column_size(r.*)) desc
limit 10