🥇PgGraph - un'utilità per l'archiviazione e la ricerca delle dipendenze delle tabelle in PostgreSQL

Oggi voglio presentare ai lettori Habr un'utilità scritta in Python per lavorare con le dipendenze delle tabelle nel DBMS PostgreSQL.

L'API dell'utilità è semplice e consiste di tre metodi:

tabella_archivio - archiviazione/eliminazione ricorsiva di righe con chiavi primarie specificate
get_table_references — cerca le dipendenze per una tabella (mostrerà le tabelle a cui fa riferimento quella specificata e quelle che fanno riferimento ad essa)
get_rows_references - cerca righe in altre tabelle che fanno riferimento a righe specificate nella tabella desiderata

Sfondo

Mi chiamo Oleg Borzov, sono uno sviluppatore nel team CRM per gestori di prestiti ipotecari a Domklik.

Il database principale del nostro sistema CRM è uno dei più grandi in termini di volume dell'azienda. È anche uno dei più antichi: è apparso proprio al lancio del progetto, quando gli alberi erano grandi, Domklik era una startup, e invece di un microservizio su un framework asincrono Python alla moda c'era un enorme monolite in PHP.

Il passaggio da PHP a Python è stato molto lungo e ha richiesto il supporto simultaneo di entrambi i sistemi, il che ha influito sulla progettazione del database.

Di conseguenza, abbiamo un database con un gran numero di tabelle enormi e altamente connesse con una serie di indici per diversi tipi di query. Tutto ciò influisce negativamente sulle prestazioni del database: a causa delle tabelle di grandi dimensioni e della serie di relazioni tra loro, la complessità delle query è in costante aumento, il che è particolarmente critico per le tabelle più caricate.

Per ridurre il carico sul database, abbiamo deciso di scrivere uno script che trasferisse i vecchi record dalle tabelle più voluminose e caricate a quelle archiviate (ad esempio, da task в task_archive).

Questo compito è complicato dal gran numero di relazioni tra le tabelle: è sufficiente spostare le righe da task в task_archive non è sufficiente, prima bisogna fare lo stesso in modo ricorsivo con tutti quelli che fanno riferimento task tavoli.

Lo dimostrerò con un esempio database demo dal sito postgrespro.ru:

Diciamo che dobbiamo eliminare i record da una tabella Flights. Postgres non ci permetterà di farlo proprio così: dobbiamo prima eliminare i record da tutte le tabelle di riferimento e così via in modo ricorsivo fino alle tabelle a cui non fa riferimento nessuno.

Nel nostro esempio a Flights richiami Ticket_flights, e su di lei - Boarding_passes.

Pertanto, è necessario eliminarlo in questo ordine:

Otteniamo i valori delle chiavi primarie (PK) delle righe in Ticket_flights, che si riferiscono alle righe da eliminare Flights.
Otteniamo righe PK Boarding_passes, a cui si riferiscono Ticket_flights.
Eliminiamo le righe per PK dal passaggio 2 nella tabella Boarding_passes.
Elimina le righe tramite PK dal passaggio 1 in Ticket_flights.
Rimozione di righe da Flights.

Il risultato è stato un'utilità chiamata PgGraph, che abbiamo deciso di rendere open source.

Come usare

L'utilità supporta due modalità di utilizzo:

Chiama dalla riga di comando (pggraph …).
Utilizzo nel codice Python (class PgGraphApi).

Installazione e configurazione

Per prima cosa devi installare l'utilità dal repository Pypi:

pip3 install pggraph

Quindi crea un file config.ini sul computer locale con la configurazione del database e lo script di archiviazione:

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

Esegui dalla console

Parametri

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

Argomenti posizionali:

action - azione richiesta: archive_table, get_table_references o get_rows_references.

Argomenti denominati:

--config_path — percorso del file di configurazione;
--table — una tabella con la quale è necessario eseguire un'azione;
--ids — elenco di ID separati da virgole, ad esempio, 1,2,3 (parametro facoltativo);
--log_path — percorso della cartella per i registri (parametro opzionale, per impostazione predefinita — cartella home);
--log_level — livello di registrazione (parametro opzionale, il valore predefinito è INFO).

Esempi di comandi

Archiviazione di una tabella

La funzione principale dell'utilità è l'archiviazione dei dati, ad es. trasferire righe dalla tabella principale alla tabella di archivio (ad esempio, da table libri в libri_archivio).

È supportata anche la cancellazione senza archiviazione: per questo è necessario impostare il parametro in config.ini in_archivio = falso).

Parametri richiesti - percorso_config, tabella e ID.

Dopo il lancio, i record verranno eliminati in modo ricorsivo ids nel tavolo table e in tutte le tabelle che ad esso fanno riferimento.

$ 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

Trova le dipendenze per una tabella specificata

Funzione per trovare le dipendenze di una tabella specificata table. Parametri richiesti - config_path и table.

Dopo l'avvio, sullo schermo verrà visualizzato un dizionario in cui:

in_refs — un dizionario di tabelle che fa riferimento a una determinata tabella, dove la chiave è il nome della tabella, il valore è un elenco di oggetti Chiave esterna (pk_main - chiave primaria nella tabella principale, pk_ref - chiave primaria nella tabella di riferimento, fk_ref — il nome della colonna che costituisce la chiave esterna della tabella sorgente);
out_refs - un dizionario di tabelle a cui fa riferimento questo.

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

Trovare riferimenti a stringhe con la chiave primaria specificata

Funzione per cercare righe in altre tabelle che fanno riferimento a righe tramite Foreign Key ids tavoli table. Parametri richiesti - config_path, table и ids.

Dopo il lancio, sullo schermo verrà visualizzato un dizionario con la seguente struttura:

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

Chiamata di esempio:

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

Utilizzo nel codice

Oltre a eseguirla nella console, la libreria può essere utilizzata nel codice Python. Di seguito sono riportati esempi di chiamate nell'ambiente interattivo iPython.

Archiviazione di una tabella

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

Trova le dipendenze per una tabella specificata

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

Trovare riferimenti a stringhe con la chiave primaria specificata

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

Il codice sorgente della libreria è disponibile su GitHub sotto licenza MIT, nonché nel repository PyPI.

Sarò felice di ricevere commenti, impegni e suggerimenti.

Cercherò di rispondere alle domande al meglio delle mie capacità qui e nel repository.

Fonte: habr.com

PgGraph è un'utilità per l'archiviazione e la ricerca delle dipendenze delle tabelle in PostgreSQL

Sfondo

Come usare

Installazione e configurazione

Esegui dalla console

Parametri

Esempi di comandi

Archiviazione di una tabella

Trova le dipendenze per una tabella specificata

Trovare riferimenti a stringhe con la chiave primaria specificata

Utilizzo nel codice

Archiviazione di una tabella

Trova le dipendenze per una tabella specificata

Trovare riferimenti a stringhe con la chiave primaria specificata

Aggiungi un commento Cancella risposta