如何通过SQL查询获取TimescaleDB服务器的总磁盘占用量？

我通过Python的sqlalchemy库远程访问TimescaleDB服务器，希望查询获取服务器的总磁盘占用空间（无法精确的话，估算值也可）。根据PostgreSQL文档，\l+命令可显示服务器上所有数据库的相关信息，下方是通过psql -E获取的该命令源码。我想知道对Size列求和是否能得到我需要的总占用量，是否存在遗漏项？

SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges",
       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",
       t.spcname as "Tablespace",
       pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"
FROM pg_catalog.pg_database d
  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
ORDER BY 1;

另外我编写了一段查询语句，想请教是否需要保留其中的CASE WHEN部分，以及是否有优化空间：

SELECT
       SUM(CASE WHEN has_database_privilege(pg_database.datname, 'CONNECT')
              THEN pg_database_size(pg_database.datname)
              ELSE 0
       END) as "Size"
FROM pg_database;

1. 对\l+的Size列求和是否能得到总磁盘占用？有哪些遗漏？ #

对\l+里的Size列（对应pg_database_size(datname)的结果）求和，只能得到所有你有权限访问的数据库的总存储占用估算，无法代表服务器整体的磁盘占用，会遗漏以下几类内容：

• 共享系统对象：存储在pg_global表空间中的共享系统表（比如pg_authid、pg_database等），这些是所有数据库共享的资源，不会被单个数据库的pg_database_size统计。
• WAL日志：PostgreSQL/TimescaleDB的预写日志（Write-Ahead Logs），用于事务恢复，这部分文件占用的磁盘空间不在数据库大小统计范围内。
• 临时文件/临时对象：查询过程中产生的临时表、排序临时文件，或者长会话中创建的临时对象，这些文件的占用不会被计入数据库大小。
• 归档日志：如果开启了WAL归档功能，归档后的日志文件会占用额外磁盘空间，同样不在统计范围内。
• 自定义表空间的非数据库对象：如果存在不属于任何数据库的自定义表空间对象（虽然少见），这部分占用也会被遗漏。

如果只是想估算所有业务数据库的总占用，这个求和结果是可用的；但如果要统计服务器磁盘的整体使用情况，还需要额外考虑上述这些部分。

2. 自定义查询的CASE WHEN是否需要保留？有哪些优化空间？ #

CASE WHEN的必要性 #

必须保留CASE WHEN部分——因为如果你的数据库用户对某些数据库没有CONNECT权限，直接调用pg_database_size会抛出权限错误，导致整个查询失败。通过CASE WHEN先判断权限，无权限时返回0，可以保证查询正常执行。

优化建议 #

• 加上pg_catalog前缀，避免和用户自定义函数/表名冲突，让语句更严谨：

  SELECT
         SUM(CASE WHEN pg_catalog.has_database_privilege(pg_catalog.pg_database.datname, 'CONNECT')
                THEN pg_catalog.pg_database_size(pg_catalog.pg_database.datname)
                ELSE 0
         END) as "Total_Database_Size"
  FROM pg_catalog.pg_database;
  

• 如果你确认当前用户对所有数据库都拥有CONNECT权限，可以去掉CASE WHEN，简化为：

  SELECT SUM(pg_catalog.pg_database_size(datname)) as "Total_Database_Size"
  FROM pg_catalog.pg_database;
  

• 可以过滤掉template0数据库（默认只读且无法连接，本身占用极小），减少不必要的判断：

  SELECT
         SUM(CASE WHEN pg_catalog.has_database_privilege(pg_catalog.pg_database.datname, 'CONNECT')
                THEN pg_catalog.pg_database_size(pg_catalog.pg_database.datname)
                ELSE 0
         END) as "Total_Database_Size"
  FROM pg_catalog.pg_database
  WHERE datname != 'template0';
  

内容的提问来源于stack exchange，提问作者Shuri2060