作者
digoal
日期
2020-05-25
标签
PostgreSQL , set search_path , 性能
背景
使用schema作为多租户隔离的技术, 当租户访问数据库时, 设置search_path. (业务不需要拼接sql nsp.object, 直接使用search_path来设置)
set search_path 和后续sql会不会分配到不同的后端连接, 导致set search_path无法达到改租户目标的目的? 这个问题不在本文讨论之列, 如果有问题, 可以考虑几个方案:
- 采用simple query, 把set search_path和业务sql封装到1个请求. 注意可能增加sql注入风险. 因为无法使用绑定变量.
- 使用事务方式, 每次发起请求时, 开启事务, 然后设置search_path, 性能会有问题.
- 使用函数发起请求, search_path放到函数里面.
本文测试大量set search_path的性能如何?
do language plpgsql $$
declare
begin
for i in 1..1000 loop
execute 'create schema sc'||i;
execute format('create table %I.abc(id int primary key, info text, crt_time timestamp);', 'sc'||i);
end loop;
end;
$$;
create or replace function upsert_multi_nsp(int, int) returns void as $$
declare
nsp name := 'sc'||$1;
begin
execute format('set search_path=%I', nsp);
execute format('insert into abc values (%s, random()::text, now()) on conflict(id) do update set info=excluded.info,crt_time=excluded.crt_time', $2);
reset search_path;
exception when others then
reset search_path;
end;
$$ language plpgsql strict;
vi test.sql
\set v1 random(1,1000)
\set v2 random(1,100000)
select upsert_multi_nsp(:v1,:v2);
```
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 1200
progress: 144.0 s, 189369.0 tps, lat 0.169 ms stddev 0.055
progress: 145.0 s, 188017.3 tps, lat 0.170 ms stddev 0.070
progress: 146.0 s, 183697.5 tps, lat 0.174 ms stddev 0.065
progress: 147.0 s, 185821.0 tps, lat 0.172 ms stddev 0.075
progress: 148.0 s, 187040.4 tps, lat 0.171 ms stddev 0.060
progress: 149.0 s, 186279.8 tps, lat 0.172 ms stddev 0.067
progress: 150.0 s, 187095.5 tps, lat 0.171 ms stddev 0.068
```
非set search_path的性能
create table abc(id int primary key, info text, crt_time timestamp);
create or replace function upsert_nsp(int) returns void as $$
declare
begin
execute format('insert into abc values (%s, random()::text, now()) on conflict(id) do update set info=excluded.info,crt_time=excluded.crt_time', $1);
end;
$$ language plpgsql strict;
vi test.sql
\set v1 random(1,100000000)
select upsert_nsp(:v1);
```
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 1200
progress: 56.0 s, 271966.4 tps, lat 0.117 ms stddev 0.159
progress: 57.0 s, 271754.7 tps, lat 0.118 ms stddev 0.161
progress: 58.0 s, 272925.0 tps, lat 0.117 ms stddev 0.151
progress: 59.0 s, 268870.2 tps, lat 0.118 ms stddev 0.163
progress: 60.0 s, 272693.3 tps, lat 0.118 ms stddev 0.167
progress: 61.0 s, 267019.3 tps, lat 0.120 ms stddev 0.151
progress: 62.0 s, 271654.5 tps, lat 0.118 ms stddev 0.137
progress: 63.0 s, 273257.4 tps, lat 0.117 ms stddev 0.139
progress: 64.0 s, 272775.8 tps, lat 0.117 ms stddev 0.127
progress: 65.0 s, 272574.6 tps, lat 0.117 ms stddev 0.137
progress: 66.0 s, 269902.3 tps, lat 0.118 ms stddev 0.146
```
1000个schema, 每次请求前设置search_path, 请求结束后reset search_path. qps 187095.
1个schema, qps 269902.
性能还是有一定差别, 但是依旧可以保持比较高的qps, 引入的rt约0.05毫秒, 对于大多数的sql来说0.05毫秒不算什么.
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
