Mats' Bachelor-Thesis: Versionsverwaltung mit PostgreSQL

Nachdem ich bereits mein Praktikum im Rahmen meines Studiums bei Sourceboat absolviert hatte und fortan als Werkstudent angestellt war, habe ich auch meine Bachelor-Thesis innerhalb der Firma geschrieben. Das Thema der Thesis lautet “Versionsverwaltung mit PostgreSQL bei kollaborativen Anwendungen”, da sich Erkenntnisse aus dem Thema in eines der Projekte integrieren lassen und ich mich auch selber für das Thema begeistert habe.

Schattentabellen mit PostgreSQL-Trigger

Die Thesis ist so aufgebaut, dass eine Versionsverwaltung in mehreren Schritten umgesetzt und anschließend getestet wird. Für den ersten Schritt wird für jede zu versionierende Tabelle eine Schattentabelle angelegt, die dieselben Spalten wie die Originaltabelle enthält. Zusätzlich dazu wird diese noch um eine weitere Spalte mit dem Erstellzeitpunkt der Version ergänzt, sowie die Originaltabelle um eine updated_at und created_at Spalte erweitert. Mit Hilfe eines Triggers wird nun bei jeder Änderung in der Originaltabelle eine Triggerfunktion ausgelöst.

CREATE TRIGGER trigger_before_update
  BEFORE UPDATE
  ON blog
  FOR EACH ROW
EXECUTE PROCEDURE create_version_for_blog();

CREATE OR REPLACE FUNCTION create_version_for_blog() 
  RETURNS TRIGGER 
  LANGUAGE plpgsql AS $ body $ 
BEGIN
  IF old.author <> new.author OR old.content <> new.content THEN
    IF old.updated_at IS null THEN
      INSERT INTO
        blog_history (blog_id, author, content, created_at) 
      VALUES
        (old.id, old.author, old.content, old.created_at);
    ELSE
      INSERT INTO
        blog_history (blog_id, author, content, created_at) 
      VALUES
        (old.id, old.author, old.content, old.updated_at);
    END IF;
  END IF;
  RETURN NEW;
END; $ body $ ;

pgMemento als externe Lösung

Im nächsten Schritt werden die Probleme, die sich aus dem ersten Schritt ergeben haben, analysiert und gelöst. Zu diesen gehören unter anderem die hohe Anzahl an Schattentabellen, die bei Datenbanken mit vielen Tabellen entstehen sowie die fehlende Aufzeichnung von Änderungen an den Tabellen und Spalten selbst. Mit der PostgreSQL-Erweiterung pgMemento können diese Probleme behoben werden. Änderungen werden mit pgMemento transaktionsweise in sechs verschiedene Tabellen versioniert. Diese beinhalten, wie im ER-Diagram zu sehen, jeweils Informationen zu den Transaktionen, den ausgeführten Events und den Änderungen für Reihen, Spalten, Tabellen und Schemata. Für die Versionierung von Spalten, Tabellen und Schemata werden die Tabellen audit_schema_log, audit_table_log und audit_column_log verwendet, während für die Versionierung der Daten table_event_log und row_log benutzt werden. Im transaction_log werden dann alle Transaktionen festgehalten.

Zusätzlich stellt pgMemento Funktionen zur Verfügung, mit denen sich vergangene Werte, Reihen und Tabellen aufrufen lassen. Da pgMemento die Versionen nicht nach Zeiten sortiert, sondern nach durchgeführten Transaktionen, muss vorerst herausgefunden werden, welche Transaktion zum gewünschten Zeitpunkt aktuell war.

SELECT
  min(id)
FROM
  pgmemento.transaction_log
WHERE
  txid_time >= '2022-01-01 00:00:00'
LIMIT 1;

Anschließend lässt sich die Transaktions-ID dazu verwenden, die Version der Reihe vor der Durchführung der ermittelten Transaktion abzufragen.

SELECT
  *
FROM
  pgmemento.restore_record(1, 21, 'blog', 'public', 58)
  AS (id integer, author text, content text, audit_trail_id bigint);

Als Ausgabe erhält man den Zustand der Reihe mit der audit_trail_id 58 bevor die Transaktion mit der ID 21 durchgeführt wurde.

 id | author |              content              | audit_trail_id
----+--------+-----------------------------------+----------------
  1 | Mats   | Beispieltext für ein Blogeintrag |             58
(1 row)

Benchmarktests mit und ohne pgMemento

Als Abschluss der Thesis wurden Benchmarktests auf einer Datenbank mit und ohne pgMemento durchgeführt. Diese sollen Aufschluss darüber geben, inwiefern sich eine erhöhte Anzahl an Daten oder Datenbanksessions auf die Performance der Datenbank auswirkt. Bei den Messungen wurden in besonders hohen Datenmengen und hoher Sessionanzahl ein wesentlicher Unterschied festgestellt, während bei niedrigeren Zahlen der Leistungsunterschied deutlich geringer war. Das ist insbesondere für unsere Projekte eine wichtige Erkenntnis, da in ihnen Millionen von Daten verwaltet werden müssen. Die Leistung lässt sich jedoch noch mit verschiedenen Ansätzen optimieren. Beispielsweise muss nicht jede kleine Änderung auch versioniert werden. Diese können zeitlich oder auf die Größe der Änderung begrenzt werden.