磁盘使用
来自 PostgreSQL wiki
跳转到导航跳转到搜索
查找数据库中各种对象的尺寸
针对分区表进行分组的通用表尺寸信息
这将以“漂亮”的形式报告所有非继承表的尺寸信息。 继承表将分组在一起。
WITH RECURSIVE pg_inherit(inhrelid, inhparent) AS
(select inhrelid, inhparent
FROM pg_inherits
UNION
SELECT child.inhrelid, parent.inhparent
FROM pg_inherit child, pg_inherits parent
WHERE child.inhparent = parent.inhrelid),
pg_inherit_short AS (SELECT * FROM pg_inherit WHERE inhparent NOT IN (SELECT inhrelid FROM pg_inherit))
SELECT table_schema
, TABLE_NAME
, row_estimate
, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
, total_bytes::float8 / sum(total_bytes) OVER () AS total_size_share
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes
FROM (
SELECT c.oid
, nspname AS table_schema
, relname AS TABLE_NAME
, SUM(c.reltuples) OVER (partition BY parent) AS row_estimate
, SUM(pg_total_relation_size(c.oid)) OVER (partition BY parent) AS total_bytes
, SUM(pg_indexes_size(c.oid)) OVER (partition BY parent) AS index_bytes
, SUM(pg_total_relation_size(reltoastrelid)) OVER (partition BY parent) AS toast_bytes
, parent
FROM (
SELECT pg_class.oid
, reltuples
, relname
, relnamespace
, pg_class.reltoastrelid
, COALESCE(inhparent, pg_class.oid) parent
FROM pg_class
LEFT JOIN pg_inherit_short ON inhrelid = oid
WHERE relkind IN ('r', 'p')
) c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
) a
WHERE oid = parent
) a
ORDER BY total_bytes DESC;
针对分区表进行分组的通用表尺寸信息
将显示如上所示的表，但尺寸将针对每个表空间单独拆分。
WITH RECURSIVE pg_inherit(inhrelid, inhparent) AS
(select inhrelid, inhparent
FROM pg_inherits
UNION
SELECT child.inhrelid, parent.inhparent
FROM pg_inherit child, pg_inherits parent
WHERE child.inhparent = parent.inhrelid),
pg_inherit_short AS (SELECT * FROM pg_inherit WHERE inhparent NOT IN (SELECT inhrelid FROM pg_inherit))
SELECT parent::regclass
, coalesce(spcname, 'default') pg_tablespace_name
, row_estimate
, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
, 100 * total_bytes::float8 / sum(total_bytes) OVER () AS PERCENT
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes
FROM (
SELECT parent
, reltablespace
, SUM(c.reltuples) AS row_estimate
, SUM(pg_total_relation_size(c.oid)) AS total_bytes
, SUM(pg_indexes_size(c.oid)) AS index_bytes
, SUM(pg_total_relation_size(reltoastrelid)) AS toast_bytes
FROM (
SELECT pg_class.oid
, reltuples
, relname
, relnamespace
, reltablespace reltablespace
, pg_class.reltoastrelid
, COALESCE(inhparent, pg_class.oid) parent
FROM pg_class
LEFT JOIN pg_inherit_short ON inhrelid = oid
WHERE relkind IN ('r', 'p')
) c
GROUP BY parent, reltablespace
) a
) a LEFT JOIN pg_tablespace ON (pg_tablespace.oid = reltablespace)
ORDER BY total_bytes DESC;
通用表尺寸信息
这将以原始字节和“漂亮”形式报告所有表的尺寸信息。
SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS index
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS table
FROM (
SELECT *, total_bytes-index_bytes-coalesce(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS table_name
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
) a
) a;
查找集群中最大的数据库
用户无法连接的数据库将按其为无限大进行排序。
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;
查找最大关系的尺寸
关系是数据库中的对象，例如表和索引，此查询显示所有各个部分的尺寸。 同时拥有常规和TOAST部分的表将被分解为单独的组件；在文档中提供了展示如何将这些组件包含在主总计中的示例，并且从 PostgreSQL 9.0 开始，可以使用 pg_table_size 代替 pg_relation_size，从而实现自动包含。
请注意，本页面此点之后的查询仅显示您当前连接到的数据库中对象的尺寸。
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
LIMIT 20;
示例输出（来自使用 pgbench 创建的数据库，scale=25）
relation | size
------------------------+------------
public.accounts | 326 MB
public.accounts_pkey | 44 MB
public.history | 592 kB
public.tellers_pkey | 16 kB
public.branches_pkey | 16 kB
public.tellers | 16 kB
public.branches | 8192 bytes
查找最大表的总尺寸
此查询版本使用pg_total_relation_size，它将包括索引和烤制数据的表使用的总磁盘空间相加，而不是将各个部分分开。
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;
轻松访问这些查询
~/.psqlrc 技巧：表尺寸展示了如何在 psql 中轻松运行此类与尺寸相关的查询。
