🥇PgGraph — alat za arhiviranje i pretraživanje zavisnosti tabela u PostgreSQL

Danes želim bralcem Habra predstaviti orodje napisano v Pythonu, namenjeno upravljanju odvisnostmi tabel v PostgreSQL.

API orodja je preprost in vključuje tri metode:

archive_table — rekurzivno arhiviranje/brisanje vrstic z navedenimi primarnimi ključi
get_table_references — iskanje odvisnosti za tabelo (prikaže tabele, na katere se sklicuje navedena in ki se sklicujejo nanjo)
get_rows_references — iskanje vrstic v drugih tabelah, ki se sklicujejo na navedene vrstice v potrebni tabeli

Pozadina

Moje ime je Oleg Borzov, sem razvijalec v ekipi CRM za upravljavce hipotekarnih kreditov v Domkiku.

Glavna baza podatkov našega CRM sistema je ena največjih po obsegu v podjetju. Je tudi ena najstarejših: nastala je ob samem zagonu projekta, ko so bila drevesa velika, Domklik je bil startup, namesto mikroservisa na modrem Pythonovem asinhronem okviru pa je bil ogromen monolit v PHP.

Prehod s PHP na Python je bil zelo dolgotrajen in je zahteval sočasno podporo obeh sistemov, kar je vplivalo na načrtovanje baze podatkov.

Zaradi tega imamo bazo z velikim številom močno povezanih in ogromnih tabel z vrsto indeksov za različne vrste poizvedb. Vse to negativno vpliva na zmogljivost baze: zaradi velikih tabel in števila povezav med njimi se kompleksnost poizvedb nenehno povečuje, kar je še posebej kritično za najbolj obremenjene tabele.

Da bi zmanjšali obremenitev baze, smo se odločili napisati skript, ki bi vsak dan prek cron-a prenesel stare vnose iz najbolj obsežnih in obremenjenih tabel v arhivske (na primer iz task u task_archive).

To nalogo otežuje velika količina povezav med tabelami: preprosto prenesti vrstice iz task u task_archive ni dovolj, pred tem je treba enako rekurzivno opraviti z vsemi tabelami, ki se nanje sklicujejo. task Jaz bom pokazal na primeru

demonstracijske baze podatkov s spletne strani postgrespro.ru Recimo, da moramo izbrisati vnose iz tabele:

Flights . Preprosto to narediti nam Postgres ne bo dovolil: najprej je treba izbrisati vnose iz vseh povezanih tabel, in tako rekurzivno do tabel, na katere se nihče ne sklicuje.V našem primeru se na

sklicuje . Preprosto to narediti nam Postgres ne bo dovolil: najprej je treba izbrisati vnose iz vseh povezanih tabel, in tako rekurzivno do tabel, na katere se nihče ne sklicuje. Ticket_flights , na to pa —Boarding_passes Zato je treba brisati v tem vrstnem redu:.

Pridobimo vrednosti primarnih ključev (Primary Keys, PK) vrstic v

, ki se sklicujejo na odstranjene vrstice v , na to pa —Pridobimo PK vrstic . Preprosto to narediti nam Postgres ne bo dovolil: najprej je treba izbrisati vnose iz vseh povezanih tabel, in tako rekurzivno do tabel, na katere se nihče ne sklicuje..
Prejemamo PK niz Zato je treba brisati v tem vrstnem redu:, ki se sklicujejo na , na to pa —.
Odstranjujemo vrstice po PK iz točke 2 v tabeli Zato je treba brisati v tem vrstnem redu:.
Odstranjujemo vrstice po PK iz točke 1 v , na to pa —.
Odstranjujemo vrstice iz . Preprosto to narediti nam Postgres ne bo dovolil: najprej je treba izbrisati vnose iz vseh povezanih tabel, in tako rekurzivno do tabel, na katere se nihče ne sklicuje..

Na koncu je nastala orodje z imenom PgGraph, ki smo ga odločili razviti kot odprto kodo.

Kako uporabljati

Orodje podpira dva načina uporabe:

Klic iz ukazne vrstice (pggraph …).
Uporaba v Python kodi (razred PgGraphApi).

Namestitev in nastavitev

Najprej je treba namestiti orodje iz Pypi repozitorija:

pip3 install pggraph

Nato ustvarite na lokalnem računalniku datoteko config.ini s konfiguracijo baze podatkov in skripta za arhiviranje:

[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; Neobvezen parameter, navedena privzeta vrednost

[archive]  ; Ta oddelek je opcijski, spodaj so navedene privzete vrednosti
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'

Zagon iz konzole

Parametri

$ pggraph -h
uporaba: pggraph akcija [-h] --table TABLE [--ids IDS] [--config_path CONFIG_PATH]
pozicijski argumenti:
  akcija        zahtevana akcija: archive_table, get_table_references, get_rows_references

opcijski argumenti:
  -h, --help                    prikaže to pomoč in konča
  --table TABLE                 ime tabele
  --ids IDS                     primarni ključni ids, ločeni z vejico, npr. 1,2,3
  --config_path CONFIG_PATH     pot do config.ini
  --log_path LOG_PATH           pot do imenika dnevnikov
  --log_level LOG_LEVEL         raven dnevnike (debug, info, error)

Pozicijski argumenti:

akcija — zahtevana akcija: archive_table, get_table_references ili get_rows_references.

Imenovani argumenti:

--config_path — pot do konfiguracijske datoteke;
--table — tabela, s katero je treba izvesti dejanje;
--ids — seznam id-jev ločenih z vejico, na primer, 1,2,3 (neobvezen parameter);
--log_path — pot do imenika za dnevnike (neobvezen parameter, privzeto — domači imenik);
--log_level — raven dnevnikov (neobvezen parameter, privzeto — INFO).

Primeri ukazov

Arhiviranje tabele

Glavna funkcija orodja — arhiviranje podatkov, to je prenos vrstice iz glavne tabele v arhiv (na primer, iz tabele books u books_archive).

Podpira tudi brisanje brez arhiviranja: za to je treba v config.ini nastaviti parameter to_archive = false).

Obvezni parametri — config_path, table in ids.

Po zagonu bodo rekursivno izbrisani zapisi ids u tabeli tabela in v vseh tabelah, ki se na to sklicujejo.

$ pggraph archive_table --config_path config.hw.local.ini --table flights --ids 1,2,3
2020-06-20 19:27:44 INFO: flights - ZAČETEK
2020-06-20 19:27:44 INFO: flights - začetek archive_recursive 3 rows (depth=0)
2020-06-20 19:27:44 INFO:       ZAČETEK ARHIVIRANJA REFERENČNIH TABLIC
2020-06-20 19:27:44 INFO:       ticket_flights - začetek archive_recursive 3 rows (depth=1)
2020-06-20 19:27:44 INFO:               ZAČETEK ARHIVIRANJA REFERENČNIH TABLIC
2020-06-20 19:27:44 INFO:               boarding_passes - začetek archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       ZAČETEK ARHIVIRANJA REFERENČNIH TABLIC
2020-06-20 19:27:44 INFO:                       KONEC ARHIVIRANJA REFERENČNIH TABLIC
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 - začetek archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       ZAČETEK ARHIVIRANJA REFERENČNIH TABLIC
2020-06-20 19:27:44 INFO:                       KONEC ARHIVIRANJA REFERENČNIH TABLIC
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 - začetek archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       ZAČETEK ARHIVIRANJA REFERENČNIH TABLIC
2020-06-20 19:27:44 INFO:                       KONEC ARHIVIRANJA REFERENČNIH TABLIC
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 - začetek archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       ZAČETEK ARHIVIRANJA REFERENČNIH TABLIC
2020-06-20 19:27:44 INFO:                       KONEC ARHIVIRANJA REFERENČNIH TABLIC
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:               KONEC ARHIVIRANJA REFERENČNIH TABLIC
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:       KONEC ARHIVIRANJA REFERENČNIH TABLIC
2020-06-20 19:27:44 INFO: flights - archive_by_ids 3 rows by id
2020-06-20 19:27:44 INFO: flights - KONEC

Iskanje odvisnosti za navedeno tabelo

Funkcija za iskanje odvisnosti navedene tabele tabela. Obvezni parametri — config_path in tabela.

Po zagonu bo na zaslonu prikazana slovar, kjer:

in_refs — slovar referenčnih tabel na to, kjer ključ — ime tabele, vrednost — seznam objektov Foreign Key (pk_main — primarni ključ v glavni tabeli, pk_ref — primarni ključ v referenčni tabeli, fk_ref — ime stolpca, ki je tuji ključ na izvorno tabelo);
out_refs — slovar tabel, na katere se sklicuje ta.

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

Iskanje povezav na vrstice z navedenimi primarnimi ključi

Funkcija za iskanje vrstic v drugih tabelah, ki se sklicujejo preko tujih ključev na vrstice ids tabele tabela. Obvezni parametri — config_path, tabela in ids.

Po zagonu bo na zaslonu prikazan slovar z naslednjo strukturo:

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

Primer klica:

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

Uporaba v kodi

Poleg zagona v konzoli, lahko knjižnico uporabljate tudi v Python kodi. Spodaj so prikazani primeri klica v interaktivnem okolju iPython.

Arhiviranje tabele

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

Iskanje odvisnosti za navedeno tabelo

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

Iskanje povezav na vrstice z navedenimi primarnimi ključi

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

Izvorna koda biblioteke je dostupna na GitHub pod MIT licencom, kao i u repozitoriju PyPI.

Biću zahvalan na komentarima, commitima i predlozima.

Na pitanja ću se truditi da odgovorim kada budem mogao ovde i u repozitoriju.

Izvor: habr.com