PgGraph — утилита для архивации и поиска зависимостей таблиц в PostgreSQL / forpes.ru

Главная
PgGraph — утилита для архивации и поиска зависимостей таблиц в PostgreSQL

PgGraph — утилита для архивации и поиска зависимостей таблиц в PostgreSQL +20

16.07.2020 08:00

olegborzov 5 1900 Источник

Сегодня я хочу представить читателям Хабра утилиту, написанную на 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 в 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.

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

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

Комментарии (5)

zaki
16.07.2020 12:29
#21851778
+2
А чем не угодило партиционирование в PostgreSQL?
1. olegborzov Автор
  16.07.2020 13:04
  #21851954
  Партиционирование не подошло из-за специфики запросов к нагруженным таблицам.
  Много запросов, которым требуются данные из разных периодов и потери на сквозном индексе среди партиций сжирали весь прирост скорости от разбиения.
  1. torbasow
    17.07.2020 09:00
    #21855662
    +1
    Простите, не уловил: а когда эти запросы обращаются к непартицированным архивным таблицам, получается более производительно?
    
    SeekerOfTruth
    17.07.2020 11:11
    #21856236
    +1
    помимо ускорения запросов преследовались и другие цели.
    БД стала слишком объёмной, что сказывалось на скорости разворачивания дампов и проведения технических работ. Ретроданные нужны только для аналитиков и аудиторов, для текущей работы приложения достаточно данных за текущий год. Поэтому было принято решение об «архивировании» данных, т к облегчалась эксплуатация текущих таблиц, а архивные таблицы можно перенести в отдельную бд
    
    torbasow
    21.07.2020 21:26
    #21873214
    Так под «много запросов, которым требуются данные из разных периодов» имелись ли в виду запросы к периодам, соответствующим архивным данным?

PgGraph — утилита для архивации и поиска зависимостей таблиц в PostgreSQL +20

Предыстория

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

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

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

Параметры

Примеры команд

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

Поиск зависимостей для указанной таблицы

Поиск ссылок на строки с указанными Primary Key

Использование в коде

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

Поиск зависимостей для указанной таблицы

Поиск ссылок на строки с указанными Primary Key

Комментарии (5)

zaki

olegborzov Автор

torbasow

SeekerOfTruth

torbasow