如何获取 PostgreSQL 数据库中的表大小、数据库大小、索引大小、模式大小、表空间大小、列大小
在这篇文章中，我分享了PostgreSQL中查找数据库、表和索引大小的几个重要功能。
在postgresql数据库中查找对象大小非常重要和常见。了解对象在表空间中所占的确切大小是否非常有用。以下脚本中的对象大小以GB为单位。这些脚本已经格式化，可以很容易地使用PUTTY SQL编辑器。
检查表大小（不包括表依赖性）：
SELECT pg_size_pretty(pg_relation_size('mhrordhu_shk.mut_kharedi_audit')); pg_size_pretty ---------------- 238 MB (1 row)
2.检查表大小（包括表依赖性）：
SELECT pg_size_pretty(pg_total_relation_size('mhrordhu_shk.mut_kharedi_audit')); pg_size_pretty ---------------- 268 MB (1 row)
3. 查找单个 postgresql 数据库大小
SELECT pg_size_pretty(pg_database_size('db_name'));
4.查找 postgresql 数据库的单个表大小-包括依赖索引：
SELECT pg_size_pretty(pg_total_relation_size('Employee_Table'));
5. postgresql 数据库的单个表大小-不包括依赖项大小：
SELECT pg_size_pretty(pg_relation_size('Employee_table'));
6.postgresql数据库的单个索引大小：
SELECT pg_size_pretty(pg_indexes_size('index_empid'));
7. 查找 postgresql 表空间大小
以下语句返回表空间的大小
SELECT pg_size_pretty (pg_tablespace_size ('tablespace_name'));
8. 获取 Postgres 中所有数据库的列表及其大小（以 GB 为单位），按最大大小排序
SELECT pg_database.datname as "database_name", pg_database_size(pg_database.datname)/1024/1024/1024 AS size_in_GB FROM pg_database ORDER by size_in_GB DESC; database_name | size_in_gb ---------------+------------ mumbai | 422 template1 | 0 template0 | 0 (3 rows)
使用元命令获取 Postgres 中所有数据库及其大小的列表
nellore=# \l+
9. 查找当前数据库中所有表大小的脚本。
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
10. 查找当前数据库中所有表和索引大小的脚本。
SELECT TableName ,pg_size_pretty(pg_table_size(TableName)) AS TableSize ,pg_size_pretty(pg_indexes_size(TableName)) AS IndexSize ,pg_size_pretty(pg_total_relation_size(TableName)) AS TotalSize FROM ( SELECT ('"' || table_schema || '"."' || table_name || '"') AS TableName FROM information_schema.tables ) AS Tables ORDER BY 4 DESC
11.检查表大小以及依赖项大小
SELECT schemaname,
relname as "Table",
pg_size_pretty(pg_total_relation_size(relid)) As " table_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;
schemaname | Table | table_Size | External Size
-----------------------+----------------------------------------+------------+---------------
mhrordhu_dhu | ror_bulk_sign_data_audit | 7940 MB | 6632 MB
mhrordhu_shi | ror_bulk_sign_data_audit | 7288 MB | 6104 MB
mhrordhu_shi | ror_sign_tables_audit | 3458 MB | 61 MB
mhrordhu_sak | ror_bulk_sign_data_audit | 3174 MB | 2667 MB
mhrordhu_dhu_os | holder_detail_audit | 2794 MB | 776 kB
mhrordhu_sak | tbl_summary_audit | 2058 MB | 584 kB
mhrordhu_sak_os | form7_khata_audit | 2042 MB | 576 kB
mhrordhu_dhu_his | holder_detail | 1963 MB | 552 kB
mhrordhu_dhu_os | holder_detail_audit_cor | 1605 MB | 464 kB
12. 当前模式或任何模式中所有表的大小、表相关对象的大小以及总表大小
SELECT stats.relname AS table, pg_size_pretty(pg_relation_size(statsio.relid)) AS table_size, pg_size_pretty(pg_total_relation_size(statsio.relid) - pg_relation_size(statsio.relid)) AS related_objects_size, pg_size_pretty(pg_total_relation_size(statsio.relid)) AS total_table_size, stats.n_live_tup AS live_rows FROM pg_catalog.pg_statio_user_tables AS statsio JOIN pg_stat_user_tables AS stats USING (relname) WHERE stats.schemaname = current_schema -- Replace with any schema name UNION ALL SELECT 'TOTAL' AS table, pg_size_pretty(sum(pg_relation_size(statsio.relid))) AS table_size, pg_size_pretty(sum(pg_total_relation_size(statsio.relid) - pg_relation_size(statsio.relid))) AS related_objects_size, pg_size_pretty(sum(pg_total_relation_size(statsio.relid))) AS total_table_size, sum(stats.n_live_tup) AS live_rows FROM pg_catalog.pg_statio_user_tables AS statsio JOIN pg_stat_user_tables AS stats USING (relname) WHERE stats.schemaname = current_schema -- Replace with any schema name ORDER BY live_rows ASC; table | table_size | related_objects_size | total_table_size | live_rows ------------------+------------+----------------------+------------------+----------- t11 | 0 bytes | 0 bytes | 0 bytes | 0 t1 | 0 bytes | 16 kB | 16 kB | 0 m_offic_temp | 8192 bytes | 32 kB | 40 kB | 3 temptbl | 8192 bytes | 32 kB | 40 kB | 41 form7_khata_temp | 1568 kB | 32 kB | 1600 kB | 8507 temp | 0 bytes | 8192 bytes | 8192 bytes | 119340 temp | 0 bytes | 8192 bytes | 8192 bytes | 119340 temp | 25 MB | 32 kB | 25 MB | 119340 temp | 0 bytes | 8192 bytes | 8192 bytes | 119340 temp | 0 bytes | 8192 bytes | 8192 bytes | 119340 TOTAL | 27 MB | 176 kB | 27 MB | 605251
以下元命令对于列出当前模式中现有表的大小也很有用
nellore=# \d+ List of relations Schema | Name | Type | Owner | Size | Description --------+------------------+-------+-----------------+---------+------------- public | form7_khata_temp | table | postgres | 1600 kB | public | temp | table | postgres | 25 MB | public | temptbl | table | raj_admin | 40 kB | (3 rows)
以下元命令可用于显示所有架构的表大小
\dt+ *.*
以下元命令可用于显示特定架构的表大小
\dt+ schema_name.*
13.PostgreSQL 列值大小
要查找存储特定值需要多少空间，可以使用 pg_column_size() 函数，例如：
nijam=# select pg_column_size(5::smallint); pg_column_size ---------------- 2 (1 row)
nijam=# select pg_column_size(5::int); pg_column_size ---------------- 4 (1 row)
nijam=# select pg_column_size(5::bigint); pg_column_size ---------------- 8 (1 row)
