25 железных правил проектирования баз данных в PostgreSQL

Каждый, кто хоть раз разбирался в три часа ночи с упавшим продом, знает: большинство катастроф в базах данных это не сбой железа и не космические лучи. Это решения, принятые на этапе проектирования схемы. «Потом поправим», «в приложении проверим», «а зачем тут индекс?» каждая из этих фраз обходилась командам в часы даунтайма и миллионы потерянных строк.

Ниже 25 правил, которые я собрал из опыта работы с высоконагруженными системами. Это не теория из учебника — это грабли, на которые уже наступили до вас. Каждое правило сопровождается примером «как надо» и «как не надо», чтобы разница была наглядной.

I. Фундамент схемы

1. Всегда используйте суррогатный первичный ключ

Критичность: максимальная

Натуральные ключи (email, ИНН, username) меняются. Когда это произойдёт, вам предстоит каскадное обновление миллионов строк и всех FK-ссылок. Суррогатные ключи (BIGSERIAL / UUID) не меняются никогда.

-- ✅ Правильно
CREATE TABLE users (
  id         BIGSERIAL PRIMARY KEY,
  email      TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ❌ Неправильно
CREATE TABLE users (
  email TEXT PRIMARY KEY  -- катастрофа, ждущая своего часа
);

2. Каждая таблица ОБЯЗАНА иметь created_at и updated_at

Критичность: максимальная

Без таймстемпов вы не сможете отлаживать продовые инциденты, строить аудит-трейл или делать инкрементальный ETL. Используйте TIMESTAMPTZ (с временной зоной), а не TIMESTAMP.

-- ✅ Правильно
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

-- ❌ Неправильно
-- Никаких колонок с временными метками вообще
-- Или TIMESTAMP без временной зоны

3. Используйте TIMESTAMPTZ, а не TIMESTAMP

Критичность: высокая

TIMESTAMP молча отбрасывает информацию о часовом поясе. Когда ваши серверы приложений работают в разных часовых поясах, сохранённое время становится двусмысленным и неконсистентным. TIMESTAMPTZ хранит всё в UTC внутри.

-- ✅ Правильно
event_time TIMESTAMPTZ NOT NULL

-- ❌ Неправильно
event_time TIMESTAMP  -- часовой пояс потерян навсегда

4. Используйте TEXT вместо VARCHAR(n)

Критичность: обычная

В PostgreSQL TEXT и VARCHAR дают идентичную производительность. VARCHAR(n) лишь добавляет CHECK-ограничение, которое придётся мигрировать, когда требования изменятся. Для реальной валидации используйте CHECK.

-- ✅ Правильно
name TEXT NOT NULL,
CONSTRAINT chk_name_len CHECK(length(name) <= 255)

-- ❌ Неправильно
name VARCHAR(255)  -- придётся менять при изменении требований

5. Используйте BIGINT / BIGSERIAL для ID, а не INT

Критичность: высокая

INT имеет максимум ~2.1 миллиарда. Такие компании, как Slack и Digg, уже упирались в этот потолок. BIGINT стоит всего на 4 байта больше на строку, но вмещает 9.2 квинтиллиона значений.

-- ✅ Правильно
id BIGSERIAL PRIMARY KEY

-- ❌ Неправильно
id SERIAL PRIMARY KEY  -- бомба замедленного действия

II. Связи и внешние ключи

6. ВСЕГДА определяйте явные внешние ключи

Критичность: максимальная

Без FK-ограничений «осиротевшие» записи будут молча накапливаться. Логика приложения — не замена: краши, баги и race condition будут создавать несогласованность.

-- ✅ Правильно
user_id BIGINT NOT NULL
  REFERENCES users(id)
  ON DELETE CASCADE

-- ❌ Неправильно
user_id BIGINT  -- "мы будем проверять в коде приложения"

7. Выбирайте ON DELETE осознанно

Критичность: максимальная

По умолчанию стоит RESTRICT (блокирует удаление). CASCADE автоматически удаляет дочерние записи. SET NULL сохраняет строку. Неверный выбор либо блокирует операции, либо молча уничтожает данные.

-- ✅ Правильно
-- Зависимые данные: CASCADE
REFERENCES orders(id) ON DELETE CASCADE

-- Необязательная ссылка: SET NULL
REFERENCES users(id) ON DELETE SET NULL

-- Критичные данные: RESTRICT (по умолчанию)
REFERENCES accounts(id)  -- блокирует удаление

-- ❌ Неправильно
-- Никогда не думать о поведении ON DELETE

8. Используйте таблицы связей (junction tables) для M:N

Критичность: максимальная

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

-- ✅ Правильно
CREATE TABLE user_roles (
  user_id    BIGINT REFERENCES users(id),
  role_id    BIGINT REFERENCES roles(id),
  granted_at TIMESTAMPTZ DEFAULT NOW(),
  PRIMARY KEY (user_id, role_id)
);

-- ❌ Неправильно
role_ids INTEGER[]      -- нельзя JOIN, нельзя FK
roles    TEXT           -- 'admin,editor' 🤮

9. Индексируйте каждую FK-колонку

Критичность: высокая

PostgreSQL НЕ создаёт индексы автоматически для FK (в отличие от MySQL). Без индекса каждое удаление/обновление родителя вызывает sequential scan дочерней таблицы, а это блокировка на уровне таблицы.

-- ✅ Правильно
CREATE INDEX idx_orders_user_id
  ON orders(user_id);

-- ❌ Неправильно
-- FK без индекса = seq scan при JOIN

10. Предпочитайте мягкое удаление для критичных бизнес-данных

Критичность: высокая

Жёсткое удаление необратимо и ломает аудит-трейл. Добавьте колонку deleted_at и фильтруйте в запросах. Используйте partial-индексы для сохранения производительности.

-- ✅ Правильно
deleted_at TIMESTAMPTZ DEFAULT NULL;

-- Partial index: только активные строки
CREATE INDEX idx_users_active
  ON users(email)
  WHERE deleted_at IS NULL;

-- ❌ Неправильно
DELETE FROM users WHERE id = 42;
-- удалено навсегда, нет аудит-трейла

III. Нормализация и целостность данных

11. Нормализуйте до 3NF как минимум, денормализуйте осознанно

Критичность: максимальная

Начинайте с нормализации. Каждая денормализация — это осознанный компромисс: скорость чтения ценой сложности записи и рисков несогласованности. Документируйте, почему вы денормализовали.

-- ✅ Правильно: единый источник правды
orders.user_id → users.id → users.email

-- Денормализация ТОЛЬКО если замерено:
-- "Добавил email в таблицу orders для
--  отчёта по биллингу: запрос ускорился
--  с 800мс до 12мс на 50M строк"

-- ❌ Неправильно
-- Дублирование user_name, user_email
-- в каждой таблице "для удобства"

12. Используйте NOT NULL по умолчанию, NULL — только намеренно

Критичность: максимальная

NULL вводит трёхзначную логику. NULL != NULL, сравнения с NULL возвращают NULL, агрегаты молча пропускают NULL. Каждая nullable-колонка требует COALESCE повсюду.

-- ✅ Правильно
status     TEXT NOT NULL DEFAULT 'pending',
deleted_at TIMESTAMPTZ  -- NULL = намеренно

-- ❌ Неправильно
name  TEXT,     -- nullable случайно
price NUMERIC  -- NULL или 0? кто знает

13. Используйте CHECK-ограничения для валидации данных

Критичность: высокая

Валидация на уровне приложения обходится через миграции, скрипты и прямой доступ к БД. Ограничения в БД — это последний рубеж обороны, и они работают всегда.

-- ✅ Правильно
CONSTRAINT chk_price_positive
  CHECK (price > 0),
CONSTRAINT chk_status_valid
  CHECK (status IN ('active','inactive','suspended'))

-- ❌ Неправильно
-- "валидация обрабатывается в API"
-- *в БД price = -500 и status = 'yolo'*

14. Используйте NUMERIC для денег, никогда FLOAT/DOUBLE

Критичность: максимальная

Арифметика с плавающей точкой: 0.1 + 0.2 = 0.30000000000000004. Для финансовых данных NUMERIC(precision, scale) даёт точную десятичную математику. Или храните копейки как BIGINT.

-- ✅ Правильно
price    NUMERIC(12,2) NOT NULL,
balance  NUMERIC(15,2) NOT NULL
-- или: price_cents BIGINT NOT NULL

-- ❌ Неправильно
price FLOAT            -- $0.30000000000000004
price DOUBLE PRECISION -- та же проблема

15. Используйте ENUM осторожно — предпочитайте CHECK или справочные таблицы

Критичность: обычная

PostgreSQL ENUM-ы нельзя легко изменить — можно добавить значения, но нельзя удалить или переименовать без пересоздания типа. CHECK-ограничения или справочные таблицы гораздо гибче.

-- ✅ Правильно: CHECK constraint
status TEXT NOT NULL
  CHECK(status IN ('draft','published'))

-- Или: справочная таблица для большого числа значений
REFERENCES statuses(code)

-- ❌ Неправильно
CREATE TYPE status AS ENUM(
  'draft','published'
);  -- трудно изменить позже

IV. Индексирование и производительность

16. Создавайте индексы для каждого WHERE, JOIN и ORDER BY

Критичность: максимальная

Без индексов PostgreSQL делает sequential scan — читает каждую строку. На таблице в 100М строк это разница между 5 мс и 5 минутами.

-- ✅ Правильно
-- Составной индекс для типичного запроса
CREATE INDEX idx_orders_user_status
  ON orders(user_id, status)
  WHERE deleted_at IS NULL;

-- ❌ Неправильно
-- "Добавим индексы, когда станет медленно"
-- (3 часа ночи, прод горит)

17. Используйте partial-индексы для индексации только нужных строк

Критичность: высокая

Если 95% строк имеют статус completed и вы запрашиваете только pending, полный индекс — пустая трата места. Partial-индексы компактнее, быстрее и экономят память.

-- ✅ Правильно
CREATE INDEX idx_orders_pending
  ON orders(created_at)
  WHERE status = 'pending';

-- ❌ Неправильно
CREATE INDEX idx_orders_created
  ON orders(created_at);
-- индексирует ВСЕ 100M строк ради 5% запросов

18. Используйте EXPLAIN ANALYZE перед деплоем запросов

Критичность: высокая

Нельзя угадать производительность запроса. EXPLAIN ANALYZE показывает фактический план выполнения, оценки строк и время. Seq Scan на большой таблице = добавьте индекс.

-- ✅ Правильно
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42 AND status = 'pending';

-- ❌ Неправильно
-- Деплоим запрос в прод
-- "На деве с 10 строками вроде нормально"

19. Используйте пулинг соединений (PgBouncer)

Критичность: максимальная

Каждое соединение PostgreSQL стоит ~10 МБ RAM. 1000 прямых соединений = 10 ГБ только на коннекты. PgBouncer мультиплексирует соединения и обслуживает тысячи клиентов с минимальным оверхедом.

-- ✅ Правильно
App → PgBouncer (порт 6432) → PostgreSQL
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

-- ❌ Неправильно
App → PostgreSQL (напрямую, 500 коннектов)
-- OOM killer вступает в чат

V. Миграции и операционная работа

20. Никогда не меняйте колонки в продакшене без плана миграции

Критичность: максимальная

ALTER TABLE может заблокировать таблицу на часы при больших объёмах данных. Всегда: добавьте новую колонку → бэкфилл → переключите чтения → удалите старую. Никогда не переименовывайте «на месте».

-- ✅ Правильно
-- Шаг 1: Добавляем новую колонку (мгновенно)
ALTER TABLE users ADD COLUMN name_new TEXT;

-- Шаг 2: Бэкфилл батчами
-- Шаг 3: Переключаем приложение на name_new
-- Шаг 4: Удаляем старую колонку

-- ❌ Неправильно
ALTER TABLE users RENAME COLUMN
  name TO full_name;
-- приложение мгновенно ломается

21. UUID v7 — для распределённых систем, BIGSERIAL — для одного узла

Критичность: высокая

BIGSERIAL проще и компактнее (8 байт против 16). Но в распределённых системах / микросервисах UUID избавляет от координации. UUIDv7 сортируется по времени и дружелюбен к индексам (в отличие от v4).

-- ✅ Правильно
-- Один инстанс PostgreSQL:
id BIGSERIAL PRIMARY KEY

-- Распределённая система / микросервисы:
id UUID PRIMARY KEY
  DEFAULT gen_random_uuid()  -- v4
-- Или генерируйте UUIDv7 на стороне приложения

-- ❌ Неправильно
-- UUID v4 как кластерный PK на огромных таблицах
-- Рандомные вставки = постоянные page splits

22. Всегда используйте транзакции для многошаговых операций

Критичность: максимальная

Без явных транзакций каждый оператор автоматически коммитится. Если шаг 2 из 3 упадёт, у вас будут неполные данные. Оборачивайте связанные операции в BEGIN/COMMIT.

-- ✅ Правильно
BEGIN;
  UPDATE accounts SET balance = balance - 100
    WHERE id = 1;
  UPDATE accounts SET balance = balance + 100
    WHERE id = 2;
COMMIT;

-- ❌ Неправильно
UPDATE accounts SET balance = balance - 100
  WHERE id = 1;
-- крэш здесь = деньги испаряются
UPDATE accounts SET balance = balance + 100
  WHERE id = 2;

23. Партиционируйте большие таблицы (100M+ строк)

Критичность: высокая

Партиционируйте по времени (range) или по тенанту (list/hash). Запросы к одной партиции пропускают сканирование остальных. VACUUM и обслуживание индексов выполняются для каждой партиции отдельно.

-- ✅ Правильно
CREATE TABLE events (
  id         BIGSERIAL,
  created_at TIMESTAMPTZ NOT NULL,
  payload    JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2025_01
  PARTITION OF events
  FOR VALUES FROM ('2025-01-01')
  TO ('2025-02-01');

-- ❌ Неправильно
-- 500M строк в одной таблице
-- VACUUM занимает 6 часов
-- Каждый запрос = full table scan

24. Храните JSON в JSONB, а не в JSON или TEXT

Критичность: обычная

JSONB — бинарный формат, поддерживает индексы (GIN) и операторы включения (@>, ?). JSON — просто валидированный текст, парсится заново при каждом обращении. TEXT вообще не проверяет валидность.

-- ✅ Правильно
metadata JSONB NOT NULL DEFAULT '{}';

CREATE INDEX idx_meta_gin
  ON products USING GIN(metadata);

-- ❌ Неправильно
metadata JSON   -- перепарсивается при каждом чтении
metadata TEXT   -- никакой валидации

25. Используйте Row-Level Security (RLS) для мультитенантных приложений

Критичность: обычная

Баги в приложении могут привести к утечке данных тенанта. RLS обеспечивает изоляцию на уровне базы данных — даже SQL-инъекция не сможет пересечь границы тенанта.

-- ✅ Правильно
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON documents
  USING (tenant_id = current_setting('app.tenant_id'));

-- ❌ Неправильно
-- WHERE tenant_id = ? в каждом запросе
-- Один пропущенный WHERE = утечка данных

Шпаргалка по именованию

Быстрая справка по конвенциям, чтобы ваша схема была консистентной:

Таблицы - множественное число, snake_case: users, order_items. Никогда не в единственном числе и не camelCase.

Первичные ключи - всегда id с типом BIGSERIAL или UUID. Никогда не составные PK на бизнес-данных.

Внешние ключи - паттерн {singular_table}_id: user_id, order_id.

Индексы - паттерн idx_{table}_{columns}: idx_users_email.

Ограничения - паттерн chk_{table}_{desc} или uq_{table}_{cols}. Будьте явными.

Таймстемпы - каждая таблица получает created_at + updated_at. Всегда TIMESTAMPTZ, никогда TIMESTAMP.

Заключение

Эти 25 правил не догма, а концентрированный опыт команд, которые обслуживают базы с миллиардами строк. Каждое «неправильно» из списка выше это реальный инцидент, который кто-то пережил (часто в нерабочее время).

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

Если статья была полезна сохраните шпаргалку и поделитесь с командой. Меньше граблей - больше сна.