Разработка первичного ключа СУБД для управления версиями строк

Question

Я хочу разработать первичный ключ для моей таблицы с контролем версий строк. Моя таблица содержит 2 основных поля: ID и Timestamp и множество других полей. Для уникального "идентификатора" я хочу сохранить предыдущие версии записи. Поэтому я создаю первичный ключ, чтобы таблица была комбинацией полей ID и timestamp. Следовательно, чтобы увидеть все версии конкретного идентификатора, я могу дать,

Select * from table_name where ID=<ID_value>

Чтобы вернуть самую последнюю версию идентификатора, я могу использовать

Select * from table_name where ID=<ID_value> ORDER BY timestamp desc

и получить первый элемент. Мой вопрос здесь заключается в том, будет ли этот запрос эффективным и выполняется в O(1) вместо сканирования всей таблицы, чтобы получить все записи, совпадающие с идентификатором, учитывая, что поле идентификатора было частью полей первичного ключа? В идеале, чтобы получить результат в O(1), я должен был предоставить весь первичный ключ. Если для этого нужно выполнить сканирование всей таблицы, то как еще можно спроектировать мой первичный ключ, чтобы выполнить этот запрос в O(1)?

Спасибо, Шрирам

Answer 1

Каноническая ссылка на эту тему - Эффективная отметка времени в базах данных: https://www.cs.arizona.edu/~rts/pubs/VLDBJ99.pdf

Я обычно проектирую с подмножеством рекомендаций этого документа, используя таблицу, содержащую только первичный ключ, с другой справочной таблицей, которая имеет этот ключ, а также столбцы change_user, valid_from и valid_until с соответствующими значениями по умолчанию. Это облегчает ссылочную целостность, а также ввод значений в будущем и сохранение истории. При необходимости внесите в указатель и рассмотрите контрольные ограничения или триггеры, чтобы предотвратить наложения и пропуски, если вы предоставите эти поля приложению для прямой модификации. Они имеют очевидные накладные расходы производительности.

Затем мы создаем "представление текущих значений", которое предоставляется разработчикам и также может быть вставлено с помощью триггера "вместо".

Answer 2

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

create table foo (
  foo_id int primary key,
  name text
);

create table foo_history (
  foo_id int,
  version int,
  name text,
  operation char(1) check ( operation in ('u','d') ),
  modified_at timestamp,
  modified_by text
  primary key (foo_id, version)
);

Создайте триггер для копирования строки foo в foo_history при обновлении или удалении.

https://wiki.postgresql.org/wiki/Audit_trigger_91plus для полного примера с postgres