
Heute möchte ich den Lesern von Habr ein Werkzeug vorstellen, das in Python geschrieben wurde und zur Arbeit mit TabellenabhÀngigkeiten in der PostgreSQL-Datenbank dient.
Die API des Werkzeugs ist einfach und besteht aus drei Methoden:
- archive_table â rekursive Archivierung / Entfernung von Zeilen mit angegebenen PrimĂ€rschlĂŒsseln
- get_table_references â Suche nach AbhĂ€ngigkeiten fĂŒr eine Tabelle (zeigt Tabellen an, auf die die angegebene verweist und die auf sie verweisen)
- get_rows_references â Suche nach Zeilen in anderen Tabellen, die auf die angegebenen Zeilen in der gewĂŒnschten Tabelle verweisen
Vorgeschichte
Ich heiĂe Oleg Borzov, ich bin Entwickler im CRM-Team fĂŒr Manager der Immobilienfinanzierung bei Domklik.
Die Hauptdatenbank unseres CRM-Systems ist eine der gröĂten nach Volumen im Unternehmen. Sie ist auch eine der Ă€ltesten: Sie entstand beim allerersten Start des Projekts, als die BĂ€ume groĂ waren, Domklik ein Startup war und anstelle eines Mikrodienstes auf dem modernen Python-asynchronen Framework ein riesiges Monolith auf PHP existierte.
Der Ăbergang von PHP auf Python war sehr langwierig und erforderte die gleichzeitige UnterstĂŒtzung beider Systeme, was sich auf das Design der Datenbank auswirkte.
In der Folge haben wir eine Datenbank mit einer groĂen Anzahl stark verbundener und sehr groĂer Tabellen mit einer Vielzahl von Indizes fĂŒr unterschiedliche Abfragetypen. All dies hat negative Auswirkungen auf die Datenbankleistung: Aufgrund der groĂen Tabellen und der zahlreichen Beziehungen zwischen ihnen wĂ€chst die KomplexitĂ€t der Abfragen stĂ€ndig, was besonders kritisch fĂŒr die am meisten belasteten Tabellen ist.
Um die Last auf die Datenbank zu reduzieren, haben wir beschlossen, ein Skript zu schreiben, das tĂ€glich durch einen Cronjob alte EintrĂ€ge aus den gröĂten und am stĂ€rksten beanspruchten Tabellen in Archivtabellen verschiebt (zum Beispiel aus task in task_archive).
Diese Aufgabe wird durch die groĂe Anzahl von Beziehungen zwischen den Tabellen erschwert: Es reicht nicht aus, einfach Zeilen aus zu verschieben, bevor wir das Gleiche rekursiv fĂŒr alle, die auf verweisen, tun mĂŒssen. task in task_archive Tabellen. task Ich werde anhand eines Beispiels demonstrieren
einer Demodatenbank von postgrespro.ru :

Flights . Einfach so erlaubt uns Postgres nicht: Wir mĂŒssen zuvor die EintrĂ€ge aus allen verweisenden Tabellen entfernen, und das rekursiv bis zu den Tabellen, auf die niemand verweist.In unserem Beispiel auf
Ticket_flights . Einfach so erlaubt uns Postgres nicht: Wir mĂŒssen zuvor die EintrĂ€ge aus allen verweisenden Tabellen entfernen, und das rekursiv bis zu den Tabellen, auf die niemand verweist. verwiesen wird, , und auf sie âBoarding_passes Daher mĂŒssen wir in folgender Reihenfolge löschen:.
Wir erhalten die Werte der PrimĂ€rschlĂŒssel (Primary Keys, PK) der Zeilen in
- , die auf die zu löschenden Zeilen in verweisen
, und auf sie âWir erhalten die PK der Zeilen. Einfach so erlaubt uns Postgres nicht: Wir mĂŒssen zuvor die EintrĂ€ge aus allen verweisenden Tabellen entfernen, und das rekursiv bis zu den Tabellen, auf die niemand verweist.. - , die auf
Daher mĂŒssen wir in folgender Reihenfolge löschen:verweisen., und auf sie â. - Wir entfernen die Zeilen nach PK aus Punkt 2 in der Tabelle
Daher mĂŒssen wir in folgender Reihenfolge löschen:. - Wir entfernen die Zeilen nach PK aus Punkt 1 in
, und auf sie â. - Wir entfernen die Zeilen aus
. Einfach so erlaubt uns Postgres nicht: Wir mĂŒssen zuvor die EintrĂ€ge aus allen verweisenden Tabellen entfernen, und das rekursiv bis zu den Tabellen, auf die niemand verweist..
Am Ende entstand ein Tool mit dem Namen PgGraph, das wir als Open Source entwickeln wollten.
Wie man es benutzt
Das Tool unterstĂŒtzt zwei Verwendungsmodi:
- Aufruf aus der Befehlszeile (
pggraph âŠ). - Verwendung im Python-Code (Klasse
PgGraphApi).
Installation und Konfiguration
Zuerst muss das Tool aus dem Pypi-Repository installiert werden:
pip3 install pggraphDann erstellen Sie auf dem lokalen Rechner eine Datei config.ini mit der Datenbankkonfiguration und dem Archivierungsskript:
[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; Optionaler Parameter, Standardwert angegeben
[archive] ; Dieser Abschnitt ist optional, die Standardwerte sind unten aufgefĂŒhrt
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'Start aus der Konsole
Parameter
$ pggraph -h
usage: pggraph action [-h] --table TABLE [--ids IDS] [--config_path CONFIG_PATH]
positional arguments:
action erforderliche Aktion: archive_table, get_table_references, get_rows_references
optional arguments:
-h, --help zeigt diese Hilfe an und beendet
--table TABLE Tabellenname
--ids IDS PrimĂ€rschlĂŒssel-IDs, durch Kommas getrennt, z.B. 1,2,3
--config_path CONFIG_PATH Pfad zur config.ini
--log_path LOG_PATH Pfad zum Logverzeichnis
--log_level LOG_LEVEL Protokollierungsebene (debug, info, error)Positionale Argumente:
Aktionâ erforderliche Aktion:archive_table,get_table_referencesoderget_rows_references.
Benannte Argumente:
--config_pathâ Pfad zur Konfigurationsdatei;--tableâ Tabelle, mit der die Aktion durchgefĂŒhrt werden soll;--idsâ Liste von IDs durch Kommas getrennt, zum Beispiel,1,2,3(optional);--log_pathâ Pfad zum Verzeichnis fĂŒr Protokolle (optionaler Parameter, Standard ist das Home-Verzeichnis);--log_levelâ Protokollierungsebene (optional, Standard ist INFO).
Wie sehen typische logscli-Befehle in der Praxis aus?
Archivierung der Tabelle
Die Hauptfunktion des Tools ist die Archivierung von Daten, d.h. das Verschieben von Zeilen aus der Haupttabelle in die Archivtabelle (zum Beispiel von der Tabelle books in books_archive).
Es wird auch eine Löschung ohne Archivierung unterstĂŒtzt: dazu muss der Parameter to_archive = false).
festgelegt werden. Erforderliche Parameter sind â.
config_path, table und ids Nach dem Start werden die EintrĂ€ge mit in der Tabelle Tabelle ids entfernt und in allen dafĂŒr referenzierten Tabellen.
$ 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 - starte archive_recursive 3 Zeilen (Tiefe=0)
2020-06-20 19:27:44 INFO: STARTE ARCHIVIEREN VON REFERENZTABELLEN
2020-06-20 19:27:44 INFO: ticket_flights - starte archive_recursive 3 Zeilen (Tiefe=1)
2020-06-20 19:27:44 INFO: STARTE ARCHIVIEREN VON REFERENZTABELLEN
2020-06-20 19:27:44 INFO: boarding_passes - starte archive_recursive 3 Zeilen (Tiefe=2)
2020-06-20 19:27:44 INFO: STARTE ARCHIVIEREN VON REFERENZTABELLEN
2020-06-20 19:27:44 INFO: ENDE ARCHIVIEREN VON REFERENZTABELLEN
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 - starte archive_recursive 3 Zeilen (Tiefe=2)
2020-06-20 19:27:44 INFO: STARTE ARCHIVIEREN VON REFERENZTABELLEN
2020-06-20 19:27:44 INFO: ENDE ARCHIVIEREN VON REFERENZTABELLEN
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 - starte archive_recursive 3 Zeilen (Tiefe=2)
2020-06-20 19:27:44 INFO: STARTE ARCHIVIEREN VON REFERENZTABELLEN
2020-06-20 19:27:44 INFO: ENDE ARCHIVIEREN VON REFERENZTABELLEN
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 - starte archive_recursive 3 Zeilen (Tiefe=2)
2020-06-20 19:27:44 INFO: STARTE ARCHIVIEREN VON REFERENZTABELLEN
2020-06-20 19:27:44 INFO: ENDE ARCHIVIEREN VON REFERENZTABELLEN
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 ARCHIVIEREN VON REFERENZTABELLEN
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 ARCHIVIEREN VON REFERENZTABELLEN
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 - ENDESuche nach AbhĂ€ngigkeiten fĂŒr die angegebene Tabelle
Funktion zur Suche nach AbhÀngigkeiten der angegebenen Tabelle Tabelle. Erforderliche Parameter sind - config_path und Tabelle.
Nach dem Start wird ein Wörterbuch angezeigt, in dem:
in_refsâ Wörterbuch der referenzierten Tabellen auf diese, wobei der SchlĂŒssel der Tabellenname und der Wert eine Liste von Foreign Key-Objekten ist (pk_mainâ PrimĂ€rschlĂŒssel in der Haupttabelle,pk_refâ PrimĂ€rschlĂŒssel in der referenzierenden Tabelle,fk_refâ Name der Spalte, die ein Foreign Key auf die Ursprungstabelle ist);out_refsâ Wörterbuch von 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 einen Foreign Key auf Zeilen Nach dem Start werden die EintrĂ€ge mit der Tabelle verweisen Tabelle. Erforderliche Parameter sind - config_path, Tabelle und Nach dem Start werden die EintrĂ€ge mit.
Nach dem Start wird ein Wörterbuch mit folgender Struktur auf dem Bildschirm angezeigt:
{
pk_id_1: {
reffering_table_name_1: {
foreign_key_1: [
{row_pk_1: value, row_pk_2: value},
...
],
...
},
...
},
pk_id_2: {...},
...
}Beispiel fĂŒr den Aufruf:
$ 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 der Verwendung in der Konsole kann die Bibliothek auch im Python-Code verwendet werden. Unten sind Beispiele fĂŒr Aufrufe in der interaktiven iPython-Umgebung dargestellt.
Archivierung der Tabelle
>>> 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: flights - START
2020-06-20 23:12:08 INFO: flights - Archivierung rekursiv starten 2 Zeilen (Tiefe=0)
2020-06-20 23:12:08 INFO: START ARCHIVIERUNG DER VERWEISENDEN TABELLEN
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 - Archivierung rekursiv starten 30 Zeilen (Tiefe=1)
2020-06-20 23:12:08 INFO: START ARCHIVIERUNG DER VERWEISENDEN TABELLEN
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 - Archivierung nach 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 nach FK flight_id, ticket_no - 30 Zeilen
2020-06-20 23:12:08 INFO: ENDE ARCHIVIERUNG DER VERWEISENDEN TABELLEN
2020-06-20 23:12:08 INFO: ticket_flights - Archivierung nach 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 nach 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 - Archivierung rekursiv starten 30 Zeilen (Tiefe=1)
2020-06-20 23:12:08 INFO: START ARCHIVIERUNG DER VERWEISENDEN TABELLEN
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 - Archivierung nach 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 nach FK flight_id, ticket_no - 30 Zeilen
2020-06-20 23:12:08 INFO: ENDE ARCHIVIERUNG DER VERWEISENDEN TABELLEN
2020-06-20 23:12:08 INFO: ticket_flights - Archivierung nach 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 nach 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 - Archivierung rekursiv starten 30 Zeilen (Tiefe=1)
2020-06-20 23:12:08 INFO: START ARCHIVIERUNG DER VERWEISENDEN TABELLEN
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 - Archivierung nach 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 nach FK flight_id, ticket_no - 30 Zeilen
2020-06-20 23:12:08 INFO: ENDE ARCHIVIERUNG DER VERWEISENDEN TABELLEN
2020-06-20 23:12:08 INFO: ticket_flights - Archivierung nach 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 nach 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 - Archivierung rekursiv starten 3 Zeilen (Tiefe=1)
2020-06-20 23:12:08 INFO: START ARCHIVIERUNG DER VERWEISENDEN TABELLEN
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 - Archivierung nach 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 nach FK flight_id, ticket_no - 3 Zeilen
2020-06-20 23:12:08 INFO: ENDE ARCHIVIERUNG DER VERWEISENDEN TABELLEN
2020-06-20 23:12:08 INFO: ticket_flights - Archivierung nach 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 nach 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 ARCHIVIERUNG DER VERWEISENDEN TABELLEN
2020-06-20 23:12:08 INFO: flights - Archivierung nach 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 flights nach 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: flights - ENDESuche nach AbhĂ€ngigkeiten fĂŒr die angegebene Tabelle
>>> 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 unter der MIT-Lizenz sowie im Repository .
Ich freue mich ĂŒber Kommentare, Commits und VorschlĂ€ge.
Ich werde versuchen, Fragen hier und im Repository nach Möglichkeit zu beantworten.
Quelle: habr.com
