PostgreSQL 9.5 beta: UPSERT закрывает боль ETL, Row Level Security идёт в мультитенант

PostgreSQL 9.5 вышел в бета на прошлой неделе, и в нём наконец появилось то, что в сообществе ждали давно и с нетерпением: нормальный UPSERT. Плюс Row Level Security и BRIN-индексы - оба тоже полезные, но про них чуть позже.

UPSERT: INSERT ON CONFLICT

До 9.5 классический upsert-паттерн в PostgreSQL выглядел примерно так - CTE с попыткой UPDATE, и если затронуто 0 строк, следом INSERT. На практике это несколько десятков строк SQL, которые нужно аккуратно написать, и которые при высокой конкурентности всё равно дают race condition. Были варианты с advisory lock, были варианты с INSERT ... WHERE NOT EXISTS в подзапросе - ни один не был элегантным.

Теперь вместо всего этого:

INSERT INTO dimension_products (product_id, name, category, updated_at)
VALUES (42, 'Виджет Pro', 'Electronics', now())
ON CONFLICT (product_id) DO UPDATE
SET
    name       = EXCLUDED.name,
    category   = EXCLUDED.category,
    updated_at = EXCLUDED.updated_at;

EXCLUDED - псевдотаблица со значениями, которые пытались вставить. Конфликт определяется по индексу или по ON CONFLICT ON CONSTRAINT. Можно сказать DO NOTHING - тогда при конфликте строка просто пропускается без ошибки.

Для ETL это принципиальное изменение. Типовая загрузка в dimension-таблицу: из источника приходит пачка строк, часть из них уже есть в DWH, часть новые. Раньше нужно было либо стейджинг-таблица плюс MERGE-логика на уровне приложения, либо громоздкая CTE-конструкция. Теперь - один INSERT ... ON CONFLICT DO UPDATE на весь батч, атомарно, без race conditions, без лишних roundtrip к базе.

Мы проверили на тестовом стенде с таблицей dimension-продуктов: загрузка батча в несколько тысяч строк с ON CONFLICT DO UPDATE работает предсказуемо. Строки, которых не было - вставляются, строки с совпадающим ключом - обновляются. Один запрос, одна транзакция.

Одна оговорка: это beta. До GA выпускать в production не стоит, но для тестирования и оценки уже вполне.

Row Level Security: мультитенант в одной базе

Второй момент, который нас зацепил - Row Level Security. Это механизм политик на уровне строк: можно написать предикат, который база будет автоматически применять к каждому SELECT/INSERT/UPDATE/DELETE в таблице, в зависимости от текущего пользователя или любого другого контекста сессии.

У нас есть проект - SaaS-платформа клиента с несколькими десятками арендаторов в одной базе данных. До 9.5 изоляция данных между арендаторами держалась исключительно на уровне приложения: каждый запрос фильтровал по tenant_id, и это нужно было не забыть в каждом запросе, в каждом джойне, в каждой процедуре. Забыл WHERE tenant_id = ? где-нибудь в хендлере - и один арендатор теоретически видит данные другого. Кейс неприятный, и с ростом кодовой базы риск человеческой ошибки растёт.

С Row Level Security это можно перенести в базу:

-- Включаем RLS для таблицы
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Политика: каждый пользователь видит только свои заказы
CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant_id')::int);

Приложение перед началом работы устанавливает SET LOCAL app.current_tenant_id = 123 в рамках транзакции, и дальше любой SELECT из orders автоматически получает фильтр по tenant_id. Без единой строки WHERE tenant_id = ? в SQL-запросах приложения.

Для superuser-а политики по умолчанию не применяются - надо явно указывать FORCE ROW LEVEL SECURITY если нужно. Это логично: администраторская роль для обслуживания базы должна видеть всё.

Протестировали на копии базы клиента: политики работают, фильтрация прозрачная. Есть нюансы с производительностью - предикат из политики добавляется к каждому запросу, и если индекс по tenant_id не покрывает нужные запросы, план может деградировать. Нужно профилировать под реальную нагрузку, а не просто включить и забыть.

BRIN-индексы

Третье из заметного - BRIN (Block Range INdex). Идея: для данных с естественной корреляцией между физическим расположением на диске и значением столбца (классика - timestamptz у таблицы событий, которая всегда пишется в конец) хранить не полное дерево значений, а мин/макс по каждому диапазону блоков. Размер индекса на несколько порядков меньше обычного B-Tree, построение быстрее.

CREATE INDEX idx_events_created_at_brin ON events
    USING BRIN (created_at);

Для таблицы фактов с temporally-упорядоченными данными и аналитическими запросами с фильтром по диапазону дат - выглядит интересно. Но это нужно проверять под конкретную нагрузку: для точечных lookups BRIN не поможет, он про range scan по коррелированным данным.

Что дальше

9.5 пока beta, GA ожидается в начале следующего года. На production-базах клиентов ничего не трогаем, но в тестовой среде уже гоняем ETL-сценарии с INSERT ON CONFLICT, и поведение нравится. RLS для мультитенантного проекта обсуждаем с командой разработки клиента - там нужно сначала разобраться с производительностью под нагрузкой.

Если работаете с PostgreSQL как DWH-базой или в мультитенантных сценариях - посмотрите beta notes, там есть что оценить.

Задачи по хранилищам данных и аналитике ведём в рамках DWH и бизнес-аналитики.