🥇PgGraph - një mjet për arkivimin dhe kërkimin e varësive të tabelave në PostgreSQL

Sot dua t'u prezantoj lexuesve të Habr një mjet të shkruar në Python për të punuar me varësitë e tabelave në PostgreSQL DBMS.

API-ja e programit është e thjeshtë dhe përbëhet nga tre metoda:

arkiv_tabela - arkivimi rekurziv/fshirja e rreshtave me çelësat kryesorë të specifikuar
merrni_tabelë_referencat — kërkoni për varësi për një tabelë (do të tregojë tabelat e referuara nga ajo e specifikuar dhe ato që i referohen asaj)
get_rows_references — поиск строк в других таблицах, которые ссылаются на указанные строки в нужной таблице

parahistorinë

Emri im është Oleg Borzov, unë jam një zhvillues në ekipin CRM për menaxherët e huadhënies hipotekore në Domklik.

Baza e të dhënave kryesore e sistemit tonë CRM është një nga më të mëdhatë për sa i përket vëllimit në kompani. Është gjithashtu një nga më të vjetrit: u shfaq në fillimin e projektit, kur pemët ishin të mëdha, Domklik ishte një startup, dhe në vend të një mikroshërbimi në një kornizë asinkrone në modë Python, kishte një monolit të madh në PHP.

Kalimi nga PHP në Python ishte shumë i gjatë dhe kërkonte mbështetje të njëkohshme të të dy sistemeve, gjë që ndikoi në hartimin e bazës së të dhënave.

В результате мы имеем базу с большим количеством сильно связанных и огромных по размерам таблиц с кучей индексов под разные типы запросов. Всё это негативно сказывается на производительности БД: из-за больших таблиц и кучи связей между ними постоянно растет сложность запросов, что особенно критично для самых нагруженных таблиц.

Для снижения нагрузки на БД мы решили написать скрипт, который бы ежедневно по крону переносил старые записи из самых объемных и нагруженных таблиц в архивные (например, из task в task_archive).

Kjo detyrë është e ndërlikuar nga numri i madh i marrëdhënieve midis tabelave: thjesht lëvizni rreshtat nga task в task_archive nuk mjafton, para kësaj ju duhet të bëni të njëjtën gjë në mënyrë rekursive me të gjithë ata që referojnë task таблицами.

Unë do ta demonstroj me një shembull baza e të dhënave demo nga faqja postgrespro.ru:

Le të themi se duhet të fshijmë të dhënat nga një tabelë Flights. Postgres nuk do të na lejojë ta bëjmë këtë ashtu si kjo: së pari duhet të fshijmë të dhënat nga të gjitha tabelat e referencës, dhe kështu me radhë në mënyrë rekursive deri te tabelat që nuk janë referuar nga askush.

Në shembullin tonë në Flights referohet Ticket_flights, dhe mbi të - Boarding_passes.

Prandaj, duhet ta fshini në këtë mënyrë:

Ne marrim vlerat e çelësave kryesorë (PK) të rreshtave Ticket_flights, të cilat i referohen rreshtave që do të fshihen Flights.
Получаем PK строк Boarding_passes, të cilat i referohen Ticket_flights.
Ne fshijmë rreshtat sipas PK nga hapi 2 në tabelë Boarding_passes.
Fshi linjat nga PK nga hapi 1 in Ticket_flights.
Heqja e linjave nga Flights.

Rezultati ishte një mjet i quajtur PgGraph, të cilin vendosëm ta bënim me burim të hapur.

Si të përdorni

Programi mbështet dy mënyra përdorimi:

Thirrni nga linja e komandës (pggraph …).
Përdorimi në kodin Python (klasa PgGraphApi).

Instalimi dhe konfigurimi

Së pari ju duhet të instaloni programin nga depoja e Pypi:

pip3 install pggraph

Pastaj krijoni një skedar config.ini në makinën lokale me konfigurimin e bazës së të dhënave dhe skriptin e arkivimit:

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

Drejtoni nga tastiera

Parameters

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

Argumentet e pozicionit:

action - veprimet e nevojshme: archive_table, get_table_references ose get_rows_references.

Argumentet e emërtuara:

--config_path - rruga për në skedarin e konfigurimit;
--table — një tabelë me të cilën duhet të kryeni një veprim;
--ids - lista e ID-së e ndarë me presje, për shembull, 1,2,3 (parametër opsional);
--log_path - rruga për në dosjen për regjistrat (parametri opsional, si parazgjedhje - dosja kryesore);
--log_level — Niveli i regjistrimit (parametri opsional, parazgjedhja është INFO).

Shembuj të komandave

Arkivimi i një tabele

Funksioni kryesor i programit është arkivimi i të dhënave, d.m.th. transferimi i rreshtave nga tabela kryesore në tabelën e arkivit (për shembull, nga tabela libra в libra_arkiv).

Fshirja pa arkivim gjithashtu mbështetet: për këtë ju duhet të vendosni parametrin në config.ini te_arkiv = false).

Parametrat e kërkuar - config_path, tabelë dhe ID.

Pas nisjes, të dhënat do të fshihen në mënyrë rekursive ids në tabelë table dhe në të gjitha tabelat që i referohen.

$ 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

Gjeni varësi për një tabelë të caktuar

Funksioni për të gjetur varësitë e një tabele të caktuar table. Parametrat e kërkuar - config_path и table.

Pas nisjes, një fjalor do të shfaqet në ekran, ku:

in_refs - një fjalor tabelash që i referohen një të caktuar, ku çelësi është emri i tabelës, vlera është një listë e objekteve të çelësit të huaj (pk_main - çelësi kryesor në tabelën kryesore, pk_ref - çelësi kryesor në tabelën e referencës, fk_ref — emri i kolonës që është çelësi i huaj për tabelën burimore);
out_refs — një fjalor tabelash të cilit i referohet ky.

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

Gjetja e referencave për vargjet me çelësin primar të specifikuar

Funksioni për të kërkuar rreshta në tabela të tjera që u referohen rreshtave nëpërmjet çelësit të huaj ids таблицы table. Parametrat e kërkuar - config_path, table и ids.

Pas nisjes, një fjalor me strukturën e mëposhtme do të shfaqet në ekran:

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

Shembull i thirrjes:

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

Përdorimi në kod

Përveç ekzekutimit të tij në tastierë, biblioteka mund të përdoret në kodin Python. Shembuj të thirrjeve në mjedisin interaktiv iPython tregohen më poshtë.

Arkivimi i një tabele

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

Gjeni varësi për një tabelë të caktuar

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

Gjetja e referencave për vargjet me çelësin primar të specifikuar

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

Kodi burimor i bibliotekës është në dispozicion në GitHub me licencë MIT, si dhe në depo PyPI.

Do të jem i lumtur për komentet, angazhimet dhe sugjerimet.

Do të përpiqem t'u përgjigjem pyetjeve sa më mirë që kam mundësi këtu dhe në depo.

Burimi: www.habr.com

PgGraph është një mjet për arkivimin dhe gjetjen e varësive të tabelave në PostgreSQL

parahistorinë

Si të përdorni

Instalimi dhe konfigurimi

Drejtoni nga tastiera

Parameters

Shembuj të komandave

Arkivimi i një tabele

Gjeni varësi për një tabelë të caktuar

Gjetja e referencave për vargjet me çelësin primar të specifikuar

Përdorimi në kod

Arkivimi i një tabele

Gjeni varësi për një tabelë të caktuar

Gjetja e referencave për vargjet me çelësin primar të specifikuar

Shto një koment Cancel përgjigje