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

      +2
      А чем не угодило партиционирование в PostgreSQL?
        0
        Партиционирование не подошло из-за специфики запросов к нагруженным таблицам.
        Много запросов, которым требуются данные из разных периодов и потери на сквозном индексе среди партиций сжирали весь прирост скорости от разбиения.
          +1
          Простите, не уловил: а когда эти запросы обращаются к непартицированным архивным таблицам, получается более производительно?
            +1
            помимо ускорения запросов преследовались и другие цели.
            БД стала слишком объёмной, что сказывалось на скорости разворачивания дампов и проведения технических работ. Ретроданные нужны только для аналитиков и аудиторов, для текущей работы приложения достаточно данных за текущий год. Поэтому было принято решение об «архивировании» данных, т к облегчалась эксплуатация текущих таблиц, а архивные таблицы можно перенести в отдельную бд
              0
              Так под «много запросов, которым требуются данные из разных периодов» имелись ли в виду запросы к периодам, соответствующим архивным данным?

      Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

      Самое читаемое