ProHoster >> Blog >> Administrimi >> PgGraph — njĂ« mjet pĂ«r arkivimin dhe kĂ«rkimin e varĂ«sive tĂ« tabelave nĂ« PostgreSQL

PgGraph — njĂ« mjet pĂ«r arkivimin dhe kĂ«rkimin e varĂ«sive tĂ« tabelave nĂ« PostgreSQL

PgGraph — njĂ« mjet pĂ«r arkivimin dhe kĂ«rkimin e varĂ«sive tĂ« tabelave nĂ« PostgreSQL
Sot dëshiroj të prezantoj me lexuesit e Habrës një mjet të shkruar në Python, për të punuar me varësitë e tabelave në DBMS PostgreSQL.

API i mjetit është i thjeshtë dhe përbëhet nga tre metoda:

  • archive_table — arkivimi/reduktimi recursiv i rreshtave me çelĂ«sa kryesorĂ« tĂ« caktuar
  • get_table_references — kĂ«rkimi i varĂ«sive pĂ«r tabelĂ«n (do tĂ« tregojĂ« tabelat qĂ« referohen nga e cila dhe ato qĂ« referohen te ajo)
  • get_rows_references — kĂ«rkimi i rreshtave nĂ« tabela tĂ« tjera, qĂ« referohen te rreshtat e caktuar nĂ« tabelĂ«n pĂ«rkatĂ«se

Pas historia

Më quajnë Oleg Borzov, jam zhvillues në ekipin CRM për menaxherët e kreditimit hipotetik në Domklik.

Baza e të dhënave kryesore të sistemit tonë CRM është një nga më të mëdhatë në kompani. Ajo është gjithashtu një nga më të vjetrit: u krijua në fillim të projektit, kur pemët ishin të mëdha, Domklik ishte një start-up, dhe në vend të mikroservisave në një framework të njohur asinkron të Python-it, kishte një monolit të madh në PHP.

Kalimi nga PHP në Python ishte shumë i gjatë dhe kërkonte mbështetje të njëkohshme për të dy sistemet, çka ndikonte në projektezimin e bazës së të dhënave.

Si rezultat, ne kemi një bazë me shumë tabela të lidhura fuqishëm dhe shumë të mëdha me shumë indekse për tipe të ndryshme kërkesash. E gjithë kjo ka një ndikim negativ në performancën e DB: për shkak të tabelave të mëdha dhe shumë lidhjeve midis tyre, kompleksiteti i kërkesave vazhdon të rritet, gjë që është veçanërisht kritike për tabelat më të ngarkuara.

Për të reduktuar ngarkesën në DB, vendosëm të shkruajmë një skenar që çdo ditë, nga krone, do të transferonte regjistrimet e vjetra nga tabelat më voluminoze dhe më të ngarkuara në archiva (p.sh., nga task në task_archive).

Kjo detyrë komplikohet nga numri i madh i lidhjeve midis tabelave: thjesht transmetimi i rreshtave nga task në task_archive nuk është e mjaftueshme, para kësaj duhet të bëjmë të njëjtën gjë në mënyrë rekurzive me të gjitha tabelat që referohen në task tabelat.

Do ta demonstroj me shembuj nga DB demo nga website-i postgrespro.ru:

PgGraph — njĂ« mjet pĂ«r arkivimin dhe kĂ«rkimin e varĂ«sive tĂ« tabelave nĂ« PostgreSQL
Supozoni, na duhet të fshijmë regjistrimet nga tabela Flights. Thjesht kështu nuk na lejon Postgres: paraprakisht duhet të fshijmë regjistrimet nga të gjitha tabelat që referohen, dhe kështu në mënyrë rekurzive deri te tabelat në të cilat nuk referohet askush.

NĂ« shembullin tonĂ« nĂ« Flights citohet Ticket_flights, dhe ajo — Boarding_passes.

Prandaj duhet të fshihet në këtë rend.

  1. Marrim vlerat e çelësave primarë (Primary Keys, PK) të rreshtave në Ticket_flights, të cilat referojnë në rreshta që po fshihen në Flights.
  2. Marrim PK të rreshtave Boarding_passes, të cilat referojnë në Ticket_flights.
  3. Fshijmë rreshtat sipas PK nga p.2 në tabelë Boarding_passes.
  4. Fshijmë rreshtat sipas PK nga p.1 në Ticket_flights.
  5. Fshijmë rreshtat nga Flights.

Si rezultat kemi krijuar një utilitet të quajtur PgGraph, të cilin vendosëm ta bëjmë open source.

Si të përdorni

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

  • Thirrje nga linja e komandĂ«s (pggraph 
).
  • PĂ«rdorimi nĂ« kodin Python (klasa PgGraphApi).

Instalimi dhe konfigurimi

Së pari, duhet të instaloni utilitetin nga depo Pypi:

pip3 install pggraph

Më pas krijoni një skedar config.ini në makinë lokale me konfigurimin e DB dhe skriptin e arkivimit:

[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; Parametër i opsional, caktuar vlera standarde

[archive]  ; Ky seksion është opsional, më poshtë janë caktuar vlerat standarde
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'

Ekzekutimi nga console

Parametrat

$ pggraph -h
përdorimi: pggraph veprime [-h] --table TABELA [--ids IDS] [--config_path RRUGA_E_KONFIGURIMI]
argumentet pozicionale:
  veprime      veprim i kërkuar: arkivo_tabelën, merr_referencat_tabelës, merr_referencat_e_rreshtave

argumentet opsionale:
  -h, --help                    shfaq këtë mesazh ndihme dhe dalë
  --table TABELA               emri i tabelës
  --ids IDS                    identifikuesit kryesor, të ndarë me presje, p.sh. 1,2,3
  --config_path RRUGA_E_KONFIGURIMI     rruga për config.ini
  --log_path RRUGA_E_LOGUT           rruga për dosjen e logut
  --log_level NIVELI_I_LOGUT         niveli i logut (debug, info, error)

Argumentet pozicionale:

  • veprim — veprim i kĂ«rkuar: archive_table, get_table_references ose get_rows_references.

Argumentet e emrituara:

  • --config_path — rruga pĂ«r skedarin e konfigurimit;
  • --table — tabela pĂ«r tĂ« cilĂ«n duhet kryer veprimi;
  • --ids — lista e id-ve tĂ« ndara me presje, pĂ«r shembull, 1,2,3 (argument opsional);
  • --log_path — rruga pĂ«r dosjen e logut (argument opsional, nĂ« mĂ«nyrĂ« default — dosja e shtĂ«pisĂ«);
  • --log_level — niveli i regjistrimit (argument opsional, nĂ« mĂ«nyrĂ« default — INFO).

Shembuj komandash

Arkivimi i tabelës

Funksioni kryesor i utilitarit — arkivimi i tĂ« dhĂ«nave, dmth, transferimi i rreshtave nga tabela kryesore nĂ« tabelĂ«n arkiv (p.sh. nga tabela books nĂ« books_archive).

Po ashtu mbështetet fshirja pa arkivim: për këtë duhet të vendosni parametrin në config.ini to_archive = false).

Parametrat e domosdoshĂ«m — config_path, table dhe ids.

Pas nisjes do të fshihen rekurzivisht shënimet ids në tabelë tavolina dhe në të gjitha tabelat që i referohen asaj.

$ pggraph archive_table --config_path config.hw.local.ini --table flights --ids 1,2,3
2020-06-20 19:27:44 INFO: fluturimet - FILLIM
2020-06-20 19:27:44 INFO: fluturimet - fillimi archive_recursive 3 rreshta (thellësia=0)
2020-06-20 19:27:44 INFO:       FILLIM ARCHIVE TABELAT E REFERIMET
2020-06-20 19:27:44 INFO:       bileta_fluturimeve - fillimi archive_recursive 3 rreshta (thellësia=1)
2020-06-20 19:27:44 INFO:               FILLIM ARCHIVE TABELAT E REFERIMET
2020-06-20 19:27:44 INFO:               karta_boarding - fillimi archive_recursive 3 rreshta (thellësia=2)
2020-06-20 19:27:44 INFO:                       FILLIM ARCHIVE TABELAT E REFERIMET
2020-06-20 19:27:44 INFO:                       MBARIM ARCHIVE TABELAT E REFERIMET
2020-06-20 19:27:44 INFO:               karta_boarding - archive_by_ids 3 rreshta sipas ticket_no, flight_id
2020-06-20 19:27:44 INFO:               karta_boarding - fillimi archive_recursive 3 rreshta (thellësia=2)
2020-06-20 19:27:44 INFO:                       FILLIM ARCHIVE TABELAT E REFERIMET
2020-06-20 19:27:44 INFO:                       MBARIM ARCHIVE TABELAT E REFERIMET
2020-06-20 19:27:44 INFO:               karta_boarding - archive_by_ids 3 rreshta sipas ticket_no, flight_id
2020-06-20 19:27:44 INFO:               karta_boarding - fillimi archive_recursive 3 rreshta (thellësia=2)
2020-06-20 19:27:44 INFO:                       FILLIM ARCHIVE TABELAT E REFERIMET
2020-06-20 19:27:44 INFO:                       MBARIM ARCHIVE TABELAT E REFERIMET
2020-06-20 19:27:44 INFO:               karta_boarding - archive_by_ids 3 rreshta sipas ticket_no, flight_id
2020-06-20 19:27:44 INFO:               karta_boarding - fillimi archive_recursive 3 rreshta (thellësia=2)
2020-06-20 19:27:44 INFO:                       FILLIM ARCHIVE TABELAT E REFERIMET
2020-06-20 19:27:44 INFO:                       MBARIM ARCHIVE TABELAT E REFERIMET
2020-06-20 19:27:44 INFO:               karta_boarding - archive_by_ids 3 rreshta sipas ticket_no, flight_id
2020-06-20 19:27:44 INFO:               MBARIM ARCHIVE TABELAT E REFERIMET
2020-06-20 19:27:44 INFO:       bileta_fluturimeve - archive_by_ids 3 rreshta sipas ticket_no, flight_id
2020-06-20 19:27:44 INFO:       MBARIM ARCHIVE TABELAT E REFERIMET
2020-06-20 19:27:44 INFO: fluturimet - archive_by_ids 3 rreshta sipas id
2020-06-20 19:27:44 INFO: fluturimet - MBARIM

Kërkimi i varësive për tabelën e specifikuar

Funksioni pĂ«r tĂ« kĂ«rkuar varĂ«sitĂ« e tabelĂ«s sĂ« specifikuar tavolina. Parametrat e detyrueshĂ«m janĂ« — config_path dhe tavolina.

Pasi të filloni, në ekran do të shfaqet një fjalor, ku:

  • in_refs — fjalori i tabelave qĂ« i referohen kĂ«saj, ku çelĂ«si Ă«shtĂ« emri i tabelĂ«s, vlera Ă«shtĂ« lista e objekteve Foreign Key (pk_main — çelĂ«si primar nĂ« tabelĂ«n kryesore, pk_ref — çelĂ«si primar nĂ« tabelĂ«n e referuar, fk_ref — emri i kolonĂ«s qĂ« Ă«shtĂ« foreign key pĂ«r tabelĂ«n origjinale);
  • out_refs — fjalori i tabelave, nĂ« tĂ« cilat referohet kjo.

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

Kërkimi i referencave për rreshtat me çelësat Primar të specifikuar

Funksioni pĂ«r tĂ« kĂ«rkuar rreshtat nĂ« tabela tĂ« tjera, tĂ« cilat i referohen pĂ«rmes Foreign Key rreshtave ids tabelĂ«s tavolina. Parametrat e detyrueshĂ«m janĂ« — config_path, tavolina dhe ids.

Pasi të filloni, në ekran do të shfaqet një fjalor me strukturën e mëposhtme:

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

Shembuj thirrjeje:

$ 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 në konsolë, biblioteka mund të përdoret në kodin Python. Më poshtë jepen shembuj thirrjeje në mjedisin interaktiv iPython.

Arkivimi i tabelës

>>> 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 - FILLIM
2020-06-20 23:12:08 INFO: flights - fillimi archive_recursive 2 rreshta (depth=0)
2020-06-20 23:12:08 INFO: 	FILLIM ARKIVIM TABELAVE REFERUAR
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 - fillimi archive_recursive 30 rreshta (depth=1)
2020-06-20 23:12:08 INFO: 		FILLIM ARKIVIM TABELAVE REFERUAR
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 - arkivo_me_fk 30 rreshta nga 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: 		FSHI NGA boarding_passes sipas FK flight_id, ticket_no - 30 rreshta
2020-06-20 23:12:08 INFO: 		FUND ARKIVIM TABELAVE REFERUAR
2020-06-20 23:12:08 INFO: 	ticket_flights - arkivo_me_ids 30 rreshta nga 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: 	FSHI NGA ticket_flights sipas flight_id, ticket_no - 30 rreshta
2020-06-20 23:12:08 DEBUG: 	FUT NË ticket_flights_archive - 30 rreshta
2020-06-20 23:12:08 INFO: 	ticket_flights - fillimi archive_recursive 30 rreshta (depth=1)
2020-06-20 23:12:08 INFO: 		FILLIM ARKIVIM TABELAVE REFERUAR
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 - arkivo_me_fk 30 rreshta nga 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: 		FSHI NGA boarding_passes sipas FK flight_id, ticket_no - 30 rreshta
2020-06-20 23:12:08 INFO: 		FUND ARKIVIM TABELAVE REFERUAR
2020-06-20 23:12:08 INFO: 	ticket_flights - arkivo_me_ids 30 rreshta nga 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: 	FSHI NGA ticket_flights sipas flight_id, ticket_no - 30 rreshta
2020-06-20 23:12:08 DEBUG: 	FUT NË ticket_flights_archive - 30 rreshta
2020-06-20 23:12:08 INFO: 	ticket_flights - fillimi archive_recursive 30 rreshta (depth=1)
2020-06-20 23:12:08 INFO: 		FILLIM ARKIVIM TABELAVE REFERUAR
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 - arkivo_me_fk 30 rreshta nga 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: 		FSHI NGA boarding_passes sipas FK flight_id, ticket_no - 30 rreshta
2020-06-20 23:12:08 INFO: 		FUND ARKIVIM TABELAVE REFERUAR
2020-06-20 23:12:08 INFO: 	ticket_flights - arkivo_me_ids 30 rreshta nga 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: 	FSHI NGA ticket_flights sipas flight_id, ticket_no - 30 rreshta
2020-06-20 23:12:08 DEBUG: 	FUT NË ticket_flights_archive - 30 rreshta
2020-06-20 23:12:08 INFO: 	ticket_flights - fillimi archive_recursive 3 rreshta (depth=1)
2020-06-20 23:12:08 INFO: 		FILLIM ARKIVIM TABELAVE REFERUAR
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 - arkivo_me_fk 3 rreshta nga 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: 		FSHI NGA boarding_passes sipas FK flight_id, ticket_no - 3 rreshta
2020-06-20 23:12:08 INFO: 		FUND ARKIVIM TABELAVE REFERUAR
2020-06-20 23:12:08 INFO: 	ticket_flights - arkivo_me_ids 3 rreshta nga 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: 	FSHI NGA ticket_flights sipas flight_id, ticket_no - 3 rreshta
2020-06-20 23:12:08 DEBUG: 	FUT NË ticket_flights_archive - 3 rreshta
2020-06-20 23:12:08 INFO: 	FUND ARKIVIM TABELAVE REFERUAR
2020-06-20 23:12:08 INFO: flights - arkivo_me_ids 2 rreshta nga 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: FSHI NGA flights sipas flight_id - 2 rreshta
2020-06-20 23:12:09 DEBUG: FUT NË flights_archive - 2 rreshta
2020-06-20 23:12:09 INFO: flights - FUND

Kërkimi i varësive për tabelën e specifikuar

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

Kërkimi i referencave për rreshtat me çelësat 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ë i disponueshëm në GitHub në përputhje me licencën MIT, si dhe në depo PyPI.

Do të isha i lumtur për komentet, angazhimet dhe propozimet.

Dhe për pyetjet do të përpiqem të përgjigjem sa më shumë që mundem këtu dhe në depo.

Burimi: habr.com

Bli njĂ« hosting tĂ« besueshĂ«m pĂ«r faqet me mbrojtje DDoS, VPS VDS serverĂ« đŸ”„ Bli njĂ« hosting tĂ« besueshĂ«m pĂ«r faqet me mbrojtje DDoS, VPS VDS serverĂ« | ProHoster