🥇PgGraph — инструмент за архивиране и търсене на зависимости между таблици в PostgreSQL

Днес искам да представя на читателите на Хабра инструмент, написан на Python, за работа със зависимости на таблиците в СУБД PostgreSQL.

API на инструмента е прост и се състои от три метода:

archive_table — рекурсивно архивиране/премахване на редове с указаните основни ключове (Primary Keys)
get_table_references — търсене на зависимости за таблица (ще покаже таблиците, на които указана таблица се позовава и които се позовават на нея)
get_rows_references — търсене на редове в други таблици, които се позовават на указани редове в нужната таблица

Предистория

Казвам се Олег Борзов, разработчик в екипа на CRM за мениджъри по ипотечно кредитиране в Домклик.

Основната база данни на нашата CRM система е една от най-големите по обем в компанията. Тя е и една от най-старите: появи се при самото стартиране на проекта, когато дърветата бяха големи, Домклик беше стартъп, а вместо микросървис на модния асинхронен Python фреймворк имаше огромен монолит на PHP.

Преходът от PHP на Python беше много дълъг и изискваше едновременно поддържане на двете системи, което се отразяваше на проектирането на базата данни.

В резултат имаме база с голямо количество силно свързани и огромни по размер таблици с куп индекси за различни видове запитвания. Всичко това негативно се отразява на производителността на базата данни: заради големите таблици и купищата зависимости между тях постоянно нараства сложността на запитванията, което е особено критично за най-натоварените таблици.

За да намалим натоварването на базата данни, решихме да напишем скрипт, който ежедневно по крон да пренася старите записи от най-обемните и натоварени таблици в архивни (например, от task в task_archive).

Тази задача се усложнява от голямото количество зависимости между таблиците: просто преместването на редове от task в task_archive не е достатъчно, преди това трябва същото рекурсивно да се направи със всички свързани таблици. task Ще демонстрирам на примера

демонстрационна база данни от сайта postgrespro.ru Да предположим, че трябва да изтрием записи от таблицата:

Flights . Просто така Postgres няма да ни позволи: предварително трябва да изтрием записи от всички свързани таблици, и така рекурсивно до таблиците, на които никой не се позовава.В нашия пример на

Ticket_flights . Просто така Postgres няма да ни позволи: предварително трябва да изтрием записи от всички свързани таблици, и така рекурсивно до таблиците, на които никой не се позовава. се позовава , а на нея —Boarding_passes Затова трябва да изтриваме в такъв ред:.

Получаваме стойностите на основните ключове (Primary Keys, PK) на редовете в

, които се позовават на изтритите редове в , а на нея —Получаваме PK на редовете . Просто така Postgres няма да ни позволи: предварително трябва да изтрием записи от всички свързани таблици, и така рекурсивно до таблиците, на които никой не се позовава..
, които се позовават на Затова трябва да изтриваме в такъв ред:, които се отнасят към , а на нея —.
Премахваме редовете по PK от т.2 в таблицата Затова трябва да изтриваме в такъв ред:.
Премахваме редовете по PK от т.1 в , а на нея —.
Премахваме редовете от . Просто така Postgres няма да ни позволи: предварително трябва да изтрием записи от всички свързани таблици, и така рекурсивно до таблиците, на които никой не се позовава..

В крайна сметка получихме утилита на име PgGraph, която решихме да направим с отворен код.

Как да ползвате

Утилитата поддържа два режима на употреба:

Извикване от командния ред (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
използване: pggraph action [-h] --table TABLE [--ids IDS] [--config_path CONFIG_PATH]
positional arguments:
  action        задължително действие: archive_table, get_table_references, get_rows_references

optional arguments:
  -h, --help                    показва това помагателно съобщение и изход
  --table TABLE                 име на таблицата
  --ids IDS                     идентификатори на основния ключ, разделени със запетая, напр. 1,2,3
  --config_path CONFIG_PATH     път до config.ini
  --log_path LOG_PATH           път до папката с логове
  --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 в 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