Базы данных: Часть 3 — PostgreSQL

PostgreSQL — свободная объектно-реляционная СУБД, которую развивает независимое международное сообщество. Проект начался в 1986 году в Калифорнийском университете в Беркли под названием POSTGRES, в 1996 году получил нынешнее имя и поддержку языка SQL. PostgreSQL известен строгим следованием стандарту SQL, надёжностью и расширяемостью: к нему подключаются модули для геоданных, временных рядов, полнотекстового и векторного поиска.

Лицензия и версии

PostgreSQL распространяется под собственной PostgreSQL License — короткой разрешительной лицензией, близкой к BSD и MIT. Её можно использовать, изменять и встраивать в коммерческие продукты без отчислений и без обязанности открывать свой код. Проект не принадлежит одной компании, поэтому риск внезапной смены условий, как у некоторых других СУБД, здесь минимален.

Новая основная версия выходит раз в год, обычно осенью, и поддерживается пять лет. Актуальная версия — PostgreSQL 18 (вышла в сентябре 2025 года). Выход PostgreSQL 19 в 2026 году задерживается: команда выпуска планирует его к концу октября. Поддержка PostgreSQL 14 заканчивается 12 ноября 2026 года — серверы на этой версии пора обновлять.

Внутри основной версии выходят корректирующие выпуски (18.1, 18.2 и так далее) не реже раза в квартал. Их нужно устанавливать всегда: они содержат только исправления ошибок и уязвимостей и не меняют формат данных.

Где PostgreSQL уместен

• Учётные и финансовые системы, ERP, CRM — где важна строгая целостность данных и сложные запросы.
• «1С:Предприятие». Платформа 1С поддерживает PostgreSQL; для неё выпускаются сборки с доработками под 1С, в том числе российская СУБД Postgres Pro компании Postgres Professional. Postgres Pro включена в реестр российского ПО и выпускается в редакциях Standard, Enterprise и Certified (с сертификатом ФСТЭК) — это важно для организаций, которым нужно импортозамещение.
• Веб-приложения на Django, Ruby on Rails, Laravel, Node.js, где пригодятся массивы, JSONB, полнотекстовый поиск.
• Геоинформационные системы — с расширением PostGIS.
• Аналитика среднего объёма — оконные функции, материализованные представления и параллельное выполнение запросов позволяют строить отчёты без отдельного аналитического хранилища.

Как устроен PostgreSQL

MVCC: одновременная работа без взаимных помех

PostgreSQL использует многоверсионное управление параллельным доступом (MVCC). При изменении строки создаётся её новая версия, а старая остаётся доступной для транзакций, которые начались раньше. Благодаря этому чтение не блокирует запись, а запись не блокирует чтение. Блокировки при этом есть: две транзакции, изменяющие одну и ту же строку, выполняются по очереди.

Обратная сторона MVCC — устаревшие версии строк, которые нужно убирать. Этим занимается процесс autovacuum. Отключать его нельзя, а долгие незавершённые транзакции (состояние idle in transaction) мешают ему работать, и таблицы разрастаются.

JSONB

Тип JSONB хранит JSON в разобранном двоичном виде и позволяет строить по нему индексы (GIN). В одной таблице можно держать строгие столбцы и гибкое поле с произвольными атрибутами — например, характеристики товаров разных категорий.

Расширения

Расширения добавляют новые типы данных, функции и индексы без изменения ядра:

• PostGIS — геометрия и география: расстояния, пересечения, поиск объектов в радиусе;

• pg_stat_statements — статистика по всем выполняемым запросам: какие из них самые частые и какие занимают больше всего времени. Первое, что стоит включить на рабочем сервере. Модуль нужно загрузить при старте сервера:

  Copy

  # postgresql.conf
  shared_preload_libraries = 'pg_stat_statements'

  затем в базе выполнить CREATE EXTENSION pg_stat_statements;;

• TimescaleDB — хранение временных рядов. Базовые функции распространяются под Apache 2.0, а расширенные (непрерывные агрегаты, сжатие, политики хранения) — под собственной лицензией разработчика: использовать их на своих серверах бесплатно можно, продавать как облачный сервис — нельзя;

• pgvector — хранение векторов и поиск похожих, используется в приложениях с языковыми моделями.

Базовая настройка

Пользователь и база для приложения

CREATE ROLE shop LOGIN PASSWORD 'надёжный_пароль';
CREATE DATABASE shop OWNER shop;

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

Кто и откуда может подключаться, задаёт файл pg_hba.conf. По умолчанию сервер слушает только localhost; если приложение на другой машине, в postgresql.conf указывают конкретный адрес в listen_addresses, а в pg_hba.conf — разрешённую подсеть и метод scram-sha-256.

Пул соединений

Каждое подключение к PostgreSQL — отдельный процесс операционной системы. Несколько сотен одновременных подключений расходуют заметный объём памяти и замедляют сервер. Поэтому перед PostgreSQL ставят пул соединений — чаще всего PgBouncer. Он держит небольшое число реальных подключений к базе и раздаёт их клиентам. Режимы работы:

• session — подключение закрепляется за клиентом на весь сеанс; безопасно для любых приложений, но экономия невелика;
• transaction — подключение выдаётся только на время транзакции; даёт основной выигрыш, но приложение не должно полагаться на состояние сеанса (SET, временные таблицы, рекомендательные блокировки между транзакциями).

Резервное копирование

Логическая копия: pg_dump

pg_dump -Fc -f shop.dump shop
pg_restore -d shop_restored shop.dump

Формат -Fc сжат и позволяет восстанавливать отдельные таблицы. pg_dump копирует одну базу; роли и права на уровне сервера сохраняются отдельно командой pg_dumpall --globals-only. Логические копии удобны для небольших баз и переноса между основными версиями.

Физическая копия и восстановление на момент времени (PITR)

Для больших баз и возможности откатиться на конкретную минуту используют физическое копирование:

1. Включают архивирование журнала предзаписи (WAL) в postgresql.conf:

   Copy

   wal_level = replica
   archive_mode = on
   archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

2. Периодически снимают базовую копию: pg_basebackup -D /backup/base -Ft -z -P.

3. При восстановлении разворачивают базовую копию, задают restore_command и при необходимости recovery_target_time, создают в каталоге данных файл recovery.signal и запускают сервер. PostgreSQL применит журналы до указанного момента.

На практике эту схему автоматизируют готовые инструменты — pgBackRest, Barman, WAL-G: они управляют расписанием, хранением, проверкой копий и отправкой их в S3-совместимое хранилище.

Типичные ошибки

• Нет пула соединений — приложение открывает сотни подключений, сервер упирается в память.
• Настройки по умолчанию. Они рассчитаны на минимальное железо. Как минимум нужно настроить shared_buffers (обычно около 25 % памяти), effective_cache_size и work_mem.
• Висящие транзакции. Сеансы idle in transaction мешают autovacuum; параметр idle_in_transaction_session_timeout завершает их автоматически.
• Обновление основной версии «на месте». Файлы данных разных основных версий несовместимы. Переход выполняют через pg_upgrade, логическую копию или логическую репликацию — и сначала проверяют на копии.
• Копии без проверки восстановления. Резервную копию нужно регулярно разворачивать на тестовом сервере.

Итог

PostgreSQL — сильный выбор по умолчанию для нового проекта: свободная лицензия без риска смены условий, строгие транзакции, богатые типы данных и расширения. Порог входа у него выше, чем у MySQL, — придётся разобраться с настройкой памяти, пулом соединений и резервным копированием, — но эти усилия окупаются надёжностью и возможностями, которые не придётся искать в других СУБД.