Возможно ли применить статический анализ структуры базы данных к реальным проектам, которые используют PostgeSQL, какой будет результат? Давайте применим и посмотрим что получится. В качестве реального проекта возьмем инструмент с открытым кодом, которым многие пользуются ежедневно - GitLab.

Список статей

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

GitLab в подробном представлении не нуждается, перейдём сразу к технической части.

Источники данных о структуре БД

Для GitLab скрипт создания БД получен из репозитория по ссылке https://gitlab.com/gitlab-org/gitlab/-/blob/master/db/structure.sql.

Структура баз данных GitLab активно развивается и обязательно изменится со временем. Одна из найденных при проверке ошибок уже исправлена, пока статья готовилась.

GitLab

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

similar indexes (i1001)

Проверка similar indexes (i1001) - индексы очень похожи (возможно совпадают), уровень warning. В отчёте 12 записей.

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

Скрытый текст

ALTER TABLE ONLY user_details ADD CONSTRAINT user_details_pkey PRIMARY KEY (user_id);

CREATE UNIQUE INDEX index_user_details_on_user_id ON user_details USING btree (user_id);

similar indexes unique and not unique (i1003)

Проверка similar indexes unique and not unique (i1003) - уникальный и не уникальный индексы очень похожи (возможно не уникальный лишний), уровень warning. В отчёте 15 записей.

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

Скрытый текст

ALTER TABLE ONLY user_statuses ADD CONSTRAINT user_statuses_pkey PRIMARY KEY (user_id);

CREATE INDEX index_user_statuses_on_user_id ON user_statuses USING btree (user_id);

constraint not validated (c1001)

Проверка constraint not validated (c1001) - ограничение не проверено для всех данных (возможно присутствуют записи, нарушающие ограничение), уровень warning. В отчёте 22 записи. Сценарии появления таких ограничений и связанные с этим риски мы рассматривали в первой статье цикла.

Таблица public.releases. Для неё создаётся ограничение (CONSTRAINT) CHECK releases_not_null_tag с явным указанием NOT VALID.

Скрытый текст

-- описание структуры таблицы сокращено
CREATE TABLE releases (
    id bigint NOT NULL,
    tag character varying
);


ALTER TABLE releases ADD CONSTRAINT releases_not_null_tag CHECK ((tag IS NOT NULL)) NOT VALID;

fk uses mismatched types (fk1001)

Проверка fk uses mismatched types (fk1001) - Внешний ключ использует колонки с несовпадающими типами, уровень error. В отчёте таких записей много, частично это объясняется использованием секционирования, когда один некорректный foreign key воспроизводится во всех секциях.

Таблица public.issue_search_data имеет 64 секции. Для неё создается внешний ключ по полю project_id с типом bigint на поле с типом integer. Пока статья готовилась эта ошибка была исправлена (commit Convert integer ID columns to bigint).

Скрытый текст

-- описание структуры таблицы сокращено
CREATE TABLE projects (
    id integer NOT NULL,
    name character varying
);

CREATE TABLE issue_search_data (
    project_id bigint NOT NULL,
    issue_id bigint NOT NULL
)
PARTITION BY HASH (project_id);



ALTER TABLE issue_search_data ADD CONSTRAINT issue_search_data_project_id_fkey FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE;

Интересные особенности

В таблицах для текстовых данных часто используется тип text, а для контроля максимального размера данных создается явное ограничение (CONSTRAINT) CHECK. Как правило в подобных случаях используется тип varchar(X). В некоторых таблицах используются одновременно оба подхода.

Скрытый текст

-- описание структуры таблицы сокращено
CREATE TABLE user_details (
    user_id bigint NOT NULL,
    job_title character varying(200) DEFAULT ''::character varying NOT NULL,
    bio character varying(255) DEFAULT ''::character varying NOT NULL,
    webauthn_xid text,
    phone text,
    bluesky text DEFAULT ''::text NOT NULL,
    CONSTRAINT check_18a53381cd CHECK ((char_length(bluesky) <= 256)),
    CONSTRAINT check_245664af82 CHECK ((char_length(webauthn_xid) <= 100)),
    CONSTRAINT check_a73b398c60 CHECK ((char_length(phone) <= 50))
);

Ограничение NOT NULL может быть реализовано разными способами даже в рамках одной таблицы.

Скрытый текст

-- описание структуры таблицы сокращено
CREATE TABLE releases (
    id bigint NOT NULL,
    tag character varying,
    description text,
    project_id bigint,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL,
    CONSTRAINT check_6bb9ce4925 CHECK ((project_id IS NOT NULL))
);

ALTER TABLE releases ADD CONSTRAINT releases_not_null_tag CHECK ((tag IS NOT NULL)) NOT VALID;

Рассмотренные выше проверки можно найти в репозитории https://github.com/sdblist/db_verifier

P.S.

Для каких открытых проектов, использующих PostgreSQL, стоило бы сделать аналогичную проверку?

Интересно протестировать скрипт проверок на СУБД родственных PostgreSQL - есть ли изменения системных представлений, появились ли новые полезные для анализа свойства объектов.

Статический анализ структуры базы данных в проекте GitLab