PostgreSQL 多租户方案比较与实践

1. 基于数据库的多租户方案

优点

数据库的隔离方式较为彻底，资源利用更高效。通过数据库层面实现存储、连接、认证和权限的隔离，确保数据安全。即使使用外部访问（如fdw或dblink），也需要进行认证和权限控制，进一步增强安全性。

数据库层面的隔离机制可以通过pg_hba.conf控制来源IP和用户权限，确保租户之间的互不影响。此外，数据库本身的权限体系支持灵活的权限分配，例如在目标数据库创建特定Schema或授予特定用户访问权限。

缺点

每个数据库都伴随着大量的元数据（如约束、索引等），随着租户数量的增加，元数据规模会显著扩大，可能导致性能问题。例如，创建500张类似的表会显著增加元数据的负载和文件操作。

实践步骤

创建用户角色并分配租户权限：

create role tenant_name with login;

配置防火墙规则：

# pg_hba.confhost all all 127.0.0.1/32 trusthost replication postgres 127.0.0.1/32 trust

使用数据库模板创建新数据库：

create database db with template template_name;

授予数据库权限：

revoke all on database db from public;grant all on database db to tenant_name;

2. 基于Schema的多租户方案

优点

Schema的隔离方式相比数据库更为灵活，资源利用更为高效。可以通过Schema实现数据的逻辑隔离，事务处理为本地事务，性能表现优于数据库隔离方式。这种方法适合企业内部私有化的多租户场景。

Schema的创建和删除操作只需简单地使用create schema和drop schema语句，操作效率显著高于数据库。同时，通过search_path路由机制，可以将应用程序动态路由至对应的Schema。

缺点

Schema隔离的安全性不足，无法通过pg_hba.conf直接控制Schema权限，存在信息泄露风险。用户可以通过查看元表（如pg_class）获取其他Schema的对象信息，增加安全性要求。

此外，创建和删除Schema会产生大量元数据变更和文件操作，可能导致性能问题。例如，删除Schema需要清理大量的系统表记录和相关文件。

实践步骤

创建用户角色：

create role tenant_name with login;

创建Schema并授予权限：

create schema tenant_schema;revoke all on schema tenant_schema from public;grant all on schema tenant_schema to tenant_name;

路由选择机制：

create or replace function public.route() returns void as $$declarebegin    execute 'set search_path='||current_setting('application_name')||', "$user", public';end;$$ language plpgsql strict;

业务逻辑封装：

create or replace function business_function(param) returns xx as $$declarebegin    perform route_function(param);    -- 执行业务SQLend;$$ language plpgsql strict;

3. 实战案例

环境准备

硬件：两块PCI-E SSD，分别存放主库和备库。

主库：监听地址5289。

备库：监听地址5290。

PostgreSQL 配置

# postgresql.conflisten_addresses = '0.0.0.0'port = 5289max_connections = 100shared_buffers = 8GBmaintenance_work_mem = 512MBdynamic_shared_memory_type = posixbgwriter_delay = 10msbgwriter_lru_maxpages = 1000bgwriter_lru_multiplier = 10.0wal_level = hot_standbysynchronous_commit = offwal_buffers = 128MBwal_writer_delay = 10mscheckpoint_segments = 256checkpoint_timeout = 30mincheckpoint_completion_target = 0.0001max_wal_senders = 10wal_keep_segments = 512hot_standby = onmax_standby_archive_delay = 300smax_standby_streaming_delay = 300swal_receiver_status_interval = 1hot_standby_feedback = onrandom_page_cost = 1.0log_destination = 'csvlog'logging_collector = onlog_truncate_on_rotation = onlog_checkpoints = onlog_connections = onlog_disconnections = onlog_error_verbosity = verboselog_timezone = 'PRC'autovacuum = onlog_autovacuum_min_duration = 0autovacuum_naptime = 10sdatestyle = 'iso, mdy'timezone = 'PRC'lc_messages = 'C'lc_monetary = 'C'lc_numeric = 'C'lc_time = 'C'default_text_search_config = 'pg_catalog.english'max_locks_per_transaction = 1000000

pg_hba.conf 配置

# pg_hba.conflocal   all             all                                     trusthost    all             all             127.0.0.1/32            trusthost    all             all             ::1/128                 trusthost    replication     postgres        127.0.0.1/32            trust

备库创建

# 主库执行pg_basebackup -D /data01/pg_root/5290 -F p -x -h 127.0.0.1 -p 5289 -U postgrescd /data01/pg_root/5290mv recovery.done recovery.confvi postgresql.confport = 5290pg_ctl start

Schema准备

# 进入 template1 数据库psql -U postgres -d template1

表创建

create table test(    c0 serial unique check (c0 > 0),    c1 serial unique check (c1 > 0),    -- ...（其余字段）    c50 serial unique check (c50 > 0));

测试删除Schema

psql -U postgres -d db0 -c "drop schema public cascade;"

REDO分析

pg_xlogdump -b 000000010000000100000013 000000010000000100000014 2 > /tmp/log

性能监控

watch -n 1 "select pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(), sent_location)), pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(), write_location)), pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(), flush_location)), pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(), replay_location)), * from pg_stat_replication;"