Эффективная стратегия сохранения журнала аудита / истории изменений для приложений БД?
Каковы некоторые стратегии, с помощью которых люди успешно поддерживают историю изменений данных в довольно сложной базе данных. Одно из приложений, которое я часто использую и разрабатываю, действительно могло бы выиграть от более полного способа отслеживания изменений записей с течением времени. Например, прямо сейчас записи могут иметь несколько временных меток и измененных пользовательских полей, но в настоящее время у нас нет схемы для регистрации нескольких изменений, например, если операция откатывается. В идеальном мире можно было бы восстановить запись, которая была после каждого сохранения и т. д.
Некоторая информация о БД:
- Требуется возможность роста на тысячи записей в неделю
- 50-60 столов
- Каждая основная ревизионная таблица может содержать несколько миллионов записей.
- Установлено разумное количество внешних ключей и индексов
- Использование PostgreSQL 8.x
Ответы 6
Раньше я использовал триггеры для создания журнала обновления / вставки / удаления базы данных.
Вы можете вставлять запись каждый раз, когда одно из вышеперечисленных действий выполняется с определенной таблицей, в таблицу регистрации, которая отслеживает действие, какой пользователь db это сделал, временную метку, таблицу, в которой оно было выполнено, и предыдущее значение.
Вероятно, есть лучший ответ, поскольку это потребует от вас кеширования значения до того, как будет выполнено фактическое удаление или обновление, я думаю. Но вы можете использовать это для отката.
Проблема с решением на уровне базы данных заключается в том, что для действия нет бизнес-контекста, то есть вы не знаете, какой пользователь это сделал или что они делали. Большинство веб-приложений подключаются к своей базе данных с использованием одного имени пользователя, поэтому имя пользователя, вошедшего в систему, не является именем пользователя, видимым триггером.
Эндрю, любое решение, не связанное с базой данных, вообще не является контрольным журналом, поскольку оно не будет перехватывать записи, не добавленные в графический интерфейс. Наша улавливает конкретных пользователей, потому что все наши таблицы имеют столбец last_updated, а вставки, обновления и т. д. Все отправляют person_id лица, выполняющего обновление, а не имя пользователя в сети.
Одна из стратегий, которую вы можете использовать, - это MVCC, Multi-Value Concurrency Control. В этой схеме вы никогда не обновляете ни одну из своих таблиц, вы просто выполняете вставки, сохраняя номера версий для каждой записи. Это дает то преимущество, что обеспечивает точный моментальный снимок в любой момент времени, а также полностью позволяет избежать проблем с блокировкой обновлений, от которых страдают многие базы данных.
Но это создает огромную базу данных, и для выбора всего требуется дополнительное предложение для выбора текущей версии записи.
Как узнать текущую версию? С первым 1 порядком по убыванию? @Eric Z Beard
@ismailyavuz, наверное, есть такая возможность: добавить дополнительный логический столбец is_current. И поддерживайте его согласованность, используя триггеры - и, возможно, ограничение уникальности для (natural_id, is_current = 1).
@ismailyavuz также см. это: stackoverflow.com/a/1051494/1475331 (используйте поля from и to). Предложение «where», которое выбирает текущую версию строки, будет WHERE "to" IS NULL.
Если вы используете Hibernate, взгляните на JBoss Envers. С домашней страницы проекта:
The Envers project aims to enable easy versioning of persistent JPA classes. All that you have to do is annotate your persistent class or some of its properties, that you want to version, with @Versioned. For each versioned entity, a table will be created, which will hold the history of changes made to the entity. You can then retrieve and query historical data without much effort.
Это несколько похоже на Подход Эрика, но, вероятно, требует гораздо меньше усилий. Однако не знаю, какой язык / технологию вы используете для доступа к базе данных.
Кто-нибудь использовал JBoss Envers на сайте eCom с высоким уровнем транзакций?
Единственная проблема с использованием триггеров заключается в том, что они увеличивают накладные расходы производительности при любой вставке / обновлении / удалении. Для большей масштабируемости и производительности вы хотели бы свести транзакции с базой данных к минимуму. Аудит с помощью триггеров увеличивает время, необходимое для выполнения транзакции, и в зависимости от объема может вызвать проблемы с производительностью.
Другой способ - выяснить, предоставляет ли база данных какой-либо способ извлечения журналов повторного выполнения, как в случае с Oracle. Журналы повтора - это то, что база данных использует для воссоздания данных в случае сбоя и необходимости восстановления.
Подобно триггеру (или даже с помощью), вы можете заставить каждую транзакцию запускать событие регистрации асинхронно, а другой процесс (или просто поток) фактически обрабатывает регистрацию. В зависимости от вашего приложения будет много способов реализовать это. Я предлагаю приложению запускать событие, чтобы оно не создавало ненужной нагрузки на вашу первую транзакцию (что иногда приводит к блокировкам из каскадных журналов аудита).
Кроме того, вы можете повысить производительность первичной базы данных, сохранив базу данных аудита в отдельном месте.
Я использую SQL Server, а не PostgreSQL, поэтому я не уверен, сработает ли это для вас или нет, но у Попа Риветта была отличная статья о создании контрольного журнала здесь: Часто задаваемые вопросы по SQL Server от Pop Rvett № 5: Поп в контрольном журнале
Создайте таблицу аудита, а затем создайте триггер для каждой таблицы, которую вы хотите проверить.
Подсказка: используйте Шифровальщик для создания триггеров.
Рассмотрите возможность использования временная база данных.