PolarDB | PostgreSQL 高并发队列处理业务的数据库性能优化实践

MacBook Pro (15-inch, 2018)  
2.2 GHz 六核Intel Core i7  
32 GB 2400 MHz DDR4  
PostgreSQL 15.1

ulimit -n 1000000

create table t_order_q (  
  id serial8 primary key,   -- 自增主键  
  order_id uuid unique,     -- 上游传递过来的订单号  
  cts timestamp not null    -- 上游传递过来的订单创建时间   
);   
-- create index on t_order_q (cts); -- 如果按订单时间先后取出处理, 则需要创建时间字段索引.  也可以按自增主键顺序处理, 则不需要时间索引.

create table t_order_u (  
  id serial8 primary key,   -- 自增主键  
  order_id uuid unique,     -- 上游传递过来的订单号  
  cts timestamp not null,    -- 上游传递过来的订单创建时间   
  uts timestamp not null,   -- 订单处理时间  
  status int not null       -- 订单处理状态标记   
);

insert into t_order_q (order_id, cts) select gen_random_uuid(), clock_timestamp() from generate_series(1,1000000);

vi t.sql  
with tmp as   
  (delete from t_order_q where ctid = (select ctid from t_order_q where pg_try_advisory_xact_lock(id) order by id limit 1) returning order_id, cts)  
insert into t_order_u (order_id,cts,uts,status) select tmp.order_id, tmp.cts, now(), 1 from tmp;
或
begin;
select id as v_id from t_order_q where pg_try_advisory_xact_lock(id) order by id limit 1 \gset
with tmp as (delete from t_order_q where id = :v_id returning order_id, cts) 
  insert into t_order_u (order_id,cts,uts,status) select tmp.order_id, tmp.cts, now(), 1 from tmp;   
end;
或(sleep 模拟应用拿到需要处理的订单后的应用端操作增加的耗时.)
begin; 
select id as v_id from t_order_q where pg_try_advisory_xact_lock(id) order by id limit 1 \gset
\sleep 10ms
with tmp as (delete from t_order_q where id = :v_id returning order_id, cts) 
  insert into t_order_u (order_id,cts,uts,status) select tmp.order_id, tmp.cts, now(), 1 from tmp;   
end;

select 1000000/256.0;  
3906.2500000000000

pgbench -M extended -f ./t.sql -n -r -P 1 -c 256 -j 2 -t 3906

transaction type: ./t.sql  
scaling factor: 1  
query mode: extended  
number of clients: 256  
number of threads: 2  
maximum number of tries: 1  
number of transactions per client: 3906  
number of transactions actually processed: 999936/999936  
number of failed transactions: 0 (0.000%)  
latency average = 8.111 ms  
latency stddev = 5.376 ms  
initial connection time = 429.698 ms  
tps = 25379.081141 (without initial connection time)  
statement latencies in milliseconds and failures:  
         8.114           0  with tmp as

vi t1.sql  
begin;  
select id as vid from t_order_q order by id for update limit 1 \gset  
with tmp as   
  (delete from t_order_q where id = :vid returning order_id, cts)  
insert into t_order_u (order_id,cts,uts,status) select tmp.order_id, tmp.cts, now(), 1 from tmp;   
end;

pgbench -M extended -f ./t1.sql -n -r -P 1 -c 256 -j 2 -t 3906

TPS 约 1200.

begin;  
select id as vid from t_order_q order by id for update skip locked limit 1 \gset  
with tmp as   
  (delete from t_order_q where id = :vid returning order_id, cts)  
insert into t_order_u (order_id,cts,uts,status) select tmp.order_id, tmp.cts, now(), 1 from tmp;   
end;