PostgreSQL如何对多租户手动分表实现动态SELECT查询

PostgreSQL可以通过以下几种方式实现你的需求，你可以根据自己的使用场景选择：

1. 静态UNION ALL（适用于租户数量少且固定的场景） #

直接手动拼接所有租户的实体表查询，在每个子查询中硬编码对应的租户名称即可：

SELECT 'tenant_a' AS tenant_name, id, last_updated FROM tenant_a_entities
UNION ALL
SELECT 'tenant_b' AS tenant_name, id, last_updated FROM tenant_b_entities
-- 有多少个租户就补充多少段SELECT语句

该方案实现简单、查询性能稳定，缺点是租户新增或删除时需要手动修改查询语句。

2. 动态SQL函数（适用于租户频繁变动的场景） #

你可以通过PL/pgSQL编写动态查询函数，自动遍历tenants表的所有租户生成查询语句，不需要手动维护租户列表：

CREATE OR REPLACE FUNCTION get_all_tenant_entities()
RETURNS TABLE (
    tenant_name varchar(50),
    id uuid,
    last_updated timestamp
) AS $$
DECLARE
    cur_tenant record;
    union_query text := '';
BEGIN
    -- 遍历所有租户拼接UNION ALL查询
    FOR cur_tenant IN SELECT name FROM tenants LOOP
        IF union_query != '' THEN
            union_query := union_query || ' UNION ALL ';
        END IF;
        union_query := union_query || format(
            'SELECT %L AS tenant_name, id, last_updated FROM %I',
            cur_tenant.name,
            cur_tenant.name || '_entities'
        );
    END LOOP;

    -- 执行动态生成的SQL并返回结果
    RETURN QUERY EXECUTE union_query;
END;
$$ LANGUAGE plpgsql STABLE;

函数创建完成后，直接调用即可得到目标结构的结果：

SELECT * FROM get_all_tenant_entities();

3. 适配FDW联邦查询的优化方案 #

如果你正在使用FDW做异构数据库联邦，可以使用继承表特性简化查询：

1. 先创建你需要的目标结构作为父表：

CREATE TABLE all_tenant_entities (
  tenant_name varchar(50),
  id uuid,
  last_updated timestamp
);

2. 将每个租户的实体表（如果是异构库的话先创建对应的外部表）设置为继承该父表，以tenant_a为例：

ALTER TABLE tenant_a_entities INHERIT all_tenant_entities;
-- 给租户表设置租户名默认值，写入数据时自动填充不需要额外处理
ALTER TABLE tenant_a_entities ALTER COLUMN tenant_name SET DEFAULT 'tenant_a';

后续直接查询父表即可拿到所有租户的全量数据：

SELECT * FROM all_tenant_entities;

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