🥇PgGraph - et verktøy for arkivering og søk i tabellavhengigheter i PostgreSQL

I dag ønsker jeg å presentere Habr-lesere med et verktøy skrevet i Python for å jobbe med tabellavhengigheter i PostgreSQL DBMS.

Verktøyets API er enkel og består av tre metoder:

arkivtabell - rekursiv arkivering/sletting av rader med spesifiserte primærnøkler
get_table_references - søk etter avhengigheter for en tabell (vil vise tabeller som refereres til av den spesifiserte og de som refererer til den)
få_rader_referanser - søk etter rader i andre tabeller som refererer til spesifiserte rader i ønsket tabell

forhistorie

Mitt navn er Oleg Borzov, jeg er utvikler i CRM-teamet for boliglånsforvaltere i Domklik.

Hoveddatabasen til vårt CRM-system er en av de største volummessig i selskapet. Den er også en av de eldste: den dukket opp ved selve lanseringen av prosjektet, da trærne var store, Domklik var en oppstart, og i stedet for en mikrotjeneste på et fasjonabelt Python asynkront rammeverk var det en enorm monolitt i PHP.

Overgangen fra PHP til Python var veldig lang og krevde samtidig støtte av begge systemene, noe som påvirket utformingen av databasen.

Som et resultat har vi en database med et stort antall høyt tilkoblede og enorme tabeller med en haug med indekser for ulike typer søk. Alt dette påvirker ytelsen til databasen negativt: på grunn av store tabeller og en rekke relasjoner mellom dem, øker kompleksiteten til spørringene stadig, noe som er spesielt kritisk for de mest lastede tabellene.

For å redusere belastningen på databasen bestemte vi oss for å skrive et skript som ville overføre gamle poster fra de mest omfangsrike og lastede tabellene til arkiverte (for eksempel fra task в task_archive).

Denne oppgaven er komplisert av det store antallet relasjoner mellom tabeller: bare flytt rader fra task в task_archive er ikke nok, før det må du gjøre det samme rekursivt med alle de som refererer task tabeller.

Jeg vil demonstrere med et eksempel demodatabase fra nettstedet postgrespro.ru:

La oss si at vi må slette poster fra en tabell Flights. Postgres vil ikke tillate oss å gjøre dette bare slik: vi må først slette poster fra alle referansetabeller, og så videre rekursivt til tabeller som ingen refererer til.

I vårt eksempel kl Flights refererer Ticket_flights, og på henne - Boarding_passes.

Derfor må du slette den i denne rekkefølgen:

Vi får inn primærnøkkelverdiene (PK) for rader Ticket_flights, som refererer til radene som skal slettes i Flights.
Vi får PK-rekker Boarding_passes, som refererer til Ticket_flights.
Vi sletter rader etter PK fra trinn 2 i tabellen Boarding_passes.
Slett linjer etter PK fra trinn 1 in Ticket_flights.
Fjerne linjer fra Flights.

Resultatet ble et verktøy kalt PgGraph, som vi bestemte oss for å lage åpen kildekode.

Hvordan bruke

Verktøyet støtter to bruksmåter:

Ring fra kommandolinjen (pggraph …).
Bruk i Python-kode (klasse PgGraphApi).

Installasjon og konfigurasjon

Først må du installere verktøyet fra Pypi-depotet:

pip3 install pggraph

Lag deretter en config.ini-fil på den lokale maskinen med konfigurasjonen av databasen og arkiveringsskriptet:

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

Kjør fra konsollen

Parametere

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

Posisjonsargumenter:

action - nødvendig handling: archive_table, get_table_references eller get_rows_references.

Navngitte argumenter:

--config_path — bane til konfigurasjonsfilen;
--table — en tabell som du trenger for å utføre en handling;
--ids – liste over id atskilt med komma, for eksempel, 1,2,3 (valgfri parameter);
--log_path — bane til mappen for logger (valgfri parameter, som standard — hjemmemappe);
--log_level — loggingsnivå (valgfri parameter, standard er INFO).

Eksempler på kommandoer

Arkivering av en tabell

Hovedfunksjonen til verktøyet er dataarkivering, dvs. overføring av rader fra hovedtabellen til arkivtabellen (for eksempel fra tabellen bøker в bøker_arkiv).

Sletting uten arkivering støttes også: for dette må du sette parameteren i config.ini to_archive = usant).

Nødvendige parametere - config_path, tabell og IDer.

Etter lansering vil postene bli slettet rekursivt ids i bordet table og i alle tabeller som refererer til det.

$ 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

Finn avhengigheter for en spesifisert tabell

Funksjon for å finne avhengigheter til en spesifisert tabell table. Nødvendige parametere - config_path и table.

Etter lansering vil en ordbok vises på skjermen, hvor:

in_refs - en ordbok med tabeller som refererer til en gitt, hvor nøkkelen er navnet på tabellen, verdien er en liste over fremmednøkkelobjekter (pk_main - primærnøkkel i hovedtabellen, pk_ref - primærnøkkel i referansetabellen, fk_ref — navnet på kolonnen som er fremmednøkkelen til kildetabellen);
out_refs — en ordbok over tabeller denne refererer til.

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

Finne referanser til strenger med den angitte primærnøkkelen

Funksjon for å søke etter rader i andre tabeller som refererer til rader via fremmednøkkel ids tabeller table. Nødvendige parametere - config_path, table и ids.

Etter lansering vil en ordbok med følgende struktur vises på skjermen:

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

Eksempelanrop:

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

Bruk i kode

I tillegg til å kjøre det i konsollen, kan biblioteket brukes i Python-kode. Eksempler på samtaler i det interaktive iPython-miljøet er vist nedenfor.

Arkivering av en tabell

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

Finn avhengigheter for en spesifisert tabell

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

Finne referanser til strenger med den angitte primærnøkkelen

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

Bibliotekets kildekode er tilgjengelig på GitHub under MIT-lisens, så vel som i depotet PyPI.

Jeg vil gjerne ha kommentarer, forpliktelser og forslag.

Jeg vil prøve å svare på spørsmål etter beste evne her og i depotet.

Kilde: www.habr.com

PgGraph er et verktøy for å arkivere og finne tabellavhengigheter i PostgreSQL

forhistorie

Hvordan bruke

Installasjon og konfigurasjon

Kjør fra konsollen

Parametere

Eksempler på kommandoer

Arkivering av en tabell

Finn avhengigheter for en spesifisert tabell

Finne referanser til strenger med den angitte primærnøkkelen

Bruk i kode

Arkivering av en tabell

Finn avhengigheter for en spesifisert tabell

Finne referanser til strenger med den angitte primærnøkkelen

Legg til en kommentar Avbryt svar