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

Dzisiaj chcę zaprezentować czytelnikom Habr narzędzie napisane w Pythonie do pracy z zależnościami tabel w systemie DBMS PostgreSQL.

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

tabela_archiwum - rekursywna archiwizacja/usuwanie wierszy z określonymi kluczami podstawowymi
get_table_references — wyszuka zależności dla tabeli (pokaże tabele, do których odwołuje się określona tabela oraz te, które się do niej odwołują)
get_rows_references - wyszukaj wiersze w innych tabelach, które odwołują się do określonych wierszy w żądanej tabeli

prehistoria

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

Główna baza danych naszego systemu CRM jest jedną z największych pod względem objętości w firmie. Jest też jednym z najstarszych: pojawił się już na samym starcie projektu, kiedy drzewa były już duże, Domklik był startupem, a zamiast mikroserwisu na modnym asynchronicznym frameworku Pythona powstał ogromny monolit w PHP.

Przejście z PHP na Python było bardzo długie i wymagało jednoczesnej obsługi obu systemów, co miało wpływ na projekt bazy danych.

W rezultacie mamy bazę danych z dużą liczbą silnie połączonych i ogromnych tabel z mnóstwem indeksów dla różnych typów zapytań. Wszystko to negatywnie wpływa na wydajność bazy danych: ze względu na duże tabele i mnóstwo relacji między nimi, złożoność zapytań stale rośnie, co jest szczególnie krytyczne w przypadku najbardziej obciążonych tabel.

Aby odciążyć bazę danych postanowiliśmy napisać skrypt, który przeniesie stare rekordy z najbardziej obszernych i załadowanych tabel do zarchiwizowanych (np. task в task_archive).

Zadanie to komplikuje duża liczba relacji między tabelami: wystarczy przenieść wiersze task в task_archive nie wystarczy, wcześniej musisz zrobić to samo rekurencyjnie ze wszystkimi odniesieniami do task stoły.

Pokażę na przykładzie baza danych demonstracyjnych ze strony postgrespro.ru:

Załóżmy, że musimy usunąć rekordy z tabeli Flights. Postgres nie pozwoli nam tego zrobić w ten sposób: najpierw musimy usunąć rekordy ze wszystkich tabel odwołujących się i tak dalej, rekurencyjnie, aż do tabel, do których nikt się nie odwołuje.

W naszym przykładzie o godz Flights odnosi się Ticket_flights, a na niej - Boarding_passes.

Dlatego musisz go usunąć w następującej kolejności:

Otrzymujemy wartości kluczy podstawowych (PK) wierszy Ticket_flights, które odnoszą się do wierszy, które mają zostać usunięte Flights.
Dostajemy wiersze PK Boarding_passes, które odnoszą się do Ticket_flights.
Usuwamy wiersze według PK z kroku 2 w tabeli Boarding_passes.
Usuń linie według PK z kroku 1 w Ticket_flights.
Usuwanie linii z Flights.

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

Jak korzystać

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

Wywołaj z wiersza poleceń (pggraph …).
Użycie w kodzie Pythona (klasa PgGraphApi).

Instalacja i konfiguracja

Najpierw musisz zainstalować narzędzie z repozytorium Pypi:

pip3 install pggraph

Następnie utwórz na komputerze lokalnym plik config.ini z konfiguracją bazy danych i skryptem archiwizującym:

[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; Необязательный параметр, указано значение по умолчанию

[archive]  ; Данный раздел заполнять необязательно, ниже указаны значения по умолчанию
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'

Uruchom z konsoli

Parametry

$ 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:

action - Wymagane działanie: archive_table, get_table_references lub get_rows_references.

Nazwane argumenty:

--config_path — ścieżka do pliku konfiguracyjnego;
--table — tabela, za pomocą której musisz wykonać akcję;
--ids — lista identyfikatorów oddzielona przecinkami, np. 1,2,3 (parametr opcjonalny);
--log_path — ścieżka do folderu logów (parametr opcjonalny, domyślnie — folder domowy);
--log_level — poziom logowania (parametr opcjonalny, domyślnie INFO).

Przykłady poleceń

Archiwizacja tabeli

Główną funkcją narzędzia jest archiwizacja danych, tj. przeniesienie wierszy z tabeli głównej do tabeli archiwalnej (na przykład z tabeli książki в książki_archiwum).

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

Wymagane parametry - ścieżka_konfiguracji, tabela i identyfikatory.

Po uruchomieniu rekordy będą rekursywnie usuwane ids w tabeli table oraz we wszystkich tabelach, które się do niego odnoszą.

$ 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

Znajdź zależności dla określonej tabeli

Funkcja znajdująca zależności określonej tabeli table. Wymagane parametry - config_path и table.

Po uruchomieniu na ekranie wyświetli się słownik, w którym:

in_refs — słownik tabel odwołujących się do danej tabeli, gdzie kluczem jest nazwa tabeli, wartością jest lista obiektów klucza obcego (pk_main - klucz podstawowy w tabeli głównej, pk_ref - klucz podstawowy w tabeli referencyjnej, fk_ref — nazwa kolumny będącej kluczem obcym 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')]}}

Znajdowanie odniesień do ciągów znaków z określonym kluczem podstawowym

Funkcja wyszukiwania wierszy w innych tabelach, które odwołują się do wierszy za pomocą klucza obcego ids stoły table. Wymagane parametry - config_path, table и 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ładowe wywołanie:

$ 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 jej w konsoli biblioteka może być używana w kodzie Pythona. Poniżej przedstawiono przykłady wywołań w interaktywnym ś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 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

Znajdź zależności dla określonej tabeli

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

Znajdowanie odniesień do ciągów znaków z określonym kluczem podstawowym

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

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

Chętnie podzielę się komentarzami, zobowiązaniami i sugestiami.

Postaram się odpowiedzieć na pytania najlepiej jak potrafię tutaj i w repozytorium.

Źródło: www.habr.com

PgGraph to narzędzie do archiwizacji i wyszukiwania zależności między tabelami w PostgreSQL

prehistoria

Jak korzystać

Instalacja i konfiguracja

Uruchom z konsoli

Parametry

Przykłady poleceń

Archiwizacja tabeli

Znajdź zależności dla określonej tabeli

Znajdowanie odniesień do ciągów znaków z określonym kluczem podstawowym

Użycie w kodzie

Archiwizacja tabeli

Znajdź zależności dla określonej tabeli

Znajdowanie odniesień do ciągów znaków z określonym kluczem podstawowym

Dodaj komentarz Anuluj odpowiedź