PgGraph — nástroj pro archivaci a vyhledávání závislostí tabulek v PostgreSQL

Dnes chci představit čtenářům Habr nástroj napsaný v Pythonu pro práci se závislostmi tabulek v databázi PostgreSQL.

API nástroje je jednoduché a skládá se ze tří metod:

• archive_table — rekurzivní archivace/odstraňování řádků s uvedenými primárními klíči
• get_table_references — hledání závislostí pro tabulku (ukáže tabulky, na které se odkazuje zadaná tabulka a které na ni odkazují)
• get_rows_references — hledání řádků v jiných tabulkách, které se odkazují na zadané řádky ve vyžadované tabulce

Pozadí

Jmenuji se Oleg Borzov, jsem vývojář v týmu CRM pro manažery hypotečního financování v Domkliku.

Hlavní databáze našeho CRM systému je jednou z největších objemově ve firmě. Je také jednou z nejstarších: vznikla při samotném spuštění projektu, když stromy byly velké, Domklik byl startupem a místo mikroservisu na módním pythonovém asynchronním frameworku byl obrovský monolit na PHP.

Přechod z PHP na Python byl velmi dlouhý a vyžadoval současnou podporu obou systémů, což se projevilo na návrhu databáze.

V důsledku toho máme databázi s velkým počtem silně propojených a obrovských tabulek s množstvím indexů pro různé typy dotazů. To vše negativně ovlivňuje výkon databáze: kvůli velkým tabulkám a množství vazeb mezi nimi neustále roste složitost dotazů, což je obzvlášť kritické pro nejzatíženější tabulky.

Pro snížení zátěže na databázi jsme se rozhodli napsat skript, který by každý den pomocí cron přenášel staré záznamy z nejobjemnějších a nejvíce zatížených tabulek do archivu (např. z task v task_archive).

Tento úkol se komplikuje velkým množstvím vazeb mezi tabulkami: jednoduše přenést řádky z task v task_archive je nedostatečné, předtím je potřeba to samé rekurzivně provést se všemi odkazujícími tabulkami. task Ukážu na příkladu

demonstrativní databáze ze stránky postgrespro.ru Předpokládejme, že potřebujeme odstranit záznamy z tabulky:

Flights . Jen tak to udělat Postgres nedovolí: předtím je potřeba odstranit záznamy ze všech odkazujících tabulek, a tak rekurzivně až k tabulkám, na které se nikdo neodkazuje.V našem příkladu na

odkazuje . Jen tak to udělat Postgres nedovolí: předtím je potřeba odstranit záznamy ze všech odkazujících tabulek, a tak rekurzivně až k tabulkám, na které se nikdo neodkazuje. Ticket_flights , a na ni —Boarding_passes Proto je třeba odstraňovat v takovém pořadí:.

Získáme hodnoty primárních klíčů (Primary Keys, PK) řádků v

1. , které se odkazují na odstraňované řádky v , a na ni —Získáme PK řádků . Jen tak to udělat Postgres nedovolí: předtím je potřeba odstranit záznamy ze všech odkazujících tabulek, a tak rekurzivně až k tabulkám, na které se nikdo neodkazuje..
2. Získáváme PK řetězec Proto je třeba odstraňovat v takovém pořadí:, které odkazují na , a na ni —.
3. Odstraníme řádky podle PK z bodu 2 v tabulce Proto je třeba odstraňovat v takovém pořadí:.
4. Odstraníme řádky podle PK z bodu 1 v , a na ni —.
5. Odstraníme řádky z . Jen tak to udělat Postgres nedovolí: předtím je potřeba odstranit záznamy ze všech odkazujících tabulek, a tak rekurzivně až k tabulkám, na které se nikdo neodkazuje..

Nakonec jsme vytvořili nástroj s názvem PgGraph, který jsme se rozhodli udělat open source.

Jak používat

Nástroj podporuje dva režimy použití:

• Volání z příkazového řádku (pggraph …).
• Použití v kódu Python (třída PgGraphApi).

Instalace a konfigurace

Nejprve je třeba nainstalovat nástroj z repozitáře Pypi:

pip3 install pggraph

Poté vytvořte na lokálním počítači soubor config.ini s konfigurací databáze a skriptu archivace:

[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; Nepovinný parametr, je uvedena výchozí hodnota

[archive]  ; Tuto část není nutné vyplňovat, níže jsou uvedeny výchozí hodnoty
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'

Spuštění z konzole

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                    zobrazit tuto nápovědu a ukončit
  --table TABLE                 jméno tabulky
  --ids IDS                     primární klíče oddělené čárkou, např. 1,2,3
  --config_path CONFIG_PATH     cesta k config.ini
  --log_path LOG_PATH           cesta k adresáři s logy
  --log_level LOG_LEVEL         úroveň logování (debug, info, error)

Povinné argumenty:

• akce — požadovaná akce: archive_table, get_table_references nebo get_rows_references.

Pojmenované argumenty:

• --config_path — cesta k konfiguračnímu souboru;
• --table — tabulka, na které je třeba provést akci;
• --ids — seznam id oddělený čárkou, například, 1,2,3 (nepovinný parametr);
• --log_path — cesta k adresáři pro logy (nepovinný parametr, výchozí hodnota je domácí složka);
• --log_level — úroveň logování (nepovinný parametr, výchozí hodnota je INFO).

Příklady příkazů

Archivace tabulky

Hlavní funkcí nástroje je archivace dat, tedy přesun řádků z hlavní tabulky do archivní (například z tabulky books v books_archive).

Také podporuje odstranění bez archivace: pro to je třeba v config.ini nastavit parametr to_archive = false).

Povinné parametry — config_path, table a ids.

Po spuštění budou rekurzivně odstraněny záznamy ids v tabulce table a ve všech tabulkách, které na ni odkazují.

$ pggraph archive_table --config_path config.hw.local.ini --table flights --ids 1,2,3
2020-06-20 19:27:44 INFO: flights - ZAČÁTEK
2020-06-20 19:27:44 INFO: flights - začít archive_recursive 3 řádky (hloubka=0)
2020-06-20 19:27:44 INFO:       ZAČÁTEK ARCHIVACE ODKAZUJÍCÍCH TABULEK
2020-06-20 19:27:44 INFO:       ticket_flights - začít archive_recursive 3 řádky (hloubka=1)
2020-06-20 19:27:44 INFO:               ZAČÁTEK ARCHIVACE ODKAZUJÍCÍCH TABULEK
2020-06-20 19:27:44 INFO:               boarding_passes - začít archive_recursive 3 řádky (hloubka=2)
2020-06-20 19:27:44 INFO:                       ZAČÁTEK ARCHIVACE ODKAZUJÍCÍCH TABULEK
2020-06-20 19:27:44 INFO:                       KONEC ARCHIVACE ODKAZUJÍCÍCH TABULEK
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 řádky podle ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - začít archive_recursive 3 řádky (hloubka=2)
2020-06-20 19:27:44 INFO:                       ZAČÁTEK ARCHIVACE ODKAZUJÍCÍCH TABULEK
2020-06-20 19:27:44 INFO:                       KONEC ARCHIVACE ODKAZUJÍCÍCH TABULEK
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 řádky podle ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - začít archive_recursive 3 řádky (hloubka=2)
2020-06-20 19:27:44 INFO:                       ZAČÁTEK ARCHIVACE ODKAZUJÍCÍCH TABULEK
2020-06-20 19:27:44 INFO:                       KONEC ARCHIVACE ODKAZUJÍCÍCH TABULEK
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 řádky podle ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - začít archive_recursive 3 řádky (hloubka=2)
2020-06-20 19:27:44 INFO:                       ZAČÁTEK ARCHIVACE ODKAZUJÍCÍCH TABULEK
2020-06-20 19:27:44 INFO:                       KONEC ARCHIVACE ODKAZUJÍCÍCH TABULEK
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 řádky podle ticket_no, flight_id
2020-06-20 19:27:44 INFO:               KONEC ARCHIVACE ODKAZUJÍCÍCH TABULEK
2020-06-20 19:27:44 INFO:       ticket_flights - archive_by_ids 3 řádky podle ticket_no, flight_id
2020-06-20 19:27:44 INFO:       KONEC ARCHIVACE ODKAZUJÍCÍCH TABULEK
2020-06-20 19:27:44 INFO: flights - archive_by_ids 3 řádky podle id
2020-06-20 19:27:44 INFO: flights - KONEC

Hledání závislostí pro zadanou tabulku

Funkce pro hledání závislostí zadané tabulky table. Povinné parametry — config_path a table.

Po spuštění se na obrazovku zobrazí slovník, kde:

• in_refs — slovník odkazujících tabulek na tuto, kde klíč — název tabulky, hodnota — seznam objektů Foreign Key (pk_main — primární klíč v hlavní tabulce, pk_ref — primární klíč v odkazující tabulce, fk_ref — název sloupce, který je foreign key na původní tabulku);
• out_refs — slovník tabulek, na které odkazuje tato.

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

Hledání odkazů na řádky s uvedenými primárními klíči

Funkce pro hledání řádků v jiných tabulkách, které odkazují pomocí Foreign Key na řádky ids tabulky table. Povinné parametry — config_path, table a ids.

Po spuštění se na obrazovce zobrazí slovník s následující strukturou:

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

Příklad volání:

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

Použití v kódu

Kromě spuštění v konzoli lze knihovnu používat také v Pythonu. Níže jsou uvedeny příklady volání v interaktivním prostředí iPython.

Archivace tabulky

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

Hledání závislostí pro zadanou tabulku

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

Hledání odkazů na řádky s uvedenými primárními klíč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'}]}}}

Kód knihovny je dostupný na GitHub pod MIT licencí, a také v repozitáři PyPI.

Budu rád za komentáře, commity a návrhy.

Na otázky se pokusím odpovědět podle možností zde a v repozitáři.

Zdroj: habr.com