PostgreSQL Multi-Tenant Usage – Performance Impact Test of set search_path

By digoal

Background

If schema is used as the multi-tenant isolation technology, when a tenant accesses the database, search_path must be set. You do not need to splice sql nsp.object but use search_path directly.

Will set search_path and subsequent SQL statements be assigned to different backend connections so set search_path cannot change the tenant aim? This issue is not discussed in this article. If you need an answer, you can consider several solutions:

Use simple query to encapsulate set search_path and business SQL statements into one request. Note: This may increase the risk of SQL injection because binding variables cannot be used.
Use transactions. Each time a request is initiated, transactions are started, and search_path is set. The performance may not be so good.
Use functions to initiate requests and put search_path into functions.

This article tests the performance of the set search_path statement when its being used many times.

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

The performance when set search_path is not used:

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

For 1,000 schemas, each time a request is initiated, search_path is set, and it is reset after the request is processed. QPS reaches 187,095.

For one schema, QPS reaches 269,902.

The performance gap is relatively wide, but a relatively high QPS is maintained. About 0.05 milliseconds are used for rt that is introduced, but for most SQL statements, 0.05 milliseconds is nothing.

Community

PostgreSQL Multi-Tenant Usage – Performance Impact Test of set search_path

Background

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

AnalyticDB for PostgreSQL

ApsaraDB RDS for SQL Server

Elastic High Performance Computing Solution