Привет, Хабр!

С 2019 года я занимаюсь разработкой open source инструмента под названием pg-index-health, который позволяет анализировать структуру БД и находить потенциальные проблемы. В одной из своих предыдущих статей я рассказывал о том, как появился этот инструмент.

Все эти годы pg-index-health эволюционировал и развивался. За 2024 год мне при поддержке нескольких контрибьюторов удалось решить большинство остававшихся «детских болячек» и довести проект до состояния, когда он готов к масштабной экспансии.

Рост количества баз данных с приходом микросервисов

С PostgreSQL я работаю с 2015-го года, и началось это увлекательное знакомство в ярославской компании Тензор.

2015-й год — это ещё было время монолитов с огромными базами данных и большим количеством таблиц. Как правило, любые изменения в структуре таких БД требовали обязательного согласования с архитектором или руководителем разработки, который являлся ключевым носителем знаний. Это защищало от большинства ошибок, но тормозило процесс внесения изменений и абсолютно не масштабировалось.

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

Пирамида тестирования и её формы

Большинство из вас, наверное, слышали про пирамиду тестирования. Для монолитов она имеет достаточно характерный вид с широким основанием из unit-тестов. За подробностями рекомендую обратиться к статье Мартина Фаулера.

Микросервисы изменили не только подход к разработке, но внешний вид пирамиды тестирования. Во многом это произошло благодаря распространению технологий контейнеризации (Docker, Testcontainers). Теперь пирамида тестирования — это уже и не пирамида вовсе. Она может иметь весьма причудливую форму. Наиболее известные — это Honeycomb и Testing Trophy.

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

Мой личный фаворит — это Testing Trophy. В основании такой пирамиды тестирования лежит статический анализ кода, который призван защищать от типовых ошибок.

О важности статического анализа кода

Анализ Java и Kotlin кода есть уже практически у всех. В сервисах на Kotlin, как правило используется detekt. Для Java приложений набор доступных инструментов (их ещё называют линтерами) несколько шире. Основные из них — это checkstyle, PMD, SpotBugs и Error Prone. Более подробно почитать о них можно в моей статье на Хабре. Стоит отметить, что detekt и checkstyle помимо всего прочего следят за форматированием кода, то есть выполняют функцию formatter’а.

Если взглянуть на современные микросервисы, то во многих из них помимо кода приложения можно обнаружить миграции для создания и обновления структуры БД.

Основные инструменты для управления миграциями в Java мире — это Liquibase и Flyway. Любые изменения в структуре БД всегда должны фиксироваться в миграциях. Даже если во время инцидента вручную вносятся какие-то изменения на production, то позднее обязательно нужно сделать миграцию и применить эти изменения на всех стендах.

Миграции лучше всего писать на чистом SQL — это даёт вам максимум возможностей и позволяет не тратить время на изучение XML-диалекта конкретного инструмента (речь в первую очередь про Liquibase). Подробнее я упоминал об этом в статье.

Для верификации SQL кода миграций я рекомендую использовать SQLFluff (по сути, это аналог checkstyle). Этот линтер поддерживает множество баз данных/диалектов (в том числе PostgreSQL) и может быть встроен в ваш CI-пайплайн. Он имеет свыше 60-ти правил, которые можно настроить под себя. Вы можете управлять псевдонимами (алиасами/aliases) таблиц и столбцов, регистром SQL-команд, отступами, очередностью столбцов в выборке и многими другими вещами. Сравните запрос с форматированием и без него:

-- со строгим форматированием
select
    pc.oid::regclass::text as table_name,
    pg_table_size(pc.oid) as table_size
from
    pg_catalog.pg_class pc
    inner join pg_catalog.pg_namespace nsp on nsp.oid = pc.relnamespace
where
    pc.relkind = 'r' and
    pc.oid not in (
        select c.conrelid as table_oid
        from pg_catalog.pg_constraint c
        where c.contype = 'p'
    ) and
    nsp.nspname = :schema_name_param::text
order by table_name;

-- без строгого форматирования
SELECT pc.oid::regclass::text AS table_name, pg_table_size(pc.oid) AS table_size
FROM pg_catalog.pg_class  pc
JOIN pg_catalog.pg_namespace AS nsp
ON nsp.oid =  pc.relnamespace
WHERE pc.relkind = 'r’ 
and pc.oid NOT in (
  select c.conrelid as table_oid
  from pg_catalog.pg_constraint   c
  where    c.contype = 'p’
)
and nsp.nspname  = :schema_name_param::text
ORDER BY  table_name;

Хорошо отформатированный SQL код гораздо легче читать и воспринимать. И самое главное — на code review вы не будете тратить время на обсуждение форматирования и вкусовщины. SQLFluff заставит разработчика привести SQL код к нужному формату. Вот как это выглядит в реальном pull request’е:

Здесь SQLFluff обнаружил проблему с форматированием возвращаемого значения в инструкции select: когда возвращается всего один столбец, то не выносим его на отдельную строку. Второй момент – это неправильный порядок столбцов в результатах выборки: сначала возвращаем простые столбцы и только потом результаты вычислений. И третье — это некорректный регистр для and в описании join’а: я предпочитаю все запросы писать в нижнем регистре.

Больше примеров использования SQLFluff можно найти у меня в open source проектах: один, два.

Использование метаданных для анализа структуры БД

Структуру самой БД тоже можно проверять. При этом работать с миграциями крайне неудобно: их может быть очень много; новая миграция может исправлять ошибки предыдущей миграции и тому подобное. Нас, как правило, больше интересует итоговая структура БД, чем её промежуточное состояние.

PostgreSQL (как и многие другие реляционные БД) хранит внутри себя метаданные о всех объектах и связях между ними и предоставляет их наружу в виде information_schema. Мы можем использовать запросы к information_schema для выявления каких-либо отклонений, проблем или типовых ошибок (именно так делает SchemaCrawler).

Поскольку мы работаем только с PostgreSQL, то вместо information_schema мы можем использовать системные каталоги (схема pg_catalog), которые дают намного больше информации о внутреннем устройстве конкретной базы данных.

Помимо метаданных PostgreSQL собирает информацию о работе каждой базы данных: какие запросы выполняются, каким образом они выполняются, какие методы доступа используются и т.д. За сбор этих сведений отвечает система накопительной статистики (The Cumulative Statistics System). Обращаясь к этой статистике через соответствующие системные представления и агрегируя полученные данные с информацией из системного каталога, мы можем, в частности, найти индексы, которые совсем не используются, или таблицы, где индексов не хватает.

Статистика может быть сброшена вручную. Дата и время последнего сброса фиксируются в системе. Важно это учитывать, чтобы понять можно доверять статистике или нет. Например, если у вас есть какая-то бизнес-логика, которая отрабатывает раз в месяц/квартал/полугодие, то статистику нужно собирать за период не меньше обозначенного выше интервала.

Если используется кластер БД, то на каждом хосте статистика собирается независимо и не реплицируется внутри кластера.

pg-index-health и его структура

Идея с анализом структуры БД на основе метаданных внутри самой базы, описанная выше, реализована мной в виде инструмента названием pg-index-health.

Моё решение включает в себя следующие части:

• Набор проверок в виде sql-запросов, вынесенных в отдельный репозиторий (на текущий момент это 25 проверок). Запросы отделены от кодовой базы на Java и могут быть переиспользованы в проектах на других языках программирования.

• Доменная модель — минимальный набор классов, позволяющих в виде объектов представить результаты выполнения проверок.

• Абстракция HighAvailabilityPgConnection над подключением к кластеру базы данных, состоящему из нескольких хостов.

• Утилиты для выполнения sql-запросов и сериализации результатов в объекты доменной модели.

• Spring Boot starter для удобного и быстрого подключения проверок в unit/компонентные/интеграционные тесты.

• Генератор миграций, который для обнаруженных проблем может создать корректирующие sql-миграции.

Виды проверок

Все проверки (они же диагностики) делятся на две группы:

• runtime проверки (требуют наличия статистики);

• статические проверки (не требуют статистики).

Runtime проверки имеет смысл выполнять только на работающем инстансе базы данных в production. Эти проверки требуют наличия накопленной статистики и агрегируют эту статистику со всех хостов в кластере.

Рассмотрим кластер БД из 3-х хостов: primary, secondary и async replica. Некоторые сервисы используют кластеры с похожей топологией и выполняют тяжёлые читающие запросы только на асинхронной реплике для балансировки нагрузки. На primary хосте такие запросы обычно не запускаются, так как создают повышенную нагрузку и негативно влияют на latency других запросов.

Как уже было сказано ранее, в PostgreSQL статистика собирается на каждом хосте отдельно и не реплицируется внутри кластера. Таким образом, у вас легко может возникнуть ситуация, что какие-то индексы используются и нужны, например, только на асинхронной реплике. Для получения достоверного вердикта о том, что какой-то индекс нужен или нет, необходимо выполнить проверку на каждом хосте в кластере и объединить результаты.

Статические проверки не требуют наличия накопленной статистики и могут быть выполнены на primary хосте сразу после наката миграций. Безусловно, их также можно использовать на production базе и в режиме реального времени получать данные. Однако, большинство проверок именно статические, и они наиболее полезны в тестах, так как позволяют на этапе разработки отлавливать и предотвращать типовые ошибки.

Как использовать pg-index-health

Основной сценарий использования pg-index-health — это добавление тестов на проверку структуры БД в ваш пайплайн тестирования. Для приложений на Spring Boot вам нужно подключить стартер в тестовые зависимости:

dependencies {
    testImplementation("io.github.mfvanek:pg-index-health-test-starter:0.14.4")
}

И добавить стандартный тест:

import io.github.mfvanek.pg.core.checks.common.DatabaseCheckOnHost;
import io.github.mfvanek.pg.core.checks.common.Diagnostic;
import io.github.mfvanek.pg.model.dbobject.DbObject;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;

import java.util.List;

import static org.assertj.core.api.Assertions.assertThat;

@SpringBootTest
@ActiveProfiles("test")
class DatabaseStructureStaticAnalysisTest {

    @Autowired
    private List<DatabaseCheckOnHost<? extends DbObject>> checks;

    @Test
    void checksShouldWork() {
        assertThat(checks)
            .hasSameSizeAs(Diagnostic.values());

        checks.stream()
            .filter(DatabaseCheckOnHost::isStatic)
            .forEach(c -> assertThat(c.check())
                .as(c.getDiagnostic().name())
                .isEmpty());
    }
}

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

В идеале каждая проверка должна вернуть пустой список. Если при добавлении очередной миграции появятся какие-либо отклонения, то тест упадёт. Разработчик будет вынужден обратить на это внимание и каким-то образом разрешить проблему: либо исправить её в своей миграции, либо явно проигнорировать.

Ложные срабатывания и добавление исключений

Важно понимать, что у pg-index-health, как и любого другого статического анализатора, бывают ложные срабатывания (false positive). А ещё некоторые проверки могут быть неактуальны для вашего проекта. Например, практикой хорошего тона считается документирование структуры БД. PostgreSQL позволяет добавлять комментарии практически ко всем сущностям. В миграции это может выглядеть следующим образом:

create table if not exists demo.warehouse
(
    id bigint primary key generated always as identity,
    name varchar(255) not null
);

comment on table demo.warehouse is 'Information about the warehouses';
comment on column demo.warehouse.id is 'Unique identifier of the warehouse';
comment on column demo.warehouse.name is 'Human readable name of the warehouse';

Внутри своей команды на уровне соглашений вы можете решить, что не будете этого делать. Тогда результаты соответствующих проверок (TABLES_WITHOUT_DESCRIPTION, COLUMNS_WITHOUT_DESCRIPTION, FUNCTIONS_WITHOUT_DESCRIPTION) для вас становятся нерелевантными.

Вы можете как полностью исключить эти проверки:

@Test
void checksShouldWork() {
    assertThat(checks)
        .hasSameSizeAs(Diagnostic.values());

    checks.stream()
        .filter(DatabaseCheckOnHost::isStatic)
        .filter(c -> c.getDiagnostic() != Diagnostic.TABLES_WITHOUT_DESCRIPTION &&
            c.getDiagnostic() != Diagnostic.COLUMNS_WITHOUT_DESCRIPTION)
        .forEach(c -> assertThat(c.check())
            .as(c.getDiagnostic().name())
            .isEmpty());
}

Так и просто проигнорировать их результаты:

@Test
void checksShouldWork() {
    assertThat(checks)
        .hasSameSizeAs(Diagnostic.values());

    checks.stream()
        .filter(DatabaseCheckOnHost::isStatic)
        .forEach(c -> {
            final ListAssert<? extends DbObject> listAssert = assertThat(c.check())
                .as(c.getDiagnostic().name());
            switch (c.getDiagnostic()) {
                case TABLES_WITHOUT_DESCRIPTION, COLUMNS_WITHOUT_DESCRIPTION -> listAssert.hasSizeGreaterThanOrEqualTo(0); // ignored

                default -> listAssert.isEmpty();
            }
        });
}

При внедрении pg-index-health у вас часто может возникать ситуация, когда в структуре БД уже есть какие-то отклонения, но вы не хотите их прямо сейчас исправлять. При этом проверка для вас релевантна и отключать её нельзя. В этом случае лучше всего будет зафиксировать все отклонения в коде:

@Test
void checksShouldWorkForAdditionalSchema() {
    final PgContext ctx = PgContext.of("additional_schema");
    checks.stream()
        .filter(DatabaseCheckOnHost::isStatic)
        .forEach(c -> {
            final ListAssert<? extends DbObject> listAssert = assertThat(c.check(ctx))
                .as(c.getDiagnostic().name());

            switch (c.getDiagnostic()) {
                case TABLES_WITHOUT_DESCRIPTION, TABLES_NOT_LINKED_TO_OTHERS ->
                    listAssert.hasSize(1)
                        .asInstanceOf(list(Table.class))
                        .containsExactly(
                            Table.of(ctx, "additional_table")
                        );

                default -> listAssert.isEmpty();
            }
        });
}

Теперь я бы хотел подробнее остановиться на наиболее часто встречающихся проблемах.

Таблицы без первичных ключей

В силу особенностей механизма MVCC в Postgres’е возможна такая ситуация, как bloat, когда размер вашей таблицы (или индекса) быстро растёт за счёт большого количества мертвых записей. Это может случиться, например, из-за наличия долгих транзакций или единовременного обновления большого количества строк.

За сборку мусора внутри БД отвечает процесс autovacuum, но он не освободит физическое место, занятое на диске. Эффективно уменьшить физический размер таблицы может только команда VACUUM FULL, которая требует эксклюзивной блокировки на всё время обработки. Для больших таблиц это может быть несколько часов, что делает малоприменимым полное вакуумирование в большинстве современных сервисов.

Поэтому для решения проблемы с bloat’ом таблиц без downtime’а обычно используют стороннее расширение pg_repack. Одно из обязательных требований pg_repack — наличие первичного ключа или иного ограничения уникальности на целевой таблице. Диагностика TABLES_WITHOUT_PRIMARY_KEY позволяет обнаруживать таблицы без первичных ключей и предотвращать проблемы, связанные с их обслуживанием в будущем.

Ниже приведен пример таблицы без первичного ключа. Если в ней возникнет bloat, то pg_repack не сможет обработать такую таблицу и выдаст ошибку.

create table if not exists demo.payment
(
    id bigint not null, -- столбец не помечен как primary key
    order_id bigint references demo.orders (id),
    status int not null,
    created_at timestamp not null,
    payment_total decimal(22, 2) not null
);

Дублирующиеся индексы

Наши базы данных работают на хостах с ограниченными/конечными ресурсами. И место на диске — один из них. Очень часто при использовании решений типа Database as a Service у нас есть физическое ограничение на максимальный размер БД, которое никак не изменить.

Каждый индекс в таблице представляет собой отдельную сущность на диске. Он занимает место и требует ресурсов на обслуживание, замедляя вставку и обновление данных. Мы создаём индексы, чтобы ускорить поиск или добиться уникальности каких-либо значений. Однако при неправильном использовании индексов можно получить ситуацию, когда их совокупный размер превышает размер полезных данных в самой таблице. Таким образом количество индексов в таблице должно быть минимальным, но достаточным для её функционирования.

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

create table if not exists demo.buyer
(
    id bigint primary key default nextval('demo.buyer_seq'), -- PK
    first_name varchar(255) not null,
    last_name varchar(255) not null,
    middle_name varchar(255),
    phone varchar(20) not null,
    email varchar(50) not null,
    ip_address varchar(100)
);

create unique index if not exists idx_buyer_pk
    on demo.buyer (id); -- можно, но бессмысленно

Аналогичная ситуация с ограничением уникальности. Когда вы помечаете какой-либо столбец (или группу столбцов) ключевым словом unique, то PostgreSQL автоматически создает уникальный индекс на этот столбец (или группу столбцов). Вручную никаких индексов создавать не нужно. Если это сделать, то получим лишний дубль. Такие индексы-дубликаты можно смело удалять, и диагностика DUPLICATED_INDEXES поможет в этом.

create table if not exists demo.order_item
(
    id bigint primary key default nextval('demo.order_item_seq'),
    order_id bigint not null references demo.orders (id),
    price decimal(22, 2) not null default 0,
    amount int not null default 0,
    sku varchar(255) not null,
    warehouse_id int not null,
    unique (sku, order_id) -- unique constraint

);

create unique index if not exists i_order_item_sku_order_id_unique
    on demo.order_item (sku, order_id); -- можно, но бессмысленно

Пересекающиеся индексы

Чаще всего индексы создаются на один столбец. Когда начинается оптимизация запросов к БД, могут добавляться более сложные индексы, включающие в себя несколько столбцов. Так появляются индексы на столбцы A, A+B, A+B+C и т.п. Первые два из этих индексов можно смело выбросить, так как они являются префиксами третьего (рекомендую посмотреть доклад). Это тоже прилично экономит место на диске и для этого есть диагностика INTERSECTED_INDEXES.

create table if not exists demo.buyer
(
    id bigint primary key default nextval('demo.buyer_seq'),
    first_name varchar(255) not null,
    last_name varchar(255) not null,
    middle_name varchar(255),
    phone varchar(20) not null
);

create index if not exists i_buyer_first_name
    on demo.buyer (first_name); -- можно удалить

create index if not exists i_buyer_names
    on demo.buyer (first_name, last_name, middle_name);

create index if not exists i_buyer_id_phone
    on demo.buyer (id, phone); -- включать в индекс первичный ключ практически всегда плохая затея

Внешние ключи без индексов

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

Давайте рассмотрим небольшой пример (я использую PostgreSQL 16.6):

create schema if not exists demo;

create table if not exists demo.orders
(
    id bigint primary key generated always as identity,
    user_id bigint not null,
    shop_id bigint not null,
    status int not null,
    created_at timestamptz not null default current_timestamp
);

create table if not exists demo.order_item
(
    id bigint primary key generated always as identity,
    order_id bigint not null references demo.orders (id),
    price decimal(22, 2) not null default 0,
    amount int not null default 0,
    sku varchar(255) not null,
    warehouse_id int
);

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

Давайте заполним таблицы некоторым количеством данных и соберём статистику. Добавим 100 тысяч заказов. У половины заказов будет два товара в заказе, у остальных — один.

insert into demo.orders (user_id, shop_id, status) 
select
    (ids.id % 10) + 1 as user_id,
    (ids.id % 4) + 1 as shop_id,
    1 as status – new order
from generate_series(1, 100000) ids (id);

insert into demo.order_item (order_id, price, amount, sku)
select
    id as order_id,
    (random() + 1) * 1000.0 as price,
    (random() * 10) + 1 as amount,
    md5(random()::text) as sku
from demo.orders;

insert into demo.order_item (order_id, price, amount, sku)
select
    id as order_id,
    (random() + 1) * 2000.0 as price,
    (random() * 5) + 1 as amount,
    md5((random() + 1)::text) as sku
from demo.orders where id % 2 = 0;

-- собираем статистику
vacuum analyze demo.orders, demo.order_item;

Если мы попробуем получить позиции для заказа с идентификатором 100, то нам успешно вернётся 2 строки. У нас есть индекс на столбец id в таблице заказов и может показаться, что этот запрос должен отрабатывать быстро.

explain (analyze, buffers)
select oi.id, oi.order_id, oi.price, oi.amount, oi.sku, o.user_id, o.shop_id, o.status
from demo.orders o
join demo.order_item oi on oi.order_id = o.id
where o.id = 100 -- есть индекс по этому столбцу
order by oi.id;

Однако, если мы попробуем отпрофилировать этот запрос, то увидим в плане выполнения запроса (query plan/query execution plan) последовательное сканирование таблицы. Также нас должно насторожить большое количество страниц, которые требуется прочитать — параметр Buffers.

Sort  (cost=3588.34..3588.35 rows=2 width=79) (actual time=25.244..25.248 rows=2 loops=1)
  Sort Key: oi.id
  Sort Method: quicksort  Memory: 25kB
  Buffers: shared hit=1711
  ->  Nested Loop  (cost=0.29..3588.33 rows=2 width=79) (actual time=0.083..25.212 rows=2 loops=1)
        Buffers: shared hit=1711
        ->  Index Scan using orders_pkey on orders o  (cost=0.29..8.31 rows=1 width=28) (actual time=0.040..0.052 rows=1 loops=1)
              Index Cond: (id = 100)
              Buffers: shared hit=6
        ->  Seq Scan on order_item oi  (cost=0.00..3580.00 rows=2 width=59) (actual time=0.036..25.145 rows=2 loops=1)
              Filter: (order_id = 100)
              Rows Removed by Filter: 149998
              Buffers: shared hit=1705
Planning:
  Buffers: shared hit=54
Planning Time: 0.793 ms
Execution Time: 25.314 ms

Если создать индекс на столбец с внешним ключом, то ситуация нормализуется:

create index concurrently if not exists idx_order_item_order_id
    on demo.order_item (order_id);

Из плана запроса пропадёт последовательный доступ. Количество читаемых страниц тоже существенно уменьшится:

Sort  (cost=19.54..19.55 rows=2 width=79) (actual time=0.110..0.113 rows=2 loops=1)
  Sort Key: oi.id
  Sort Method: quicksort  Memory: 25kB
  Buffers: shared hit=8
  ->  Nested Loop  (cost=0.71..19.53 rows=2 width=79) (actual time=0.076..0.086 rows=2 loops=1)
        Buffers: shared hit=8
        ->  Index Scan using orders_pkey on orders o  (cost=0.29..8.31 rows=1 width=28) (actual time=0.029..0.030 rows=1 loops=1)
              Index Cond: (id = 100)
              Buffers: shared hit=3
        ->  Index Scan using idx_order_item_order_id on order_item oi  (cost=0.42..11.20 rows=2 width=59) (actual time=0.033..0.040 rows=2 loops=1)
              Index Cond: (order_id = 100)
              Buffers: shared hit=5
Planning:
  Buffers: shared hit=15 read=1
Planning Time: 1.065 ms
Execution Time: 0.178 ms

Диагностика FOREIGN_KEYS_WITHOUT_INDEX позволит вам уже на этапе разработки отлавливать такие кейсы и не допускать проблем с производительностью.

Null value в индексах

По умолчанию Postgres включает null значения в btree-индексы, но они там, как правило, не нужны. Все null значения уникальны, и вы не можете просто так взять и найти одну запись, у которой значение столбца равно null. Чаще всего лучше исключать null’ы из индекса, создавая частичные индексы на nullable-столбцы по типу where <A> is not null. Диагностика INDEXES_WITH_NULL_VALUES поможет находить такие кейсы.

Рассмотрим на примере заказов и позиций в заказе. В таблице order_item есть nullable колонка warehouse_id — идентификатор склада.

create table if not exists demo.order_item
(
    id bigint primary key generated always as identity,
    order_id bigint not null references demo.orders (id),
    price decimal(22, 2) not null default 0,
    amount int not null default 0,
    sku varchar(255) not null,
    warehouse_id int
);

Допустим у нас есть несколько складов. После оплаты заказа мы начинаем его комплектовать. Обновим статус у части заказов и будем считать их оплаченными:

-- индекс для эффективного поиска
create index concurrently if not exists idx_orders_creates_at_status on demo.orders (created_at, status);

-- обновляеми статус у нескольких заказов
update demo.orders
    set status = 2 -- paid order
where
    status = 1 -- new order
    and id in (
    select id from demo.orders where id % 4 = 0 order by id limit 100);

Отдельные позиции в заказе могут отгружаться с разных складов по какому-то внутреннему алгоритму с учетом логистики, количества товара на складе, загруженности склада и т.д. Назначив склад и зафиксировав остатки, на каждой позиции заказа мы обновляем поле warehouse_id (по умолчанию там было null).

update demo.order_item
    set warehouse_id = case when order_id % 8 = 0 then 1 else 2 end
where
    warehouse_id is null
    and order_id in (
    select id from demo.orders
    where
        status = 2
        and created_at >= current_timestamp - interval '1 day');

Нам потребуется поиск по конкретному идентификатору склада, чтобы знать, какие позиции нужно укомплектовать и отгрузить. Берём только оплаченные заказы за определенный промежуток времени:

select oi.id, oi.order_id, oi.price, oi.amount, oi.sku, o.user_id, o.shop_id, o.status
from demo.orders o
join demo.order_item oi on oi.order_id = o.id
where
    oi.warehouse_id = 2
    and o.status = 2
    and o.created_at >= current_timestamp - interval '1 day'
order by oi.id;

Первым решением скорее всего будет обычный индекс на столбец warehouse_id:

create index concurrently if not exists idx_order_item_warehouse_id
    on demo.order_item (warehouse_id);

Если создать такой индекс, то он будет без особых проблем использоваться при поиске позиций для конкретного склада. И может показаться, что этот индекс позволяет эффективно найти все позиции, для которых ещё не назначен склад, отфильтровав записи по условию warehouse_id is null:

explain (analyze, buffers)
select * from demo.order_item where warehouse_id is null;

Однако, если мы посмотрим на план выполнения запроса, то увидим там последовательный доступ — индекс не используется.

Seq Scan on order_item  (cost=0.00..3208.00 rows=149755 width=63) (actual time=0.214..24.909 rows=149800 loops=1)
  Filter: (warehouse_id IS NULL)
  Rows Removed by Filter: 200
  Buffers: shared hit=1708
Planning Time: 0.291 ms
Execution Time: 33.720 ms

Разумеется, это связано с конкретным распределением данных в тестовой БД. Столбец warehouse_id имеет низкую кардинальность, то есть количество уникальных значений в нём невелико. А индекс по этому столбцу имеет низкую селективность. Под селективностью индекса понимается отношение количества различных проиндексированных значений (то есть кардинальности) к общему количеству строк в таблице: distinct / count(). Так, например, уникальный индекс имеет селективность, равную единице.

Мы можем увеличить селективность индекса, убрав null значения и создав частичный индекс на столбец warehouse_id.

create index concurrently if not exists idx_order_item_warehouse_id_without_nulls
    on demo.order_item (warehouse_id) where warehouse_id is not null;

Мы сразу же увидим этот индекс в плане запроса:

Sort  (cost=860.61..860.61 rows=1 width=79) (actual time=0.628..0.640 rows=100 loops=1)
  Sort Key: oi.id
  Sort Method: quicksort  Memory: 35kB
  Buffers: shared hit=417
  ->  Nested Loop  (cost=0.44..860.60 rows=1 width=79) (actual time=0.052..0.546 rows=100 loops=1)
        Buffers: shared hit=417
        ->  Index Scan using idx_order_item_warehouse_id_without_nulls on order_item oi  (cost=0.14..246.83 rows=80 width=59) (actual time=0.023..0.069 rows=100 loops=1)
              Index Cond: (warehouse_id = 2)
              Buffers: shared hit=5
        ->  Index Scan using orders_pkey on orders o  (cost=0.29..7.67 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=100)
              Index Cond: (id = oi.order_id)
              Filter: ((status = 2) AND (created_at >= (CURRENT_TIMESTAMP - '1 day'::interval)))
              Buffers: shared hit=356
Planning:
  Buffers: shared hit=17
Planning Time: 0.835 ms
Execution Time: 0.702 ms

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

select
    x.indrelid::regclass::text as table_name,
    x.indexrelid::regclass::text as index_name,
    pg_relation_size(x.indexrelid) as index_size_bytes
from pg_index x
join pg_stat_all_indexes psai on x.indexrelid = psai.indexrelid
where psai.schemaname = 'demo'::text;

Планы на будущее

Это далеко не все проблемы, которые pg-index-health может находить. Полный список диагностик доступен в README проекта на GitHub и он регулярно расширяется.

Внедрить pg-index-health в приложение на Spring Boot достаточно просто. При этом накладные расходы на запуск проверок минимальны. В результате вы получите защиту от типовых ошибок и проблем. Я призываю вас попробовать это сделать!

В ближайшем будущем я планирую добавить полноценную поддержку секционированных таблиц во всех проверках. Сейчас это реализовано только для 11-ти проверок из 25-ти. Также я хочу расширить количество проверок: уже есть тикеты на реализацию как минимум 5-ти новых проверок. А ещё в 2025-м году я планирую перейти на Java 17 и Spring Boot 3.

Дополнительные материалы по теме

• Статический анализ структуры базы данных (часть 1, часть 2, часть 3)

• DBA: находим бесполезные индексы

• Здоровье индексов в PostgreSQL глазами Java-разработчика

• Похожее решение – SchemaCrawler