🥇 PgGraph — utilitate pentru arhivarea și căutarea dependențelor tabelelor în PostgreSQL

Astăzi vreau să prezint cititorilor de pe Habr un utilitar scris în Python pentru gestionarea dependențelor tabelurilor în SGBD PostgreSQL.

API-ul utilitarului este simplu și constă din trei metode:

archive_table — arhivarea/ștergerea recursivă a rândurilor cu cheile primare specificate
get_table_references — căutarea dependențelor pentru un tabel (va arăta tabelele la care se referă tabelul specificat și cele care se referă la el)
get_rows_references — căutarea rândurilor din alte tabele care se referă la rândurile specificate din tabelul dorit

Povestea

Mă numesc Oleg Borzov, sunt dezvoltator în echipa CRM pentru managerii de creditare ipotecară la Domclick.

Baza de date principală a sistemului nostru CRM este una dintre cele mai mari ca volum din companie. De asemenea, este una dintre cele mai vechi: a apărut odată cu lansarea proiectului, când copacii erau mari, Domclick era un startup, iar în loc de microservicii pe framework-ul Python modern asincron era un monolit uriaș pe PHP.

Trecerea de la PHP la Python a fost foarte lungă și a necesitat suport simultan pentru ambele sisteme, ceea ce a afectat proiectarea bazei de date.

Ca rezultat, avem o bază cu un număr mare de tabele strâns interconectate și uriașe ca dimensiune, cu o mulțime de indecși pentru diferite tipuri de interogări. Toate acestea afectează negativ performanța bazei de date: din cauza tabelelor mari și a mulțimii de relații între ele, complexitatea interogărilor crește constant, ceea ce este deosebit de critic pentru cele mai solicitate tabele.

Pentru a reduce sarcina asupra bazei de date, am decis să scriem un script care să mute zilnic prin cron înregistrările vechi din cele mai voluminoase și solicitante tabele în tabelele arhivă (de exemplu, din task în task_archive).

Această sarcină este complicată de numărul mare de relații între tabele: a muta pur și simplu rândurile din task în task_archive nu este suficient, înainte de asta trebuie să facem același lucru recursiv cu toate tabelele care se referă la task tabele.

Voi demonstra cu exemplul bazei de date demonstrative de pe site-ul postgrespro.ru:

Să presupunem că trebuie să ștergem înregistrările din tabelul Flights. Pur și simplu nu ne va permite Postgres să facem asta: mai întâi trebuie să ștergem înregistrările din toate tabelele care se referă, și așa recursiv până ajungem la tabelele la care nu se referă nimeni.

În exemplul nostru, pe Flights se referă Ticket_flights, iar la acesta — Boarding_passes.

Așadar, trebuie să ștergem în această ordine:

Obținem valorile cheilor primare (Primary Keys, PK) ale rândurilor din Ticket_flights, care se referă la rândurile care trebuie șterse din Flights.
Obținem PK ale rândurilor Boarding_passes, care se referă la Ticket_flights.
Ștergem liniile după PK din p.2 în tabel Boarding_passes.
Ștergem liniile după PK din p.1 în Ticket_flights.
Ștergem liniile din Flights.

În final, am obținut un utilitar numit PgGraph, pe care am decis să-l facem open source.

Cum se folosește

Utilitarul suportă două moduri de utilizare:

Apel din linia de comandă (pggraph …).
Utilizare în cod Python (clasă PgGraphApi).

Instalare și configurare

Mai întâi, trebuie să instalăm utilitarul din repository-ul Pypi:

pip3 install pggraph

Apoi, creați un fișier config.ini pe mașina locală cu configurația BD și a scriptului de arhivare:

[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; Parametru opțional, valoarea implicită

[archive]  ; Această secțiune poate fi lăsată necompletată, mai jos sunt indicate valorile implicite
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'

Rularea din consolă

Setări

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

optional arguments:
  -h, --help                    arată acest mesaj de ajutor și închide
  --table TABLE                 numele tabelului
  --ids IDS                     id-urile cheie primare, separate prin virgulă, ex. 1,2,3
  --config_path CONFIG_PATH     calea către config.ini
  --log_path LOG_PATH           calea către directorul de loguri
  --log_level LOG_LEVEL         nivelul de logare (debug, info, error)

Argumente poziționale:

action — acțiune necesară: archive_table, get_table_references sau get_rows_references.

Argumente denumite:

--config_path — calea către fișierul de configurare;
--table — tabelul pe care trebuie să-l modificăm;
--ids — lista id-urilor separate prin virgulă, de exemplu, 1,2,3 (parametru opțional);
--log_path — calea către folderul de loguri (parametru opțional, implicit — folderul home);
--log_level — nivelul de jurnalizare (parametru opțional, implicit — INFO).

Exemple de comenzi

Arhivarea tabelului

Funcția principală a utilitarului — arhivarea datelor, adică mutarea liniilor din tabelul principal în cel arhivat (de exemplu, din tabelul books în books_archive).

De asemenea, se suportă ștergerea fără arhivare: pentru aceasta, trebuie să setați parametrul în config.ini to_archive = false).

Parametrii obligatorii — config_path, table și ids.

După rulare, vor fi eliminate recursiv înregistrările ids din tabel table și din toate tabelele care se referă la acesta.

$ pggraph archive_table --config_path config.hw.local.ini --table flights --ids 1,2,3
2020-06-20 19:27:44 INFO: flights - ÎNCEPUT
2020-06-20 19:27:44 INFO: flights - începe archive_recursive 3 rânduri (adâncime=0)
2020-06-20 19:27:44 INFO:       ÎNCEPUT ARCHIVE TABELLE REFERITE
2020-06-20 19:27:44 INFO:       ticket_flights - începe archive_recursive 3 rânduri (adâncime=1)
2020-06-20 19:27:44 INFO:               ÎNCEPUT ARCHIVE TABELLE REFERITE
2020-06-20 19:27:44 INFO:               boarding_passes - începe archive_recursive 3 rânduri (adâncime=2)
2020-06-20 19:27:44 INFO:                       ÎNCEPUT ARCHIVE TABELLE REFERITE
2020-06-20 19:27:44 INFO:                       SFÂRȘIT ARCHIVE TABELLE REFERITE
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rânduri după ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - începe archive_recursive 3 rânduri (adâncime=2)
2020-06-20 19:27:44 INFO:                       ÎNCEPUT ARCHIVE TABELLE REFERITE
2020-06-20 19:27:44 INFO:                       SFÂRȘIT ARCHIVE TABELLE REFERITE
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rânduri după ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - începe archive_recursive 3 rânduri (adâncime=2)
2020-06-20 19:27:44 INFO:                       ÎNCEPUT ARCHIVE TABELLE REFERITE
2020-06-20 19:27:44 INFO:                       SFÂRȘIT ARCHIVE TABELLE REFERITE
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rânduri după ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - începe archive_recursive 3 rânduri (adâncime=2)
2020-06-20 19:27:44 INFO:                       ÎNCEPUT ARCHIVE TABELLE REFERITE
2020-06-20 19:27:44 INFO:                       SFÂRȘIT ARCHIVE TABELLE REFERITE
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rânduri după ticket_no, flight_id
2020-06-20 19:27:44 INFO:               SFÂRȘIT ARCHIVE TABELLE REFERITE
2020-06-20 19:27:44 INFO:       ticket_flights - archive_by_ids 3 rânduri după ticket_no, flight_id
2020-06-20 19:27:44 INFO:       SFÂRȘIT ARCHIVE TABELLE REFERITE
2020-06-20 19:27:44 INFO: flights - archive_by_ids 3 rânduri după id
2020-06-20 19:27:44 INFO: flights - SFÂRȘIT

Căutarea dependențelor pentru tabela specificată

Funcția pentru căutarea dependențelor tabelei specificate table. Parametrii necesari - config_path și table.

După execuție, un dicționar va fi afișat, în care:

in_refs — dicționar al tabelelor care fac referire la aceasta, unde cheia este numele tabelului, iar valoarea este lista obiectelor Foreign Key (pk_main — cheia primară în tabela principală, pk_ref — cheia primară în tabela referitoare, fk_ref — numele coloanei care este foreign key pentru tabela sursă);
out_refs — dicționar al tabelelor către care aceasta face referire.

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

Căutarea referințelor pentru rânduri cu cheile primare specificate

Funcția pentru căutarea rândurilor în alte tabele, care fac referire prin foreign key la rândurile ids tabelei table. Parametrii necesari - config_path, table și ids.

După lansare, pe ecran va fi afișat un dicționar cu următoarea structură:

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

Exemplu de apel:

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

Utilizarea în cod

Pe lângă lansarea în consolă, biblioteca poate fi utilizată în codul Python. Mai jos sunt prezentate exemple de apel în medii interactive iPython.

Arhivarea tabelului

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

Căutarea dependențelor pentru tabela specificată

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

Căutarea referințelor pentru rânduri cu cheile primare specificate

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

Codul sursă al bibliotecii este disponibil pe GitHub sub licența MIT, precum și în repository-ul PyPI.

Aștept comentarii, commit-uri și sugestii.

Voi încerca să răspund la întrebări pe măsură ce îmi va permite timpul aici și în repository.

Sursa: habr.com