PgGraph — een tool voor archivering en het zoeken naar afhankelijkheden van tabellen in PostgreSQL

Vandaag wil ik de lezers van Habr een Python-hulpmiddel presenteren voor het werken met tabelafhankelijkheden in de PostgreSQL-database.

De API van de tool is eenvoudig en bestaat uit drie methoden:

• archive_table — recursieve archivering/verwijdering van rijen met opgegeven primaire sleutels
• get_table_references — zoeken naar afhankelijkheden voor een tabel (toont de tabellen waarop de opgegeven tabel verwijst en die naar haar verwijzen)
• get_rows_references — zoeken naar rijen in andere tabellen die verwijzen naar de opgegeven rijen in de gewenste tabel

Achtergrond

Mijn naam is Oleg Borzov, ik ben ontwikkelaar in het CRM-team voor hypotheekmanagers bij Domclick.

De primaire database van ons CRM-systeem is een van de grootste in volume binnen het bedrijf. Het is ook een van de oudste: het werd opgericht bij de lancering van het project, toen de bomen groot waren, en Domclick een startup was, en in plaats van microservices op een trendy Python-asynchrone framework was er een enorme monoliet in PHP.

De overstap van PHP naar Python was zeer langdurig en vereiste gelijktijdige ondersteuning van beide systemen, wat effect had op het ontwerp van de database.

Als gevolg hiervan hebben we een database met een groot aantal sterk verbonden en enorme tabellen met veel indexen voor verschillende soorten verzoeken. Dit heeft een negatieve invloed op de prestaties van de database: door grote tabellen en veel onderlinge verbindingen groeit de complexiteit van de query's voortdurend, wat vooral kritiek is voor de meest belaste tabellen.

Om de belasting van de database te verminderen, besloten we een script te schrijven dat dagelijks via cron oude records vanuit de grootste en meest belastende tabellen naar archieven zou verplaatsen (bijvoorbeeld vanaf task in task_archive).

Deze taak wordt gecompliceerd door het grote aantal verbindingen tussen tabellen: het is niet voldoende om simpelweg rijen te verplaatsen uit task in task_archive voordat we hetzelfde recursief moeten doen met alle verwijzende task tabellen.

Ik zal het demonstreren met een voorbeeld van de demonstratiedatabase van de site postgrespro.ru:

Stel dat we records uit de tabel willen verwijderen Flights. Gewoon zo doen, dat laat Postgres ons niet toe: we moeten eerst de records verwijderen uit alle verwijzende tabellen, en zo recursief doen tot de tabellen waarop niemand verwijst.

In ons voorbeeld is dat de Flights wordt verwezen Ticket_flights, en daarop bevindt zich Boarding_passes.

Daarom moeten we in deze volgorde verwijderen:

1. We halen de primaire sleutels (Primary Keys, PK) van de rijen in Ticket_flights, die verwijzen naar de te verwijderen rijen in Flights.
2. We halen de PK van de rijen Boarding_passes, die verwijzen naar Ticket_flights.
3. Verwijder rijen op PK uit punt 2 in de tabel Boarding_passes.
4. Verwijder rijen op PK uit punt 1 in Ticket_flights.
5. Verwijder rijen uit Flights.

Uiteindelijk hebben we een hulpprogramma genaamd PgGraph gemaakt, dat we open source hebben besloten te maken.

Hoe te gebruiken

Het hulpprogramma ondersteunt twee gebruiksmodi:

• Aanroep vanuit de opdrachtregel (pggraph …).
• Gebruik in Python-code (klasse PgGraphApi).

Installatie en configuratie

Eerst moet het hulpprogramma worden geïnstalleerd vanuit de Pypi-repository:

pip3 install pggraph

Creëer vervolgens een config.ini-bestand op de lokale machine met de databaseconfiguratie en het archiveringsscript:

[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; Optionele parameter, standaardwaarde opgegeven

[archive]  ; Dit gedeelte kan optioneel worden ingevuld, hieronder staan de standaardinstellingen
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'

Uitvoering vanuit de console

Instellingen

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

optional arguments:
  -h, --help                    toon deze helptekst en verlaat
  --table TABLE                 tabelnaam
  --ids IDS                     primaire sleutel id's, gescheiden door een komma, bijv. 1,2,3
  --config_path CONFIG_PATH     pad naar config.ini
  --log_path LOG_PATH           pad naar logdirectory
  --log_level LOG_LEVEL         logniveau (debug, info, error)

Positie argumenten:

• actie — vereiste actie: archive_table, get_table_references of get_rows_references.

Genomineerde argumenten:

• --config_path — pad naar het configuratiebestand;
• --table — tabel waarop de actie moet worden uitgevoerd;
• --ids — lijst van id's gescheiden door komma's, bijvoorbeeld, 1,2,3 (optioneel parameter);
• --log_path — pad naar de logdirectory (optioneel parameter, standaard is de thuismap);
• --log_level — logniveau (optioneel parameter, standaard is INFO).

Command examples

Tabel archivering

De belangrijkste functie van het hulpprogramma is de archivering van gegevens, dat wil zeggen het verplaatsen van rijen van de hoofdtafel naar de archieftafel (bijvoorbeeld van de tabel boeken in boeken_archief).

Verwijdering zonder archivering wordt ook ondersteund: hiervoor moet de parameter to_archive = false).

Vereiste parameters — config_path, table en ids.

Na uitvoering worden de records met de id's en in alle tabellen die ernaar verwijzen, recursief verwijderd. in de tabel table en alle tabellen waarnaar wordt verwezen.

$ 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

Zoek afhankelijkheden voor de opgegeven tabel

Functie voor het zoeken naar de afhankelijkheden van een opgegeven tabel table. Verplichte parameters zijn config_path en table.

Na het starten verschijnt er een woordenboek op het scherm, waarin:

• in_refs — een woordenboek van verwijzende tabellen naar deze, waarbij de sleutel de tabelnaam is en de waarde een lijst van Foreign Key objecten (pk_main — de primaire sleutel in de hoofdtafel, pk_ref — de primaire sleutel in de verwijzende tabel, fk_ref — de naam van de kolom die de foreign key naar de oorspronkelijke tabel is);
• out_refs — een woordenboek van tabellen waarop deze verwijst.

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

Zoek naar links naar rijen met de opgegeven Primary Key

Functie voor het zoeken naar rijen in andere tabellen die verwijzen via Foreign Key naar rijen en in alle tabellen die ernaar verwijzen, recursief verwijderd. van de tabel table. Verplichte parameters zijn config_path, table en en in alle tabellen die ernaar verwijzen, recursief verwijderd..

Na het starten verschijnt er een woordenboek met de volgende structuur op het scherm:

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

Voorbeeld van aanroep:

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

Gebruik in de code

Naast het uitvoeren in de console kan de bibliotheek ook in Python-code worden gebruikt. Hieronder staan voorbeelden van aanroepen in de interactieve omgeving iPython.

Tabel archivering

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

Zoek afhankelijkheden voor de opgegeven tabel

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

Zoek naar links naar rijen met de opgegeven Primary Key

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

De broncode van de bibliotheek is beschikbaar op GitHub onder de MIT-licentie, evenals in de repository PyPI.

Ik sta open voor opmerkingen, commits en suggesties.

Ik zal proberen vragen te beantwoorden hier en in de repository voor zover mogelijk.

Bron: habr.com