Продолжаем серию публикаций «Адаптивное администрирование Sigla Vision».  

В предыдущей статье мы изложили основную концепцию построения объектной модели (ОМ), подробно разобрали сборку данных таблиц и привели код для ее развертывания.

В этом материале расскажем, как построить систему версионирования для репозитория метаданных Sigla Vision (БД FineDB). Самой FineDB такая информация недоступна — там в основном данные только о текущем состоянии системы.

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

Описанный подход не привязан к Sigla Vision — он применим к любой аналитической системе, где метаданные хранятся во внешней СУБД с поддержкой триггеров.

Концепция системы версионирования таблиц

Причина появления функционала версионирования данных проста. В Sigla Vision «из коробки» нет возможности отследить все изменения в системе:

1. БД FineDB содержит в основном объекты Sigla Vision для текущего состояния системы.

2. Записи в таблицах БД LogDB отражают только заранее определенные события, которые произошли в системе.

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

Иногда нам достаточно знать, что и как менялось, даже если мы не знаем кем. Большая часть действий в системе оставляет след на уровне таблиц БД FineDB. ПО вносит изменения в таблицы под своей учетной записью СУБД. Логинов пользователей Sigla Vision здесь нет, но для многих задач они и не нужны. Главное в мониторинге — само событие и его временна́я метка.

Если вести учет изменений в таблице по типу (вставка, обновление, удаление строк) с привязкой ко времени, можно восстановить ее состояние на любой момент. Для этого нужна стартовая точка — инициализация таблицы версионирования: при этом копируются все текущие записи исходной таблицы. 

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

Основная цель доработки — добавить протоколирование изменений в таблицах репозитория метаданных Sigla Vision в разрезе сущностей системы.

Функционал версионирования таблиц FineDB качественно расширяет возможности мониторинга Sigla Vision. С полной историей изменений содержимого таблиц FineDB администраторы могут:

1. просматривать историю изменений содержимого таблицы или историю изменений отдельных объектов;

2. восстанавливать состояние таблицы на определенный момент в прошлом;

3. отслеживать динамику изменений таблиц и объектов.

Все это помогает быстро решать задачи по сбору статистики и мониторингу состояния системы за прошлые периоды — и администрировать систему с гораздо лучшим пониманием процессов.

Особенности реализации системы версионирования таблиц

У функционала версионирования есть особенности, которые надо учитывать.

1. Быстрое развертывание решения (менее 1 минуты).

2. Очень низкое потребление вычислительных ресурсов (ЦП, ОЗУ).

3. Рост занимаемого места в БД: минимум на старте — в 1,5 раза, максимум зависит от состава версионируемых таблиц и интенсивности изменений в них (примеры — ниже).

4. Минимальная задержка при записи в таблицу FineDB (используется только запись в таблицы версионирования после события с данными из основной таблицы).

5. Отдельный префикс zst_ в названиях таблиц — чтобы отделить таблицы версионирования от исходных.

Версионирование таблиц увеличивает размер БД за счет инкрементального накопления данных. Поэтому важно контролировать рост размеров таблиц.

История может вестись:

1. для отдельных таблиц FineDB;

2. для отдельных вспомогательных таблиц (производные таблицы, справочники, сущности), не относящихся к FineDB.

При добавлении, изменении и удалении данных в любом поле версионируемой таблицы изменения логируются в таблицу с префиксом zst_.

В триггерах по умолчанию используется условие на запись любых изменений. Функции можно кастомизировать — записывать только избранные изменения и таким образом существенно экономить занимаемое место в БД.

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

Реализация решения

Объекты системы версионирования таблиц

Чтобы развернуть систему, нужно создать таблицы для хранения версий данных на основе фактической структуры версионируемых таблиц, наполнить их данными, создать триггеры событий (after insert / after update / after delete) и прописать в них вызовы функций обработки.

Функции системы версионирования таблиц:

1. zst_1_create_table_trigger — создает таблицу для хранения данных версионирования, триггеры и функции триггеров;

2. zst_2_delete_table_trigger — удаляет таблицу для хранения данных версионирования, триггеры и функции триггеров;

3. zst_3_auto_create_table_trigger — запускает функцию zst_1_create_table_trigger на основе данных из таблицы zst_1_table_list (списка таблиц для логирования с установочными параметрами);

4. zst_4_auto_delete_table_trigger — запускает функцию zst_2_delete_table_trigger на основе данных из таблицы zst_1_table_list;

5. zst_5_create_table_trigger_stat — собирает статистику по виду изменений таблиц (вспомогательная функция);

6. zst_6_compress_snapshot — сжимает период данных в версионированных таблицах.

Порядок развертывания системы версионирования таблиц

Чтобы настроить и использовать решение, нужно выполнить следующие шаги.

1. Создать все функции: 

• zst_1_create_table_trigger 

• zst_2_delete_table_trigger 

• zst_3_auto_create_table_trigger 

• zst_4_auto_delete_table_trigger 

• zst_5_create_table_trigger_stat 

• zst_6_compress_snapshot

2. Создать все таблицы: 

• zst_1_table_list 

• zst_2_table_list_stat 

• zst_3_table_list_stat_size

3. Заполнить таблицу со списком версионируемых таблиц zst_1_table_list  через вставку результата SQL-запроса. 

4. При необходимости вручную исключить или включить таблицы zst_1_table_list (опциональный шаг).

5. Вызвать функцию zst_3_auto_create_table_trigger, которая создает версионированные таблицы, инициализирует их, добавляет функции обработки событий DML и создает триггеры, которые вызывают эти функции.

6. Запустить функцию zst_5_create_table_trigger_stat для сбора статистики состояния таблиц версионирования.

Сопровождение

Во время работы системы версионирования любые изменения в исходных таблицах — в первую очередь из списка zst_1_table_list — попадают в таблицы версионирования. Любые данные занимают место, поэтому количество версионируемых таблиц и их размеры нужно контролировать.

Основные задачи сопровождения системы версионирования таблиц:

1. добавление новых таблиц в систему версионирования;

2. исключение отдельных таблиц из системы версионирования;

3. сжатие данных версионирования;

4. мониторинг размера версионированных таблиц.

Добавление новых и исключение старых таблиц

Периодически в систему версионирования нужно добавлять новые таблицы (таблицы Sigla Vision, таблицы ОМ или какие-то другие).

Добавлять таблицы можно по одной — через вызов функции zst_1_create_table_trigger с параметрами, без внесения в список таблиц версионирования, или же сразу несколько — через вызов функции zst_3_auto_create_table_trigger с добавлением таблиц в список с необходимыми флагами.

Исключать отдельные таблицы из системы версионирования имеет смысл, когда данные из них малоинформативны, динамика изменений чрезмерно высока или таблица слишком быстро растет.

Опять же, доступно два пути: 

• по одной — через вызов функции zst_2_delete_table_trigger с параметрами, без внесения в список таблиц версионирования;

• сразу несколько — через вызов функции zst_4_auto_delete_table_trigger с добавлением таблиц в список с необходимыми флагами.

Сжатие данных версионирования

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

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

При отборе ранжированных строк записи с событиями insert и update в новом снапшоте становятся только событиями insert — так же, как и при инициализации таблицы версионирования.

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

Мониторинг размера версионированных таблиц

Чтобы мониторить размеры версионированных таблиц, мы создали системный дашборд на основе сводных таблиц (с показателями «количество строк» и «размер таблиц на диске»).

Несколько примеров скриншотов с информацией по размерам таблиц.

Вывод: система версионирования кратно увеличивает размер БД по сравнению с исходной.

Выводы

1. Часть таблиц система не изменяет — их можно исключить из версионирования.

2. Часть таблиц не использует механизмы изменения с помощью UPDATE — при высокой интенсивности удаления/вставки именно они становятся основным фактором роста занимаемого места в БД.

3. Часть таблиц имеет крайне высокую интенсивность изменений и тоже заметно влияет на рост занимаемого места.

Вывод: более 75% всего объема среди подключенных к системе версионирования таблиц приходится на четыре из них:

• zst_finebi_report_config_en

• zst_fine_extra_property

• zst_finebi_widget_config_en

• zst_fine_schedule_task_log

Из этих четырех для долгосрочного хранения мы оставили только zst_fine_schedule_task_log (лог задач рассылки контента). Остальные можно сжать до минимально необходимого периода — например, до месяца или квартала.

Заключение

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

В репозитории GitHub лежит SQL-код (в диалекте PostgreSQL) для создания нужных объектов системы версионирования. Решение можно развернуть у себя, изучить и использовать в работе без каких-либо ограничений. В архиве также есть код решения по созданию объектной модели из предыдущей статьи.

В следующей статье разберем, как с помощью триггеров БД FineDB реализовать дополнительную логику обработки данных при выполнении операций в интерфейсе ПО.