Статический анализ структуры базы данных (часть 1)
Статический анализ структуры базы данных — это процесс выявления ошибок, нерекомендуемых практик и потенциальных проблем в базе данных только на основе структуры, типов данных, свойств объектов. Статический анализ структуры не задействует ни пользовательские данные, ни статистику по таким данным.
Рассмотрим подробнее статический анализ структуры базы данных — что это, какие задачи решает, как интегрировать статический анализ в CI.
Опираясь на данное выше определение мы получаем следующие важные свойства:
анализ можно проводить в любой среде (dev/test/prod) и даже на пустой БД, где нет данных (бэкап структуры без данных);
затраты ресурсов зависят только от количества объектов в БД и количества проверок, а не от объема данных или качества приближения тестовых данных и запросов к реальным;
статический анализ может быть встроен в CI как элемент повышения качества разработки;
статический анализ не помогает оптимизировать запросы к данным.
Рассмотрим примеры статического анализа на базе PostgreSQL. Для упрощения кода ниже в примерах будем подразумевать, что операции выполняются в схеме public.
Проверки свойств и состояния служебных объектов
Начнём с самого простого — проверки свойств и состояния служебных объектов. Это больше характеризует текущее состояние базы данных и лишь частично относится к процессу разработки.
У пользователя базы данных, под которым будут выполняться проверки, должно быть достаточно прав, в противном случае часть данных будет недоступна или будет возвращаться NULL вместо актуального значения.
Состояние validated ограничений (все ли данные соответствуют ограничениям)
Некоторые типы ограничений (в настоящее время это ограничение-проверка CHECK и, с некоторыми оговорками, FOREIGN KEY) [1], могут создаваться с ключом NOT VALID. При создании ограничений для больших таблиц проверка уже имеющихся данных может быть длительной, поэтому разработчики пользуются удобным механизмом в PostgreSQL и разделяют процессы создания ограничения и проверки всех данных.
Важно отметить, что ограничение сразу после создания будет действовать при добавлении или изменении данных и любая из этих эти операции будут прервана, если новые данные не соответствуют ограничению. Однако на уровне базы данных будет установлен признак, что ограничение не было проверено для всех данных, пока оно не будет проверено командой VALIDATE CONSTRAINT.
Типовой сценарий:
создание ограничений с минимальными затратами времени в рамках обновления (применения пакета миграций, минимизируя время обновления). Это позволяет не останавливать обновление из-за ошибок в уже имеющихся данных, если потенциальное наличие таких ошибок в исторических данных прогнозируется и допускается;
новые данные будут проверяться и будут соответствовать ограничению. Новые данные не соответствующие ограничению не будут появляться, не будет гонки между процессами добавления и исправления данных;
уже имеющиеся данные дополнительно проверяются и, при необходимости, корректируются вне привязки к моменту обновления. Длительность этого этапа зависит от объема данных, сложности корректировок, наличия достаточных технологических окон. Здесь могут пересекаться зоны ответственности и разработки и эксплуатации;
корректные данные переводятся в состояние «всё проверено» с помощью
VALIDATE CONSTRAINT.
В некоторых случаях процесс корректировки может затянуться или потеряться между ответственными, либо в процессе разработки появились ограничения с NOT VALID, а задокументированный процесс по их валидации — нет.
Задача: проверить базу данных на наличие ограничений, у которых «не все данные проверены» с помощью VALIDATE CONSTRAINT, т.к. это признак потенциального наличия данных не соответствующих действующему ограничению.
Пример для проверки состояния validated ограничений
--
CREATE TABLE public.c1001_1
(
id integer GENERATED ALWAYS AS IDENTITY NOT NULL,
parent_id integer NOT NULL,
value integer NOT NULL,
CONSTRAINT c1001_1_pk PRIMARY KEY (id)
);
-- добавляем корректные и некорректные данные
INSERT INTO public.c1001_1 (parent_id, value) VALUES(1, 1);
INSERT INTO public.c1001_1 (parent_id, value) VALUES(-1, -1);
-- создаем ограничения с NOT VALID
-- чтобы исторические данные не проверялись при создании ограничений
ALTER TABLE public.c1001_1 ADD CONSTRAINT c1001_1_fk FOREIGN KEY (parent_id) REFERENCES public.c1001_1(id) NOT VALID;
ALTER TABLE public.c1001_1 ADD CONSTRAINT c1001_1_chk CHECK ( value > 0 ) NOT VALID;
-- добавляем корректные данные
-- ограничения уже действуют, некорректные не добавить
INSERT INTO public.c1001_1 (parent_id, value) VALUES(2, 2);
-- пока исторические данные не откорректированы
-- выполнить VALIDATE CONSTRAINT без ошибок не получится
ALTER TABLE public.c1001_1 VALIDATE CONSTRAINT c1001_1_fk;
ALTER TABLE public.c1001_1 VALIDATE CONSTRAINT c1001_1_chk;Код проверки состония validated ограничений
SELECT
t.relname, -- наименование отношения
c.conname, -- наименование ограничения
c.contype -- тип ограничения
FROM pg_catalog.pg_constraint AS c
INNER JOIN pg_catalog.pg_class AS t
ON t.oid = c.conrelid AND c.contype IN ('c', 'f')
AND (NOT c.convalidated);Состояние последовательностей (много ли осталось до предельного значения)
Суррогатные PK, если они не определены как UUID, как правило, заполняются значениями из некоторой последовательности (генератора). Каждая последовательность имеет граничные значения MINVALUE и MAXVALUE, к которым она стремится, в зависимости от значения шага INCREMENT [2]. СУБД проконтролирует, чтобы MINVALUE было меньше MAXVALUE, а INCREMENT был отличен от ноля.
Из-за массовых загрузок данных или изменения обстоятельств после создания последовательности расход значений может возрастать, объем оставшихся доступных для использования значений до достижения границы сокращается, если последовательность не создана цикличной. Применение цикличных последовательностей подходит не для всех типов данных и имеет свои подводные камни.
Задача: оценить в процентах объем оставшихся значений в последовательностях, низкое значение это признак возможного скорого исчерпания доступных значений.
Задача не сложная, если иметь параметры последовательности и её актуальное значение. Необходимое актуальное значение, с некоторыми оговорками, присутствует в представлении pg_catalog.pg_sequences [3], но отсутствует в системном каталоге pg_catalog.pg_sequence [4] (наименования различаются одной буквой). В формулы в примере ниже можно внести корректировку на единицу в числитель и/или знаменатель, как правило, это не сильно влияет на результат оценки.
Пример для проверки состония последовательностей
-- 8.08%
DROP SEQUENCE IF EXISTS public.s1010_2;
CREATE SEQUENCE public.s1010_2 AS smallint INCREMENT BY 1 MAXVALUE 100 START WITH 92;
-- 15.15%
DROP SEQUENCE IF EXISTS public.".s1010_3 ";
CREATE SEQUENCE public.".s1010_3 " AS smallint INCREMENT BY -1 MINVALUE -100 START WITH -85;
-- зацикленная последовательность, да неё не строим оценку
DROP SEQUENCE IF EXISTS public.s1010_5;
CREATE SEQUENCE public.s1010_5 AS smallint INCREMENT BY 1 MAXVALUE 100 START WITH 99 CYCLE;Код проверки состония последовательностей
SELECT
schemaname, -- наименование схемы
sequencename, -- наименование последовательности
CASE -- проверка направления роста последовательности
WHEN increment_by > 0 THEN 100.0*(max_value - COALESCE(last_value, start_value))/(max_value - min_value)
ELSE 100.0*(COALESCE(last_value, start_value) - min_value)/(max_value - min_value)
END::numeric(5, 2) -- значение в %
FROM pg_catalog.pg_sequences
WHERE NOT cycle -- исключаем цикличные последовательностиИндексирование полей, содержащих массив значений (правильный ли тип индекса выбран)
PostgreSQL, как и многие другие СУБД, поддерживает создание колонок, которые содержат многомерный массив [5], и широкий набор функций для работы с массивами [6].
Реализация работы с массивами в PostgreSQL лишь частично соответствует стандарту ISO/IEC 9075–15 Многомерные массивы (SQL/MDA) [7, 8].
Если в базе данных используются колонки с массивами часто на них создают индекс для ускорения поиска [9]. Здесь важно учитывать какие операции сравнения с массивами и их элементами будут использоваться. Если будет выполняться сравнение массивов целиком, эффективен будет индекс b-tree, который используется по умолчанию. Если же будут проверки на вхождение элементов в массив или их пересечение, эффективен индекс gin, который, если сильно упростить, заглядывает внутрь массива. Не все разработчики используют EXPLAIN [10] для оценки запросов и знают особенности разных типов индексов (методов доступа) PostgreSQL.
Второй случай, когда выполняется фильтрация по элементам массива, встречается на практике значительно чаще. Учитывая сказанное выше, целесообразно обратить внимание разработчика на ситуацию, когда для колонки с массивом значений создан индекс b-tree.
Пример для проверки индексирование полей, содержащих массив значений
DROP TABLE IF EXISTS public.i1010_1 CASCADE;
CREATE TABLE public.i1010_1
(
id integer GENERATED ALWAYS AS IDENTITY NOT NULL,
value integer[] NOT NULL,
CONSTRAINT i1010_1_pk PRIMARY KEY (id)
);
INSERT INTO public.i1010_1 (value) VALUES('{1, 3, 5}');
INSERT INTO public.i1010_1 (value) VALUES(ARRAY[2, 4, 6]);
-- создаем индексы, явно указывая методы доступа btree и gin
CREATE INDEX i_btree_value ON public.i1010_1 USING btree (value);
CREATE INDEX i_gin_value ON public.i1010_1 USING gin (value);
-- для демонстрации задействования индексов
-- отключаем последовательное сканирование
SET enable_seqscan TO off;
-- проверяем планы запросов в зависимости от операций
-- Bitmap Index Scan on i_gin_value
EXPLAIN
SELECT id FROM public.i1010_1 WHERE value @> ARRAY[1];
-- Index Scan using i_btree_value
EXPLAIN
SELECT id FROM public.i1010_1 WHERE value = ARRAY[2, 4, 6];Код для проверки индексирвоание полей, содержащих массив значений
SELECT
c.relname, -- наименование отношения
ic.relname, -- наименование индекса
a.amname -- тип индекса
FROM pg_catalog.pg_index AS i
INNER JOIN pg_catalog.pg_class AS ic ON i.indexrelid = ic.oid
INNER JOIN pg_catalog.pg_am AS a ON ic.relam = a.oid AND a.amname = 'btree'
INNER JOIN pg_catalog.pg_class AS c ON i.indrelid = c.oid
WHERE
-- проверяем существование в индексе колонки с типом массива
EXISTS (SELECT * FROM pg_catalog.pg_attribute AS att
INNER JOIN pg_catalog.pg_type AS typ ON typ.oid = att.atttypid
WHERE att.attrelid = i.indrelid
AND att.attnum = ANY ((string_to_array(indkey::text, ' ')::int2[])[1:indnkeyatts])
AND typ.typcategory = 'A')Продолжение
В следующей части рассмотрим проверки FOREIGN KEY — совпадение типов с обех сторон ограничения, не всегда очевидное поведение при наличии nullable колонок.
Статический анализ структуры базы данных целесообразно проводить в т.ч. в prod окружении, на регулярной основе, завязать результаты проверок на мониторинг. Уверен, что в недалеком будущем это перестанет восприниматься как экзотика и подобные инструменты для проверки будут «из коробки» предоставлять облачные провайдеры баз данных и плагины к IDE.
Ссылки
[1] https://postgrespro.ru/docs CONSTRAINT NOT VALID
[2] https://postgrespro.ru/docs CREATE SEQUENCE
[3] https://postgrespro.ru/docs pg_sequences
[4] https://postgrespro.ru/docs pg_sequence
[5] https://postgrespro.ru/docs arrays
[6] https://postgrespro.ru/docs functions array
[7] https://postgrespro.ru/docs features
[8] https://postgrespro.ru/docs features sql standard
[9] https://postgrespro.ru/docs CREATE INDEX
[10] https://postgrespro.ru/docs EXPLAIN
Рассмотренные выше проверки можно найти в репозитории https://github.com/sdblist/db_verifier
