Temporal-таблицы: как хранить состояние данных на любой момент

Прямой ответ

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

Каждая версия строки имеет границы действия:

CREATE TABLE product_prices (
    product_id  integer      NOT NULL,
    price       numeric(12,2) NOT NULL,
    valid_from  timestamptz  NOT NULL,
    valid_to    timestamptz  NOT NULL,
    PRIMARY KEY (product_id, valid_from WITHOUT OVERLAPS)
);

Конструкция WITHOUT OVERLAPS, появившаяся в PostgreSQL 17, гарантирует на уровне базы, что периоды действия одной сущности не пересекаются. Без неё непересекаемость придётся проверять в приложении, и ошибка проявится как два одновременных значения.

Запрос состояния на момент времени:

SELECT product_id, price
  FROM product_prices
 WHERE product_id = 42
   AND valid_from <= '2026-03-01+03'
   AND valid_to    > '2026-03-01+03';

Обновление выполняется закрытием текущей версии и вставкой новой:

BEGIN;

UPDATE product_prices
   SET valid_to = now()
 WHERE product_id = 42
   AND valid_to = 'infinity';

INSERT INTO product_prices (product_id, price, valid_from, valid_to)
VALUES (42, 1499.00, now(), 'infinity');

COMMIT;

Обе операции в одной транзакции: разрыв в истории невозможен.

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

CREATE OR REPLACE FUNCTION archive_price() RETURNS trigger AS $$
BEGIN
    IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
        INSERT INTO product_prices_history
        VALUES (OLD.*);
    END IF;
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_archive_price
BEFORE UPDATE OR DELETE ON product_prices
FOR EACH ROW EXECUTE FUNCTION archive_price();

Практика: выбор подхода и его границы

1. Различайте temporal-данные и журнал аудита. Temporal-таблица отвечает на вопрос «каким было значение». Журнал аудита отвечает на вопрос «кто и когда что изменил». Это разные задачи, и одна не заменяет другую.

2. Temporal-таблица не заменяет event sourcing. Хранение версий строк описывает состояния, а не события. Если нужна последовательность действий с причиной, это отдельная модель; о подходе — в статье про event sourcing.

3. Выбирайте границу периода осознанно. Вариант с открытым концом (infinity) проще для запросов «актуальное значение»; явная дата окончания удобнее, если известны будущие изменения, например плановое повышение цены.

4. Учитывайте рост объёма. Каждая версия занимает место. Для часто меняющихся строк история растёт быстро, поэтому нужен срок хранения и очистка; о секционировании больших таблиц — в статье про партиционирование.

5. Не смешивайте время действия и время записи. «Цена действовала с первого марта» и «запись внесена пятого марта» — разные понятия. Хранение обоих периодов нужно, если данные вводятся задним числом.

6. Проверяйте запросы на индекс. Условие по диапазону дат требует индекса на (product_id, valid_from); без него каждый запрос читает всю историю.

7. Согласуйте с аналитикой. В хранилищах тот же паттерн известен как медленно меняющееся измерение второго типа; о нём — в статье про SCD.

Рекомендации:

1. Начинайте с истории на уровне приложения. Триггер и архивная таблица проще в поддержке, чем полная темпоральная модель, и покрывают большинство задач.
2. Фиксируйте момент времени в связанных записях. Если счёт выставлен по определённой цене, её нужно сохранить в счёте, а не вычислять из истории.
3. Не используйте историю как основной источник. Актуальное значение читается из основной таблицы; история служит для разбора и отчётности.
4. Тестируйте корректность периодов. Тест, который меняет значение дважды и проверяет отсутствие пересечений, выявляет ошибки в логике закрытия версий.
5. Сравнивайте с CDC. Если история нужна только для синхронизации с другой системой, достаточно журнала изменений; о механизме — в статье про CDC.

Практический сценарий: перерасчёт заказов требовал ручного разбора

Контекст: при изменении тарифов требовалось пересчитать стоимость старых заказов. Актуальные тарифы хранились в одной строке, поэтому прежние значения восстановить было нельзя.

1. Диагностика. Для каждого перерасчёта разработчик искал прежнее значение тарифа в переписке и в коде, затем вносил его вручную. Процедура занимала часы и была подвержена ошибкам.
2. Проверка требований. Бизнесу нужно было состояние тарифа на дату заказа, то есть классический temporal-запрос.
3. Действия. (а) Добавили период действия к таблице тарифов с ограничением WITHOUT OVERLAPS; (б) обновление тарифа закрыло текущую версию и создало новую в одной транзакции; (в) в заказ добавили ссылку на версию тарифа, чтобы расчёт не зависел от даты запроса; (г) добавили индекс на (tariff_id, valid_from), поскольку запрос по диапазону читал всю таблицу; (д) настроили секционирование истории по годам и удаление версий старше пяти лет; (е) добавили тест на отсутствие пересечений периодов.
4. Результат. Перерасчёт выполняется одним запросом к истории; ручной разбор не требуется, а расхождение между расчётом и выставленным счётом стало проверяемым.

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

FAQ

Поддерживает ли PostgreSQL темпоральные таблицы из SQL:2011 полностью? Начиная с версии 17 поддерживаются первичные и внешние ключи с WITHOUT OVERLAPS. Автоматического системного версионирования, как в некоторых других СУБД, нет — история ведётся приложением или триггером.

Чем это отличается от журналирования изменений? Журнал фиксирует факты изменения, а темпоральная таблица — состояния с периодами действия. По журналу состояние на дату восстанавливается перебором изменений, что медленнее и сложнее.

Нужно ли хранить историю всех таблиц? Нет. История нужна там, где есть требование восстановить значение на дату: тарифы, договоры, ставки, права. Для справочников без таких требований она только увеличивает объём.

Как удалить данные по требованию о персональных данных? Удаление должно охватывать и историю, иначе данные останутся в архивных версиях. Это нужно учитывать при проектировании, а не после получения запроса.

Источники: PostgreSQL docs (CREATE TABLE, constraints, triggers) — ссылки в шапке.