如何獲取 PostgreSQL 資料庫中的表大小、資料庫大小、索引大小、模式大小、表空間大小、列大小

wongchaofan發表於2024-07-27
SQL資料庫索引模式

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

相關文章