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.
Read previous post:
Quick Creation of a Large Number of Partition Indexes in PostgreSQL
Read next post:
PostgreSQL Multi-Tenant Usage – Multi-Schema Batch Execution of DDL Scripts
Comments
Related Products
-
PolarDB for PostgreSQL
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn More -
AnalyticDB for PostgreSQL
An online MPP warehousing service based on the Greenplum Database open source program
Learn More -
ApsaraDB RDS for SQL Server
An on-demand database hosting service for SQL Server with automated monitoring, backup and disaster recovery capabilities
Learn More -
ApsaraDB RDS for PostgreSQL
An on-demand database hosting service for PostgreSQL with automated monitoring, backup and disaster recovery capabilities
Learn More
More Posts by digoal
- Tips for Executing SQL Statements in Batch: Which Is Better, gexec or Dynamic SQL?
- What Could Be the Factor Leading to the Slow Execution of the First SQL Statement?
- How to Remove Duplicate and Similar Contents in Vector Databases
- How to Implement Parallelism and Concurrency Control (Queue) in Shell
- How to Determine Whether the Current PostgreSQL Database is in a Consistent State?
- PolarDB for PostgreSQL | Retrieval and Matching-Degree Sorting by Tags on Blogs and Websites
- Three Key Details and Principles of PostgreSQL Online Backup & Recovery
- PolarDB for PostgreSQL | Database Performance Optimization for High-Concurrency Queue Processing
- Implementing PostgreSQL Hook: Stats on Tables with Full Scans & Corresponding SQLs
- Optimization on Memory Usage During Rust Cargo Code Compiling