Блог

Я работаю над системой, которая должна иметь возможность находить «состояние» элемента в определенный момент истории. Состояние является двоичным (либо включено, либо выключено). В этом случае необходимо определить, куда направлять (в конкретное «ключевое пространство») фрагмент данных с временной меткой, определяемый временной меткой данных. Мне трудно решить, каков наилучший способ моделирования данных.

Метод 1 заключается в использовании tstzrange с состоянием, подразумеваемым границами диапазона:

 create extension btree_gist;
create table core.range_director (
    range tstzrange,
    directee_id text,
    keyspace text,
    -- allow a directee to be directed to multiple keyspaces at once
    exclude using gist (directee_id with =, keyspace with =, range with amp;amp;)
);

insert into core.range_director values
    ('[2021-01-15 00:00:00 -0:00,2021-01-20 00:00:00 -0:00)', 'THING_ID', 'KEYSPACE_1'),
    ('[2021-01-15 00:00:00 -0:00,)', 'THING_ID', 'KEYSPACE_2');

select keyspace from core.range_director
    where directee_id = 'THING_ID' and range_director.range @> '2021-01-15'::timestamptz;
-- returns KEYSPACE_1 and KEYSPACE_2
select keyspace from core.range_director
    where directee_id = 'THING_ID' and range_director.range @> '2021-01-21'::timestamptz;
-- returns KEYSPACE_2
 

Метод 2 заключается в том, чтобы иметь явные изменения состояния:

 create table core.status_director (
    status_time timestamptz,
    status text,
    directee_id text,
    keyspace text
); -- not sure what pk to use for this method

insert into core.status_director values
    ('2021-01-15 00:00:00 -0:00','Open','THING_ID','KEYSPACE_1'),
    ('2021-01-20 00:00:00 -0:00','Closed','THING_ID','KEYSPACE_1'),
    ('2021-01-15 00:00:00 -0:00','Open','THING_ID','KEYSPACE_2');

select distinct on(keyspace) keyspace, status from core.status_director
    where directee_id = 'THING_ID'
    and status_time < '2021-01-16'
    order by keyspace, status_time desc;
-- returns KEYSPACE_1:Open KEYSPACE_2:Open

select distinct on(keyspace) keyspace, status from core.status_director
    where directee_id = 'THING_ID'
    and status_time < '2021-01-21'
    order by keyspace, status_time desc;
-- returns KEYSPACE_1:Closed, KEYSPACE_2:Open
-- so, client code has to ensure that it only directs to status=Open keyspaces
 

Возможно, есть и другие методы, которые также сработали бы, но эти два кажутся мне наиболее разумными. Преимуществом первого метода является действительно простой запрос, но недостатком является то, что теперь вам нужно обновлять строки, чтобы закрыть состояние, тогда как во втором методе вы можете просто публиковать новые состояния, что кажется проще.

Таблица может вырасти до тысяч или десятков тысяч строк, но, вероятно, не вырастет до миллионов (но меняется ли наилучший метод в зависимости от ожидаемого количества строк?). У меня есть пара похожих таблиц с одинаковыми запросами «состояния» на определенный момент времени, поэтому очень важно, чтобы я правильно подобрал для них модель.

Мой инстинкт заключается в том, чтобы использовать метод 1, но есть ли какие-либо рекомендации или соображения производительности, о которых я не думаю, которые побудили бы использовать вариант использования метода 2 (или другого метода, который я не рассматривал?)

Никаких пехотинцев с методом 1, просто большие огромные пушки. С помощью этого метода, как вы определяете текущее состояние. Вам нужно сканировать каждое изменение статуса и для каждого из них переключать статус или, возможно, использовать что-то вроде «count (*)% 2» нечетное значение дает одно состояние даже другому. Что произойдет, если какая-либо строка будет удалена или данные удалены, и вы не знаете, сколько было транзакций состояния. С помощью метода 2 вы извлекаете наибольшую дату и напрямую получаете статус.
Для себя я бы выбрал метод 3. Это Метод1 Метод 2. Да, у меня был бы диапазон дат статуса и само значение статуса. Это дает мне сложный исторический анализ, поскольку у меня есть полная история, а также прямой доступ к текущему статусу в любое время.

Итак, после проведения ряда исследований по этой теме я обнаружил, что мой случай является разновидностью «Таблицы состояний действительного времени». См. Главы 2 и 5 Ричарда Снодграсса «Разработка приложений баз данных, ориентированных на время, в SQL«.

Поддержка этих таблиц невелика, но и не ужасна (по крайней мере, в PostgreSQL есть tstzranges для работы). Метода 1 моего поста в значительной степени достаточно — основная проблема связана с таблицей состояний и другими таблицами.

Поскольку PostgreSQL не имеет встроенной поддержки для таких временных таблиц, вам необходимо самостоятельно создать ссылочную целостность. Существует множество способов сделать это, но для тех, кто в будущем будет искать какое-то направление, вот пример того, как это может выглядеть для ссылочного запроса к двум временным таблицам:

 create table a (
    row_id bigserial, -- to track individual rows
    id int,
    pov tstzrange, -- period of validity
    pop tstzrange -- period of presence
);

create table b (
    row_id bigserial,
    id int,
    pov tstzrange,
    pop tstzrange,
    a_id int
);

-- are we good?
with each_pov as (
    select bool_or(a.pov @> b.pov) as ok
    from a
    join b on a.id = b.a_id
        and upper(a.pop) is null
        and upper(b.pop) is null
    group by b.pov
) select coalesce(
    bool_and(each_pov.ok),
    (select count(*) = 0 from b where upper(pop) is null)
) from each_pov;
 

Вы можете поместить запрос в триггер ограничения как для основной таблицы, так и для таблицы, на которую ссылается ссылка, чтобы получить что-то, приближающееся к последовательности ссылочной целостности для текущего периода присутствия.