在這篇文章中，我分享了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)