🥇PgGraph - apuohjelma taulukkoriippuvuuksien arkistointiin ja etsimiseen PostgreSQL:ssä

Tänään haluan esitellä Habr-lukijalle Pythonilla kirjoitetun apuohjelman taulukkoriippuvuuksien kanssa työskentelemiseen PostgreSQL DBMS:ssä.

Apuohjelman API on yksinkertainen ja koostuu kolmesta menetelmästä:

arkisto_taulukko - Rekursiivinen arkistointi / poistaminen rivit määritellyt ensisijaiset avaimet
get_table_references - etsi taulukon riippuvuuksia (näyttää taulukot, joihin määritetty taulukko viittaa, ja ne, jotka viittaavat siihen)
get_rows_references - etsiä muista taulukoista rivejä, jotka viittaavat halutun taulukon tiettyihin riveihin

esihistoria

Nimeni on Oleg Borzov, olen kehittäjä Domklikin asuntolainajohtajien CRM-tiimissä.

CRM-järjestelmämme päätietokanta on volyymiltaan yksi yrityksen suurimmista. Se on myös yksi vanhimmista: se ilmestyi heti projektin käynnistyessä, kun puut olivat isoja, Domklik oli startup, ja muodikkaan Python-asynkronisen kehyksen mikropalvelun sijaan PHP:ssä oli valtava monoliitti.

Siirtyminen PHP:stä Pythoniin oli erittäin pitkä ja vaati molempien järjestelmien samanaikaista tukea, mikä vaikutti tietokannan suunnitteluun.

Tämän seurauksena meillä on tietokanta, jossa on suuri määrä hyvin yhteydessä olevia ja valtavia taulukoita, joissa on joukko indeksejä erityyppisille kyselyille. Kaikki tämä vaikuttaa negatiivisesti tietokannan suorituskykyyn: suurten taulukoiden ja niiden välisten suhteiden vuoksi kyselyiden monimutkaisuus kasvaa jatkuvasti, mikä on erityisen kriittistä eniten ladatuille taulukoille.

Tietokannan kuormituksen vähentämiseksi päätimme kirjoittaa skriptin, joka siirtää vanhat tietueet suurikokoisimmista ja ladatuimmista taulukoista arkistoituihin (esim. task в task_archive).

Tätä tehtävää vaikeuttaa taulukkojen välisten suhteiden suuri määrä: siirrä vain rivejä alkaen task в task_archive ei riitä, sitä ennen sinun on tehtävä sama rekursiivisesti kaikkien viittaavien kanssa task taulukoita.

Esitän esimerkin demo-tietokanta sivustolta postgrespro.ru:

Oletetaan, että meidän on poistettava tietueita taulukosta Flights. Postgres ei salli meidän tehdä tätä vain sillä tavalla: meidän on ensin poistettava tietueet kaikista viittaustaulukoista ja niin edelleen rekursiivisesti taulukoihin, joihin kukaan ei viittaa.

Esimerkissämme osoitteessa Flights viittaa Ticket_flightsja hänen päällä - Boarding_passes.

Siksi sinun on poistettava se tässä järjestyksessä:

Saamme rivien ensisijaisten avainten (PK) arvot Ticket_flights, jotka viittaavat poistettaviin riveihin Flights.
Saamme PK-rivejä Boarding_passes, jotka viittaavat Ticket_flights.
Poistamme rivit PK:n mukaan taulukon vaiheesta 2 Boarding_passes.
Poista rivit PK:lla vaiheesta 1 Ticket_flights.
Viivojen poistaminen kohteesta Flights.

Tuloksena oli apuohjelma nimeltä PgGraph, josta päätimme tehdä avoimen lähdekoodin.

Kuinka käyttää

Apuohjelma tukee kahta käyttötapaa:

Soita komentoriviltä (pggraph …).
Käyttö Python-koodissa (luokka PgGraphApi).

Asennus ja konfigurointi

Ensin sinun on asennettava apuohjelma Pypi-arkistosta:

pip3 install pggraph

Luo sitten paikalliseen koneeseen config.ini-tiedosto tietokannan määrityksillä ja arkistointikomentosarjalla:

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

Suorita konsolista

Parametrit

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

Asema-argumentit:

action - vaadittava toimenpide: archive_table, get_table_references tai get_rows_references.

Nimetyt argumentit:

--config_path - polku asetustiedostoon;
--table — pöytä, jonka kanssa sinun on suoritettava toiminto;
--ids - luettelo tunnisteista pilkuilla erotettuna, esim. 1,2,3 (valinnainen parametri);
--log_path — polku lokikansioon (valinnainen parametri, oletusarvoisesti kotikansio);
--log_level — kirjaustaso (valinnainen parametri, oletus on INFO).

Esimerkkejä komentoista

Pöydän arkistointi

Apuohjelman päätehtävä on tietojen arkistointi, ts. rivien siirtäminen päätaulukosta arkistotaulukkoon (esimerkiksi taulukosta kirjat в kirjat_arkisto).

Myös poistaminen ilman arkistointia on tuettu: tätä varten sinun on asetettava parametri tiedostossa config.ini to_archive = false).

Vaaditut parametrit - config_path, taulukko ja tunnukset.

Käynnistyksen jälkeen tietueet poistetaan rekursiivisesti ids taulukossa table ja kaikissa siihen viittaavissa taulukoissa.

$ 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

Etsi riippuvuuksia määritetylle taulukolle

Toiminto tietyn taulukon riippuvuuksien etsimiseen table. Vaaditut parametrit - config_path и table.

Käynnistyksen jälkeen näytölle tulee sanakirja, jossa:

in_refs — tiettyyn taulukkoon viittaavien taulukoiden sanakirja, jossa avain on taulukon nimi, arvo on lista vierasavainobjekteista (pk_main - ensisijainen avain päätaulukossa, pk_ref - ensisijainen avain viitetaulukossa, fk_ref — sen sarakkeen nimi, joka on lähdetaulukon vierasavain);
out_refs — taulukoiden sanakirja, johon tämä viittaa.

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

Etsitään viittauksia merkkijonoihin määritetyllä ensisijaisella avaimella

Toiminto, jolla voit etsiä rivejä muista taulukoista, jotka viittaavat riveihin vierasavaimella ids taulukot table. Vaaditut parametrit - config_path, table и ids.

Käynnistyksen jälkeen näytölle tulee seuraavan rakenteen mukainen sanakirja:

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

Esimerkki puhelusta:

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

Käyttö koodissa

Konsolissa ajamisen lisäksi kirjastoa voidaan käyttää Python-koodissa. Alla on esimerkkejä puheluista interaktiivisessa iPython-ympäristössä.

Pöydän arkistointi

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

Etsi riippuvuuksia määritetylle taulukolle

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

Etsitään viittauksia merkkijonoihin määritetyllä ensisijaisella avaimella

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

Kirjaston lähdekoodi on saatavilla osoitteessa GitHub MIT-lisenssillä sekä arkistossa PyPI.

Otan mielelläni vastaan kommentteja, sitoumuksia ja ehdotuksia.

Yritän vastata kysymyksiin parhaani mukaan täällä ja arkistossa.

Lähde: will.com

PgGraph on apuohjelma taulukkoriippuvuuksien arkistointiin ja etsimiseen PostgreSQL:ssä

esihistoria

Kuinka käyttää

Asennus ja konfigurointi

Suorita konsolista

Parametrit

Esimerkkejä komentoista

Pöydän arkistointi

Etsi riippuvuuksia määritetylle taulukolle

Etsitään viittauksia merkkijonoihin määritetyllä ensisijaisella avaimella

Käyttö koodissa

Pöydän arkistointi

Etsi riippuvuuksia määritetylle taulukolle

Etsitään viittauksia merkkijonoihin määritetyllä ensisijaisella avaimella

Lisää kommentti Peruuta vastaus