PGHist
История и аудит изменений таблиц в PostgreSQL

Описание

Устройство и принцип работы

Основные функции и представление

-- Создаем таблицу
create table example(
  id int primary key,
  name varchar(20),
  number numeric(10,2),
  date date
);

-- Включаем ведение истории
call pghist.hist_enable('example');

-- Изменяем данные 
insert into example values (1, 'Пример', 10, current_date);
update example set number=20, date=date-1;

-- Получаем лог изменений по строкам
select * from example_hist;

-- Получаем изменения по полям
select * from example_changes();

-- Получаем таблицу в прошлом
select * from example_at_timestamp(now()-interval '10 second');

-- drop table example cascade;

• Оптимизация хранения

  - хранятся только старые значения измененных полей, для операции insert хранится ссылка на SQL-выражение. Всегда сохраняется только первичный ключ. Структура хранения Транзакция-Выражение-Строка соответствует работе СУБД и минимизирует избыточность

• Триггеры уровня инструкции

  - DML-триггеры реализованы с уровнем срабатывания for each statement и использованием переходных таблиц для пакетной вставки изменений. Это исключает построчную обработку и минимизирует накладные расходы СУБД при массовых операциях

• Универсальность

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

• Описания

  - для каждой таблицы и ее столбцов возможно определить SQL-выражения для описания измененных строк и значений полей. По умолчанию описания формируются для ссылочных полей и строк таблицы

• Наследование

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

• Транзакции и SQL-выражения

  - сквозная иерархическая связь (Изменение→Запрос→Транзакция), дающая полную структурированную картину изменений: можно быстро получить все правки в рамках одной транзакции или все строки, обновленные конкретным SQL-запросом

• Индексы

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

• Условие для списка изменений

  - список изменений можно получить по первичному ключу, внешнему ключу на мастер-таблицу или полностью

• Автокорректировка

  - при выполнении DDL-операций над таблицей (alter table, create index и т.д.) срабатывает триггер, который корректирует ведение истории. При удалении таблицы удаляется и ее история

• Расширенные имена

  - поддерживаются расширенные (в двойных кавычках) наименования таблиц и столбцов

• Большие проекты

  - применение в больших проектах не рекомендуется, т.к. из-за универсальности и избыточности возникает значительное потребление ресурсов. Как правило, в больших проектах участвуют SQL-разработчики и администраторы БД, которые реализуют сохранение только необходимых данных

• Изменение первичного ключа

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

• Пересоздание представления

  - при удалении или изменении типа столбца представление [schema].[table]_hist удаляется и создается снова (PostgreSQL поддерживает только добавление столбцов в представление), рекомендуется создать хранимую функцию-обертку с динамическим запросом

• Радикальные изменения таблицы

  - при радикальных изменениях таблицы (например, переназначение первичного ключа на другое поле) возможна некорректная корректировка таблицы истории, рекомендуется удалить и создать историю (pghist.hist_disable и pghist.hist_enable)

Bash-скрипт замера производительности с использованием утилиты pgbench

#!/bin/bash
set -e
set -x

# Test configuration
DB=benchmark
OPTIONS="-j $(nproc) -c $(( $(nproc)*4 )) -t 1000" 

# Recreate DB, init pghist extension
psql -q -c "drop database if exists $DB" -c "create database $DB"
psql $DB -q -f pghist_init.sql
pgbench $DB -i

# Run baseline tests
pgbench $DB $OPTIONS
pgbench $DB $OPTIONS -b select-only

# Enable history tracking on target tables
psql $DB -q -c "call pghist.hist_enable('pgbench_accounts')"
psql $DB -q -c "call pghist.hist_enable('pgbench_branches')"
psql $DB -q -c "call pghist.hist_enable('pgbench_tellers')"

# Run post-pghist tests
pgbench $DB $OPTIONS
pgbench $DB $OPTIONS -b select-only

Лог тестирования

postgres@db-debian:~$ ./benchmark.sh
+ DB=benchmark
++ nproc
++ nproc
+ OPTIONS='-j 2 -c 8 -t 1000'
+ psql -q -c 'drop database if exists benchmark' -c 'create database benchmark'
+ psql benchmark -q -f pghist_init.sql
+ pgbench benchmark -i
dropping old tables...
creating tables...
generating data (client-side)...
vacuuming...
creating primary keys...
done in 1.00 s (drop tables 0.00 s, create tables 0.02 s, client-side generate 0.57 s, vacuum 0.22 s, primary keys 0.20 s).
+ pgbench benchmark -j 2 -c 8 -t 1000
pgbench (17.10 (Debian 17.10-1.pgdg13+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 2
maximum number of tries: 1
number of transactions per client: 1000
number of transactions actually processed: 8000/8000
number of failed transactions: 0 (0.000%)
latency average = 27.442 ms
initial connection time = 34.556 ms
tps = 291.529019 (without initial connection time)
+ pgbench benchmark -j 2 -c 8 -t 1000 -b select-only
pgbench (17.10 (Debian 17.10-1.pgdg13+1))
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 2
maximum number of tries: 1
number of transactions per client: 1000
number of transactions actually processed: 8000/8000
number of failed transactions: 0 (0.000%)
latency average = 0.899 ms
initial connection time = 39.618 ms
tps = 8896.985145 (without initial connection time)
+ psql benchmark -q -c 'call pghist.hist_enable('\''pgbench_accounts'\'')'
+ psql benchmark -q -c 'call pghist.hist_enable('\''pgbench_branches'\'')'
+ psql benchmark -q -c 'call pghist.hist_enable('\''pgbench_tellers'\'')'
+ pgbench benchmark -j 2 -c 8 -t 1000
pgbench (17.10 (Debian 17.10-1.pgdg13+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 2
maximum number of tries: 1
number of transactions per client: 1000
number of transactions actually processed: 8000/8000
number of failed transactions: 0 (0.000%)
latency average = 51.236 ms
initial connection time = 31.243 ms
tps = 156.139254 (without initial connection time)
+ pgbench benchmark -j 2 -c 8 -t 1000 -b select-only
pgbench (17.10 (Debian 17.10-1.pgdg13+1))
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 2
maximum number of tries: 1
number of transactions per client: 1000
number of transactions actually processed: 8000/8000
number of failed transactions: 0 (0.000%)
latency average = 1.025 ms
initial connection time = 32.404 ms
tps = 7801.795388 (without initial connection time)

Выводы

• В большинстве баз данных более 95% запросов - это читающие select, на них влияния отсутствует
• При работе в интерфейсе пользователи не заметят разницы, так как увеличение средней задержки (latency average) на ~25 мс (с 27.442 до 51.236 мс) значительно меньше времени сетевого взаимодействия интерфейс↔backend-сервер↔база данных (обычно до ~150-200 мс) даже без учета дополнительной бизнес-логики, обрабатывающей данные
• Время выполнения операций, меняющих большие объемы данных без сложных подзапросов и логики, увеличится в 2 раза. Обычно такие задачи выполняются в фоновом режиме

Сравнительный анализ архитектур

Сравнение функционала

Выводы

• Узкоспециализированные аналоги отлично делают только что-то одно: pgAudit пишет надежные ИБ-логи, temporal_tables быстро отдает снимки, JSONB-триггеры просты
• PGHist отлично формирует в готовом виде список изменений для показа пользователю, а все остальное делает на уверенном среднем уровне и закрывает сразу все задачи аудита изменений и версионирования: связывает данные с транзакциями и SQL-выражениями, экономит место, не боится bulk-нагрузок и автоматически поддерживает DDL
• Защита истории: в pgAudit изменить логи из базы нельзя, в PGHist изменить историю может только суперпользователь, temporal_tables требует точной настройки прав, при использовании JSONB-триггеров обычные пользователи могут подменить исторические данные