Целесообразность внешних ключей на регистровых таблицах

Содержание

6.8.2. Целесообразность внешних ключей на регистровых таблицах#

6.8.2.1. Введение#

В нашей ERP-системе PostgreSQL используется как основное хранилище данных. База данных содержит тысячи таблиц, включая как основные бизнес-сущности с полноценной реляционной моделью, так и различные технические структуры хранения данных.

Помимо классических регистровых таблиц в системе существуют и другие технические структуры, которые формально регистрами не являются, но по своей природе выполняют схожую роль. Это таблицы, содержащие копии или проекции бизнес-данных, предназначенные для унификации API, упрощения запросов или реализации общих интерфейсов.

Примером такой структуры является класс складской операции STK_Operation, а также все миксины. Данные в подобных таблицах не являются первичными: они формируются на основе других сущностей системы и используются как технический слой представления или агрегации данных.

Общая особенность всех таких таблиц заключается в том, что данные в них:

  • не вводятся пользователем напрямую;

  • формируются из уже проверенных источников;

  • не являются источником истины.

Поэтому с точки зрения нагрузки на базу данных регистровые таблицы и подобные технические структуры должны рассматриваться одинаково. Применение к ним внешних ключей и большого количества индексов требует особенно аккуратного и осознанного подхода, поскольку повторная проверка целостности на этом уровне, как правило, не добавляет новой ценности, но создаёт заметную эксплуатационную нагрузку.

6.8.2.2. Внешние ключи на регистровых таблицах и их реальная цена#

Часто регистровые таблицы создаются по аналогии с основными сущностями — с внешними ключами на справочники и связанные таблицы. Формально это выглядит корректно, но на практике такие внешние ключи почти никогда не добавляют новой ценности.

Данные, попадающие в регистр, уже прошли проверку ссылочной целостности в исходных таблицах. Повторная проверка на уровне регистра не защищает от новых ошибок, но создаёт дополнительную нагрузку на базу данных.

В PostgreSQL внешний ключ — это не просто декларативное ограничение. При каждой вставке или обновлении строки в дочерней таблице выполняется служебный запрос, проверяющий наличие соответствующей строки в родительской таблице:

SELECT 1 FROM ONLY "public"."bs_depowner" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x

Этот запрос выполняется для каждой вставляемой строки. Для регистров, где характерны массовые операции (INSERT … SELECT, пакетные загрузки, перерасчёты), это означает огромное количество служебных запросов, которые хорошо видны в отчётах по базе данных и создают ощутимую нагрузку на CPU и систему блокировок.

6.8.2.3. Почему внешний ключ всегда приводит к появлению индекса#

Если в таблице существует внешний ключ, на соответствующее поле обязан быть индекс. Это требование связано с операциями удаления и обновления строк в родительской таблице.

При удалении строки из справочника PostgreSQL обязан проверить, что в дочерних таблицах нет ссылающихся строк. Без индекса на поле внешнего ключа такая проверка выполняется через последовательное сканирование всей дочерней таблицы. Для крупных регистров это приводит к длительным блокировкам и резкой деградации операций DELETE и UPDATE.

Поэтому наличие внешнего ключа практически всегда означает наличие индекса, который постоянно обновляется при записи данных в регистр.

6.8.2.4. Стоимость индексов при вставке данных#

Каждый индекс — это отдельная структура данных, которая должна быть обновлена при вставке строки. Если таблица не имеет индексов, вставка относительно дешёвая и сводится к записи строки в heap и генерации WAL.

Добавление индексов делает вставку существенно дороже. Практика эксплуатации показывает следующие порядки величин:

  • один btree-индекс замедляет вставку примерно на 20–40 %;

  • три–пять индексов увеличивают стоимость вставки в 2–3 раза;

  • десять и более индексов могут замедлять вставку в 4–6 раз и более, особенно при конкурентной нагрузке.

Эта цена платится всегда, независимо от того, используется индекс в запросах или нет.

6.8.2.5. Анализ использования индексов#

Для анализа использования индексов применяются представления pg_stat_user_indexes и pg_stat_all_indexes. Основной показатель — idx_scan, количество сканирований индекса.

Если индекс имеет idx_scan = 0 за продолжительный период времени, он является кандидатом на удаление. Однако корректность такого вывода напрямую зависит от момента сброса статистики.

6.8.2.6. Сброс статистики и интерпретация данных#

PostgreSQL хранит точную дату и время последнего сброса статистики по базе данных. Эта информация доступна в представлении pg_stat_database:

SELECT
    datname,
    stats_reset
FROM pg_stat_database;

Все счётчики в pg_stat_user_tables и pg_stat_user_indexes интерпретируются начиная с этого момента. Отдельной даты сброса статистики для таблиц или индексов не существует.

Если статистика была сброшена недавно, низкие значения idx_scan не являются признаком неиспользуемости индекса — они лишь означают, что период наблюдения был слишком коротким.

При необходимости начать контролируемый интервал наблюдения статистику можно сбросить вручную:

SELECT pg_stat_reset();

6.8.2.7. Ограничения статистики: bitmap-сканирования#

Индексы, используемые в Bitmap Index Scan, могут иметь заниженные значения idx_scan или выглядеть как неиспользуемые. Это особенность учёта статистики, которую необходимо учитывать при анализе кандидатов на удаление.

6.8.2.8. Пример запроса для поиска кандидатов на удаление индексов#

Запрос помогает выявить кандидатов на удаление индексов на регистровых и производных таблицах. Он ориентирован на поиск одноколоночных индексов, которые:

  • ни разу не использовались (idx_scan = 0);

  • не имеют зафиксированного времени последнего использования;

  • не являются первичными ключами;

  • не являются уникальными индексами;

  • не поддерживают внешние ключи.

Внимание

Запрос не является универсальным и требует фильтрации по таблицам. Его цель — предоставить инженеру список кандидатов для анализа, а не готовое решение для автоматического удаления.

6.8.2.8.1. SQL-запрос#

WITH index_usage AS (
    SELECT
        s.schemaname,
        s.relname      AS table_name,
        s.indexrelname AS index_name,
        s.idx_scan,
        s.last_idx_scan,
        s.indexrelid,
        s.relid        AS table_relid,
        pg_relation_size(s.indexrelid) AS index_size
    FROM pg_stat_user_indexes s
),
single_column_indexes AS (
    SELECT
        i.indexrelid,
        i.indrelid     AS table_relid,
        i.indkey[0]    AS attnum,
        i.indisprimary,
        i.indisunique
    FROM pg_index i
    WHERE i.indnkeyatts = 1
      AND i.indkey[0] > 0
),
fk_columns AS (
    SELECT
        con.conrelid AS table_relid,
        unnest(con.conkey) AS attnum
    FROM pg_constraint con
    WHERE con.contype = 'f'
),
table_estimates AS (
    SELECT
        c.oid AS table_relid,
        c.reltuples::bigint AS approx_table_rows
    FROM pg_class c
    WHERE c.relkind = 'r'
)
SELECT
    iu.schemaname,
    iu.table_name,
    te.approx_table_rows,
    iu.index_name,
    iu.idx_scan,
    iu.last_idx_scan,
    pg_size_pretty(iu.index_size) AS index_size,
    sci.indisprimary AS is_primary_key,
    sci.indisunique  AS is_unique_index,
    EXISTS (
        SELECT 1
        FROM fk_columns fk
        WHERE fk.table_relid = sci.table_relid
          AND fk.attnum = sci.attnum
    ) AS is_foreign_key_index,
    (
        iu.idx_scan = 0
        AND iu.last_idx_scan IS NULL
        AND NOT sci.indisprimary
        AND NOT sci.indisunique
        AND NOT EXISTS (
            SELECT 1
            FROM fk_columns fk
            WHERE fk.table_relid = sci.table_relid
              AND fk.attnum = sci.attnum
        )
    ) AS candidate_for_drop
FROM index_usage iu
JOIN single_column_indexes sci
     ON sci.indexrelid = iu.indexrelid
LEFT JOIN table_estimates te
       ON te.table_relid = iu.table_relid
ORDER BY
    iu.table_name,
    iu.index_name;

6.8.2.8.2. Как использовать результат#

Результат содержит признак candidate_for_drop, но его нельзя трактовать как автоматическую рекомендацию.

Перед использованием запроса необходимо:

  1. Добавить фильтр по таблицам
    Выполняйте запрос только для:

    • регистровых таблиц;

    • производных технических структур (миксины, проекции, API-таблицы);

    • таблиц, не являющихся источниками истины.

  2. Проверить момент сброса статистики
    Все значения idx_scan и last_idx_scan актуальны с момента последнего сброса в pg_stat_database.

  3. Учитывать bitmap-сканирования
    Индексы в Bitmap Index Scan могут иметь idx_scan = 0, но при этом реально использоваться.

  4. Оценить контекст использования таблицы
    Решение об удалении принимайте только при понимании назначения таблицы и всех сценариев её использования.

6.8.2.9. Удаление индексов и внешних ключей#

6.8.2.9.1. Генерация индексов и внешних ключей#

Все настройки генерации индексов и внешних ключей задаются в метаданных (ODM) конкретных классов.

По умолчанию система автоматически создаёт внешние ключи и индексы для ссылочных атрибутов. Для всех остальных атрибутов внешний ключ и индекс генерируется только при явном указании дополнительных условий в коде метаданных.

6.8.2.9.1.1. Отключение генерации внешних ключей для интеграции со сторонними системами#

Для параметров jidext_dz и sidext_dz, используемых при взаимодействии со внешними базами данных, по умолчанию всегда генерируются внешние ключи и индексы для всех таблиц.

Чтобы отключить автогенерацию, установите параметр externalId.Enabled в метаданных ODM:

<class xmlns="http://www.global-system.ru/xsd/global3-class-1.0" name="Btktst_SimpleReference"
       caption="Справочник без коллекции"
       cardEditor.representation="Card" listEditor.representation="List"
       viewOptions.openCardType="mdi" supertype="reference" externalId.Enabled="false">

Этот параметр отключает создание внешних ключей и индексов для всех атрибутов, связанных с jidext_dz и sidext_dz.

Внимание

Если индексы jidext_dz и sidext_dz уже созданы в БД, их нужно удалять через миграцию схемы БД (dbSchema/dbData)

6.8.2.9.1.2. Отключение индексов и внешних ключей на ссылочных атрибутах#

Для ссылочных атрибутов внутри системы (например, связи между регистрами и справочниками) автогенерацию индекса и внешнего ключа можно отключить в метаданных ODM:

<attr name="idModule" attribute-type="Long" type="refObject" ref.class="Btk_Module">
    <column indexType="noIndex" foreignKeyType="disable"/>
</attr>

Эта настройка:

  • отключает создание внешнего ключа (foreignKeyType="disable");

  • отключает создание индекса (indexType="noIndex").

Внимание

Параметр indexType="noIndex" предотвращает только создание новых индексов. Если индекс уже существует, его необходимо удалить вручную через миграцию схемы БД (dbSchema/dbData).
Существующие внешние ключи удаляются автоматически при применении foreignKeyType="disable".

Генерация схемы базы данных на основе этих настроек выполняется автоматически при сборке модуля.

6.8.2.9.1.3. Рекомендации при создании регистровых таблиц#

При проектировании новых регистровых таблиц соблюдайте следующие правила:

  • Создавайте документы с типом supertype="journal" или supertype="setting". Эти типы являются наиболее лёгковесными и не содержат избыточной бизнес-логики.

  • Удаляйте поле gid, если оно было добавлено автоматически. Поле gid создаёт дополнительный индекс и увеличивает объём данных.

6.8.2.9.2. Техническая операция удаления#

Удаление индексов и внешних ключей выполняется в рамках релиза модуля. Все изменения схемы базы данных должны быть оформлены в виде SQL-операторов в миграционных скриптах.

Конкурентное удаление индексов (DROP INDEX CONCURRENTLY) не требуется, поскольку операция выполняется при остановленной системе и отключённых пользователях.

6.8.2.9.2.1. Удаление внешнего ключа#

ALTER TABLE public.reg_table
DROP CONSTRAINT fk_reg_table_ref;

Перед удалением внешнего ключа убедитесь, что:

  • Он не используется для обеспечения критичной логики.

  • Данные в регистре формируются только из проверенных источников.

6.8.2.9.2.2. Удаление индекса#

DROP INDEX public.idx_reg_table_ref_id;

Индекс удаляется обычной командой DROP INDEX, без конкурентного режима.

6.8.2.10. Выводы#

Регистровые таблицы в нашей ERP-системе предназначены для хранения уже проверенных данных. Внешние ключи и связанные с ними индексы на этом уровне часто не добавляют новых гарантий, но стабильно увеличивают стоимость записи и создают дополнительную нагрузку на базу данных. Осознанное управление схемой регистров, основанное на архитектурной роли таблиц и анализе реального использования, позволяет снизить нагрузку на БД без потери корректности данных.

Содержание