🥇PgGraph — ein Tool zur Archivierung und Suche nach Abhängigkeiten von Tabellen in PostgreSQL

Heute möchte ich den Lesern von Habr ein Tool vorstellen, das in Python geschrieben wurde, um mit Tabellenabhängigkeiten in der PostgreSQL-Datenbank umzugehen.

Die API des Tools ist einfach und besteht aus drei Methoden:

archive_table — rekursive Archivierung/Löschung von Zeilen mit angegebenen Primärschlüsseln
get_table_references — Suche nach Abhängigkeiten für eine Tabelle (zeigt die Tabellen an, auf die verwiesen wird, sowie die, die auf die angegebene verweisen)
get_rows_references — Suche nach Zeilen in anderen Tabellen, die auf die angegebenen Zeilen in der gewünschten Tabelle verweisen

Hintergrund

Ich heiße Oleg Borzov und bin Entwickler im CRM-Team für Immobilienkreditmanager bei Domklik.

Die Hauptdatenbank unseres CRM-Systems gehört zu den größten in unserem Unternehmen. Sie ist auch eine der ältesten: sie wurde beim Start des Projekts eingerichtet, als die Bäume groß waren, Domklik ein Startup war und anstelle eines Mikrodienstes auf einem angesagten Python-Async-Framework ein riesiges Monolith auf PHP lief.

Der Übergang von PHP zu Python war sehr langwierig und erforderte gleichzeitig die Unterstützung beider Systeme, was sich auf das Design der Datenbank auswirkte.

Das Ergebnis ist eine Datenbank mit einer Vielzahl von stark verknüpften und umfangreichen Tabellen, die zahlreiche Indizes für verschiedene Abfragetypen aufweisen. All dies hat negative Auswirkungen auf die Datenbankleistung: Aufgrund der großen Tabellen und der Vielzahl an Beziehungen zwischen ihnen wächst die Komplexität der Abfragen ständig, was insbesondere für die am stärksten belasteten Tabellen kritisch ist.

Um die Belastung der Datenbank zu verringern, haben wir uns entschieden, ein Skript zu schreiben, das täglich über einen Cron-Job alte Datensätze aus den umfangreichsten und am stärksten belasteten Tabellen in Archivtabellen überträgt (zum Beispiel aus task in task_archive).

. Diese Aufgabe wird durch die Vielzahl der Beziehungen zwischen den Tabellen kompliziert: Es reicht nicht aus, einfach die Zeilen aus task in task_archive zu übertragen, bevor dies erforderlich ist, muss dasselbe rekursiv für alle abhängigen task Tabellen durchgeführt werden.

Ich werde dies anhand eines Beispiels demonstrieren von der Demodatenbank von postgrespro.ru:

Angenommen, wir müssen Datensätze aus der Tabelle Flightslöschen. Einfach so erlaubt uns Postgres das nicht: Zuvor müssen die Datensätze aus allen verknüpften Tabellen gelöscht werden, und dies rekursiv bis zu den Tabellen, auf die niemand verweist.

In unserem Beispiel wird auf Flights verwiesen, Ticket_flights,und auf sie – Boarding_passes..

Deshalb muss in folgender Reihenfolge gelöscht werden:

Erhalten der Primärschlüssel (Primary Keys, PK) der Zeilen in Ticket_flights,, die auf zu löschende Zeilen verweisen in Flights.
Erhalten der PK der Zeilen Boarding_passes., die verweisen auf Ticket_flights,.
Löschen der Zeilen nach PK aus Punkt 2 in der Tabelle Boarding_passes..
Löschen der Zeilen nach PK aus Punkt 1 in Ticket_flights,.
Löschen von Zeilen aus Flights.

Am Ende entstand ein Tool namens PgGraph, das wir als Open Source bereitstellen möchten.

So verwenden Sie es

Das Tool unterstützt zwei Nutzungsmodi:

Aufruf aus der Kommandozeile (pggraph …).
Verwendung im Python-Code (Klasse PgGraphApi).

Installation und Konfiguration

Zuerst muss das Tool aus dem Pypi-Repository installiert werden:

pip3 install pggraph

Dann eine Datei config.ini auf dem lokalen Rechner erstellen mit der Datenbank- und Archivierungsskript-Konfiguration:

[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; Optionaler Parameter, Standardwert angegeben

[archive]  ; Dieser Abschnitt kann optional ausgefüllt werden, Standardwerte sind unten angegeben
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'

Start aus der Konsole

Einstellungen

$ pggraph -h
Nutzung: pggraph Aktion [-h] --table TABELLE [--ids IDS] [--config_path KONFIG_PATH]
positional arguments:
  action        erforderliche Aktion: archive_table, get_table_references, get_rows_references

optionale Argumente:
  -h, --help                    zeigt diese Hilfe an und beendet das Programm
  --table TABELLE               Tabellenname
  --ids IDS                     Primärschlüssel-IDs, durch Kommas getrennt, z. B. 1,2,3
  --config_path KONFIG_PATH     Pfad zur config.ini
  --log_path LOG_PATH           Pfad zum Log-Verzeichnis
  --log_level LOG_LEVEL         Protokollierungsstufe (debug, info, error)

Positionalargumente:

Aktion — erforderliche Aktion: archive_table, get_table_references oder get_rows_references.

Benannte Argumente:

--config_path — Pfad zur Konfigurationsdatei;
--table — Tabelle, auf die die Aktion angewendet werden soll;
--ids — Liste der IDs, durch Kommas getrennt, z. B. 1,2,3 (optional);
--log_path — Pfad zum Log-Verzeichnis (optional, Standardwert — Home-Verzeichnis);
--log_level — Protokollierungsstufe (optional, Standardwert — INFO).

Beispiele für Befehle

Tabellenarchivierung

Die Hauptfunktion des Dienstprogramms besteht in der Archivierung von Daten, d. h. dem Verschieben von Zeilen aus der Haupttabelle in die Archivtabelle (z. B. von der Tabelle bücher in bücher_archiv).

Es wird auch das Löschen ohne Archivierung unterstützt: Dazu muss in der config.ini der Parameter to_archive = false).

Erforderliche Parameter — config_path, table und ids.

Nach dem Start werden die Einträge ids in der Tabelle Tabelle und in allen darauf verweisenden Tabellen rekursiv gelöscht.

$ pggraph archive_table --config_path config.hw.local.ini --table flights --ids 1,2,3
2020-06-20 19:27:44 INFO: Flüge - START
2020-06-20 19:27:44 INFO: Flüge - beginne archive_recursive mit 3 Zeilen (Tiefe=0)
2020-06-20 19:27:44 INFO:       BEGINNE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO:       ticket_flights - beginne archive_recursive mit 3 Zeilen (Tiefe=1)
2020-06-20 19:27:44 INFO:               BEGINNE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO:               boarding_passes - beginne archive_recursive mit 3 Zeilen (Tiefe=2)
2020-06-20 19:27:44 INFO:                       BEGINNE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO:                       ENDE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 Zeilen nach ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - beginne archive_recursive mit 3 Zeilen (Tiefe=2)
2020-06-20 19:27:44 INFO:                       BEGINNE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO:                       ENDE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 Zeilen nach ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - beginne archive_recursive mit 3 Zeilen (Tiefe=2)
2020-06-20 19:27:44 INFO:                       BEGINNE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO:                       ENDE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 Zeilen nach ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - beginne archive_recursive mit 3 Zeilen (Tiefe=2)
2020-06-20 19:27:44 INFO:                       BEGINNE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO:                       ENDE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 Zeilen nach ticket_no, flight_id
2020-06-20 19:27:44 INFO:               ENDE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO:       ticket_flights - archive_by_ids 3 Zeilen nach ticket_no, flight_id
2020-06-20 19:27:44 INFO:       ENDE ARCHIVIERUNG VERWEISENDER TABELLEN
2020-06-20 19:27:44 INFO: Flüge - archive_by_ids 3 Zeilen nach id
2020-06-20 19:27:44 INFO: Flüge - ENDE

Abhängigkeiten für die angegebene Tabelle suchen

Funktion zur Suche nach Abhängigkeiten der angegebenen Tabelle Tabelle. Erforderliche Parameter — config_path und Tabelle.

Nach dem Start wird ein Wörterbuch auf dem Bildschirm angezeigt, in dem:

in_refs — ein Wörterbuch der Tabellen, die auf diese verweisen, wobei der Schlüssel der Name der Tabelle und der Wert eine Liste von Foreign Keys ist (pk_main — der Primärschlüssel in der Haupttabelle, pk_ref — der Primärschlüssel in der referenzierenden Tabelle, fk_ref — der Name der Spalte, die als Foreign Key auf die Ursprüngliche Tabelle verweist);
out_refs — ein Wörterbuch der Tabellen, auf die diese verweist.

$ pggraph get_table_references --config_path config.hw.local.ini --table flights
{'in_refs': {'ticket_flights': [ForeignKey(pk_main='flight_id', pk_ref='ticket_no, flight_id', fk_ref='flight_id')]},
 'out_refs': {'aircrafts': [ForeignKey(pk_main='aircraft_code', pk_ref='flight_id', fk_ref='aircraft_code')],
              'airports': [ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='arrival_airport'),
                           ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='departure_airport')]}}

Suche nach Verweisen auf Zeilen mit angegebenen Primärschlüsseln

Funktion zur Suche nach Zeilen in anderen Tabellen, die über Foreign Keys auf Zeilen der ids Tabelle verweisen Tabelle. Erforderliche Parameter — config_path, Tabelle und ids.

Nach dem Start wird ein Wörterbuch mit folgender Struktur angezeigt:

{
	pk_id_1: {
		reffering_table_name_1: {
			foreign_key_1: [
				{row_pk_1: value, row_pk_2: value},
				...
			], 
			...
		},
		...
	},
	pk_id_2: {...},
	...
}

Beispielaufruf:

$ pggraph get_rows_references --config_path config.hw.local.ini --table flights --ids 1,2,3
{1: {'ticket_flights': {'flight_id': [{'flight_id': 1,
                                       'ticket_no': '0005432816945'},
                                      {'flight_id': 1,
                                       'ticket_no': '0005432816941'}]}},
 2: {'ticket_flights': {'flight_id': [{'flight_id': 2,
                                       'ticket_no': '0005433101832'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005433101864'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005432919715'}]}},
 3: {'ticket_flights': {'flight_id': [{'flight_id': 3,
                                       'ticket_no': '0005432817560'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817568'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817559'}]}}}

Verwendung im Code

Neben dem Start in der Konsole kann die Bibliothek auch im Python-Code verwendet werden. Nachfolgend sind Beispiele für Aufrufe in der interaktiven Umgebung iPython dargestellt.

Tabellenarchivierung

>>> from pg_graph.main import setup_logging
>>> setup_logging(log_level='DEBUG')
>>> from pg_graph.api import PgGraphApi
>>> api = PgGraphApi('config.hw.local.ini')
>>> api.archive_table('flights', [4,5])
2020-06-20 23:12:08 INFO: flüge - START
2020-06-20 23:12:08 INFO: flüge - beginne archive_recursive 2 Zeilen (tiefe=0)
2020-06-20 23:12:08 INFO: 	START ARCHIVIEREN DER REFERENZTABELLEN
2020-06-20 23:12:08 DEBUG: 	ticket_flights - ForeignKey(pk_main='flight_id', pk_ref='flight_id, ticket_no', fk_ref='flight_id')
2020-06-20 23:12:08 DEBUG: 	SQL('SELECT flight_id, ticket_no FROM bookings.ticket_flights WHERE (flight_id) IN (%s, %s)')
2020-06-20 23:12:08 INFO: 	ticket_flights - beginne archive_recursive 30 Zeilen (tiefe=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVIEREN DER REFERENZTABELLEN
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 30 Zeilen durch ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		LÖSCHEN VON boarding_passes durch FK flight_id, ticket_no - 30 Zeilen
2020-06-20 23:12:08 INFO: 		ENDE ARCHIVIEREN DER REFERENZTABELLEN
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 Zeilen durch flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	LÖSCHEN VON ticket_flights durch flight_id, ticket_no - 30 Zeilen
2020-06-20 23:12:08 DEBUG: 	EINFÜGEN IN ticket_flights_archive - 30 Zeilen
2020-06-20 23:12:08 INFO: 	ticket_flights - beginne archive_recursive 30 Zeilen (tiefe=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVIEREN DER REFERENZTABELLEN
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 30 Zeilen durch ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		LÖSCHEN VON boarding_passes durch FK flight_id, ticket_no - 30 Zeilen
2020-06-20 23:12:08 INFO: 		ENDE ARCHIVIEREN DER REFERENZTABELLEN
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 Zeilen durch flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	LÖSCHEN VON ticket_flights durch flight_id, ticket_no - 30 Zeilen
2020-06-20 23:12:08 DEBUG: 	EINFÜGEN IN ticket_flights_archive - 30 Zeilen
2020-06-20 23:12:08 INFO: 	ticket_flights - beginne archive_recursive 30 Zeilen (tiefe=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVIEREN DER REFERENZTABELLEN
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 30 Zeilen durch ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		LÖSCHEN VON boarding_passes durch FK flight_id, ticket_no - 30 Zeilen
2020-06-20 23:12:08 INFO: 		ENDE ARCHIVIEREN DER REFERENZTABELLEN
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 Zeilen durch flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	LÖSCHEN VON ticket_flights durch flight_id, ticket_no - 30 Zeilen
2020-06-20 23:12:08 DEBUG: 	EINFÜGEN IN ticket_flights_archive - 30 Zeilen
2020-06-20 23:12:08 INFO: 	ticket_flights - beginne archive_recursive 3 Zeilen (tiefe=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVIEREN DER REFERENZTABELLEN
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 3 Zeilen durch ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		LÖSCHEN VON boarding_passes durch FK flight_id, ticket_no - 3 Zeilen
2020-06-20 23:12:08 INFO: 		ENDE ARCHIVIEREN DER REFERENZTABELLEN
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 3 Zeilen durch flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	LÖSCHEN VON ticket_flights durch flight_id, ticket_no - 3 Zeilen
2020-06-20 23:12:08 DEBUG: 	EINFÜGEN IN ticket_flights_archive - 3 Zeilen
2020-06-20 23:12:08 INFO: 	ENDE ARCHIVIEREN DER REFERENZTABELLEN
2020-06-20 23:12:08 INFO: flüge - archive_by_ids 2 Zeilen durch flight_id
2020-06-20 23:12:09 DEBUG: SQL('CREATE TABLE IF NOT EXISTS bookings.flights_archive (LIKE bookings.flights)')
2020-06-20 23:12:09 DEBUG: LÖSCHEN VON flüge durch flight_id - 2 Zeilen
2020-06-20 23:12:09 DEBUG: EINFÜGEN IN flights_archive - 2 Zeilen
2020-06-20 23:12:09 INFO: flüge - ENDE

Abhängigkeiten für die angegebene Tabelle suchen

>>> from pg_graph.api import PgGraphApi
>>> from pprint import pprint
>>> api = PgGraphApi('config.hw.local.ini')
>>> res = api.get_table_references('flights')
>>> pprint(res)
{'in_refs': {'ticket_flights': [ForeignKey(pk_main='flight_id', pk_ref='flight_id, ticket_no', fk_ref='flight_id')]},
 'out_refs': {'aircrafts': [ForeignKey(pk_main='aircraft_code', pk_ref='flight_id', fk_ref='aircraft_code')],
              'airports': [ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='arrival_airport'),
                           ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='departure_airport')]}}

Suche nach Verweisen auf Zeilen mit angegebenen Primärschlüsseln

>>> from pg_graph.api import PgGraphApi
>>> from pprint import pprint
>>> api = PgGraphApi('config.hw.local.ini')
>>> rows = api.get_rows_references('flights', [1,2,3])
>>> pprint(rows)
{1: {'ticket_flights': {'flight_id': [{'flight_id': 1,
                                       'ticket_no': '0005432816945'},
                                      {'flight_id': 1,
                                       'ticket_no': '0005432816941'}]}},
 2: {'ticket_flights': {'flight_id': [{'flight_id': 2,
                                       'ticket_no': '0005433101832'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005433101864'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005432919715'}]}},
 3: {'ticket_flights': {'flight_id': [{'flight_id': 3,
                                       'ticket_no': '0005432817560'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817568'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817559'}]}}}

Der Quellcode der Bibliothek ist verfügbar unter GitHub unter MIT-Lizenz und im Repository PyPI.

Ich freue mich über Kommentare, Commits und Vorschläge.

Auf Fragen werde ich hier und im Repository möglichst antworten.

Quelle: habr.com