Как лучше хранить диапазоны дат в PostgreSQL

В некоторых случаях необходимо хранить в базе данных диапазон дат (например, брони номеров в гостинице, события в календаре). В последствии, обычно, требуется найти записи в базе, у которых диапазон дат пересекается с указанным. Например, вывести все события в календаре, которые пересекаются с сегодняшним днем.

Диапазон дат можно хранить 2 способами:

1. Хранить даты начала и окончания в двух колонках.
2. Хранить диапазон дат в одной колонке.

В данной статье мы сравним оба способа и определим при каком способе скорость поиска будет выше. В качестве примера будем хранить события, которые имеют даты начала и окончания. Наша цель – максимально быстро найти все события, которые пересекаются с указанным диапазоном, чтобы в дальнейшем их можно было показать в календаре (события на указанный день, события на эту неделю).

Как хранить диапазон дат в одной колонке

В PostgreSQL существуют диапазонные типы, при помощи которых можно хранить диапазоны значений определенного типа данных, который называется подтипом диапазона. Это может быть диапазон дат для хранения событий в календаре (подтип – timestamp), диапазон чисел для хранения диапазонов измерений приборов (подтип – numeric) и т.п. Также существуют многодиапазонные типы, в которых можно хранить сразу несколько диапазонов (напр, чтобы сохранить встречу, которая будет длиться с 11:00 до 12:00 и с 14:00 до 16:00).

Для хранения диапазонов дат используются следующие типы:

• tsrange – диапазон дат с подтипом timestamp with time zone. Многодиапазонный тип – tsmultirange.
• tstzrange – диапазон дат с подтипом timestamp with time zone. Многодиапазонный тип – tstzmultirange.
• daterange – диапазон дат с подтипом date. Многодиапазонный тип – datemultirange.

Диапазон задается следующим образом:

-- Встреча назначена на 1 января 2022 и будет длиться с 10:00 до 12:00
SELECT '[2022-01-01 10:00, 2022-01-01 12:00]'::tsrange;

Если требуется хранить сразу несколько диапазонов, то необходимо воспользоваться многодиапазонным типом:

-- Встреча назначена на 1 января 2022 и будет длиться с 11:00 до 12:00 и с 14:00 до 16:00
SELECT '{[2022-01-01 11:00, 2022-01-01 12:00], [2022-01-01 14:00, 2022-01-01 16:00]}'::tsmultirange;

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

-- 1 января 2022 с 8 до 9 утра, включая 8 утра и исключая 9 утра
SELECT '[2022-01-01 08:00, 2022-01-01 09:00)'::tsrange;

Можно указать только начало или окончание диапазона:

SELECT '(, 2022-01-01 10:00]'::tsrange; -- До 1 января 2022 10 утра
SELECT '[2022-01-01 10:00,)'::tsrange; -- После 1 января 2022 10 утра
SELECT '(,)'::tsrange; -- Диапазон, в который входят все даты

Подробнее про диапазонные типы в официальной документации.

Индексы, поддерживающие диапазоны дат

Для ускорения поиска по колонкам, которые имеют диапазонные типы, используются индексы GiST и SP-GiST. Многодиапазонные типы поддерживаются только GiST индексом. Подробнее про эти индексы можно почитать тут и тут.

CREATE TABLE events (during tsrange);
CREATE INDEX events_during_idx ON events USING gist (during);

Индексы GiST и SP-GiST поддерживают следующие операторы:

= – равны ли диапазоны.

SELECT '(2022-01-01, 2022-01-05]'::daterange = '(2022-01-01, 2022-01-05]'::daterange; -- true

&& – пересекаются ли диапазоны.

SELECT '[2022-01-01, 2022-01-05]'::daterange && '(2022-01-04, 2022-01-10]'::daterange; -- true

<@ – содержится ли первый диапазон во втором.

SELECT '[2022-01-02, 2022-01-04]'::daterange <@ '(2022-01-01, 2022-01-05)'::daterange; -- true

<@ – содержится ли элемент в диапазоне.

SELECT '2022-01-03'::date <@ '[2022-01-02, 2022-01-04]'::daterange; -- true

@> – содержится ли второй диапазон в первом.

SELECT '(2022-01-01, 2022-01-05)'::daterange @> '[2022-01-02, 2022-01-04]'::daterange; -- true

@> – содержится ли элемент в диапазоне.

SELECT '[2022-01-02, 2022-01-04]'::daterange @> '2022-01-03'::date; -- true

<< – располагается ли первый диапазон строго слева от второго.

SELECT '[2022-01-01, 2022-01-03]'::daterange << '[2022-01-05, 2022-01-08]'::daterange; -- true

>> – располагается ли первый диапазон строго справа от второго.

SELECT '[2022-01-05, 2022-01-08]'::daterange >> '[2022-01-01, 2022-01-03]'::daterange; -- true

-|- – примыкают ли диапазоны друг к другу.

SELECT '[2022-01-01, 2022-01-03]'::daterange -|- '[2022-01-04, 2022-01-06]'::daterange; -- true

&< – не простирается ли первый диапазон правее второго.

SELECT '[2022-01-01, 2022-01-10]'::daterange &< '[2022-01-05, 2022-01-15]'::daterange; -- true

&> – не простирается ли первый диапазон левее второго.

SELECT '[2022-01-05, 2022-01-15]'::daterange &> '[2022-01-01, 2022-01-10]'::daterange; -- true

Ограничения для диапазонов дат

Иногда необходимо сделать так, чтобы события в календаре не могли пересекаться друг с другом. Для этого необходимо задать ограничение исключения с использованием оператора && для проверки пересечения.

CREATE TABLE events (during tsrange, EXCLUDE USING gist (during WITH &&));
INSERT INTO events VALUES ('[2022-01-01 10:00, 2022-01-01 11:00)');
INSERT INTO events VALUES ('[2022-01-01 10:59, 2022-01-01 12:00)');

ERROR:  conflicting key value violates exclusion constraint "events_during_excl"
DETAIL:  Key (during)=(["2022-01-01 10:59:00+00","2022-01-01 12:00:00+00")) conflicts with existing key (during)=(["2022-01-01 10:00:00+00","2022-01-01 11:00:00+00")).

Скорее всего у каждого пользователя будет календарь со своими событиями. Например, в салоне красоты есть несколько мастеров и посетители могут записаться к ним. При этом 2 человека может записаться на одно и то же время к разным мастерам, но нельзя допустить, чтобы кто-то записался на время, в которое мастер будет занят другим клиентом.

Чтобы разрешить пересечение событий разных пользователей, необходимо в ограничение исключения добавить дополнительное сравнение на равенство ID пользователя. Для этого нам потребуется расширение btree_gist.

CREATE EXTENSION btree_gist;
CREATE TABLE events (user_id int, during tsrange, EXCLUDE USING gist (user_id WITH =, during WITH &&));
INSERT INTO events VALUES (1, '[2022-01-01 10:00, 2022-01-01 11:00)');
INSERT INTO events VALUES (2, '[2022-01-01 10:00, 2022-01-01 11:00)');
INSERT INTO events VALUES (2, '[2022-01-01 10:59, 2022-01-01 12:00)');

ERROR:  conflicting key value violates exclusion constraint "events_user_id_during_excl"
DETAIL:  Key (user_id, during)=(2, ["2022-01-01 10:59:00+00","2022-01-01 12:00:00+00")) conflicts with existing key (user_id, during)=(2, ["2022-01-01 10:00:00+00","2022-01-01 11:00:00+00")).

Сравнение индексов

Для поиска событий, которые пересекаются с указанным дизапазоном (например, чтобы показать события в календаре на сегодняшний день) мы можем воспользоваться одним из 3 вариантов:

1. Хранить дату начала и окончания. Использовать B-tree индекс для ускорения поиска.
2. Хранить диапазон дат. Использовать GiST индекс.
3. Хранить диапазон дат. Использовать SP-GiST индекс.

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

-- Создаем таблицы
CREATE TABLE events_two_dates (start_date timestamp, end_date timestamp);
CREATE TABLE events_range_gist (during tsrange);
CREATE TABLE events_range_spgist (during tsrange);

-- Генерируем диапазоны дат и заполняем первую таблицу
INSERT INTO events_two_dates SELECT i, i + INTERVAL '10 MINUTES' FROM (SELECT '2022-01-01'::timestamp + random() * INTERVAL '1 YEAR' as i FROM generate_series(1, 1000000)) AS t;

-- Копируем диапазоны дат из первой таблицы во вторую и третью
INSERT INTO events_range_gist SELECT tsrange(start_date, end_date) FROM events_two_dates;
INSERT INTO events_range_spgist SELECT tsrange(start_date, end_date) FROM events_two_dates;

-- Создаем индексы
CREATE INDEX events_two_dates_idx ON events_two_dates (start_date, end_date);
CREATE INDEX events_range_gist_idx ON events_range_gist USING gist (during);
CREATE INDEX events_range_spgist_idx ON events_range_spgist USING spgist (during);

При генерации диапазонов сперва генерируются начальные даты, которые могут принимать значения от 1 января 2022 года (включительно) до 1 января 2023 года (исключительно), а затем на их основе создаются диапазоны дат длительностью 10 минут.

Для тестирования запросов будем производить поиск событий, которые пересекаются с 5 января 2022 года с 8 до 9 утра. Таких событий 146.

EXPLAIN ANALYZE SELECT * FROM events_two_dates WHERE start_date <= '2022-01-05 09:00'::timestamp AND end_date >= '2022-01-05 08:00'::timestamp;

 Index Only Scan using events_two_dates_idx on events_two_dates  (cost=0.42..429.94 rows=11805 width=16) (actual time=7.253..8.629 rows=146 loops=1)
   Index Cond: ((start_date <= '2022-01-05 09:00:00+00'::timestamp with time zone) AND (end_date >= '2022-01-05 08:00:00+00'::timestamp with time zone))
   Heap Fetches: 0
 Planning Time: 7.594 ms
 Execution Time: 10.156 ms

Размер индекса events_two_dates_idx – 30 MB.

EXPLAIN ANALYZE SELECT * FROM events_range_gist WHERE during && '[2022-01-05 08:00, 2022-01-05 09:00]'::tsrange;

 Index Only Scan using events_range_gist_idx on events_range_gist  (cost=0.29..10.89 rows=149 width=22) (actual time=0.894..2.350 rows=146 loops=1)
   Index Cond: (during && '["2022-01-05 08:00:00+00","2022-01-05 09:00:00+00"]'::tsrange)
   Heap Fetches: 0
 Planning Time: 8.629 ms
 Execution Time: 3.604 ms

Размер индекса events_range_gist_idx – 58 MB.

EXPLAIN ANALYZE SELECT * FROM events_range_spgist WHERE during && '[2022-01-05 08:00, 2022-01-05 09:00]'::tsrange;

 Index Only Scan using events_range_spgist_idx on events_range_spgist  (cost=0.29..10.77 rows=142 width=22) (actual time=0.944..2.364 rows=146 loops=1)
   Index Cond: (during && '["2022-01-05 08:00:00+00","2022-01-05 09:00:00+00"]'::tsrange)
   Heap Fetches: 0
 Planning Time: 5.096 ms
 Execution Time: 3.670 ms

Размер индекса events_range_spgist_idx – 61 MB.

Выводы

Поиск с использованием диапазонных типов выполняется примерно в 3 раза быстрее по сравнению с использованием стандартного подхода в виде 2 дат (даты начала и окончания). Однако GiST и SP-GiST индексы, которые используются для поиска по диапазонным типам занимают в 2 раза больше места на диске.

Используя многодиапазонные типы (поддерживается только GiST индексом), в одной колонке можно хранить сразу несколько диапазонов. Например, чтобы сохранить встречу, которая будет длиться с 11:00 до 12:00 и с 14:00 до 16:00.

В итоге для хранения диапазонов дат лучше использовать диапазонные типы и использовать GiST индекс для ускорения поиска, если место на диске не так важно.

Шпаргалка

-- Храним диапазон дат в одной колонке
CREATE TABLE events (during tsrange);

-- Создаем GiST индекс для ускорения поиска
CREATE INDEX events_during_idx ON events USING gist (during);

-- Используем оператор && для поиска диапазонов, которые пересекаются с указанным
SELECT * FROM events WHERE during && '[2022-01-05 08:00, 2022-01-05 09:00]'::tsrange;