Практики на PostgreSQL — Приложение на търсене в графики в общности за съдържание

От Digoal

Заден план

По принцип може да се наложи уебсайт на общността на съдържание да записва такива данни: статии, потребители и тагове.

Връзката между трите също трябва да бъде записана, включително че етикетът принадлежи на статията, потребителят е прочел статията, потребителят е добавил статията към любими, потребителят е последвал потребител и потребителят е автор на статия.

Крайната цел е да се постигнат прозрачни запитвания. Например: Какви статии четат и тези, които са прочели тази статия? Кой може да има подобни интереси като мен?

Въпреки това, в уебсайт на общността обикновено има десетки милиони статии и близо 10 милиона потребители.

Как може да се изпълни това изискване?

Всъщност това изискване може лесно да се приложи с масиви и smlar в PostgreSQL. Ще започнем дизайна и стрес теста по-долу.

Масивите се използват за съхраняване на релации напред и назад, етикети и т.н.

Smlar се използва за запитване към подобни масиви (намиране на потребители с подобни интереси).

Дизайн

Метаданни

1. Потребителска таблица

create table users(  
  uid int primary key,             -- user ID  
  info text,           -- additional information  
  crt_time timestamp   -- time  
);

2. Таблица с етикети

create table tags(  
  tagid int primary key,              -- tag ID  
  info text,              -- additional information   
  crt_time timestamp      -- time  
);

3. Таблица на артикулите

create table arts(  
  artid int primary key,      -- article ID  
  info text,      -- additional information and content  
  uids int[],     -- user IDs (the author may be multiple users, so an array is used)  
  tags int[]      -- tag  
);

Релационни данни

1. Връзка напред

1.1. Кой е чел статията?

create table art_uids_view (  
  artid int primary key,  
  uids int[]  
);

1.2. Кой е добавил статията към Любими?

create table art_uids_like (  
  artid int primary key,  
  uids int[]  
);

2. Обратна връзка

2.1. Какви статии е чел потребителят? И какви тагове са включени в тези статии?

create table uid_arts_view (  
  uid int primary key,  
  arts int[],  
  tags int[]  
);

2.2. Какви статии е добавил потребителят към любими? И какви тагове са включени в тези статии?

create table uid_arts_like (  
  uid int primary key,  
  arts int[],  
  tags int[]  
);

Запитване

1. Други статии, които други потребители, които са чели тази статия, четат (с изключение на настоящата статия и статиите, които съм чел).

Логиката е следната, написана като UDF:

create extension intarray ;  -- create the intarry plugin to simplify the addition and subtraction of arrays.  
  
select (uids - $current_uid) into v1 from art_uids_view where artid = $current_artid ;  -- retrieve all users who have read the current article (except the current user)  
  
select (unnest(arts) as arts, count(*) as cnt) into v2 from uid_arts_view where uid = any (v1) group by 1 ;  -- obtain other articles that the users, who have read the same article, have also read  
  
select arts into v3 from uid_arts_view where uid= $current_uid ;          -- articles that the current user has read  
  
result = v2.arts - v3 ;   -- in all articles read by other users, the articles read by the current user are removed, and the articles read by other users are obtained.  These articles are sorted by number of overlaps from large to small and recommended to the user

СДС са както следва. Индексите могат да се използват за всички, а заявките за точки се извършват след агрегиране. Изпълнението е много добро:

create or replace function rec_arts_view(  
  i1 int,  -- article ID  
  i2 int,  -- current user ID  
  i3 int   -- limit  
) returns setof int as 
$$
  
declare  
  res int[];  -- result  
  v1 int[];   --  users who have read the article  
begin  
    
  -- users who have read the article  
  select (uids - i2) into v1 from art_uids_view where artid = i1 ;  
    
  -- From the articles that other users, who have read this article, have also read, exclude the articles that the current user has read, then sort them by repetition rate, and finally return N articles.  
  -- If other users who have read the article have read many other articles, sorting may take a little time.
  return query  
  select t1.arts from   
  (  
    select unnest(arts) arts, count(*) cnt from uid_arts_view where uid = any (v1) group by 1  
  ) t1  
  left join  
  (  
    select unnest(arts) arts, 1 cnt from uid_arts_view where uid= i2   
  ) t2  
  on (t1.arts=t2.arts)   
  where t2.* is null  
  order by t1.cnt desc  
  limit i3;  
  
end;  
$$
 language plpgsql strict;

2. За да се определи кои потребители споделят моите интереси по отношение на прочетените статии, индексът GIN се представя много добре.

create extension smlar;  
  
set smlar.type='overlap';        
set smlar.threshold=? ;             -- set the overlap threshold  
    
select arts into v1 from uid_arts_view where uid = ? ; -- articles I have read    
  
select      
    *,      
    smlar( arts, v1, 'N.i' )       -- number of overlaps between articles that other users have read and articles that I have read  
  from      
    uid_arts_view      
  where      
    arts % v1                      -- where cosine similarity >= smlar.threshold      
;

3. Потребители, които споделят моите интереси по отношение на таговете в четат статии

Конкретното съдържание е подобно на това на 2, което е пропуснато тук.

4. Потребители, които споделят моите интереси по отношение на любими статии

Конкретното съдържание е подобно на това на 2, което е пропуснато тук.

5. Потребители, които споделят моите интереси по отношение на таговете в избрани статии

Конкретното съдържание е подобно на това на 2, което е пропуснато тук.

UDF за генериране на права и обратна връзка

Използвайте UDF, за да намалите взаимодействията и да завършите следните типове бизнес логически операции. UDF могат да бъдат компилирани с помощта на plpgsql, което е много просто. Конкретното съдържание е представено в тази статия: https://www.postgresql.org/docs/10/static/plpgsql.html

1. Когато се създават нови статии, етикетите се генерират автоматично и таблицата с етикети се актуализира или добавя.

insert into tags values ();  
  
insert into arts values ();

2. При четене на статията, пряко-обратната връзка се променя.

Информацията за етикетите на статията е получена от чл

insert into art_uids_view values ();  
  
insert into uid_arts_view values ();

3. При предпочитане на статията се променя връзката напред-обратно.

Информацията за етикетите на статията е получена от чл

insert into art_uids_like values ();  
  
insert into uid_arts_like values ();

Добавяне на индекс

-- smlar similarity query  
create index idx_gin_1 on art_uids_view using gin ( uids _int4_sml_ops );    
create index idx_gin_2 on art_uids_like using gin ( uids _int4_sml_ops );    
  
create index idx_gin_3 on uid_arts_view using gin ( arts _int4_sml_ops );    
create index idx_gin_4 on uid_arts_view using gin ( tags _int4_sml_ops );    
  
create index idx_gin_5 on uid_arts_like using gin ( arts _int4_sml_ops );    
create index idx_gin_6 on uid_arts_like using gin ( tags _int4_sml_ops );    
  
create index idx_gin_7 on art_uids_view using gin ( uids _int4_sml_ops );    
create index idx_gin_8 on art_uids_like using gin ( uids _int4_sml_ops );

Индекс по избор

-- array intersection, and inclusion query  
create index idx_gin_01 on art_uids_view using gin ( uids gin__int_ops );    
create index idx_gin_02 on art_uids_like using gin ( uids gin__int_ops );    
  
create index idx_gin_03 on uid_arts_view using gin ( arts gin__int_ops );    
create index idx_gin_04 on uid_arts_view using gin ( tags gin__int_ops );    
  
create index idx_gin_05 on uid_arts_like using gin ( arts gin__int_ops );    
create index idx_gin_06 on uid_arts_like using gin ( tags gin__int_ops );    
  
create index idx_gin_07 on art_uids_view using gin ( uids gin__int_ops );    
create index idx_gin_08 on art_uids_like using gin ( uids gin__int_ops );

Попълнете тестовите данни

1. Генерирайте 10 милиона потребители

insert into users select id, md5(id::text), now() from generate_series(1,10000000) t(id);

2. Генерирайте 100 000 тагове

insert into tags select id, md5(id::text), now() from generate_series(1,100000) t(id);

3. Генерирайте 50 милиона статии

create or replace function gen_arr(int,int) returns int[] as 
$$
  
  select array(select ceil(random()*$1) from generate_series(1,$2))::int[];  
$$
 language sql strict;  
insert into arts select id, md5(id::text),   
  gen_arr(10000000 ,3),    
  gen_arr(100000 ,10)   
from generate_series(1,50000000) t(id);

4. Създайте връзка напред, като всяка статия се чете от 500 потребители и се предпочита средно от 50 потребители.

insert into art_uids_view select id, gen_arr(10000000, 500) from generate_series(1,50000000) t(id);  
  
insert into art_uids_like select id, gen_arr(10000000, 50) from generate_series(1,50000000) t(id);

5. Генерирайте обратна връзка (на теория обратната връзка и правата връзка трябва да съответстват едно към едно. За удобство на тестването няма да изпълня тази операция тук. Резултатът от теста е същият.)

Всеки потребител чете средно 1000 статии, включващи 500 тагове. 100 статии се добавят към Любими, включващи 50 тагове.

insert into uid_arts_view select id, gen_arr(50000000, 1000), gen_arr(100000, 500) from generate_series(1,10000000) t(id);  
  
insert into uid_arts_like select id, gen_arr(50000000, 100), gen_arr(100000, 50) from generate_series(1,10000000) t(id);

Тестове за ефективност

select rec_arts_view(1,2,10);    -- the article ID is 1, the current user ID is 2, and 10 recommended articles are returned to the current user.

Други потребители са прочели около 500 000 други статии. Получаването и сортирането на този резултат отнема 200 милисекунди.

postgres=# select count(*) from rec_arts_view(1,4,1000000);
 count  
--------
 497524
(1 row)
Time: 565.524 ms
postgres=# select count(*) from rec_arts_view(1,4,10);
 count 
-------
    10
(1 row)
Time: 198.368 ms

2. Потребители, които споделят моите интереси по отношение на прочетените статии

set smlar.type='overlap';        
set smlar.threshold=10;             -- set the overlap threshold  
    
select arts into v1 from uid_arts_view where uid = 1;       -- articles I have read  
  
select      
    *,      
    smlar( arts, v1, 'N.i' )       -- number of overlaps between articles that other users have read and articles that I have read  
  from      
    uid_arts_view      
  where      
    arts % v1                      -- where cosine similarity >= smlar.threshold      
;

Време: 2,4 милисекунди.

Bitmap Heap Scan on public.uid_arts_view  (cost=933.50.. 29296.93 rows=10000 width=72) (actual time=1.955.. 2.351 rows=2 loops=1)
   Output: uid, arts, tags, smlar(arts, '{25213311,49886221,45108456,27929256,20760231,35023889,17507871,43947072,48578113,41561690,39557908,26852171,29310889,5029778,24892381,12174141,9191797,41397570,25795440,27806324,28635176}'::integer[], 'N.i'::text)
   Recheck Cond: (uid_arts_view.arts % '{25213311,49886221,45108456,27929256,20760231,35023889,17507871,43947072,48578113,41561690,39557908,26852171,29310889,5029778,24892381,12174141,9191797,41397570,25795440,27806324,28635176}'::integer[])
   Heap Blocks: exact=2
   Buffers: shared hit=107
   ->  Bitmap Index Scan on idx_gin_3  (cost=0.00.. 931.00 rows=10000 width=0) (actual time=1.506.. 1.506 rows=2 loops=1)
         Index Cond: (uid_arts_view.arts % '{25213311,49886221,45108456,27929256,20760231,35023889,17507871,43947072,48578113,41561690,39557908,26852171,29310889,5029778,24892381,12174141,9191797,41397570,25795440,27806324,28635176}'::integer[])
         Buffers: shared hit=85
 Planning time: 0.110 ms
 Execution time: 2.378 ms
(10 rows)

Предварително изчисление и оптимизиране на производителността

Горното съдържание (препоръка на статии и намиране на потребители с подобни интереси) се отнася до изпълнението на заявка в реално време, но всъщност тези операции могат да бъдат предварително изчислени (тъй като увеличаването на статиите не е твърде голямо и потребителите, които ги четат статиите не се променят твърде много). Например, статиите се опресняват веднъж на ден, така че когато потребители с подобни интереси и подобни статии се препоръчват на потребителя, ако се извърши предварително изчисление, резултатите трябва само да бъдат директно запитвани и производителността се подобрява до 0,0N милисекунди - ниво на реакция. За нови статии, които не са били предварително изчислени, могат да се извършват заявки в реално време (и съответна актуализация на таблицата за предварително изчисление), която също може да отговори за милисекунди.

Предварителното изчисление може да работи и в друг режим. Когато някой попита за тази статия, можете да решите дали да направите повторно запитване и да актуализирате таблицата според последното предварително изчислено време. (Тоест режимът на изчисления в реално време + кеш + изчакване на кеша.)

Логиката е следната:

select xxx from pre_view_tbl where xxx=xxx;  -- query the cache, and return
-- write or update the cache
if not found then
  -- write synchronously
  insert into pre_view_tbl select xxxx returning *; -- compute in real time, and return
else if mod_time < (now() - timeout threshold) then
  -- asynchronously
  delete from pre_view_tbl where xxx=xxx;
  insert into pre_view_tbl select xxxx; -- compute in real time
end if;

Резюме

Тридесет процента развитие и седемдесет процента експлоатация. Уебсайтовете със съдържание са подобни на социалния софтуер и работата е приоритет. Ключовата връзка в операцията е „кръгът“, който може да набере популярност. Образуването на кръг често зависи от препоръките, а източникът на препоръки е поведението. Съдържанието и потребителите, които да се препоръчват на целта, зависят от поведението. Това е принципът, според който птичките се събират заедно.

С PostgreSQL масиви и smlar е много лесно да се приложат ефективни класификационни заявки и препоръки.

1. Масивите се използват за съхраняване на релации напред и назад, етикети и т.н.

2. Smlar се използва за запитване към подобни масиви (за намиране на потребители с подобни интереси).

Той е много удобен и ефикасен в сценарии за социална работа и работа със съдържание.

Те могат да се използват и за лесно препоръчване на горещи потребители и горещи статии, което е тествано в други случаи. Вижте края на тази статия за подробности.