Навигация

Основы PostgreSQL: SQL, таблицы и индексы

Надёжная работа с PostgreSQL начинается не с выбора ORM, а с понимания модели данных и того, как база выполняет запросы. В этой статье разберём таблицы и связи, ограничения, базовый SQL, индексы и транзакции — фундамент для серверных приложений и дальнейшего изучения PostgreSQL.

Содержание

  1. Реляционная модель и схема
  2. Ограничения и связи
  3. Базовый SQL
  4. Индексы и план выполнения
  5. Транзакции и изоляция
  6. Нормализация и денормализация
  7. Ключевые тезисы для интервью
  8. Заключение
  9. Полезные ссылки

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

Таблица описывает сущность: например, 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 и диагностировать проблемы производительности.

Полезные ссылки