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.