如何在PostgreSQL所有数据库Schema中高效执行增删改查？

针对你用Schema做租户隔离、动态创建Schema且表结构一致的场景，以下是几种高效操作方案：

1. 单Schema操作：用搜索路径简化前缀 #

如果是针对单个租户（Schema）操作，先设置search_path，后续操作无需重复写Schema前缀：

-- 切换到目标Schema，public作为 fallback
SET search_path TO customer_001, public;

-- 直接操作表，PostgreSQL会自动在指定Schema查找
INSERT INTO orders (order_id, amount) VALUES (1001, 299.99);
UPDATE orders SET status = 'completed' WHERE order_id = 1001;
SELECT * FROM orders WHERE status = 'pending';

2. 批量操作：动态生成并执行SQL #

通过查询系统元数据获取所有目标Schema，动态拼接SQL实现批量CRUD。比如批量插入数据到所有Schema的users表：

-- 第一步：生成所有INSERT语句（可复制结果手动执行）
SELECT 'INSERT INTO ' || quote_ident(schema_name) || '.users (user_id, name) VALUES (1000, ''admin'');'
FROM information_schema.schemata
WHERE schema_name LIKE 'customer_%'; -- 按你的Schema命名规则过滤

-- 第二步：用DO块自动执行批量操作（推荐）
DO $$
DECLARE
    schema_rec record;
BEGIN
    FOR schema_rec IN SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE 'customer_%' LOOP
        -- 用format函数安全处理标识符和字符串，避免SQL注入
        EXECUTE format('INSERT INTO %I.users (user_id, name) VALUES (1000, %L);', schema_rec.schema_name, 'admin');
    END LOOP;
END $$;

同理，批量更新、删除只需要修改EXECUTE里的SQL语句即可。

3. 封装通用函数：简化重复操作 #

把常用的批量CRUD逻辑封装成PL/pgSQL函数，后续调用更便捷。比如批量更新所有Schema的orders表状态：

CREATE OR REPLACE FUNCTION batch_update_orders(new_status text)
RETURNS void AS $$
DECLARE
    schema_rec record;
BEGIN
    FOR schema_rec IN SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE 'customer_%' LOOP
        EXECUTE format('UPDATE %I.orders SET status = %L WHERE status = ''processing'';', schema_rec.schema_name, new_status);
    END LOOP;
END $$ LANGUAGE plpgsql;

-- 调用函数完成批量更新
SELECT batch_update_orders('completed');

4. 长期优化：用分区表替代多Schema #

如果所有租户的表结构完全一致，建议用分区表替代多Schema的隔离方式，按租户ID作为分区键：

-- 创建主表（分区父表）
CREATE TABLE orders (
    order_id INT,
    tenant_id INT,
    amount NUMERIC,
    status TEXT
) PARTITION BY LIST (tenant_id);

-- 为每个租户创建单独分区
CREATE TABLE orders_tenant001 PARTITION OF orders FOR VALUES IN (1);
CREATE TABLE orders_tenant002 PARTITION OF orders FOR VALUES IN (2);

-- 插入数据时指定tenant_id，自动路由到对应分区
INSERT INTO orders (order_id, tenant_id, amount, status) VALUES (1001, 1, 299.99, 'pending');

-- 查询单个租户数据：过滤tenant_id
SELECT * FROM orders WHERE tenant_id = 1;
-- 查询所有租户数据：直接查主表
SELECT * FROM orders;

这种方式避免了多Schema的元数据管理成本，CRUD操作和普通表完全一致，无需动态SQL，性能也更适合大规模租户场景。

关键注意事项 #

• 动态SQL必须用quote_ident或format的%I（处理标识符）、%L（处理字符串），防止SQL注入风险。
• 批量操作前务必在测试环境验证逻辑，避免误操作所有租户数据。
• 当租户数量达到上万级时，分区表的扩展性和性能远优于多Schema架构。

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