Zapraszam na małe zajęcia praktyczno-techniczne. Dziś przedstawię przepis na liczniki w PostgreSQL'u, które mogą nam zaoszczędzić drogich count'ów na tabelach. Rozwiązanie jest wprawdzie opisane na konkretną bazę, ale powinno być do zaimplementowania na każdą bazę, która obsługuje triggery. Poziom trudności w okolicach średnio-zaawansowanego.
Szkic sytuacyjny: mamy aplikację forumopodobną, a w bazie aplikacji użytkowników, którzy robią różne rzeczy: wysyłają wiadomości do innych użytkowników, tworzą wątki na forum, odpowiadają na nie, robią całą masę innych rzeczy. Każdą czynność powinniśmy policzyć, może nie tylko jednostkowo, ale też powartościować każdą czynność według specyfikacyjnego widzimisię (np. bardziej się liczy założenie wątku niż napisanie posta w utworzonym wątku). Żeby utrudnić zadanie, powinniśmy też liczyć taką aktywność w pewnych odstępach czasu, np. od niedzieli do niedzieli - żeby na końcu wyszło coś takiego: użytkownik Kaziu od początku tygodnia napisał 10 postów na forum i wysłał 5 wiadomości. Dodatkowo utrudnieniem może być to, że w zależności od czynności, dane zbieramy z inną granulacją - niektóre liczymy co 24 h, inne co tydzień, inne co miesiąc.
W pierwszej chwili oczywiście na myśl przychodzi count na tabelach z postami i z wiadomościami, z warunkiem where. Rozwiązanie jest relatywnie szybkie, kiedy w systemie mamy kilku użytkowników, ale kiedy jest ich kilkuset, a każdy codziennie coś 'skrobnie', to robi się smutno....
Zamiast tego, można pójść inną ścieżką: tabele z licznikami. Potrzebne są nam 3 tabele: definicje liczników, dane grupowane dla wszystkich przedziałów czasowych i ostatnie wartości.
Tabela definicji
create table counters.counters (
cnt_id serial primary key,
cnt_name varchar(32),
cnt_created timestamptz not null default current_timestamp,
cnt_start_date timestamptz not null default current_timestamp,
cnt_interval interval not null default interval '1 day'
);
W tabeli definiujemy trzy elementy:
- unikalną nazwę (jakoś te liczniki musimy rozróżniać)
- datę startową, od której będziemy liczyć
- przedział czasowy, w ramach którego będziemy agregować dane
Tabela grupująca
create table counters.counters_grouped (
ctg_id serial primary key,
ctg_cnt_id int not null references counters.counters (cnt_id),
ctg_cnt_name varchar(32),
ctg_created timestamptz not null default current_timestamp,
ctg_modified timestamptz not null default current_timestamp,
ctg_value float not null default 0,
ctg_last_change float not null default 0,
ctg_usr_id int not null,
ctg_changed int not null default 1,
ctg_timestamp timestamptz not null default current_timestamp
);
Tabela grupująca jest 'sercem' liczników - przechowuje wartości dla wyliczanych okresów. Do tabeli doczepione są dwa triggery:
- before insert, który określa początek interwału, faktycznie robi update istniejących danych lub wykonuje insert, jeśli dla danego przedziału i parametrów nie było wcześniej danych
- after insert or update, który propaguje ostatnią zmianę do tabeli z ostatnimi wartościami.
W naszym przykładzie posłużymy się tylko polem ctg_usr_id, aby zwiększyć rozdzielczość danych. Jeśli jest potrzeba większej granulacji, trzeba zmodyfikować strukturę i triggery.
Tabela ostatnich wartości jest tabelą pomocniczą - służy tylko po to, aby łatwo wyciągnąć ostatnie wartości liczników. Struktura jest niemal identyczna, jak tabeli grupującej. Do tabeli jest dołączony trigger before insert, który usuwa poprzednie rekordy dla parametrów licznika i wstawia aktualne dane.
Jak używać liczników?
Po zaciągnięciu schematu, tworzymy nową definicję licznika, który będzie grupowany co 10 sekund:
insert into counters.counters (cnt_name, cnt_start_date, cnt_interval) values ('test', current_timestamp, interval '10 seconds');
Nowe dane robimy insertem z id licznika lub jego nazwą, ostatnią zmianą i parametrem:
insert into counters.counters_grouped (ctg_cnt_name, ctg_last_change, ctg_usr_id) values ('test', 1, 1);
insert into counters.counters_grouped (ctg_cnt_id, ctg_last_change, ctg_usr_id) values (1, 3, 1);
insert into counters.counters_grouped (ctg_cnt_name, ctg_last_change, ctg_usr_id) values ('test', 1, 2);
insert into counters.counters_grouped (ctg_cnt_id, ctg_last_change, ctg_usr_id) values (1, -1, 2);
Sprawdzamy, co jest w tabelach:
baza=# select ctg_cnt_id, ctg_value, ctg_last_change, ctg_usr_id, ctg_timestamp from counters.counters_grouped order by ctg_timestamp, ctg_usr_id;
ctg_cnt_id | ctg_value | ctg_last_change | ctg_usr_id | ctg_timestamp
------------+-----------+-----------------+------------+-------------------------------
1 | 4 | 3 | 1 | 2008-02-12 21:21:39.023769+01
1 | 0 | -1 | 2 | 2008-02-12 21:21:39.023769+01
(2 rows)
baza=# select ctl_cnt_id, ctl_value, ctl_last_change, ctl_usr_id, ctl_timestamp from counters.counters_last_value order by ctl_timestamp, ctl_usr_id;
ctl_cnt_id | ctl_value | ctl_last_change | ctl_usr_id | ctl_timestamp
------------+-----------+-----------------+------------+-------------------------------
1 | 4 | 3 | 1 | 2008-02-12 21:21:39.023769+01
1 | 0 | -1 | 2 | 2008-02-12 21:21:39.023769+01
(2 rows)
baza=#
Po pg_sleep() robimy kolejne inserty:
insert into counters.counters_grouped (ctg_cnt_name, ctg_last_change, ctg_usr_id) values ('test', 1, 1);
insert into counters.counters_grouped (ctg_cnt_id, ctg_last_change, ctg_usr_id) values (1, 3, 1);
insert into counters.counters_grouped (ctg_cnt_name, ctg_last_change, ctg_usr_id) values ('test', 1, 2);
insert into counters.counters_grouped (ctg_cnt_id, ctg_last_change, ctg_usr_id) values (1, -1, 2);
, a potem patrzymy w tabele:
baza=# select ctg_cnt_id, ctg_value, ctg_last_change, ctg_usr_id, ctg_timestamp
from counters.counters_grouped order by ctg_timestamp, ctg_usr_id;
ctg_cnt_id | ctg_value | ctg_last_change | ctg_usr_id | ctg_timestamp
------------+-----------+-----------------+------------+-------------------------------
1 | 4 | 3 | 1 | 2008-02-12 21:21:39.023769+01
1 | 0 | -1 | 2 | 2008-02-12 21:21:39.023769+01
1 | 4 | 3 | 1 | 2008-02-12 21:22:49.023769+01
1 | 0 | -1 | 2 | 2008-02-12 21:22:49.023769+01
(4 rows)
baza=# select ctl_cnt_id, ctl_value, ctl_last_change, ctl_usr_id, ctl_timestamp
from counters.counters_last_value order by ctl_timestamp, ctl_usr_id;
ctl_cnt_id | ctl_value | ctl_last_change | ctl_usr_id | ctl_timestamp
------------+-----------+-----------------+------------+-------------------------------
1 | 4 | 3 | 1 | 2008-02-12 21:22:49.023769+01
1 | 0 | -1 | 2 | 2008-02-12 21:22:49.023769+01
(2 rows)
baza=#
Pełen schemat (tabele, triggery) są w załączniku.
