我从Oracle来到Postgres,寻找一种方法来查找表和索引大小的字节/MB/GB/等,甚至更好的所有表的大小。在Oracle中,我有一个讨厌的长查询,查看user_lobs和user_segments以返回一个答案。
我假设在Postgres中有一些东西我可以在information_schema表中使用,但我不知道在哪里。
我从Oracle来到Postgres,寻找一种方法来查找表和索引大小的字节/MB/GB/等,甚至更好的所有表的大小。在Oracle中,我有一个讨厌的长查询,查看user_lobs和user_segments以返回一个答案。
我假设在Postgres中有一些东西我可以在information_schema表中使用,但我不知道在哪里。
当前回答
试试这个脚本来查找所有表的大小:
SELECT
table_schema || '.' || table_name AS TableName,
pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS TableSize
FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC
对于PostgreSQL中其他不同的脚本,请访问这个url: http://www.dbrnd.com/2015/05/how-to-find-size-of-database-and-table-in-postgresql/
其他回答
显示数据库大小:
\l+
e.g.
=> \l+
berbatik_prd_commerce | berbatik_prd | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 19 MB | pg_default |
berbatik_stg_commerce | berbatik_stg | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8633 kB | pg_default |
bursasajadah_prd | bursasajadah_prd | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 1122 MB | pg_default |
显示表大小:
\d+
e.g.
=> \d+
public | tuneeca_prd | table | tomcat | 8192 bytes |
public | tuneeca_stg | table | tomcat | 1464 kB |
只适用于psql。
(@zkutch的回答总结。)
如果数据库名称为snort,则用下面的语句指定大小:
psql -c "\l+ snort" | awk -F "|" '{print $7}'
试试这个脚本来查找所有表的大小:
SELECT
table_schema || '.' || table_name AS TableName,
pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS TableSize
FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC
对于PostgreSQL中其他不同的脚本,请访问这个url: http://www.dbrnd.com/2015/05/how-to-find-size-of-database-and-table-in-postgresql/
查看这个维基。https://wiki.postgresql.org/wiki/Disk_Usage
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
PostgreSQL表有三个组件:表本身、表上的索引以及潜在的TOAST数据。在http://wiki.postgresql.org/wiki/Disk_Usage上有几个例子展示了如何以各种方式滑动和切割可用信息