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.

Поэтому удалять нужно в таком порядке:

1. Получаем значения первичные ключи (Primary Keys, PK) строк в Ticket_flights, которые ссылаются на удаляемые строки в Flights.
2. Получаем PK строк Boarding_passes, которые ссылаются на Ticket_flights.
3. Удаляем строки по PK из п.2 в таблице Boarding_passes.
4. Удаляем строки по PK из п.1 в Ticket_flights.
5. Удаляем строки из Flights.

В итоге получилась утилита под названием PgGraph, которую мы решили сделать open source.

Как пользоваться

Утилита поддерживает два режима использования:

• Вызов из командной строки (pggraph ).
• Использование в коде Python (класс PgGraphApi).

Установка и настройка

Сначала нужно установить утилиту из Pypi-репозитория:

pip3 install pggraph

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

[db]host = localhostport = 5432user = postgrespassword = postgresdbname = postgresschema = public ; Необязательный параметр, указано значение по умолчанию[archive]  ; Данный раздел заполнять необязательно, ниже указаны значения по умолчаниюis_debug = falsechunk_size = 1000max_depth = 20to_archive = truearchive_suffix = 'archive'

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

Параметры

$ pggraph -husage: pggraph action [-h] --table TABLE [--ids IDS] [--config_path CONFIG_PATH]positional arguments:  action        required action: archive_table, get_table_references, get_rows_referencesoptional 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 в 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,32020-06-20 19:27:44 INFO: flights - START2020-06-20 19:27:44 INFO: flights - start archive_recursive 3 rows (depth=0)2020-06-20 19:27:44 INFO:       START ARCHIVE REFERRING TABLES2020-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 TABLES2020-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 TABLES2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id2020-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 TABLES2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id2020-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 TABLES2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id2020-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 TABLES2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id2020-06-20 19:27:44 INFO:               END ARCHIVE REFERRING TABLES2020-06-20 19:27:44 INFO:       ticket_flights - archive_by_ids 3 rows by ticket_no, flight_id2020-06-20 19:27:44 INFO:       END ARCHIVE REFERRING TABLES2020-06-20 19:27:44 INFO: flights - archive_by_ids 3 rows by id2020-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 - START2020-06-20 23:12:08 INFO: flights - start archive_recursive 2 rows (depth=0)2020-06-20 23:12:08 INFO: START ARCHIVE REFERRING TABLES2020-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 TABLES2020-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 rows2020-06-20 23:12:08 INFO: END ARCHIVE REFERRING TABLES2020-06-20 23:12:08 INFO: ticket_flights - archive_by_ids 30 rows by flight_id, ticket_no2020-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 rows2020-06-20 23:12:08 DEBUG: INSERT INTO ticket_flights_archive - 30 rows2020-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 TABLES2020-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 rows2020-06-20 23:12:08 INFO: END ARCHIVE REFERRING TABLES2020-06-20 23:12:08 INFO: ticket_flights - archive_by_ids 30 rows by flight_id, ticket_no2020-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 rows2020-06-20 23:12:08 DEBUG: INSERT INTO ticket_flights_archive - 30 rows2020-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 TABLES2020-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 rows2020-06-20 23:12:08 INFO: END ARCHIVE REFERRING TABLES2020-06-20 23:12:08 INFO: ticket_flights - archive_by_ids 30 rows by flight_id, ticket_no2020-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 rows2020-06-20 23:12:08 DEBUG: INSERT INTO ticket_flights_archive - 30 rows2020-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 TABLES2020-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 rows2020-06-20 23:12:08 INFO: END ARCHIVE REFERRING TABLES2020-06-20 23:12:08 INFO: ticket_flights - archive_by_ids 3 rows by flight_id, ticket_no2020-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 rows2020-06-20 23:12:08 DEBUG: INSERT INTO ticket_flights_archive - 3 rows2020-06-20 23:12:08 INFO: END ARCHIVE REFERRING TABLES2020-06-20 23:12:08 INFO: flights - archive_by_ids 2 rows by flight_id2020-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 rows2020-06-20 23:12:09 DEBUG: INSERT INTO flights_archive - 2 rows2020-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.

Буду рад комментариям, коммитам и предложениям.

На вопросы постараюсь ответить по мере возможностей здесь и в репозитории.