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;