ProHoster > Blog > Pangangasiwa > Ang PgGraph ay isang utility para sa pag-archive at paghahanap ng mga dependency ng talahanayan sa PostgreSQL

Ang PgGraph ay isang utility para sa pag-archive at paghahanap ng mga dependency ng talahanayan sa PostgreSQL

Ang PgGraph ay isang utility para sa pag-archive at paghahanap ng mga dependency ng talahanayan sa PostgreSQL
Ngayon nais kong ipakita sa mga mambabasa ng Habr ang isang utility na nakasulat sa Python para sa pagtatrabaho sa mga dependency ng talahanayan sa PostgreSQL DBMS.

Ang API ng utility ay simple at binubuo ng tatlong pamamaraan:

  • archive_table - recursive na pag-archive/pagtanggal ng mga row na may tinukoy na Pangunahing Key
  • get_table_references β€” maghanap ng mga dependency para sa isang talahanayan (ipapakita ang mga talahanayan na isinangguni ng tinukoy at ang mga tumutukoy dito)
  • get_rows_references - maghanap ng mga row sa ibang mga table na tumutukoy sa mga tinukoy na row sa gustong table

prehistory

Ang pangalan ko ay Oleg Borzov, isa akong developer sa CRM team para sa mga manager ng mortgage lending sa Domklik.

Ang pangunahing database ng aming CRM system ay isa sa pinakamalaki sa mga tuntunin ng volume sa kumpanya. Isa rin ito sa pinakaluma: lumitaw ito sa mismong paglulunsad ng proyekto, nang malalaki ang mga puno, ang Domklik ay isang startup, at sa halip na isang microservice sa isang naka-istilong Python asynchronous na balangkas ay mayroong isang malaking monolith sa PHP.

Ang paglipat mula sa PHP patungo sa Python ay napakatagal at nangangailangan ng sabay-sabay na suporta ng parehong mga sistema, na nakaapekto sa disenyo ng database.

Bilang resulta, mayroon kaming database na may malaking bilang ng lubos na konektado at malalaking talahanayan na may grupo ng mga index para sa iba't ibang uri ng mga query. Ang lahat ng ito ay negatibong nakakaapekto sa pagganap ng database: dahil sa malalaking talahanayan at isang bungkos ng mga ugnayan sa pagitan ng mga ito, ang pagiging kumplikado ng mga query ay patuloy na tumataas, na partikular na kritikal para sa mga pinaka-load na mga talahanayan.

Upang bawasan ang pag-load sa database, nagpasya kaming magsulat ng script na maglilipat ng mga lumang talaan mula sa pinakamarami at na-load na mga talahanayan patungo sa mga naka-archive (halimbawa, mula sa task Π² task_archive).

Ang gawaing ito ay kumplikado sa pamamagitan ng malaking bilang ng mga ugnayan sa pagitan ng mga talahanayan: ilipat lamang ang mga hilera mula sa task Π² task_archive ay hindi sapat, bago iyon kailangan mong gawin ang parehong recursively sa lahat ng mga reference task mga mesa.

Ipapakita ko sa isang halimbawa demo database mula sa site na postgrespro.ru:

Ang PgGraph ay isang utility para sa pag-archive at paghahanap ng mga dependency ng talahanayan sa PostgreSQL
Sabihin nating kailangan nating magtanggal ng mga tala mula sa isang talahanayan Flights. Hindi kami papayagan ng mga postgres na gawin ito nang ganoon lang: kailangan muna naming tanggalin ang mga talaan mula sa lahat ng mga talahanayan ng pagtukoy, at iba pa nang paulit-ulit sa mga talahanayan na walang tinutukoy.

Sa aming halimbawa sa Flights tumutukoy Ticket_flights, at sa kanya - Boarding_passes.

Samakatuwid, kailangan mong tanggalin ito sa ganitong pagkakasunud-sunod:

  1. Nakukuha namin ang mga pangunahing susi (PK) na halaga ng mga hilera Ticket_flights, na tumutukoy sa mga row na tatanggalin Flights.
  2. Nakukuha namin ang mga hilera ng PK Boarding_passes, na tumutukoy sa Ticket_flights.
  3. Tinatanggal namin ang mga hilera ayon sa PK mula sa hakbang 2 sa talahanayan Boarding_passes.
  4. Tanggalin ang mga linya ayon sa PK mula sa hakbang 1 sa Ticket_flights.
  5. Pag-alis ng mga linya mula sa Flights.

Ang resulta ay isang utility na tinatawag na PgGraph, na nagpasya kaming gawing open source.

Paano gamitin

Sinusuportahan ng utility ang dalawang mode ng paggamit:

  • Tawag mula sa command line (pggraph …).
  • Paggamit sa Python code (class PgGraphApi).

Pag-install at pagsasaayos

Una kailangan mong i-install ang utility mula sa Pypi repository:

pip3 install pggraph

Pagkatapos ay lumikha ng isang config.ini file sa lokal na makina na may configuration ng database at ang script ng pag-archive:

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

Tumakbo mula sa console

Mga Parameter

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

Mga posisyong argumento:

  • action - kinakailangang aksyon: archive_table, get_table_references o get_rows_references.

Mga pinangalanang argumento:

  • --config_path β€” landas sa config file;
  • --table β€” isang talahanayan kung saan kailangan mong magsagawa ng isang aksyon;
  • --ids β€” listahan ng id na pinaghihiwalay ng mga kuwit, halimbawa, 1,2,3 (opsyonal na parameter);
  • --log_path β€” landas sa folder para sa mga log (opsyonal na parameter, bilang default β€” folder ng bahay);
  • --log_level β€” antas ng pag-log (opsyonal na parameter, ang default ay INFO).

Mga halimbawa ng utos

Pag-archive ng talahanayan

Ang pangunahing pag-andar ng utility ay ang pag-archive ng data, i.e. paglilipat ng mga hilera mula sa pangunahing talahanayan patungo sa talahanayan ng archive (halimbawa, mula sa talahanayan mga libro Π² books_archive).

Sinusuportahan din ang pagtanggal nang walang pag-archive: para dito kailangan mong itakda ang parameter sa config.ini to_archive = mali).

Mga kinakailangang parameter - config_path, talahanayan at mga id.

Pagkatapos ng paglunsad, ang mga tala ay muling tatanggalin ids sa mesa table at sa lahat ng mga talahanayan na tumutukoy dito.

$ 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

Maghanap ng mga dependency para sa isang tinukoy na talahanayan

Pag-andar upang mahanap ang mga dependency ng isang tinukoy na talahanayan table. Mga kinakailangang parameter - config_path ΠΈ table.

Pagkatapos ng paglunsad, may ipapakitang diksyunaryo sa screen, kung saan:

  • in_refs β€” isang diksyunaryo ng mga talahanayan na tumutukoy sa isang ibinigay, kung saan ang susi ay ang pangalan ng talahanayan, ang halaga ay isang listahan ng mga bagay na Foreign Key (pk_main - pangunahing susi sa pangunahing talahanayan, pk_ref - pangunahing susi sa reference table, fk_ref β€” ang pangalan ng column na foreign key sa source table);
  • out_refs β€” isang diksyunaryo ng mga talahanayan na tinutukoy ng isang ito.

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

Paghahanap ng mga sanggunian sa mga string na may tinukoy na Pangunahing Key

Function na maghanap ng mga row sa iba pang mga table na tumutukoy sa mga row sa pamamagitan ng Foreign Key ids mga talahanayan table. Mga kinakailangang parameter - config_path, table ΠΈ ids.

Pagkatapos ng paglunsad, ang isang diksyunaryo na may sumusunod na istraktura ay ipapakita sa screen:

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

Halimbawa ng tawag:

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

Paggamit sa code

Bilang karagdagan sa pagpapatakbo nito sa console, ang library ay maaaring gamitin sa Python code. Ang mga halimbawa ng mga tawag sa iPython interactive na kapaligiran ay ipinapakita sa ibaba.

Pag-archive ng talahanayan

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

Maghanap ng mga dependency para sa isang tinukoy na talahanayan

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

Paghahanap ng mga sanggunian sa mga string na may tinukoy na Pangunahing 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'}]}}}

Available ang source code ng library sa GitHub sa ilalim ng lisensya ng MIT, gayundin sa imbakan PyPI.

Ako ay natutuwa sa mga komento, pangako at mungkahi.

Susubukan kong sagutin ang mga tanong sa abot ng aking makakaya dito at sa repositoryo.

Pinagmulan: www.habr.com

Magdagdag ng komento