🧩 Реляционная модель данных

Короткий ответ SERIAL — «синтаксический сахар», создающий отдельную sequence и DEFAULT на колонку, а GENERATED ... AS IDENTITY (стандарт SQL, PostgreSQL 10+) — более современный и строгий способ, явно привязывающий генерацию значения к колонке; в новом коде предпочтительнее IDENTITY. Как это работает подробнее Оба механизма используют один и тот же объект sequence, но SERIAL реализован через DEFAULT nextval('seq_name'), который можно случайно переопределить обычным INSERT с явным значением, тогда как GENERATED ALWAYS AS IDENTITY по умолчанию запрещает указывать значение вручную. Пример кода Что использовать на практике Практика. IDENTITY ближе к стандарту SQL:2003, лучше защищает от случайной перезаписи id и чище выглядит в DDL; SERIAL остаётся широко распространён в legacy-коде и старых примерах. Подводные камни Ошибка. Вручную вставляют id без учёта текущего значения sequence (например, при миграции данных), из-за чего последующий автоинкремент пытается вставить уже занятый id и падает с unique violation —

Читать полностьюconstraints

Короткий ответ NULL обозначает отсутствие значения, а не конкретное значение, поэтому любое сравнение с NULL через =, !=, <, > возвращает NULL (не true/false), и строка не попадает в результат WHERE; проверять нужно через IS NULL / IS NOT NULL. Как это работает подробнее SQL использует трёхзначную логику (TRUE/FALSE/UNKNOWN); WHERE оставляет только строки, где условие дало TRUE, а UNKNOWN отбрасывается так же, как FALSE. Агрегатные функции COUNT(column), SUM, AVG игнорируют NULL-значения. Пример кода Что использовать на практике Практика. Для замены NULL на значение по умолчанию используют COALESCE(column, 'default'); для подсчёта всех строк включая NULL используют COUNT(*), а не COUNT(column). Подводные камни Ошибка. Пишут WHERE column = NULL вместо WHERE column IS NULL и получают пустой результат без ошибки; не учитывают, что NOT IN с подзапросом, содержащим NULL, возвращает пустой результат для всех строк.

Читать полностьюconstraints

Короткий ответ JSONB уместен для действительно полуструктурированных или часто меняющихся по составу данных, когда заранее неизвестна или нестабильна схема, а не как универсальная замена нормализованных таблиц; для ускорения поиска по ключам/значениям внутри JSONB создают GIN-индекс. Как это работает подробнее JSONB хранит данные в бинарном разложенном виде, что ускоряет выборочный доступ к ключам и позволяет строить GIN-индекс, поддерживающий операторы @>, ?, ?& и поиск по пути jsonb_path_ops. Пример кода Что использовать на практике Практика. Типичный баланс — вынести стабильные, часто фильтруемые атрибуты в обычные колонки/таблицы, а вариативные или редко запрашиваемые — в JSONB; злоупотребление JSONB усложняет валидацию, JOIN и миграции данных внутри самого JSON. Подводные камни Ошибка. Хранят в JSONB данные, которые по сути реляционные и регулярно участвуют в JOIN/агрегации, что затрудняет аналитические запросы; создают обычный B-tree индекс на JSONB-колонку целиком вместо GIN-индекса.

Читать полностьюconstraints

Короткий ответ PRIMARY KEY — это единственное на таблицу ограничение, идентифицирующее строку, автоматически NOT NULL; UNIQUE гарантирует отсутствие дублей в колонке(ах), но допускает NULL (в PostgreSQL несколько NULL допустимы), и таких ограничений может быть сколько угодно. Как это работает подробнее Оба ограничения реализуются через уникальный индекс. Разница в семантике: PK один на таблицу и используется как основной идентификатор для внешних ключей, UNIQUE может быть добавлен на любые колонки для бизнес-правил (например, email должен быть уникален). Пример кода Что использовать на практике Практика. Типичный паттерн — SERIAL/UUID как PK для внутренней идентификации плюс UNIQUE на email или на связке колонок (например, (user_id, role_id) для предотвращения дублирования связи). Подводные камни Ошибка. Думают, что UNIQUE запрещает NULL — в PostgreSQL несколько строк с NULL в UNIQUE-колонке допустимы, так как NULL не равен NULL; если нужно запретить больше одной «пустой» записи, нужен отдельный partial uniqu

Читать полностьюconstraints

Короткий ответ В PostgreSQL VARCHAR(n) и TEXT хранятся одинаково и не имеют разницы в производительности — VARCHAR(n) лишь добавляет ограничение максимальной длины, TEXT не ограничивает длину вовсе, а CHAR(n) дополняет строку пробелами до фиксированной длины n и почти никогда не нужен в современных приложениях. Как это работает подробнее Внутри PostgreSQL все три типа используют один и тот же механизм хранения переменной длины (varlena); VARCHAR(n) добавляет проверку длины при вставке/обновлении, CHAR(n) всегда дополняет строку до n символов пробелами справа. Пример кода Что использовать на практике Практика. Для большинства полей (имена, описания, email) рекомендуется TEXT без ограничения длины на уровне БД, валидацию делать на уровне приложения/ORM; VARCHAR(n) оправдан, когда ограничение длины — реальное бизнес-правило. Подводные камни Ошибка. Выбирают CHAR(n) по привычке из других СУБД, не замечая паддинг пробелами, что ломает сравнение строк; жёстко ограничивают VARCHAR(255) «по умолчанию» без анализа тре

Читать полностьюconstraints

Короткий ответ DELETE удаляет строки по условию и может быть отменён в транзакции, TRUNCATE мгновенно очищает всю таблицу без построчной обработки (и в PostgreSQL тоже транзакционен), а DROP полностью удаляет объект таблицы вместе со схемой. Как это работает подробнее DELETE построчно проверяет условие WHERE и может вызывать триггеры ON DELETE. TRUNCATE не сканирует строки, быстро освобождает страницы таблицы и по умолчанию сбрасывает SERIAL/IDENTITY, если не указано CONTINUE IDENTITY. DROP удаляет саму структуру таблицы, включая индексы и ограничения. Пример кода Что использовать на практике Практика. DELETE используют, когда нужно удалить часть данных по условию или нужны триггеры; TRUNCATE — для быстрой очистки всей таблицы в тестах/ETL; DROP — при удалении таблицы из схемы, например при откате миграции. Подводные камни Ошибка. Используют DELETE FROM table; без WHERE для очистки большой таблицы — это медленнее TRUNCATE и сильнее нагружает WAL и автовакуум; забывают, что TRUNCATE требует ACCESS EXCLUSIVE lo

Читать полностьюconstraints

Короткий ответ Нормализация — это процесс структурирования таблиц так, чтобы устранить избыточное дублирование данных и функциональные зависимости, не относящиеся напрямую к первичному ключу; на практике чаще всего ориентируются на 3-ю нормальную форму (3NF), где неключевые атрибуты зависят только от первичного ключа. Как это работает подробнее Денормализованная таблица часто хранит повторяющиеся группы атрибутов (например, название и адрес компании в каждой строке заказа); нормализация выносит такие данные в отдельную таблицу со своим PK, а исходная ссылается на неё через FK. Пример кода Что использовать на практике Практика. Нормализация снижает избыточность и риск рассинхронизации данных при обновлении, но увеличивает число JOIN на чтение; в реальных системах иногда сознательно идут на частичную денормализацию для производительности, документируя это решение. Подводные камни Ошибка. Нормализуют «до упора» без учёта паттернов доступа, получая схему с 8-10 JOIN на типовой запрос; либо оставляют дублирование

Читать полностьюconstraints