PostgreSQL
DDL: Создание и изменение структур
2012 год. Knight Capital Group - крупнейший маркет-мейкер США. За 45 минут они потеряли 440 миллионов долларов. Причина: деплой нового кода без миграции схемы базы данных. Одна таблица имела неправильные constraints, и торговый алгоритм начал исполнять сделки в 1000 раз больше нужного. DDL - это не просто синтаксис. Это контракт между вашим кодом и данными.
- **Миграции схемы**: ALTER TABLE без блокировки таблицы - критическая задача для high-load систем
- **Constraints как документация**: NOT NULL, UNIQUE, CHECK - код читает схему, чтобы понять инварианты данных
- **Zero-downtime deployments**: порядок DDL операций определяет, будет ли даунтайм при деплое
- **PostgreSQL partitioning**: PARTITION BY требует DDL планирования на этапе проектирования
SQL DDL: от System R до PostgreSQL
В 1974 году IBM Research разработала SEQUEL (Structured English Query Language) для проекта System R. Чемберлин и Бойс создали синтаксис CREATE TABLE и ALTER TABLE, который с минимальными изменениями дожил до PostgreSQL 16. Когда Oracle, IBM DB2 и PostgreSQL соревновались за рынок в 1980-90е, DDL был зоной жёсткой стандартизации - SQL-86, SQL-92, SQL:2003. PostgreSQL добавил расширения: CREATE TABLE AS, INHERITS, PARTITION BY, которых нет в стандарте. Но базовый CREATE TABLE остался неизменным 50 лет.
CREATE TABLE: Создание таблицы
CREATE TABLE определяет структуру таблицы: колонки, типы данных, constraints. Это фундаментальный DDL оператор.
**BIGSERIAL vs BIGINT GENERATED ALWAYS AS IDENTITY:** BIGSERIAL - старый синтаксис (создаёт sequence). IDENTITY (PostgreSQL 10+) - стандарт SQL:2003, более корректный. Для новых проектов предпочитать IDENTITY.
Что произойдёт при выполнении: CREATE TABLE users (id BIGSERIAL PRIMARY KEY); второй раз в той же базе данных?
Constraints: Ограничения целостности
Constraints - это инварианты данных, которые PostgreSQL проверяет автоматически. Они предотвращают некорректные данные даже если application код содержит баг.
| Constraint | Описание | Пример |
|---|---|---|
| NOT NULL | Значение обязательно | email TEXT NOT NULL |
| UNIQUE | Уникальность значения | username VARCHAR UNIQUE |
| PRIMARY KEY | NOT NULL + UNIQUE + индекс | id BIGINT PRIMARY KEY |
| FOREIGN KEY | Ссылочная целостность | REFERENCES users(id) |
| CHECK | Произвольное условие | CHECK (price >= 0) |
| DEFAULT | Значение по умолчанию | DEFAULT NOW() |
**FOREIGN KEY и производительность:** FK автоматически не создаёт индекс на ссылающейся колонке (только на PRIMARY KEY ссылаемой таблицы). Для JOIN по FK всегда создавайте INDEX вручную: CREATE INDEX ON orders(user_id).
У таблицы orders есть FOREIGN KEY на users(id) с ON DELETE RESTRICT. Что произойдёт при попытке удалить пользователя у которого есть заказы?
ALTER TABLE: Изменение структуры
ALTER TABLE изменяет структуру существующей таблицы. Критически важно понимать, какие операции блокируют таблицу, а какие - нет.
**Блокировки ALTER TABLE в PostgreSQL:** ADD COLUMN без DEFAULT - мгновенно. ADD COLUMN с DEFAULT (PG11+) - мгновенно. ALTER COLUMN TYPE - полная перезапись таблицы (AccessExclusiveLock). ADD UNIQUE - создаёт индекс (блокирует). Для production используйте CREATE INDEX CONCURRENTLY перед ADD CONSTRAINT.
Нужно добавить NOT NULL колонку `status VARCHAR(20)` в таблицу orders с 50 миллионами строк. Как это сделать без длительной блокировки?
CREATE INDEX: Создание индексов
Индексы создаются DDL командами. PostgreSQL автоматически создаёт индекс для PRIMARY KEY и UNIQUE constraints. Остальные - вручную.
**CREATE INDEX CONCURRENTLY** позволяет создавать индекс без блокировки записей. Занимает больше времени (2-3 прохода по таблице), но таблица остаётся доступной для INSERT/UPDATE/DELETE. Незаменимо в production.
Чем больше индексов, тем быстрее SELECT запросы
Каждый индекс замедляет INSERT/UPDATE/DELETE и занимает место. Нужно создавать только необходимые индексы на основе реальных запросов.
Индекс - это дополнительная структура данных. При каждом INSERT PostgreSQL обновляет все индексы таблицы. 10 индексов = 10 дополнительных операций на каждый INSERT. Для write-heavy таблиц лишние индексы критически снижают производительность.
Нужно создать индекс на таблице products (1М строк) в production. Запросы к таблице не должны ждать. Какую команду использовать?
Ключевые идеи
- **CREATE TABLE**: определяет структуру - колонки, типы, constraints
- **Constraints**: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK - инварианты данных на уровне БД
- **ALTER TABLE**: добавление/удаление колонок и constraints - осторожно с блокировками!
- **Индексы**: CREATE INDEX автоматически для PRIMARY KEY и UNIQUE, вручную для performance
- **Миграции**: DDL в продакшене требует планирования - некоторые операции блокируют таблицу
Вопросы для размышления
- Почему NOT NULL constraint лучше реализовывать на уровне базы данных, а не только в application коде?
- ALTER TABLE ADD COLUMN с DEFAULT VALUE в PostgreSQL 11+ выполняется мгновенно без перезаписи таблицы - как это возможно?
- Как правильно добавить FOREIGN KEY на таблицу с миллионами строк без блокировки?