🥇PgGraph — narzędzie do archiwizacji i wyszukiwania zależności tabel w PostgreSQL

Dziś chcę przedstawić czytelnikom Habr narzędzie napisane w Pythonie do zarządzania zależnościami tabel w bazach danych PostgreSQL.

Interfejs API narzędzia jest prosty i składa się z trzech metod:

archive_table — rekurencyjne archiwizowanie/usuwanie wierszy z podanymi kluczami podstawowymi
get_table_references — wyszukiwanie zależności dla tabeli (pokaże tabele, na które odnosi się podana oraz te, które się na nią odnoszą)
get_rows_references — wyszukiwanie wierszy w innych tabelach, które odnoszą się do podanych wierszy w potrzebnej tabeli

Tło

Nazywam się Oleg Borzow, jestem programistą w zespole CRM dla menedżerów kredytów hipotecznych w Domklik.

Główna baza danych naszego systemu CRM jest jedną z największych pod względem objętości w firmie. Jednocześnie jest to jedna z najstarszych: pojawiła się przy samym uruchomieniu projektu, gdy drzewa były duże, Domklik — startupem, a zamiast mikroserwisu na modnym asynchronicznym frameworku Pythona był ogromny monolit na PHP.

Przejście z PHP na Pythona było bardzo długie i wymagało jednoczesnego wsparcia obu systemów, co miało wpływ na projektowanie bazy danych.

W rezultacie mamy bazę z dużą ilością mocno powiązanych i ogromnych tabel z mnogością indeksów pod różne typy zapytań. Całość negatywnie wpływa na wydajność bazy danych: z powodu dużych tabel i mnóstwa powiązań między nimi stale rośnie złożoność zapytań, co jest szczególnie krytyczne dla najbardziej obciążonych tabel.

Aby zmniejszyć obciążenie bazy danych, postanowiliśmy napisać skrypt, który codziennie za pomocą crona przenosiłby stare rekordy z najbardziej obszernych i obciążonych tabel do archiwalnych (na przykład z task do task_archive).

To zadanie komplikuje się z powodu dużej liczby powiązań między tabelami: po prostu przeniesienie wierszy z task do task_archive nie wystarczy, przed tym należy to samo zrobić rekurencyjnie ze wszystkimi tabelami, które się do nich odnoszą. task Zademonstruję na przykładzie

demonstracyjnej bazy danych ze strony postgrespro.ru Załóżmy, że musimy usunąć rekordy z tabeli:

Flights . Po prostu tak nie pozwoli nam zrobić Postgres: wcześniej musimy usunąć rekordy ze wszystkich tabel, które się do nich odnoszą, i tak rekurencyjnie aż do tabel, na które nikt się nie odnosi.W naszym przykładzie na

Ticket_flights . Po prostu tak nie pozwoli nam zrobić Postgres: wcześniej musimy usunąć rekordy ze wszystkich tabel, które się do nich odnoszą, i tak rekurencyjnie aż do tabel, na które nikt się nie odnosi. wskazuje , a na niej —Boarding_passes Dlatego musimy usuwać w takiej kolejności:.

Pobieramy wartości kluczy podstawowych (Primary Keys, PK) wierszy w

, które odnoszą się do usuwanych wierszy w , a na niej —Pobieramy PK wierszy . Po prostu tak nie pozwoli nam zrobić Postgres: wcześniej musimy usunąć rekordy ze wszystkich tabel, które się do nich odnoszą, i tak rekurencyjnie aż do tabel, na które nikt się nie odnosi..
, które odnoszą się do Dlatego musimy usuwać w takiej kolejności:, które odwołują się do , a na niej —.
Usuwamy wiersze według klucza PK z pkt. 2 w tabeli Dlatego musimy usuwać w takiej kolejności:.
Usuwamy wiersze według klucza PK z pkt. 1 w , a na niej —.
Usuwamy wiersze z . Po prostu tak nie pozwoli nam zrobić Postgres: wcześniej musimy usunąć rekordy ze wszystkich tabel, które się do nich odnoszą, i tak rekurencyjnie aż do tabel, na które nikt się nie odnosi..

W rezultacie powstało narzędzie o nazwie PgGraph, które postanowiliśmy udostępnić jako open source.

Jak korzystać

Narzędzie obsługuje dwa tryby użycia:

Wywołanie z linii poleceń (pggraph …).
Użycie w kodzie Python (klasa PgGraphApi).

Instalacja i konfiguracja

Najpierw należy zainstalować narzędzie z repozytorium Pypi:

pip3 install pggraph

Następnie należy stworzyć na lokalnej maszynie plik config.ini z konfiguracją bazy danych i skryptu archiwizacji:

[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; Opcjonalny parametr, podano wartość domyślną

[archive]  ; Sekcję tę można wypełnić opcjonalnie, poniżej podano wartości domyślne
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'

Uruchomienie z konsoli

Ustawienia

$ pggraph -h
usage: pggraph action [-h] --table TABLE [--ids IDS] [--config_path CONFIG_PATH]
positional arguments:
  action        required action: archive_table, get_table_references, get_rows_references

optional arguments:
  -h, --help                    show this help message and exit
  --table TABLE                 table name
  --ids IDS                     primary key ids, separated by comma, e.g. 1,2,3
  --config_path CONFIG_PATH     path to config.ini
  --log_path LOG_PATH           path to log dir
  --log_level LOG_LEVEL         log level (debug, info, error)

Argumenty pozycyjne:

akcja — wymagane działanie: archive_table, get_table_references lub get_rows_references.

Argumenty nazwane:

--config_path — ścieżka do pliku konfiguracyjnego;
--table — tabela, na której ma zostać wykonane działanie;
--ids — lista id oddzielona przecinkami, na przykład, 1,2,3 (opcjonalny parametr);
--log_path — ścieżka do folderu dla logów (opcjonalny parametr, domyślnie — folder domowy);
--log_level — poziom logowania (opcjonalny parametr, domyślnie — INFO).

Przykłady poleceń

Archiwizacja tabeli

Podstawową funkcją narzędzia jest archiwizacja danych, czyli przeniesienie wierszy z głównej tabeli do archiwalnej (na przykład z tabeli books do books_archive).

Obsługiwane jest również usuwanie bez archiwizacji: w tym celu w config.ini należy ustawić parametr to_archive = false).

Parametry obowiązkowe to — config_path, table i ids.

Po uruchomieniu zostaną rekurencyjnie usunięte rekordy ids w tabeli stół i we wszystkich odwołujących się do niej tabelach.

$ pggraph archive_table --config_path config.hw.local.ini --table flights --ids 1,2,3
2020-06-20 19:27:44 INFO: flights - START
2020-06-20 19:27:44 INFO: flights - start archive_recursive 3 rows (depth=0)
2020-06-20 19:27:44 INFO:       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:       ticket_flights - start archive_recursive 3 rows (depth=1)
2020-06-20 19:27:44 INFO:               START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:       ticket_flights - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO: flights - archive_by_ids 3 rows by id
2020-06-20 19:27:44 INFO: flights - END

Wyszukiwanie zależności dla wskazanej tabeli

Funkcja do wyszukiwania zależności wskazanej tabeli stół. Obowiązkowe parametry — config_path i stół.

Po uruchomieniu na ekranie zostanie wyświetlony słownik, w którym:

in_refs — słownik tabel odwołujących się do tej, gdzie klucz — nazwa tabeli, wartość — lista obiektów Foreign Key (pk_main — klucz główny w tabeli podstawowej, pk_ref — klucz główny w tabeli odnoszącej się, fk_ref — nazwa kolumny będącej kluczem obcym w tabeli źródłowej);
out_refs — słownik tabel, do których ta tabela się odnosi.

$ 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')]}}

Wyszukiwanie odnośników do wierszy z określonymi Primary Key

Funkcja do wyszukiwania wierszy w innych tabelach, które są powiązane przez Foreign Key z wierszami ids tabeli stół. Obowiązkowe parametry — config_path, stół i ids.

Po uruchomieniu na ekranie wyświetli się słownik o następującej strukturze:

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

Przykład wywołania:

$ 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'}]}}}

Użycie w kodzie

Oprócz uruchamiania w konsoli, bibliotekę można wykorzystać w kodzie Pythona. Poniżej znajdują się przykłady wywołań w interaktywnej środowisku iPython.

Archiwizacja tabeli

>>> 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 - start archive_recursive 2 rows (depth=0)
2020-06-20 23:12:08 INFO: 	START ARCHIVE REFERRING TABLES
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 - start archive_recursive 30 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
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 rows by 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: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rows by 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: 	DELETE FROM ticket_flights by flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rows
2020-06-20 23:12:08 INFO: 	ticket_flights - start archive_recursive 30 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
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 rows by 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: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rows by 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: 	DELETE FROM ticket_flights by flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rows
2020-06-20 23:12:08 INFO: 	ticket_flights - start archive_recursive 30 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
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 rows by 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: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rows by 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: 	DELETE FROM ticket_flights by flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rows
2020-06-20 23:12:08 INFO: 	ticket_flights - start archive_recursive 30 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
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 rows by 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: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rows by 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: 	DELETE FROM ticket_flights by flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rows
2020-06-20 23:12:08 INFO: 	ticket_flights - start archive_recursive 3 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
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 rows by 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: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 3 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 3 rows by 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: 	DELETE FROM ticket_flights by flight_id, ticket_no - 3 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 3 rows
2020-06-20 23:12:08 INFO: 	END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: flights - archive_by_ids 2 rows by 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: DELETE FROM flights by flight_id - 2 rows
2020-06-20 23:12:09 DEBUG: INSERT INTO flights_archive - 2 rows
2020-06-20 23:12:09 INFO: flights - END

Wyszukiwanie zależności dla wskazanej tabeli

>>> z pg_graph.api import PgGraphApi
>>> z 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')]}}

Wyszukiwanie odnośników do wierszy z określonymi Primary Key

>>> z pg_graph.api import PgGraphApi
>>> z 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'}]}}}

Kod źródłowy biblioteki jest dostępny na GitHub na licencji MIT, a także w repozytorium PyPI.

Będę wdzięczny za komentarze, commity i sugestie.

Na pytania postaram się odpowiedzieć w miarę możliwości tutaj i w repozytorium.

Źródło: habr.com