PostgreSQL: что это, как устроена база данных и когда её выбирать

PostgreSQL — серверная система управления реляционными базами данных. Приложение отправляет ей запросы на языке SQL, а PostgreSQL проверяет правила, находит строки, согласует одновременную работу пользователей и записывает изменения на диск.

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

Разделите PostgreSQL, базу данных и SQL

Три близких термина обозначают разные вещи:

• PostgreSQL — программа-сервер и связанные с ней инструменты;
• база данных — именованное хранилище внутри работающего сервера PostgreSQL;
• SQL — язык, на котором создают структуру и запрашивают данные.

Веб-приложение обычно не читает файлы PostgreSQL напрямую. Оно подключается к серверу по сетевому протоколу, проходит аутентификацию, выбирает базу и выполняет запрос. Консольная программа psql, графический клиент и код приложения — разные клиенты одного сервера.

Это важно для диагностики. Сообщение «не работает PostgreSQL» может означать, что служба не запущена, порт недоступен, имя базы неверно, пользователь не прошёл проверку или сам SQL-запрос содержит ошибку.

Как данные раскладываются по уровням

Один работающий экземпляр PostgreSQL управляет набором баз данных. Внутри конкретной базы находятся схемы, а в схемах — таблицы, представления, функции и другие объекты.

Схема похожа на пространство имён. По умолчанию часто используется public, но приложение может разделить объекты на billing, catalog и другие схемы. Это не отдельные серверы и не отдельные резервные копии.

Таблица состоит из столбцов и строк. Столбец задаёт имя и тип значения, например целое число, текст, дату или логическое значение. Строка хранит один объект предметной области: пользователя, заказ или платёж.

Соберите небольшую связанную модель

Представим сервис задач. Пользователь может иметь много задач, но каждая задача принадлежит существующему пользователю. В уже созданной учебной базе откройте psql под учётной записью с правом создавать таблицы и выполните:

CREATE TABLE app_user (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE
);

CREATE TABLE task (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  owner_id bigint NOT NULL REFERENCES app_user(id),
  title text NOT NULL,
  completed boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE task_event (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  task_id bigint NOT NULL REFERENCES task(id),
  event_type text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

Это изменяющий блок: запускайте его только в отдельной учебной базе, не в production. PRIMARY KEY делает id уникальным идентификатором строки. NOT NULL запрещает отсутствие обязательного значения. UNIQUE не позволяет повторить email. REFERENCES создаёт внешний ключ: owner_id должен указывать на существующую строку app_user.

Успешный psql ответит CREATE TABLE три раза. Таблица task_event хранит журнал действий: каждая её строка ссылается на существующую задачу. Посмотреть структуру можно встроенной командой клиента:

\d app_user
\d task
\d task_event

Если таблица уже существует, PostgreSQL остановит соответствующую команду. Не добавляйте IF NOT EXISTS только для подавления сообщения: сначала выясните, совпадает ли существующая структура с ожидаемой.

После эксперимента удалите объекты в обратном порядке связей:

DROP TABLE task_event;
DROP TABLE task;
DROP TABLE app_user;

DROP TABLE безвозвратно удаляет таблицу в текущей базе. Перед выполнением проверьте подключение командой \conninfo и используйте блок только для созданных учебных объектов.

Почему ограничения важнее проверки в форме

Приложение может проверить, что поле email заполнено, но к базе одновременно обращаются фоновые задачи, администраторские скрипты и несколько экземпляров приложения. Ошибка в одном пути способна записать некорректные данные.

Ограничение базы действует для всех клиентов. Если попытаться создать задачу с несуществующим owner_id, внешний ключ отклонит запрос. Это не замена понятному сообщению в интерфейсе: приложение должно обработать ошибку, но база остаётся последней линией защиты целостности.

Помимо NOT NULL, UNIQUE и внешних ключей существуют CHECK-ограничения. Например, можно запретить отрицательную сумму. Правило должно описывать неизменное свойство данных, а не текущую бизнес-акцию, зависящую от внешнего сервиса.

Объединяйте связанные изменения транзакцией

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

BEGIN;

UPDATE task
SET owner_id = 2
WHERE id = 15;

INSERT INTO task_event (task_id, event_type)
VALUES (15, 'owner_changed');

COMMIT;

Это пример структуры, а не готовый запрос для вашей базы: таблица task_event должна существовать, а идентификаторы нужно получить из приложения. BEGIN открывает транзакцию, COMMIT фиксирует обе операции.

Если второй запрос завершится ошибкой, текущая транзакция перейдёт в состояние ошибки. Вместо COMMIT выполните:

ROLLBACK;

ROLLBACK отменит изменения незавершённой транзакции. Он не возвращает уже подтверждённые прошлые транзакции и не является универсальной кнопкой восстановления.

Как несколько пользователей работают одновременно

PostgreSQL использует MVCC — многоверсионное управление конкурентным доступом. Упрощённо, запрос читает согласованный снимок данных, пока параллельная транзакция создаёт новые версии изменяемых строк. Поэтому обычное чтение и запись во многих случаях не блокируют друг друга напрямую.

MVCC не устраняет все конфликты. Две операции могут попытаться изменить одну строку, транзакции могут ждать блокировки, а при неверном порядке захвата ресурсов возникает deadlock — взаимная блокировка. PostgreSQL обнаруживает deadlock и отменяет одну транзакцию, но приложение должно корректно обработать ошибку и при допустимости повторить операцию целиком.

Долгие незавершённые транзакции удерживают старые версии строк и мешают обслуживанию таблиц. Открывать транзакцию перед запросом к медленному внешнему API — плохая идея: сначала получите внешние данные, затем сделайте короткое согласованное изменение в базе.

Индекс ускоряет поиск не бесплатно

Без подходящего индекса сервер может прочитать таблицу целиком. Индекс создаёт дополнительную структуру, по которой быстрее находятся строки для определённых условий и сортировок.

Для списка незавершённых задач пользователя может пригодиться составной индекс:

CREATE INDEX task_owner_completed_idx
ON task (owner_id, completed);

Команда изменяет схему и занимает место. На большой рабочей таблице обычное создание индекса способно мешать записи; способ и окно изменения выбирают после оценки нагрузки.

Индекс ускоряет не «таблицу вообще», а конкретные планы запросов. Он расходует диск и обновляется при изменениях. Перед добавлением получите реальный медленный запрос, изучите EXPLAIN и статистику, затем измерьте результат. Индекс на каждом столбце увеличит стоимость записи и не гарантирует ускорение сложных запросов.

Удалить этот учебный индекс можно так:

DROP INDEX task_owner_completed_idx;

Убедитесь, что имя относится к созданному вами индексу и что он не используется рабочей нагрузкой.

Что нужно резервировать

Реплика и резервная копия решают разные задачи. Реплика принимает изменения основного сервера и помогает с доступностью или чтением. Ошибочный DELETE обычно также уйдёт на реплику.

Логическая копия через pg_dump сохраняет структуру и данные выбранной базы в переносимом формате. Физическая копия охватывает файлы кластера и используется вместе с совместимыми средствами PostgreSQL. Для восстановления на момент времени применяют базовую физическую копию и архив WAL — журнала изменений.

Выбор зависит от объёма, допустимого времени простоя и требуемой точки восстановления. В любом варианте копия считается пригодной только после пробного восстановления в отдельное окружение. Сохранённый файл, который никто не пытался развернуть, — лишь надежда на резервную копию.

Когда PostgreSQL подходит проекту

PostgreSQL стоит рассматривать, когда:

• данные связаны и требуют внешних ключей и ограничений;
• несколько пользователей одновременно читают и изменяют записи;
• операции нужно объединять в транзакции;
• нужны сложные выборки, агрегации и индексы;
• важны зрелые средства ролей, резервирования и расширения типов данных.

Для небольшого локального приложения без отдельного сервера проще может оказаться SQLite. Для уже работающей команды выбор между PostgreSQL и другой серверной СУБД часто определяют компетенции, совместимость приложения, эксплуатационные инструменты и стоимость миграции, а не список функций на главной странице.

Документная или ключ-значение база полезна для своих моделей доступа, но слово NoSQL не означает «без структуры и правил». Сначала опишите данные, связи, объём, характер запросов, требования к согласованности и восстановлению. После этого сравнивайте конкретные продукты на измеримой нагрузке.

Что проверить перед production

Установка работающего сервера — только начало. До размещения рабочих данных подготовьте:

1. отдельную роль приложения без прав суперпользователя;
2. сетевой доступ только от нужных узлов;
3. TLS там, где соединение проходит через недоверенную сеть;
4. миграции схемы с понятным порядком релиза;
5. лимиты подключений и пул соединений приложения;
6. наблюдение за местом на диске, ошибками, долгими запросами и блокировками;
7. резервные копии с проверенным восстановлением;
8. план обновления поддерживаемой основной версии.

Не открывайте порт базы всему интернету ради удобства подключения. Административный доступ лучше проводить через защищённую сеть или SSH-туннель, оставляя правила firewall и pg_hba.conf минимальными.

Источники

• PostgreSQL: о проекте
• PostgreSQL: вводный учебник
• PostgreSQL: создание таблиц
• PostgreSQL: управление конкурентным доступом
• PostgreSQL: резервное копирование и восстановление