PostgreSQL相似搜尋分散式架構設計與實踐-dblink非同步呼叫與多機並行(遠端遊標+記錄UDF例項)

德哥發表於2018-02-28

SQL分散式架構非同步並行

背景

背景請參考如下：

《PostgreSQL 相似搜尋設計與效能 – 地址、QA、POI等文字毫秒級相似搜尋實踐》

當需要進行相似搜尋的資料量大於單機處理能力時，我們需要水平拆分來提高搜尋能力。

或者可以使用阿里雲的PolarDB for PG的產品（類似ORACLE RAC，支援增加計算節點）。比水平分庫的好處是資料是共享儲存的，不需要拆分。

回到水平分庫的場景，如果我們把資料庫拆成了多個，那麼，如何讓查詢並行起來呢？

用DBLINK非同步呼叫，可以讓查詢並行起來。架構設計如下：

實際上採用DBLINK非同步呼叫實現並行的例子很多：

《PostgreSQL dblink非同步呼叫實現並行hash分片JOIN – 含資料交、並、差提速案例》

進入正題，下面是一個DEMO，按部就班的演示如何使用非同步呼叫實現多庫並行相似搜尋。

DEMO

1、我們這裡使用本地的4個DB來代表遠端資料庫，這4個DB完全可以安裝到遠端。這裡只是為了測試方便。

本地庫名：

postgres

遠端庫名：

db0  
db1  
db2  
db3

2、首先需要建立使用者和測試DB

create role test login encrypted password `secret`;  
create database db0 with owner test;  
create database db1 with owner test;  
create database db2 with owner test;  
create database db3 with owner test;

3、在本地某個庫中建立dblink外掛

create extension dblink;

4、建立連線遠端庫的SERVER

CREATE SERVER db0 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr `127.0.0.1`, dbname `db0`);  
CREATE SERVER db1 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr `127.0.0.1`, dbname `db1`);  
CREATE SERVER db2 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr `127.0.0.1`, dbname `db2`);  
CREATE SERVER db3 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr `127.0.0.1`, dbname `db3`);

5、配置連線遠端庫的使用者密碼（使用者密碼都是遠端庫的，可不是本地的哦，你想用本地使用者連遠端庫，沒門）

CREATE USER MAPPING FOR postgres SERVER db0 OPTIONS (user `test`, password `secret`);  
CREATE USER MAPPING FOR postgres SERVER db1 OPTIONS (user `test`, password `secret`);  
CREATE USER MAPPING FOR postgres SERVER db2 OPTIONS (user `test`, password `secret`);  
CREATE USER MAPPING FOR postgres SERVER db3 OPTIONS (user `test`, password `secret`);

遠端庫操作

在所有遠端庫上建立測試表，灌入測試資料，建立相似搜尋函式。（注意下面的指令碼需要調整好對應排程dbname）

1、必要的外掛（注意下面的指令碼需要調整好對應排程dbname）

c db3 postgres  
create extension pg_trgm;    
create extension dblink;

2、主表和相似搜尋依賴的索引（注意下面的指令碼需要調整好對應排程dbname）

c db3 test  
create unlogged table tbl(id int primary key, info text);    
create index idx_tbl_info on tbl using gin (info gin_trgm_ops);  
    
-- alter table tbl set (parallel_workers =64);

3、建立分割槽（本文僅做測試，真正的分割槽表用法請參考：《PostgreSQL 11 分割槽表用法及增強 – 增加HASH分割槽支援 (hash, range, list)》）

do language plpgsql $$    
declare    
begin    
  for i in 0..63    
  loop    
    execute format(`drop table if exists tbl%s `, i);    
    execute format(`create unlogged table tbl%s (like tbl including all) inherits(tbl)`, i);    
    -- 提前設定好表級並行度，方便後面做並行測試    
    -- execute format(`alter table tbl%s set (parallel_workers =64)`, i);    
  end loop;    
end;    
$$;

4、建立連線函式

create or replace function conn(      
  name,   -- dblink名字      
  text    -- 連線串,URL      
) returns void as $$        
declare        
begin        
  perform dblink_connect($1, $2);       
  return;        
exception when others then        
  return;        
end;        
$$ language plpgsql strict;

5、建立生成隨機函式的函式

-- 生成隨機漢字串      
create or replace function gen_hanzi(int) returns text as $$      
declare      
  res text;      
begin      
  if $1 >=1 then      
    select string_agg(chr(19968+(random()*20901)::int), ``) into res from generate_series(1,$1);      
    return res;      
  end if;      
  return null;      
end;      
$$ language plpgsql strict;

6、寫入測試資料，隨機文字（注意下面的指令碼需要調整好對應排程dbname）

do language plpgsql $$    
declare    
  dbname name := `db3`;  
begin    
  for i in 0..63    
  loop    
    perform conn(`link`||i,  `hostaddr=127.0.0.1 user=test password=secret dbname=`||dbname);     
    perform dblink_send_query(`link`||i, format(`insert into tbl%s select generate_series(1, 15625), gen_hanzi(64); analyze tbl%s;`, i, i));    
  end loop;    
end;    
$$;

7、建立相似搜尋用到的UDF

create or replace function get_res(  
  text,     -- 要按相似搜的文字  
  int8,     -- 限制返回多少條  
  float4 default 0.3,   -- 相似度閾值，低於這個值不再搜搜  
  float4 default 0.1    -- 相似度遞減步長，直至閾值  
) returns setof record as $$    
declare    
  lim float4 := 1;    
begin    
  -- 判定  
  if not ($3 <= 1 and $3 > 0) then   
    raise notice `$3 must >0 and <=1`;  
    return;  
  end if;  
    
  if not ($4 > 0 and $4 < 1) then  
    raise notice `$4 must >0 and <=1`;  
    return;  
  end if;  
  loop    
    -- 設定相似度閾值    
    perform set_limit(lim);    
        
    -- 檢視當前閾值下，有沒有相似記錄    
    perform similarity(info, $1) as sml, * from tbl where info % $1 limit 1;    
        
    -- 如果有，則返回N條    
    if found then    
      return query select similarity(info, $1) as sml, * from tbl where info % $1 order by sml desc limit $2;    
      return;    
    end if;    
    
    -- 否則繼續，降低閾值    
    -- 當閾值小於0.3時，不再降閾值搜尋，認為沒有相似。    
    if lim < $3 then    
      return;    
    else    
      lim := lim - $4;    
    end if;    
  end loop;    
end;    
$$ language plpgsql strict;

本地庫操作

建立建立遠端連線的函式

create or replace function conn(        
  name,   -- dblink名字        
  text    -- 連線串,URL        
) returns void as $$          
declare          
begin          
  perform dblink_connect($1, $2);         
  return;          
exception when others then          
  return;          
end;          
$$ language plpgsql strict;

返回遊標

1、定義UDF1 – 返回遊標（如果返回記錄數很多，建議使用遊標，因為PLPGSQL是需要等所有記錄都拿到才會開始返回，返回記錄的話RT會較高）

例子

create or replace function get_res_cursor(  
  text,     -- 要按相似搜的文字  
  int8,     -- 限制返回多少條  
  float4 default 0.3,   -- 相似度閾值，低於這個值不再搜搜  
  float4 default 0.1    -- 相似度遞減步長，直至閾值  
) returns setof refcursor as $$    
declare    
  i int := 1;  
  ref refcursor[];    
  res refcursor;   
  dbname name[] := array[`db0`, `db1`, `db2`, `db3`];  -- 定義叢集  
  db name;  
begin  
  foreach db in array dbname  
  loop   
    ref[i] := `link`||i;  
    res := ref[i];  
    perform conn(`link`||i,  db);         
    perform dblink_open(`link`||i, `link`||i, format(`select * from get_res(%L, %s, %s, %s) as t(sml real, id int, info text)`, $1, $2, $3, $4));    
    return next res;  
    i := i+1;  
  end loop;  
end;  
$$ language plpgsql strict;

使用例子

postgres=# begin;  
BEGIN  
Time: 0.045 ms  
postgres=# select * from get_res_cursor(`怮媕苸淏倍椡帪暀虻爴蕩巒讉輶魂馜慮範噞蠭鯀烳渃麠鈽趥剘偣瑴鑪颭蚢佚簀哌內霢擷槧緸褫齈跊甏軙襧漆疅泅睤帍槇驗縐櫺`, 10, 0.1, 0.05);  
 get_res_cursor   
----------------  
 link1  
 link2  
 link3  
 link4  
(4 rows)  
  
Time: 18.624 ms  
postgres=# select * from dblink_fetch(`link1`,`link1`,10) as t(sml real, id int, info text);  
 sml | id | info   
-----+----+------  
(0 rows)  
  
Time: 219.972 ms  
postgres=# select * from dblink_fetch(`link1`,`link1`,10) as t(sml real, id int, info text);  
 sml | id | info   
-----+----+------  
(0 rows)  
  
Time: 0.252 ms  
postgres=# select * from dblink_fetch(`link2`,`link2`,10) as t(sml real, id int, info text);  
 sml | id | info   
-----+----+------  
(0 rows)  
  
Time: 215.891 ms  
postgres=# select * from dblink_fetch(`link3`,`link3`,10) as t(sml real, id int, info text);  
 sml | id | info   
-----+----+------  
(0 rows)  
  
Time: 215.188 ms  
postgres=# select * from dblink_fetch(`link4`,`link4`,10) as t(sml real, id int, info text);  
   sml    | id |                                                               info                                                                 
----------+----+----------------------------------------------------------------------------------------------------------------------------------  
 0.779412 |  1 | 遞陊怮媕苸淏倍椡帪暀虻爴蕩巒讉輶魂馜慮範噞蠭鯀烳渃麠鈽趥剘偣瑴鑪颭蚢佚簀哌內霢擷槧緸褫齈跊甏軙襧漆疅泅睤帍槇驗縐櫺軑氐洚重銄懟諔  
(1 row)  
  
Time: 106.692 ms

返回記錄

1、定義UDF2 – 返回記錄(注意，建議限制返回的條數，因為PLPGSQL是需要等所有記錄都拿到才會開始返回)

例子

create or replace function get_res_record(  
  text,     -- 要按相似搜的文字  
  int8,     -- 限制返回多少條  
  float4 default 0.3,   -- 相似度閾值，低於這個值不再搜搜  
  float4 default 0.1    -- 相似度遞減步長，直至閾值  
) returns setof record as $$    
declare    
  i int;  
  ref refcursor[];    
  res refcursor;   
  dbname name[] := array[`db0`, `db1`, `db2`, `db3`];  -- 定義叢集  
  db name;  
begin  
  i := 1;  
  foreach db in array dbname  
  loop   
    perform conn(`link`||i,  db);     
    perform 1 from dblink_get_result(`link`||i) as t(sml real, id int, info text);       
    perform dblink_send_query(`link`||i, format(`select * from get_res(%L, %s, %s, %s) as t(sml real, id int, info text)`, $1, $2, $3, $4));    
    i := i+1;  
  end loop;  
  
  i := 1;  
  foreach db in array dbname  
  loop   
    return query SELECT * FROM dblink_get_result(`link`||i) as t(sml real, id int, info text);     
    i := i+1;  
  end loop;  
end;  
$$ language plpgsql strict;

使用例子

postgres=# select * from get_res_record(`怮媕苸淏倍椡帪暀虻爴蕩巒讉輶魂馜慮範噞蠭鯀烳渃麠鈽趥剘偣瑴鑪颭蚢佚簀哌內霢擷槧緸褫齈跊甏軙襧漆疅泅睤帍槇驗縐櫺`, 10, 0.77, 0.4) as (sml real, id int, info text);  
   sml    | id |                                                               info                                                                 
----------+----+----------------------------------------------------------------------------------------------------------------------------------  
 0.779412 |  1 | 遞陊怮媕苸淏倍椡帪暀虻爴蕩巒讉輶魂馜慮範噞蠭鯀烳渃麠鈽趥剘偣瑴鑪颭蚢佚簀哌內霢擷槧緸褫齈跊甏軙襧漆疅泅睤帍槇驗縐櫺軑氐洚重銄懟諔  
(1 row)  
  
Time: 32.329 ms