如何定位向pg_largeobject溢出数据的表与列？

一、回溯现有大对象的来源表和列 #

PostgreSQL默认没有记录pg_largeobject写入来源的内置日志，需要通过关联业务表的大对象类型列来排查：

1. 找出所有存储大对象引用的列 #

pg_largeobject中的loid对应业务表中oid或lo类型列的值，先查询数据库中所有这类列：

SELECT n.nspname AS schema_name,
       c.relname AS table_name,
       a.attname AS column_name
FROM pg_attribute a
JOIN pg_class c ON a.attrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE a.atttypid IN (SELECT oid FROM pg_type WHERE typname IN ('oid', 'lo'))
  AND a.attnum > 0
  AND NOT a.attisdropped
  AND c.relkind = 'r';

2. 关联pg_largeobject统计来源数据 #

对上述查询到的列，统计哪些loid存在于pg_largeobject中，并计算对应数据大小。可以用以下PL/pgSQL函数批量遍历查询：

CREATE OR REPLACE FUNCTION find_lo_owners()
RETURNS TABLE(schema_name text, table_name text, column_name text, loid oid, total_size text) AS $$
DECLARE
    col_rec record;
BEGIN
    FOR col_rec IN
        SELECT n.nspname, c.relname, a.attname
        FROM pg_attribute a
        JOIN pg_class c ON a.attrelid = c.oid
        JOIN pg_namespace n ON c.relnamespace = n.oid
        WHERE a.atttypid IN (SELECT oid FROM pg_type WHERE typname IN ('oid', 'lo'))
          AND a.attnum > 0
          AND NOT a.attisdropped
          AND c.relkind = 'r'
    LOOP
        RETURN QUERY EXECUTE format(
            'SELECT %L, %L, %L, %I, pg_size_pretty(sum(l.length))
             FROM %I.%I t
             JOIN pg_largeobject l ON t.%I = l.loid
             GROUP BY t.%I',
            col_rec.nspname, col_rec.relname, col_rec.attname,
            col_rec.attname, col_rec.nspname, col_rec.relname,
            col_rec.attname, col_rec.attname
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 执行查询获取结果
SELECT * FROM find_lo_owners();

3. 定位体积最大的大对象来源 #

先找出pg_largeobject中体积最大的loid，再关联到业务表：

WITH top_large_objects AS (
    SELECT loid, sum(length) AS total_bytes
    FROM pg_largeobject
    GROUP BY loid
    ORDER BY total_bytes DESC
    LIMIT 10
)
SELECT t.loid,
       pg_size_pretty(t.total_bytes) AS size,
       n.nspname AS schema,
       c.relname AS table,
       a.attname AS column
FROM top_large_objects t
JOIN pg_attribute a ON EXISTS (
    SELECT 1 FROM pg_class tbl
    WHERE tbl.oid = a.attrelid
      AND a.atttypid IN (SELECT oid FROM pg_type WHERE typname IN ('oid', 'lo'))
      AND a.attnum > 0
      AND NOT a.attisdropped
      AND EXISTS (SELECT 1 FROM tbl WHERE (tbl).a.attname = t.loid)
)
JOIN pg_class c ON a.attrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid;

二、监控未来的pg_largeobject写入事件 #

可以通过自定义日志表+包装函数的方式捕获写入行为：

1. 创建写入日志表 #

CREATE TABLE lo_operation_log (
    log_id SERIAL PRIMARY KEY,
    op_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    username TEXT DEFAULT current_user,
    client_ip INET DEFAULT inet_client_addr(),
    operation TEXT, -- 如lo_create、lo_import、lo_write
    loid OID,
    source_query TEXT DEFAULT current_query()
);

2. 包装大对象操作函数 #

PostgreSQL的大对象操作通过pg_catalog下的函数完成，我们可以包装这些函数，写入日志：

-- 备份原始lo_create函数
CREATE OR REPLACE FUNCTION pg_catalog.lo_create_orig(oid) RETURNS oid AS 'pg_catalog.lo_create' LANGUAGE C;

-- 创建带日志的lo_create包装函数
CREATE OR REPLACE FUNCTION pg_catalog.lo_create(oid) RETURNS oid AS $$
DECLARE
    new_oid oid;
BEGIN
    new_oid := pg_catalog.lo_create_orig($1);
    INSERT INTO lo_operation_log (operation, loid)
    VALUES ('lo_create', new_oid);
    RETURN new_oid;
END;
$$ LANGUAGE plpgsql;

-- 同理包装lo_import函数
CREATE OR REPLACE FUNCTION pg_catalog.lo_import_orig(text) RETURNS oid AS 'pg_catalog.lo_import' LANGUAGE C;

CREATE OR REPLACE FUNCTION pg_catalog.lo_import(text) RETURNS oid AS $$
DECLARE
    new_oid oid;
BEGIN
    new_oid := pg_catalog.lo_import_orig($1);
    INSERT INTO lo_operation_log (operation, loid)
    VALUES ('lo_import', new_oid);
    RETURN new_oid;
END;
$$ LANGUAGE plpgsql;

后续调用lo_create、lo_import等函数时，操作记录会自动写入lo_operation_log，通过source_query字段可以查看触发操作的业务SQL，从而定位来源表和列。

3. 实时监控活跃写入会话 #

如果需要临时排查正在写入的会话，可以查询pg_stat_activity：

SELECT pid, usename, client_addr, query, state
FROM pg_stat_activity
WHERE query LIKE '%pg_largeobject%' OR query LIKE '%lo_%'
  AND state = 'active';

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