Основы PostgreSQL: SQL, таблицы и индексы
Надёжная работа с PostgreSQL начинается не с выбора ORM, а с понимания модели данных и того, как база выполняет запросы. В этой статье разберём таблицы и связи, ограничения, базовый SQL, индексы и транзакции — фундамент для серверных приложений и дальнейшего изучения PostgreSQL.
Содержание
- Реляционная модель и схема
- Ограничения и связи
- Базовый SQL
- Индексы и план выполнения
- Транзакции и изоляция
- Нормализация и денормализация
- Ключевые тезисы для интервью
- Заключение
- Полезные ссылки
Реляционная модель и схема
Таблица описывает сущность: например, users хранит пользователей, а orders — заказы. Строка — отдельная запись, столбец — атрибут с типом данных. Схема должна отражать предметную область и явно фиксировать обязательные поля.
CREATE TABLE users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id bigint NOT NULL REFERENCES users(id),
total numeric(12, 2) NOT NULL CHECK (total >= 0),
created_at timestamptz NOT NULL DEFAULT now()
);
Связь «один ко многим» задаётся внешним ключом: у одного пользователя может быть много заказов, но каждый заказ принадлежит существующему пользователю.
Ограничения и связи
Ограничения — это исполняемые правила качества данных. NOT NULL запрещает отсутствие значения, CHECK проверяет условие, UNIQUE защищает от дублей, а PRIMARY KEY объединяет уникальность и идентификацию строки.
Внешний ключ можно дополнить поведением при изменении или удалении родителя: ON DELETE CASCADE удалит зависимые строки, а ON DELETE RESTRICT не позволит удалить родителя. Выбор зависит от смысла данных: каскад уместен для дочерних объектов, но опасен для финансовой истории.
Базовый SQL
Чтение данных обычно строится вокруг SELECT, фильтрации, соединений и группировки:
SELECT u.id, u.email, count(o.id) AS order_count
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE u.created_at >= date '2026-01-01'
GROUP BY u.id, u.email
HAVING count(o.id) > 0
ORDER BY order_count DESC
LIMIT 20;
JOIN объединяет строки по условию, LEFT JOIN сохраняет пользователей без заказов, WHERE фильтрует отдельные строки, а HAVING — результат группировки. В приложении значения передаются параметрами, а не конкатенацией строк: это защищает от SQL-инъекций и помогает переиспользовать планы.
Индексы и план выполнения
Индекс — отдельная структура, которая позволяет не просматривать всю таблицу для каждого поиска. Например, для частого поиска заказов пользователя пригодится:
CREATE INDEX orders_user_created_idx
ON orders (user_id, created_at DESC);
Порядок столбцов важен: составной индекс хорошо работает для фильтра по user_id и затем по created_at, но не обязательно эффективен для поиска только по created_at. Индекс не бесплатен: он увеличивает размер базы и стоимость INSERT, UPDATE и DELETE. EXPLAIN (ANALYZE, BUFFERS) показывает фактический план и помогает сравнить оценку планировщика с реальным числом строк.
Транзакции и изоляция
Транзакция группирует несколько изменений:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
Если проверка или обновление не удалось, выполняется ROLLBACK. PostgreSQL использует MVCC: читатели обычно не блокируют писателей, а каждая транзакция видит согласованный снимок данных. Уровни изоляции позволяют выбрать баланс между параллелизмом и защитой от аномалий; SERIALIZABLE даёт наиболее строгую модель, но может потребовать повторить транзакцию после ошибки сериализации.
Нормализация и денормализация
Нормализация раскладывает данные по связанным таблицам и уменьшает дублирование. Это снижает риск противоречий: адрес пользователя не приходится исправлять в каждой строке заказа. Цена — дополнительные JOIN и более сложные запросы.
Денормализация может быть оправдана для read-heavy сценариев, отчётов или заранее рассчитанных представлений. Но дублированное поле становится производным: нужно определить владельца значения, способ обновления и допустимую задержку согласования.
Ключевые тезисы для интервью
- Реляционная схема описывает сущности таблицами, а связи — внешними ключами; ограничения должны защищать инварианты на уровне базы.
WHEREфильтрует строки до группировки,HAVING— группы послеGROUP BY; физический порядок выполнения выбирает оптимизатор.PRIMARY KEY,UNIQUE,NOT NULLиCHECKпредотвращают разные классы некорректных данных, аFOREIGN KEYзащищает ссылочную целостность.- Индекс ускоряет подходящие чтения и сортировки, но увеличивает размер базы и стоимость каждой записи;
EXPLAIN ANALYZEнужен для проверки фактического плана. - Транзакции обеспечивают атомарность изменений, а MVCC и уровень изоляции определяют, как параллельные операции видят данные.
- Нормализация уменьшает аномалии обновления, а денормализация — осознанный компромисс ради скорости чтения и должна иметь стратегию поддержания согласованности.
Заключение
PostgreSQL хранит не просто строки, а согласованную модель предметной области. Ключи и ограничения защищают данные, SQL выражает выборки, индексы помогают им выполняться быстро, а транзакции сохраняют корректность при сбоях и конкуренции. Эти основы позволяют осознанно выбирать ORM, проектировать API и диагностировать проблемы производительности.