PostgreSQL

01Почему Postgres: возможности, которые важны

PostgreSQL близко следует стандарту SQL и добавляет к нему очень многое. Ограничения строгие: CHECK, внешние ключи, проверку которых можно отложить до коммита, и ограничения EXCLUDE, которые могут запретить пересекающиеся временные диапазоны, например двойные бронирования. DDL транзакционный, так что неудачная миграция полностью откатывается. Общие табличные выражения (запросы WITH), включая рекурсивные для деревьев, сохраняют сложные запросы читаемыми. Оконные функции, upsert (INSERT ... ON CONFLICT), RETURNING, массивы, диапазоны, enum и генерируемые столбцы — всё встроено. Расширения добавляют целые возможности: pg_trgm для нечёткого поиска, PostGIS для карт, pgvector для эмбеддингов, pg_stat_statements для статистики запросов. PostgreSQL 18 (сентябрь 2025) добавил асинхронный I/O и нативную функцию uuidv7().

1-- recursive CTE: all subcategories of category 1
2WITH RECURSIVE tree AS (
3  SELECT id, parent_id, name FROM categories WHERE id = 1
4  UNION ALL
5  SELECT c.id, c.parent_id, c.name
6  FROM categories c JOIN tree t ON c.parent_id = t.id
7)
8SELECT * FROM tree;
9 
10-- forbid overlapping bookings for the same room
11CREATE EXTENSION IF NOT EXISTS btree_gist;
12ALTER TABLE bookings ADD CONSTRAINT no_overlap
13  EXCLUDE USING gist (room_id WITH =, period WITH &&);

02JSONB правильно

JSONB хранит JSON в разобранном бинарном формате, который можно индексировать и запрашивать. Операторы вроде -> и ->> читают поля, @> проверяет вхождение, а jsonb_path_query поддерживает JSONPath. Это идеально для действительно переменных данных: атрибутов товаров, которые отличаются по категориям, payload'ов интеграций, настроек или блоков CMS. И плохо подходит для данных, по которым вы часто фильтруете, делаете join или накладываете ограничения; им место в настоящих столбцах с типами и внешними ключами. Практичный паттерн — держать основные поля реляционными, а длинный хвост класть в столбец JSONB с индексом GIN. Если какое-то поле JSON становится важным, превратите его в генерируемый или настоящий столбец. Помните, что обновление любой части значения JSONB перезаписывает всё значение целиком.

1ALTER TABLE products ADD COLUMN attrs jsonb NOT NULL DEFAULT '{}';
2CREATE INDEX products_attrs_gin ON products USING gin (attrs jsonb_path_ops);
3 
4-- all products with colour "black" and size "M"
5SELECT id, name
6FROM products
7WHERE attrs @> '{"colour": "black", "size": "M"}';

03Индексы: B-tree, GIN и другие

B-tree — индекс по умолчанию, он работает с равенством, диапазонами и сортировкой; используйте его для id, внешних ключей, дат и большинства столбцов. GIN (generalised inverted index) индексирует элементы внутри значения, поэтому подходит для проверки вхождения в JSONB, массивов и полнотекстового поиска; он больше и медленнее обновляется, чем B-tree. GiST поддерживает диапазоны, геометрию и exclusion constraints. BRIN крошечный и работает для огромных, естественно упорядоченных таблиц вроде логов. Ещё в Postgres есть частичные индексы (только строки, подходящие под условие WHERE, например активные), индексы по выражениям (по lower(email)) и столбцы INCLUDE для покрывающих индексов. На живых таблицах стройте индексы через CREATE INDEX CONCURRENTLY, а планы проверяйте через EXPLAIN (ANALYZE, BUFFERS).

• B-tree: равенство, диапазоны, ORDER BY.
• GIN: JSONB, массивы, полнотекстовый и триграммный поиск.
• GiST: диапазоны, геометрия, exclusion constraints.
• Частичные индексы и индексы по выражениям: меньше и точнее.

04MVCC, VACUUM и соединения

PostgreSQL реализует MVCC (многоверсионное управление конкурентным доступом), никогда не обновляя строки на месте. UPDATE записывает новую версию строки и помечает старую как мёртвую; каждая транзакция видит версии, которые были видимы в момент создания её снимка. Читатели никогда не блокируют писателей. Цена — уборка: мёртвые строки остаются, пока VACUUM их не удалит, и autovacuum делает это в фоне. Если autovacuum отстаёт, таблицы раздуваются, а запросы замедляются; долгие транзакции ухудшают ситуацию, потому что мешают уборке. VACUUM также предотвращает wraparound идентификаторов транзакций, поэтому никогда его не отключайте. Уровень изоляции по умолчанию — READ COMMITTED. Каждое соединение — отдельный серверный процесс, поэтому используйте пулер вроде PgBouncer в режиме transaction, когда подключается много экземпляров приложения или serverless-функций.

• Следите за мёртвыми кортежами и autovacuum в pg_stat_user_tables.
• Избегайте долгих сессий в состоянии idle-in-transaction; задайте idle_in_transaction_session_timeout.
• Используйте пул соединений; сотни прямых соединений впустую тратят память.

05Row-level security для мультитенантности

Row-level security (RLS) позволяет базе фильтровать строки для каждого запроса. Вы включаете её на таблице и пишете политику, например «строка видна, только если её company_id равен текущей настройке тенанта». Приложение выставляет эту настройку в начале каждой транзакции, и тогда даже запрос, в котором забыли WHERE, не увидит данные другого тенанта. Важные детали: владельцы таблиц и суперпользователи обходят RLS, если не использовать FORCE ROW LEVEL SECURITY и не подключаться под отдельной ролью, которая не является владельцем; при пуле соединений выставляйте тенанта через set_config(..., true), чтобы настройка была локальной для транзакции; и проиндексируйте столбец тенанта, потому что теперь каждый запрос по нему фильтруется. RLS лучше всего работает как второй слой поверх явных фильтров по тенанту в приложении.

1ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
2ALTER TABLE orders FORCE ROW LEVEL SECURITY;
3 
4CREATE POLICY tenant_isolation ON orders
5  USING (company_id = current_setting('app.company_id')::uuid)
6  WITH CHECK (company_id = current_setting('app.company_id')::uuid);
7 
8-- per request, inside a transaction:
9BEGIN;
10SELECT set_config('app.company_id', '0193a1b2-0000-7000-8000-000000000001', true);
11SELECT id, status FROM orders;   -- only this tenant's rows
12COMMIT;

06Prisma с PostgreSQL

Prisma — ORM для TypeScript: вы описываете модели в schema.prisma, а она генерирует полностью типизированный клиент и SQL-миграции. В разработке prisma migrate dev сравнивает схему с базой, пишет файл миграции и применяет его. В CI и продакшене запускайте prisma migrate deploy, который применяет только закоммиченные миграции и никогда не сбрасывает данные. Используйте --create-only, чтобы сгенерировать миграцию и отредактировать SQL перед применением, — так добавляют то, что схема Prisma не может полностью выразить, например политики RLS, ограничения CHECK и exclusion или CREATE INDEX CONCURRENTLY. Client extensions могут добавлять сквозные правила, например автоматически ограничивать каждый запрос тенантом. Prisma 7 перешла на клиент без Rust с driver adapters и файлом prisma.config.ts, так что при переходе с версии 6 сверьтесь с руководством по обновлению.

1model Order {
2  id         String   @id @default(uuid(7)) @db.Uuid
3  companyId  String   @db.Uuid
4  status     String
5  totalMinor BigInt
6  createdAt  DateTime @default(now()) @db.Timestamptz
7  items      OrderItem[]
8 
9  @@index([companyId, createdAt(sort: Desc)])
10}
11 
12// npx prisma migrate dev --name add_orders --create-only
13// review/edit SQL, then: npx prisma migrate dev
14// production:            npx prisma migrate deploy

07Бэкапы и Postgres vs MySQL

pg_dump делает логический бэкап одной базы; custom-формат (-Fc) сжат и позволяет pg_restore восстанавливать выборочно и параллельно. Для больших систем и восстановления на момент времени сочетайте физический базовый бэкап (pg_basebackup) с непрерывным архивированием WAL, в идеале через инструмент вроде pgBackRest или WAL-G, который берёт на себя хранение по сроку, сжатие и хранение вне площадки. Регулярно проверяйте восстановление. Выбор между Postgres и MySQL: обе отлично подходят для стандартных веб-приложений. Я склоняюсь к Postgres для сложных доменов, строгой целостности данных, JSONB, продвинутого SQL, мультитенантности с RLS или когда нужны расширения вроде PostGIS или pgvector. Я остаюсь на MySQL или MariaDB, когда хостинг, команда или экосистема фреймворка построены вокруг неё, а нагрузка — простые чтения и записи.

1# logical backup, compressed custom format
2pg_dump -Fc -U backup_user -h localhost -d shop -f shop-$(date +%F).dump
3 
4# restore into a fresh database with 4 parallel jobs
5createdb -U postgres shop_restore_test
6pg_restore -U postgres -d shop_restore_test -j 4 shop-2026-11-05.dump

08Чтение планов: EXPLAIN, статистика и оценки

Планировщик выбирает план по оценкам количества строк, и большинство плохих планов происходит от плохих оценок. EXPLAIN (ANALYZE, BUFFERS) выполняет запрос и показывает для каждого узла оценку строк против фактических строк, время и сколько страниц пришло из shared buffers (hit), а сколько с диска (read); начиная с PostgreSQL 18 BUFFERS включается автоматически вместе с ANALYZE. Когда оценки расходятся в десять раз и больше, планировщик может выбрать nested loop там, где нужен был hash join. Оценки берутся из статистики, которую собирает ANALYZE (его запускает autovacuum), но она предполагает, что колонки независимы. Для коррелированных колонок, например города и почтового индекса или тенанта и статуса, создавайте extended statistics, чтобы планировщик знал о связи. Для колонок с перекосом поднимайте statistics target на этой колонке. Prepared statements добавляют нюанс: после пяти выполнений Postgres может переключиться на generic plan, который игнорирует значения параметров, и это плохо, когда у одного тенанта миллионы строк, а у других десять; plan_cache_mode позволяет это контролировать.

1EXPLAIN (ANALYZE, BUFFERS)
2SELECT * FROM orders WHERE company_id = $1 AND status = 'open';
3 
4-- planner thinks company and status are independent; teach it otherwise
5CREATE STATISTICS st_orders_company_status (dependencies, mcv)
6  ON company_id, status FROM orders;
7ALTER TABLE orders ALTER COLUMN company_id SET STATISTICS 1000;
8ANALYZE orders;
9 
10-- top queries by total time (needs pg_stat_statements)
11SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 1) AS mean_ms, query
12FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

09Блокировки и DDL без простоя

Большинство форм ALTER TABLE берут блокировку ACCESS EXCLUSIVE, которая конфликтует со всем, включая обычные SELECT. Сама блокировка может держаться всего миллисекунды, но настоящая опасность — очередь: если длинная транзакция держит любую блокировку на таблице, ALTER ждёт, а каждый запрос, пришедший после него, встаёт в очередь за ALTER. Приложение замирает, хотя формально ничего не медленное. Решение — задавать в миграциях lock_timeout в несколько секунд и повторять, чтобы заблокированный ALTER сдавался, а не останавливал трафик. Знайте, какие операции дешёвые. Добавление колонки с константным значением по умолчанию начиная с PostgreSQL 11 меняет только метаданные. Добавление foreign key или CHECK-ограничения можно разделить: добавить его с NOT VALID, что быстро, а затем выполнить VALIDATE CONSTRAINT, который сканирует таблицу с более слабой блокировкой, разрешающей чтение и запись. SET NOT NULL может пропустить сканирование таблицы, если уже есть валидированный CHECK (col IS NOT NULL). Смена типа колонки обычно переписывает таблицу, поэтому лучше новая колонка и пакетный backfill.

1SET lock_timeout = '5s';
2 
3-- foreign key without blocking writes for the whole scan
4ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
5  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
6ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_customer;
7 
8-- NOT NULL without a long exclusive scan
9ALTER TABLE orders ADD CONSTRAINT orders_currency_nn CHECK (currency IS NOT NULL) NOT VALID;
10ALTER TABLE orders VALIDATE CONSTRAINT orders_currency_nn;
11ALTER TABLE orders ALTER COLUMN currency SET NOT NULL;   -- uses the check, no scan
12ALTER TABLE orders DROP CONSTRAINT orders_currency_nn;

10Режимы пула соединений и их побочные эффекты

Каждое соединение PostgreSQL — это серверный процесс со своей памятью, а память для сортировок и хэшей (work_mem) выделяется на каждую операцию, поэтому один сложный запрос может использовать её в несколько раз больше. Сотни прямых соединений, таким образом, тратят память и добавляют конкуренцию; обычно оптимальны несколько десятков активных соединений. PgBouncer в режиме transaction позволяет тысячам клиентов делить маленький пул, выдавая серверное соединение только на время транзакции. Это ломает всё, что опирается на состояние сессии между транзакциями: SET без LOCAL, advisory locks уровня сессии, LISTEN и NOTIFY, временные таблицы и, в старых версиях PgBouncer, серверные prepared statements (версия 1.21 добавила поддержку prepared statements на уровне протокола через max_prepared_statements). Именно поэтому настройки тенанта для row-level security должны быть локальными для транзакции. Миграции схемы запускайте через прямое соединение, а не через transaction pooler, потому что инструменты миграций, включая Prisma Migrate, опираются на advisory locks уровня сессии. Serverless-платформы делают пул обязательным, ведь каждый экземпляр функции открывает свои соединения.

• Размер пула выбирайте по ядрам и скорости хранилища, а не по числу экземпляров приложения.
• С transaction pooler используйте внутри транзакций set_config(name, value, true) или SET LOCAL.
• Держите отдельную строку прямого подключения для миграций и обслуживания.

11Репликация, failover и мажорные обновления

Streaming replication передаёт журнал упреждающей записи (WAL) на standby-серверы, которые его проигрывают, и даёт побайтно идентичную копию для чтений и failover. С synchronous_standby_names и synchronous_commit можно требовать, чтобы standby подтверждал каждый коммит: задержка в обмен на нулевую потерю данных при failover. Сам Postgres не выбирает новый primary; проверки здоровья, повышение и fencing берут на себя инструменты вроде Patroni или managed-сервис. Логическая репликация передаёт изменения строк по таблицам, а не байты WAL, поэтому работает между мажорными версиями и может копировать выбранные таблицы — это стандартный путь для обновлений и миграций почти без простоя; она не реплицирует DDL и значения sequence, так что их вы обрабатываете сами. Replication slots заставляют primary хранить WAL, пока потребитель его не получит; брошенный slot заполняет диск, поэтому задайте max_slot_wal_keep_size и настройте алерт на неактивные slots. Для обновления на месте pg_upgrade --link занимает минуты даже для больших баз, а начиная с PostgreSQL 18 сохраняет статистику планировщика, так что производительность нормальная сразу после обновления.

1-- is any slot holding back WAL?
2SELECT slot_name, slot_type, active,
3       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
4FROM pg_replication_slots;
5 
6-- replication lag per standby (run on the primary)
7SELECT application_name, state, replay_lag FROM pg_stat_replication;