ProHoster > Blog > Administrim > 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, sot dua të paraqes lexuesit e Habr një utilitar të shkruar në Python për të punuar me varësitë e tabelave në DBMS PostgreSQL.

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

  • archive_table — arkivimi/redukimi rekursiv i rreshtave me Çelika Kryesore tĂ« gjithĂ« pĂ«rcaktuar
  • get_table_references — kĂ«rkimi i varĂ«sive pĂ«r njĂ« tabelĂ« (do tĂ« tregojĂ« tabelat qĂ« referojnĂ« tĂ« dhĂ«nĂ« dhe qĂ« referojnĂ« pĂ«r tĂ«)
  • get_rows_references — kĂ«rkimi i rreshtave nĂ« tabela tĂ« tjera qĂ« referojnĂ« rreshtat e dhĂ«nĂ« nĂ« tabelĂ«n e nevojshme

Historia e mëparshme

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

Baza kryesore e të dhënave të sistemit tonë CRM është një nga më të mëdhatë në kompani. Ajo është gjithashtu një nga më të vjetrat: u krijua në fillim të projektit, kur pemët ishin të mëdha, Domklik ishte një startup, dhe në vend të mikroshërbimeve në fushën e modës të kuadrove asinkronë me Python kishte një monolit të madh në PHP.

Kalimi nga PHP në Python ishte shumë i gjatë dhe kërkonte mbështetje në të njëjtën kohë për të dy sistemet, e cila kishte ndikim në projektimin e DB.

Si rezultat, ne kemi një bazë me një numër të madh tabelash të lidhura fort dhe të mëdha në përmasa me shumë indekse për lloje të ndryshme kërkesash. E gjithë kjo ndikon negativisht në performancën e DB: për shkak të tabelave të mëdha dhe lidhjeve të shumta midis tyre, kompleksi i kërkesave rritet vazhdimisht, që është veçanërisht kritik për tabelat më të ngarkuara.

Për të ulur ngarkesën në DB, ne vendosëm të shkruajmë një skenar që çdo ditë të transferonte të dhënat e vjetra nga tabelat më të mëdha dhe më të ngarkuara në arkiv (p.sh, nga task në task_archive).

Kjo detyrë komplikohet nga numri i madh i lidhjeve midis tabelave: thjesht të transferosh rreshtat nga task në task_archive nuk është e mjaftueshme, para se të bësh të njëjtën gjë rekursivisht me të gjitha tabelat që referojnë në task tabelat.

Do ta demonstroj me një shembuj të DB-së demonstrative nga faqja postgrespro.ru:

PgGraph — njĂ« mjet pĂ«r arkivimin dhe kĂ«rkimin e varĂ«sive tĂ« tabelave nĂ« PostgreSQL
Supozoni se na nevojitet të fshijmë të dhëna nga tabela Flights. Thjesht kështu, Postgres nuk do të na lejojë: paraprakisht duhet të fshijmë rreshtat nga të gjitha tabelat që referojnë, dhe kështu rekursivisht deri te tabelat në të cilat askush nuk referon.

NĂ« shembullin tonĂ« mbi Flights referohet Ticket_flights, dhe mbi tĂ« — Boarding_passes.

Prandaj, duhet të fshijmë në këtë rend:

  1. Marrim vlerat e çelikut kryesor (Primary Keys, PK) të rreshtave në Ticket_flights, të cilat referojnë në rreshtat që do të fshihen në Flights.
  2. Marrim PK të rreshtave Boarding_passes, të cilat referojnë në Ticket_flights.
  3. Fshirjem rreshtat sipas PK nga pika 2 në tabelë Boarding_passes.
  4. Fshirjem rreshtat sipas PK nga pika 1 në Ticket_flights.
  5. Fshirjem rreshtat nga Flights.

Si rezultat, u krijua një utilitare me emrin PgGraph, të cilën vendosëm ta bëjmë open source.

Si të përdoret

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

  • Thirrja nga.console (pggraph 
).
  • PĂ«rdorimi nĂ« kodin Python (klasa PgGraphApi).

Instalimi dhe konfigurimi

Së pari, duhet të instaloni utilitarin nga depoja Pypi:

pip3 install pggraph

Pastaj, krijoni në makinerinë tuaj lokale skedarin config.ini me konfigurimin e DB-së dhe skenarit të arkivimit:

[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; Parametri i opsional, vlera e vendosur si e dhënë për default

[archive]  ; Ky seksion mund të plotësohet opsionalisht, më poshtë janë dhënë vlerat për default
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'

Ekzekutimi nga konsola

Parametrat

$ pggraph -h
përdorimi: pggraph veprimi [-h] --table TABELA [--ids IDS] [--config_path RRUGA_CFG]
argumentet pozitive:
  veprimi        veprim i kërkuar: archive_table, get_table_references, get_rows_references

argumentet opsionale:
  -h, --help                    tregon këtë mesazh ndihme dhe del
  --table TABELA                 emri i tabelës
  --ids IDS                     id-të e çelikut kryesor, të ndara me presje, p.sh. 1,2,3
  --config_path RRUGA_CFG       rruga për config.ini
  --log_path RRUGA_LOG          rruga për dosjen e log-ut
  --log_level NIVEL_LOG         niveli i log-ut (debug, info, error)

Argumentet pozitive:

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

Argumentet emëruar:

  • --config_path — rruga pĂ«r skedarin e konfigurimit;
  • --table — tabela nga e cila duhet tĂ« kryhet veprimi;
  • --ids — lista e id-ve tĂ« ndara me presje, pĂ«r shembull, 1,2,3 (parametri opsional);
  • --log_path — rruga pĂ«r dosjen e logĂ«ve (parametri opsional, pĂ«r default — folderi i pĂ«rdoruesit);
  • --log_level — niveli i regjistrimit (parametri opsional, pĂ«r default — INFO).

Shembuj komandash

Arkivimi i tabelës

Funksioni kryesor i utilitarit — arkivimi i tĂ« dhĂ«nave, pra, transferimi i rreshtave nga tabela kryesore nĂ« atĂ« arkivare (pĂ«r shembull, nga tabela books nĂ« books_archive).

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

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

Pas ekzekutimit, do të fshihen rekursivisht regjistrimet ids në tabelë table dhe në të gjitha tabelat që referojnë atyre.

$ 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 - fill archive_recursive 3 rows (depth=0)
2020-06-20 19:27:44 INFO:       FILL ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:       ticket_flights - fill archive_recursive 3 rows (depth=1)
2020-06-20 19:27:44 INFO:               FILL ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - fill archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       FILL ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END FILL 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 - fill archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       FILL ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END FILL 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 - fill archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       FILL ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END FILL 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 - fill archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       FILL ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END FILL 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 FILL 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 FILL 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

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

Funksioni pĂ«r kĂ«rkimin e varĂ«sive tĂ« tabelĂ«s sĂ« dhĂ«nĂ« table. Parametrat e obligueshĂ«m — config_path dhe table.

Pasi të ekzekutohet do të shfaqet një fjalor ku:

  • in_refs — fjalori i tabelave qĂ« referohen nĂ« kĂ«tĂ« tabelĂ«, 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 qĂ« referohet, fk_ref — emri i kolonĂ«s qĂ« Ă«shtĂ« foreign key nĂ« tabelĂ«n burimore);
  • out_refs — fjalori i tabelave qĂ« kjo tabelĂ« referon.

$ 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ësa të primarit të dhënë

Funksioni pĂ«r kĂ«rkimin e rreshtave nĂ« tabela tĂ« tjera qĂ« referohen pĂ«rmes Foreign Key nĂ« rreshtat ids tabelĂ«s table. Parametrat e obligueshĂ«m — config_path, table dhe ids.

Pas lançimit, 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: value, row_pk_2: value},
				...
			], 
			...
		},
		...
	},
	pk_id_2: {...},
	...
}

Shembulli 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ç lançimit në konsolë, bibliotekën mund ta përdorni në kodin Python. Më poshtë janë disa shembuj thirrjesh në ambientin 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 - 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

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

>>> 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ësa të primarit të dhënë

>>> 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 burimi i bibliotekës është i disponueshëm në GitHub në licencën MIT, si dhe në depo PyPI.

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

Në pyetje do të përpiqem të përgjigjem në masën e mundësive këtu dhe në depo.

Burimi: habr.com

Blini hosting tĂ« besueshĂ«m pĂ«r faqe interneti me mbrojtje nga DDoS, serverĂ« VPS VDS đŸ”„ Blini hosting tĂ« besueshĂ«m pĂ«r faqe interneti me mbrojtje nga DDoS, serverĂ« VPS VDS | ProHoster