🥇PgGraph - утыліта для архівацыі і пошуку залежнасцяў табліц у PostgreSQL

Сёння я жадаю прадставіць чытачам Хабра ўтыліту, напісаную на Python, для працы з залежнасцямі табліц у СКБД PostgreSQL.

API утыліты простае і складаецца з трох метадаў:

archive_table — рэкурсіўнае архіваванне/выдаленне радкоў з паказанымі Primary Keys
get_table_references - Пошук залежнасцяў для табліцы (пакажа табліцы, на якія спасылаецца паказаная і спасылаюцца на яе)
get_rows_references — пошук радкоў у іншых табліцах, якія спасылаюцца на ўказаныя радкі ў патрэбнай табліцы

перадгісторыя

Мяне клічуць Алег Барзоў, я распрацоўшчык у камандзе CRM для мэнэджэраў іпатэчнага крэдытавання ў Домкліку.

Асноўная БД нашай CRM-сістэмы з'яўляецца адной з найбуйных па аб'ёме ў кампаніі. Яна ж адна з самых старых: з'явілася пры самым запуску праекта, калі дрэвы былі вялікімі, Домклік – стартапам, а замест мікрасэрвісу на модным пітонаўскім асінхронным фрэймворку быў велізарны маналіт на PHP.

Пераход з PHP на Python быў вельмі доўгім і патрабаваў адначасовай падтрымкі абедзвюх сістэм, што адбівалася на праектаванні БД.

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

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

Гэтая задача ўскладняецца вялікай колькасцю сувязяў паміж табліцамі: проста перанесці радкі з task в task_archive недастаткова, перад гэтым трэба тое ж самае рэкурсіўна прарабіць з усімі, хто спасылаецца на task табліцамі.

Прадэманструю на прыкладзе дэманстрацыйнай БД з сайта postgrespro.ru:

Дапушчальны, нам трэба выдаліць запісы з табліцы Flights. Проста так гэта зрабіць Postgres нам не дазволіць: папярэдне трэба выдаліць запісы з усіх табліц, якія спасылаюцца, і так рэкурсіўна да табліц, на якія ніхто не спасылаецца.

У нашым прыкладзе на Flights спасылаецца Ticket_flights, а на яе - Boarding_passes.

Таму выдаляць трэба ў такім парадку:

Атрымліваем значэння першасныя ключы (Primary Keys, PK) радкоў у Ticket_flights, якія спасылаюцца на выдаляныя радкі ў Flights.
Атрымліваем PK радкоў Boarding_passes, якія спасылаюцца на Ticket_flights.
Выдаляем радкі па PK з п.2 у табліцы Boarding_passes.
Выдаляем радкі па PK з п.1 у Ticket_flights.
Выдаляем радкі з Flights.

У выніку атрымалася ўтыліта пад назовам PgGraph, якую мы вырашылі зрабіць open source.

як карыстацца

Утыліта падтрымлівае два рэжыму выкарыстання:

Выклік з каманднага радка (pggraph …).
Выкарыстанне ў кодзе Python (клас PgGraphApi).

Ўстаноўка і настройка

Спачатку трэба ўсталяваць утыліту з Pypi-рэпазітара:

pip3 install pggraph

Затым стварыць на лакальнай машыне файл config.ini з канфігурацыяй БД і скрыпта архівацыі:

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

Запуск з кансолі

Параметры

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

Пазіцыйныя аргументы:

action - патрабаванае дзеянне: archive_table, get_table_references або get_rows_references.

Найменныя аргументы:

--config_path - шлях да канфіг-файла;
--table - табліца, з якой трэба здзейсніць дзеянне;
--ids - спіс id праз коску, напрыклад, 1,2,3 (неабавязковы параметр);
--log_path - шлях да тэчкі для логаў (неабавязковы параметр, па змаўчанні - хатняя тэчка);
--log_level - узровень часопісавання (неабавязковы параметр, па змаўчанні - INFO).

Прыклады каманд

Архівацыя табліцы

Асноўная функцыя ўтыліты - архівацыя дадзеных, г.зн. перанос радкоў з асноўнай табліцы ў архіўную (напрыклад, з табліцы кнігі в books_archive).

Таксама падтрымліваецца выдаленне без архівацыі: для гэтага трэба ў config.ini ўсталяваць параметр to_archive = false).

Абавязковыя параметры - config_path, table і ids.

Пасля запуску будуць рэкурсіўна выдалены запісы ids ў табліцы table і ва ўсіх табліцах, якія спасылаюцца на яе.

$ 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

Пошук залежнасцяў для пазначанай табліцы

Функцыя для пошуку залежнасцяў пазначанай табліцы table. Абавязковыя параметры - config_path и table.

Пасля запуску на экран будзе выведзены слоўнік, дзе:

in_refs - слоўнік якія спасылаюцца табліц на дадзеную, дзе ключ - назва табліцы, значэнне - спіс аб'ектаў Foreign Key (pk_main - першасны ключ у асноўнай табліцы, pk_ref - першасны ключ у якая спасылаецца табліцы, fk_ref - Назва калонкі, якая з'яўляецца foreign key на зыходную табліцу);
out_refs - слоўнік табліц, на якую спасылаецца дадзеная.

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

Пошук спасылак на радкі з указанымі Primary Key

Функцыя для пошуку радкоў у іншых табліцах, якія спасылаюцца праз Foreign Key на радкі ids табліцы table. Абавязковыя параметры - config_path, table и ids.

Пасля запуску на экран будзе выведзены слоўнік з наступнай структурай:

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

Прыклад выкліку:

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

Выкарыстанне ў кодзе

Апроч запуску ў кансолі, бібліятэку можна выкарыстоўваць у кодзе Python. Ніжэй паказаны прыклады выкліку ў інтэрактыўным асяроддзі iPython.

Архівацыя табліцы

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

Пошук залежнасцяў для пазначанай табліцы

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

Пошук спасылак на радкі з указанымі Primary 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'}]}}}

Зыходны код бібліятэкі даступны на GitHub пад MIT ліцэнзіяй, а таксама ў рэпазітары PyPI.

Буду рады каментарам, комітам і прапановам.

На пытанні пастараюся адказаць па меры магчымасцяў тут і ў рэпазітары.

Крыніца: habr.com

PgGraph - утыліта для архівацыі і пошуку залежнасцяў табліц у PostgreSQL

перадгісторыя

як карыстацца

Ўстаноўка і настройка

Запуск з кансолі

Параметры

Прыклады каманд

Архівацыя табліцы

Пошук залежнасцяў для пазначанай табліцы

Пошук спасылак на радкі з указанымі Primary Key

Выкарыстанне ў кодзе

Архівацыя табліцы

Пошук залежнасцяў для пазначанай табліцы

Пошук спасылак на радкі з указанымі Primary Key

Дадаць каментар Адмяніць адказ