PostgreSQL如何列出各数据库前10大表（含索引）并按大小排序

要实现遍历所有数据库并获取每个库中前10大（含索引）的表，核心问题是在PL/pgSQL中切换目标数据库查询——常规SELECT语句只能访问当前连接的数据库，这里需要用dblink扩展来动态连接到每个目标数据库执行查询。

前置步骤 #

首先确保已安装dblink扩展（如果未安装）：

CREATE EXTENSION IF NOT EXISTS dblink;

修正后的PL/pgSQL代码 #

DO $$
DECLARE
    database_name pg_database.datname%TYPE;
    table_name pg_tables.tablename%TYPE;
    total_table_size bigint; -- pg_total_relation_size返回bigint类型
    dblink_conn text; -- dblink连接字符串
    rec record;
BEGIN
    -- 遍历所有非系统数据库（可根据需求调整过滤条件）
    FOR database_name IN 
        SELECT datname 
        FROM pg_database 
        WHERE datistemplate = false -- 排除模板库
          AND datname NOT IN ('postgres') -- 排除默认postgres库（可选）
    LOOP
        -- 构造dblink连接字符串，连接到目标数据库
        dblink_conn := 'dbname=' || quote_ident(database_name);
        
        RAISE NOTICE '===== 数据库: % =====', database_name;
        
        -- 用dblink连接到目标数据库，查询前10大表（含索引）
        FOR table_name, total_table_size IN
            SELECT t.tablename, pg_total_relation_size(c.oid) AS total_size
            FROM dblink(
                dblink_conn,
                'SELECT schemaname, tablename FROM pg_tables WHERE schemaname NOT IN (''pg_catalog'', ''information_schema'')'
            ) AS t(schemaname text, tablename text)
            JOIN pg_class c ON c.relname = t.tablename
            JOIN pg_namespace n ON n.oid = c.relnamespace AND n.nspname = t.schemaname
            ORDER BY pg_total_relation_size(c.oid) DESC
            LIMIT 10
        LOOP
            -- 格式化输出大小（转成MB），方便阅读
            RAISE NOTICE '表: %.%, 总大小: %.2f MB', 
                quote_ident(t.schemaname), quote_ident(table_name), 
                total_table_size / 1024.0 / 1024.0;
        END LOOP;
        
        RAISE NOTICE ''; -- 空行分隔不同数据库的结果
    END LOOP;
END;
$$;

关键修正说明 #

1. 解决跨库查询问题：用dblink动态连接目标数据库，替代原代码中直接查询当前库pg_tables的错误逻辑。
2. 变量类型修正：将total_table_size改为bigint，匹配pg_total_relation_size的返回类型，避免类型不兼容错误。
3. 过滤系统对象：排除模板库、默认postgres库，以及系统模式下的表，只返回用户自定义表。
4. 准确计算表大小：通过pg_class.oid调用pg_total_relation_size，确保计算包含表本身、所有索引和TOAST表的总大小。
5. 语法与安全修正：添加未声明的table_name变量，用quote_ident()处理标识符，避免特殊字符导致的语法错误或注入风险。

内容的提问来源于stack exchange，提问作者Diogo dos Santos