空间使用

以下所有命令，请在用psql登录数据库后执行。

查看表空间使用率

select * from pg_tablespace;
使用操作系统命令查看剩余可用空间
df -h

查看数据库大小

SELECT pg_database.datname,
pg_size_pretty(pg_database_size(pg_database.datname)) AS
SIZE
FROM pg_database;

查看当前数据库 top 10 大的表

SELECT relnamespace::regnamespace as "Schema",relname as "Name",
pg_relation_size(relnamespace::regnamespace||'."' || relname || '"')/1024/1024 AS "MB",
relkind as "Kind"
FROM pg_class
WHERE relnamespace IN
(SELECT oid
FROM pg_namespace
WHERE nspname NOT IN ('pg_toast',
'pg_catalog',
'information_schema')
AND nspname NOT LIKE 'pg_temp_%'
AND nspname NOT LIKE 'pg_toast_temp_%')
AND relkind in ('r','p')
ORDER BY 3 DESC LIMIT 10 ;

查看当前数据库 top 10 对象

SELECT relnamespace::regnamespace as "Schema",relname as "Name",
pg_relation_size(relnamespace::regnamespace||'."' || relname || '"')/1024/1024 AS "MB",
CASE relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'm' THEN 'materialized view'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special'
WHEN 'f' THEN 'foreign table'
WHEN 'p' THEN 'partition table '
WHEN 'I' THEN 'index'
END as "Type"
FROM pg_class
WHERE relnamespace IN
(SELECT oid
FROM pg_namespace
WHERE nspname NOT IN ('pg_toast',
'pg_catalog',
'information_schema')
AND nspname NOT LIKE 'pg_temp\_%'
AND nspname NOT LIKE 'pg_toast_temp\_%')
ORDER BY 3 DESC LIMIT 10 ;