Temporal Tables¶

Temporal Tables (темпоральные таблицы) — это функциональность систем управления базами данных (СУБД), позволяющая автоматически хранить и обрабатывать историю изменений данных в таблице, обеспечивая возможность выполнения запросов к состоянию данных на любой момент времени в прошлом.

Темпоральные таблицы реализуют концепцию управления данными, основанную на времени (time-varying data). В отличие от обычных таблиц, которые хранят только текущее (актуальное) состояние записей, темпоральные таблицы сохраняют полную историю всех изменений: вставок, обновлений и удалений. Это позволяет решить задачи аудита, восстановления данных, анализа изменений, соответствия нормативным требованиям (например, GDPR или бухгалтерским стандартам) и построения отчётов, показывающих состояние объекта в заданный момент времени.

¶История и стандартизация

Концепция темпоральных баз данных разрабатывалась с 1970-х годов, однако массовое внедрение в промышленных СУБД началось лишь в 2010-х. Ключевой вехой стало включение поддержки темпоральных таблиц в стандарт SQL:2011 (ISO/IEC 9075:2011). Этот стандарт определил два типа временных измерений:

Время действия (Valid Time, Business Time) — период, в течение которого факт был истинным в реальном мире (например, период действия контракта).
Время транзакции (Transaction Time, System Time) — период, когда запись физически существовала в базе данных (автоматически фиксируется системой).

Первыми крупными СУБД, реализовавшими системно-временные таблицы (System-Versioned Temporal Tables), стали IBM DB2 (в версии 10.1, 2012) и Microsoft SQL Server (в версии 2016). Позднее поддержка была добавлена в Oracle (в версии 12.2, 2017, как Flashback Archive — временная таблица) и других системах. В 2022 году в рамках стандарта SQL:2023 были уточнены и расширены возможности темпоральных запросов.

¶Принцип работы

Темпоральная таблица обычно состоит из двух логических частей:

Текущая (активная) таблица — хранит актуальное состояние данных.
Историческая таблица — автоматически создаваемая системой таблица, в которую копируются старые версии строк при их изменении или удалении.

Управление временем транзакции (системное версионирование) в большинстве реализаций осуществляется автоматически на уровне СУБД. Для каждой строки автоматически добавляются два скрытых служебных столбца:

SysStartTime (или ValidFrom) — время, когда строка была создана или последний раз изменена.
SysEndTime (или ValidTo) — время, когда строка перестала быть текущей (была заменена или удалена). Для текущей строки это значение часто устанавливается на «бесконечность» (например, 9999-12-31).

При выполнении операции UPDATE СУБД не модифицирует существующую строку, а:

Копирует старую версию строки в историческую таблицу, устанавливая её SysEndTime равным текущему времени.
Обновляет текущую строку в основной таблице, устанавливая её SysStartTime равным текущему времени.

При выполнении DELETE строка просто помечается в текущей таблице как удалённая (её SysEndTime фиксируется), и она перемещается в историю.

¶Пример реализации в Microsoft SQL Server

Создание системно-версионированной темпоральной таблицы:

``sql CREATE TABLE dbo.Employees ( EmployeeID INT PRIMARY KEY, Name NVARCHAR(100), Position NVARCHAR(100), Salary DECIMAL(10,2), -- Служебные столбцы, обязательные для темпоральной таблицы SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL, SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL, -- Указание периода для версионирования PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Employees_History)); ``

Запрос для получения состояния данных на 1 января 2023 года:

``sql SELECT * FROM dbo.Employees FOR SYSTEM_TIME AS OF '2023-01-01T00:00:00'; ``

¶Типы темпоральных таблиц

Хотя стандарт SQL:2011 описывает оба измерения, на практике наибольшее распространение получили системно-версионированные таблицы (System-Versioned Temporal Tables). Выделяют следующие основные типы:

Системно-версионированные таблицы (System-Versioned Temporal Tables, SVTT) — управление временем транзакции автоматизировано СУБД. История изменений фиксируется системой и не поддаётся ручной модификации (в стандартной конфигурации). Это наиболее распространённый тип для аудита и восстановления.
Таблицы с временем действия (Valid-Time Tables) — управление временем действия осуществляется приложением (программистом). В таких таблицах есть поля ValidFrom и ValidTo, но СУБД не контролирует их автоматически. Этот тип используется, когда необходимо моделировать бизнес-периоды, не привязанные к системному времени (например, срок действия паспорта).
Битемпоральные таблицы (Bitemporal Tables) — объединяют оба измерения: системное время транзакции и время действия. Позволяют, например, узнать, каким было состояние данных на 1 марта 2023 года по версии, зафиксированной в системе 10 марта 2023 года. Этот тип сложен в реализации и редко применяется на практике.

¶Преимущества и недостатки

¶Преимущества

Автоматизация аудита — нет необходимости писать триггеры или хранить логи изменений в отдельных таблицах.
Точечное восстановление — возможность восстановить случайно изменённые или удалённые данные на любой момент времени в прошлом.
Анализ трендов — возможность строить отчёты, показывающие динамику изменения ключевых показателей (например, рост средней заработной платы за год).
Соответствие нормативным требованиям — хранение полной истории изменений может быть обязательным для финансовых и медицинских систем.
Простота запросов — язык запросов (например, FOR SYSTEM_TIME AS OF) интуитивно понятен и не требует сложных JOIN или подзапросов.

¶Недостатки

Увеличение объёма хранимых данных — для каждой изменённой строки хранится её копия. Для таблиц с высокой частотой обновлений это может привести к значительному росту базы данных.
Снижение производительности операций записи — каждая операция UPDATE или DELETE требует дополнительной записи в историческую таблицу.
Сложность администрирования — необходимо контролировать размер исторической таблицы и настраивать политики архивирования или очистки старых версий.
Ограничения на изменение схемы — изменение структуры таблицы (добавление/удаление столбцов) может потребовать обновления и исторической таблицы, что иногда создаёт накладные расходы.
Не все СУБД поддерживают все типы — большинство реализаций ограничены только системным временем транзакции.

¶Применение

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

Банковские и бухгалтерские системы — аудит операций, восстановление балансов на заданную дату.
Системы управления документами — отслеживание версий контрактов, заявок.
Системы управления персоналом — история должностей, зарплат и отделов сотрудников.
Системы электронной коммерции — изменение цен, каталогов товаров.
Научные и исследовательские базы данных — фиксация изменений в наборах данных.

¶Критика

Основная критика в адрес темпоральных таблиц связана с тем, что их реализация в разных СУБД существенно отличается от стандарта SQL:2011. Каждая СУБД (Microsoft SQL Server, Oracle, IBM DB2, PostgreSQL через расширение pg_cron или через триггеры) имеет свой синтаксис и свои особенности, что затрудняет перенос приложений между системами. Кроме того, в некоторых СУБД (например, в MySQL) встроенная поддержка темпоральных таблиц до сих пор отсутствует, и разработчики вынуждены реализовывать аналогичную логику самостоятельно, что снижает надёжность и увеличивает время разработки.

¶Источники

K. Kulkarni, J-E. Michels. Temporal features in SQL:2011. ACM SIGMOD Record, 2012. — Основополагающая статья, описывающая стандарт SQL:2011.
Microsoft Docs. Temporal Tables (SQL Server). — Официальная документация.
Oracle Help Center. Managing Temporal Validity and Flashback Data Archive. — Официальная документация.
IBM Documentation. System-period temporal tables. — Официальная документация.
C. J. Date, H. Darwen, N. Lorentzos. Temporal Data and the Relational Model. — Классическая монография по теории темпоральных баз данных.

BFOmetr — база данных и аналитика по компаниям России.

На главную BFOmetr →