PostgreSQL: таблицы, запросы, транзакции и JSONB

Оглавление · Модели хранения · Идемпотентность · Жизненный цикл БД

Задача: связать контракт публикации с ограничениями и запросами PostgreSQL 17. Нужны ключи, связи и предикаты. SQL ниже — учебная таблица, не схема Strapi.

Колонки и документ могут жить вместе

Slug, заголовок, статус публикации и версия имеют устойчивую роль в запросах. Гибкие сведения можно выделить в JSONB. Это выбор границ, а не требование положить всю запись в один JSON.

PostgreSQL json сохраняет исходный текст; jsonb хранит разобранное представление, не сохраняет пробелы, порядок ключей объекта и повторяющиеся ключи. JSONB поддерживает специальные операторы и индексы. «Валидный JSON» не означает соответствие бизнес-схеме. JSON-типы PostgreSQL 17.

Цельный учебный сценарий

Запускайте блок только в отдельном учебном подключении к PostgreSQL 17. Временная таблица исчезает при commit; пример не подключается к таблицам CMS. Собственный сервер БД для этого этапа не добавляется.

BEGIN;
CREATE TEMP TABLE lesson_article (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug text NOT NULL UNIQUE CHECK (char_length(slug) BETWEEN 1 AND 120),
  title text NOT NULL CHECK (char_length(btrim(title)) > 0),
  published boolean NOT NULL DEFAULT false,
  revision integer NOT NULL DEFAULT 1 CHECK (revision > 0),
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb
    CHECK (jsonb_typeof(metadata) = 'object')
) ON COMMIT DROP;

INSERT INTO lesson_article (slug, title, published, metadata) VALUES
  ('first', 'Первая статья', true, '{"topic":"react","tags":["ui"]}'),
  ('draft', 'Черновик', false, '{"topic":"react"}'),
  ('second', 'Вторая статья', true, '{"topic":"data"}');

SELECT slug, title
FROM lesson_article
WHERE published AND metadata @> '{"topic":"react"}'::jsonb
ORDER BY slug;

CREATE INDEX ON lesson_article USING gin (metadata);
EXPLAIN SELECT slug FROM lesson_article
WHERE metadata @> '{"topic":"react"}'::jsonb;

UPDATE lesson_article
SET title = 'Исправленная статья', revision = revision + 1
WHERE slug = 'first' AND revision = 1
RETURNING slug, revision;

UPDATE lesson_article
SET title = 'Старая правка', revision = revision + 1
WHERE slug = 'first' AND revision = 1
RETURNING slug, revision;
COMMIT;

Первый SELECT ожидает только first. Первый UPDATE возвращает revision=2, второй — ноль строк: исходная версия уже не подходит. Контракт приложения должен отличить конфликт от отсутствия/запрета доступа; ноль строк сам по себе не объясняет причину. Исполнение этого SQL в текущем этапе не проверялось.

CHECK гарантирует объект metadata, но не строковый topic или структуру tags. JSON null внутри документа отличается от SQL NULL всей колонки. Извлечение несуществующего поля может вернуть SQL NULL; учитывайте это в предикатах. Полная runtime-схема и ограничения хранения решают разные части задачи.

Параметры вместо склейки SQL

Значение slug передают через параметры драйвера. Для учебного SQL-соединения та же идея выглядит так:

PREPARE lesson_read(text) AS
  SELECT slug, title FROM lesson_article WHERE slug = $1 AND published;
EXECUTE lesson_read('first');
DEALLOCATE lesson_read;

Этот блок выполняется до COMMIT первого сценария, пока таблица существует. PREPARE отделяет значения от текста запроса. Имена колонок и направление сортировки не становятся безопасными от подстановки в $1: для них задают allowlist. Параметризация не проверяет полномочия.

Индекс выбирают под запрос

Индекс ускоряет часть чтений ценой хранения и записи. GIN по metadata подходит некоторым JSONB-предикатам; это не ускоритель любого выражения над документом. Индекс по выражению metadata ->> 'topic' имеет другую область применения. Индексы JSONB.

EXPLAIN показывает выбранный план и оценки. На трёх строках sequential scan может быть разумнее индекса; наличие индекса не обязывает планировщик его выбрать. EXPLAIN ANALYZE действительно исполняет запрос: для записи это особенно важно. Планы запросов.

Для JOIN сначала задайте ключи и кардинальность. «Одна статья → много меток» может размножить строки результата; COUNT(*) после такого JOIN уже не обязательно считает статьи. Разберите это на маленьких данных до оптимизации.

Транзакция и конкурентность

Транзакция объединяет изменения в выбранную границу commit/rollback; она не включает автоматически HTTP-вызов или письмо. В PostgreSQL Read Committed — уровень по умолчанию: разные команды могут видеть разные снимки. Serializable может потребовать повторить всю транзакцию после ошибки сериализации. Изоляция.

В UPDATE выше проверка revision и изменение находятся в одной команде. Это лучше, чем отдельное чтение версии и поздняя безусловная запись. Но пример не показывает два реальных параллельных соединения и не доказывает согласованность всего редакторского процесса. Увеличение revision требует согласованного правила для всех писателей.

Что относится к Atmanki

Compose выбирает postgres:17-alpine и отдельный том; конфигурация Strapi задаёт подключение по DATABASE_* и пул. В Compose явно указан DATABASE_CLIENT=postgres; значение по умолчанию в самостоятельной конфигурации CMS другое.

Сайт не выполняет этот SQL: он читает CMS repository. Не делайте вывод о физическом хранении Strapi Blocks из нашего примера metadata. Внутренняя схема CMS принадлежит Strapi; изменение через её API описано в главе о контенте.

Практика

В отдельной учебной БД проверьте ожидаемые строки, конфликт revision и отказ UNIQUE/NOT NULL/CHECK. Неверные INSERT выполняйте отдельными транзакциями: ошибка прерывает текущую транзакцию до rollback. Сравните JSON null, отсутствующее поле и SQL NULL. Попробуйте два соединения для конкурирующего UPDATE.

После корректности измерьте план на достаточном наборе строк. Отдельно запишите, что проверено: текст SQL, реальное исполнение, конкурентность или производительность. Это разные свидетельства.