PostgreSQL多租户架构下管理员跨Schema聚合数据的最优方案咨询

问题描述 #

我正在开发基于PostgreSQL的多租户应用，每个企业对应独立Schema，各企业仅能访问自身Schema的数据；管理员租户需查看所有企业的数据。示例表结构如下：

company1.table #

company2.table #

期望的admin.table（逻辑视图） #

我期望ORM可直接查询admin.table，但需通过中间步骤遍历所有Schema，替换current_query()以引用各Schema，最终用UNION ALL聚合结果。此前尝试的方案均存在问题：

• 普通视图：丢失索引导致查询极慢
• 继承表：同样存在索引失效、查询性能差的问题
• 物化视图：无法创建插入触发器来实时同步数据

最优解决方案 #

1. 动态SQL函数封装核心逻辑 #

创建一个返回目标结构的PL/pgSQL函数，内部动态遍历所有企业Schema，生成UNION ALL查询并执行。该方案能直接利用各Schema表的索引，同时让ORM像查询普通表一样调用。

CREATE OR REPLACE FUNCTION get_admin_table()
RETURNS TABLE (column_a text, column_b text, company_id integer) AS $$
DECLARE
    rec record;
    query text := '';
BEGIN
    -- 遍历所有企业Schema（这里假设Schema命名以company开头，可根据实际调整匹配规则）
    FOR rec IN SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE 'company%' LOOP
        IF query != '' THEN
            query := query || ' UNION ALL ';
        END IF;
        -- 拼接子查询，自动提取company_id并映射到结果列
        query := query || format(
            'SELECT column_a, column_b, %L::integer AS company_id FROM %I.table', 
            substring(rec.schema_name from 'company(\d+)'), 
            rec.schema_name
        );
    END LOOP;
    
    RETURN QUERY EXECUTE query;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;

2. 创建视图适配ORM接口 #

基于上述函数创建视图，让ORM可以直接查询admin.table，完全适配现有查询逻辑：

CREATE VIEW admin.table AS
SELECT * FROM get_admin_table();

该视图不存储数据，每次查询都会触发函数执行动态聚合，每个子查询都会利用对应Schema表的索引，解决普通视图的性能瓶颈。

3. 严格权限控制保障数据隔离 #

• 普通企业用户：仅授予对应Schema表的SELECT权限，禁止访问admin.table视图和get_admin_table()函数。
• 管理员用户：授予admin.table视图的SELECT权限；同时确保get_admin_table()函数的所有者拥有所有企业Schema表的访问权限（通过SECURITY DEFINER属性实现权限提升，需注意函数所有者的权限范围）。

4. 性能优化建议 #

• 缓存Schema列表：若企业数量较多，可在函数中添加缓存逻辑，比如用临时表存储Schema列表，定期刷新（例如通过定时任务），减少每次查询遍历information_schema的开销。
• 索引优化：确保每个企业Schema的table表都有合适的索引，动态生成的UNION ALL查询会自动继承这些索引的查询优化。
• 并行查询：PostgreSQL会对UNION ALL的每个子查询进行并行处理，可通过调整max_parallel_workers_per_gather参数提升查询效率。

5. 写入支持（可选） #

如果需要管理员端执行批量写入操作，可封装对应的写入函数，根据company_id路由到对应的企业Schema表：

CREATE OR REPLACE FUNCTION insert_admin_table(p_column_a text, p_column_b text, p_company_id integer)
RETURNS void AS $$
DECLARE
    schema_name text := format('company%s', p_company_id);
BEGIN
    EXECUTE format('INSERT INTO %I.table (column_a, column_b) VALUES (%L, %L)', 
                   schema_name, p_column_a, p_column_b);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

方案优势 #

• 性能优异：动态UNION ALL让每个子查询独立使用底层表的索引，避免了普通视图、继承表的性能问题。
• 数据实时：直接读取源数据，无需同步，替代物化视图的实时性痛点。
• 隔离彻底：各企业Schema完全独立，权限控制简单可靠。
• ORM友好：视图接口完全适配现有ORM查询逻辑，无需修改业务代码。

内容的提问来源于stack exchange，提问作者Bruno Bergamini do Nascimento