PostgreSQL 9.5 в продакшне: переписываем ETL под UPSERT и смотрим на Row-Level Security

PostgreSQL 9.5 стал стабильным. Не бетой, не RC - полноценным релизом, который уже можно ставить в продакшн без экзорцизма. Мы ждали именно этого момента, потому что бета - это бета, а у нас на этой базе живут данные клиентов.

Главное в релизе для нас - два дополнения: INSERT ON CONFLICT (он же UPSERT) и Row-Level Security. О них писали ещё в ноябре, когда вышла бета, теперь пора переходить от наблюдений к делу.

Что было не так с ETL без UPSERT

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

До 9.5 это решалось примерно так:

BEGIN;
DELETE FROM fact_orders
WHERE order_id IN (SELECT order_id FROM staging_orders);
INSERT INTO fact_orders
SELECT * FROM staging_orders;
COMMIT;

Работает. Но у подхода есть неприятные свойства:

• DELETE перед INSERT - это полная замена строк. Если в staging-таблице запись неполная (а такое бывает), мы теряем данные, которые уже были в таблице-приёмнике.
• Локи. DELETE блокирует строки, INSERT пишет новые. При большом объёме это заметно давит на конкурентные читающие запросы.
• Нет идемпотентности из коробки. Если пайплайн упал в середине и откатился - ладно, транзакция спасёт. Но логика "что делать с конфликтом" размазана по нескольким шагам.

Как это выглядит теперь

INSERT INTO fact_orders (order_id, customer_id, amount, updated_at)
SELECT order_id, customer_id, amount, updated_at
FROM staging_orders
ON CONFLICT (order_id) DO UPDATE
SET
  customer_id = EXCLUDED.customer_id,
  amount      = EXCLUDED.amount,
  updated_at  = EXCLUDED.updated_at
WHERE fact_orders.updated_at < EXCLUDED.updated_at;

EXCLUDED - псевдотаблица с теми значениями, которые мы пытались вставить. WHERE в секции DO UPDATE позволяет обновлять запись только если входящее значение новее - это нам и нужно, чтобы не затирать данные устаревшей дельтой.

Для случаев, когда дубль - это просто "уже есть, ничего делать не нужно":

INSERT INTO dim_customers (customer_id, name)
SELECT customer_id, name FROM staging_customers
ON CONFLICT (customer_id) DO NOTHING;

Чисто, читаемо, транзакционно. Логика прямо в запросе, не надо угадывать что делал DELETE за три шага до.

Мы уже переписали два пайплайна. Третий - со сложной составной логикой конфликта - пока разбираем, там чуть нетривиальнее с составным ключом.

Row-Level Security: первый взгляд в сторону мультиаренды

RLS - это политики на уровне строк. Задаёшь условие: какие строки видит какая роль. Без лишних JOIN-ов и без прокси-слоя между приложением и базой.

Базовый пример - изоляция данных по клиенту:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.current_tenant')::int);

Приложение при старте сессии выполняет SET app.current_tenant = 42 - и дальше видит только строки с tenant_id = 42, даже если делает SELECT * FROM orders без всяких WHERE. База фильтрует сама.

Это интересно для нескольких сценариев, которые у нас есть:

• SaaS-проекты с общей схемой. Сейчас изоляция арендаторов реализована через условия в ORM или через отдельные схемы. RLS - потенциально более аккуратный механизм, особенно если нужно давать прямой доступ к базе аналитикам клиента.
• Разграничение доступа в DWH. Не весь отдел должен видеть все данные. Сейчас это view + права. RLS даёт то же самое, но без дублирования таблиц через view.

Пока что это первые прикидки - RLS нужно нормально тестировать под нагрузкой и проверять, как оно ведёт себя с ORM-ами вроде SQLAlchemy. Есть нюансы с суперпользователями: по умолчанию BYPASSRLS активен для суперюзера, и это надо учитывать.

Что ещё в 9.5

Не только UPSERT и RLS - в релизе ещё:

• BRIN-индексы - для больших таблиц с физически упорядоченными данными (логи, временные ряды) они занимают значительно меньше места чем B-tree.
• TABLESAMPLE - выборка случайных строк без ORDER BY random(), которое убивало производительность на больших таблицах.
• jsonb_set() и другие jsonb-функции** - работа с JSONB стала удобнее.

Для нас наиболее важны первые два пункта. BRIN смотрим отдельно - у одного клиента есть таблица событий на несколько сотен гигабайт, индексы там давно стали головной болью.

Переход с 9.4 прошёл без сюрпризов. pg_upgrade отработал штатно, из замеченного: планировщик в 9.5 в паре мест выбрал другой план - проверили, оба раза он оказался лучше старого.

Работа по переводу ETL-пайплайнов на UPSERT продолжается - это небыстро, потому что каждый пайплайн нужно проверять отдельно. Но направление понятное.