Привет, Хабр!

В журнале проводок 20 000 счетов и 4 миллиона строк, и каждое списание погашено возвратом той же суммы. Запрос сверки с HAVING sum(amount) <> 0 должен вернуть пустой список. Он находит 17 808 несведённых счетов. Через минуту тот же запрос находит 17 785, ещё через минуту — 17 840. С выключенными параллельными воркерами получается 19 596, и это число от запуска к запуску уже не меняется.

Суммы в журнале лежат в double precision, потому что таблицу загрузили из CSV через pandas, а там дробные числа по умолчанию float64. Результат сложения с плавающей точкой зависит от порядка слагаемых, и при параллельном плане порядок каждый раз свой. SQL не обещает, в каком порядке агрегат увидит строки, и Postgres этим пользуется, когда так быстрее.

Ещё два таких же незафиксированных порядка аналитик встречает каждый день. Атрибуция по первому касанию опирается на порядок строк с одинаковым временем, и его выбирает план. Дашборд из нескольких плиток опирается на момент, с которого каждый запрос видит данные, и во время загрузки у соседних плиток этот момент разный.

Сумма, которая зависит от числа воркеров

Журнал для проверки я сгенерировал на PostgreSQL 16. В нём 2 миллиона случайных сумм от 100 до 10 000 рублей с копейками, у каждой есть пара с минусом, все 4 миллиона строк перемешаны, и сумма по любому счёту должна быть равна нулю.

SELECT count(*)
FROM (SELECT acc FROM ledger GROUP BY acc HAVING sum(amount) <> 0) t;

max_parallel_workers_per_gather = 0    19596   19596
max_parallel_workers_per_gather = 2    17808   17785   17840   17833

Однопоточный план ошибается стабильно. У 19 596 счетов из 20 000 вместо нуля остаётся хвост от 1,1·10⁻¹³ до 1,3·10⁻¹⁰, потому что большинство сумм с копейками в двоичной дроби точно не записать, и каждое сложение что‑то теряет. При двух воркерах хвостов меньше, их число скачет, и от запуска к запуску они появляются у разных счетов.

В плане запроса сумма считается в два этапа:

Finalize HashAggregate
  Group Key: acc
  Filter: (sum(amount) <> '0'::double precision)
  ->  Gather
        Workers Planned: 2
        ->  Partial HashAggregate
              Group Key: acc
              ->  Parallel Seq Scan on ledger

Воркеры и ведущий процесс разбирают блоки таблицы по мере того, как освобождаются, и считают частичные суммы по счетам. Потом Finalize складывает частичные суммы между собой. Распределение блоков от запуска к запуску разное, поэтому одни и те же слагаемые каждый раз складываются в другом порядке.

Сумма по всему журналу ведёт себя так же. Без воркеров sum(amount) даёт 1,0·10⁻⁷. С двумя воркерами три запуска подряд дали −9,8·10⁻⁷, 1,3·10⁻⁶ и 9,3·10⁻⁸. Знак итога сменился дважды.

Если суммы округлять до копеек, разница пропадает. На таблице платежей на 5 миллионов строк с 50 000 пользователей я снял подряд две выгрузки сумм по пользователям с параллельным планом. Сырые суммы разошлись у 26 773 пользователей. После round(..., 2) не разошлась ни одна.

Вылезает это там, где сравниваются неокруглённые значения. Это:

• = 0 и <> 0;

• соединение двух витрин по сумме;

• EXCEPT между вчерашней и сегодняшней выгрузкой;

• хеш строки, которым проверяют, что отчёт не поменялся.

Можно выключить параллельность через SET max_parallel_workers_per_gather = 0. Результат станет повторяемым и остановится на 19 596 несведённых счетах из 20 000.

Деньги во float попадают в хранилище обычно через pandas. pandas.read_csv читает дробные числа как float64, а DataFrame.to_sql создаёт для такой колонки double precision, если тип не задан явно. Бывает, что таблицу создавали руками и выбрали real ради экономии места. У real около шести значащих цифр, и sum(real) в Postgres возвращает тоже real:

SELECT pg_typeof(sum(amount::real)), sum(amount::real), sum(amount_numeric)
FROM payments;

real | 2.523995e+10 | 25250529639.42

На 5 миллионах платежей итог в real разошёлся с точным на 10,6 миллиона рублей. На выборке из 50 000 строк расхождение 649 рублей. Такую сумму на сверке заметят, а связать её с типом колонки догадаются не сразу.

Чинится это типом колонки, а если её пока поменять нельзя, достаточно привести к numeric каждое значение до суммирования:

SELECT acc FROM ledger GROUP BY acc HAVING sum(amount::numeric) <> 0;

Этот запрос возвращает пустой список при любом числе воркеров.

Приведение double precision к numeric округляет значение до 15 значащих цифр, и копейки, которые когда‑то превратились в двоичную дробь, возвращаются на место. Для сумм до триллиона рублей этого хватает.

Число 12 345 678 901 234,56 после такого приведения превращается в 12 345 678 901 234,6, разряд копеек пропадает.

На 4 миллионах строк сумма через numeric считалась 1,1 секунды, у double precision — 128 миллисекунд. Для ночной сверки лишняя секунда роли не играет, а в витрину деньги лучше сразу грузить в numeric(12,2) с явным типом колонки.

Первое касание, которое выбирает сортировка

Атрибуцию по первому касанию чаще всего пишут через row_number(). События пользователя нумеруют по времени и оставляют первое:

SELECT source, count(*)
FROM (
  SELECT user_id, source,
         row_number() OVER (PARTITION BY user_id ORDER BY ts) AS rn
  FROM hits
) t
WHERE rn = 1
GROUP BY source;

Запрос корректен, пока у пользователя нет двух событий с одинаковым ts. Время в выгрузках трекеров и в логах часто обрезано до секунды. Переход по рекламе и последующий заход с UTM‑меткой из письма или мессенджера легко попадают в одну секунду.

В моей таблице 400 000 пользователей по 5 событий за 20 минут, и время я обрезал до минуты, чтобы совпадений было побольше. У 48 617 пользователей первых событий оказалось два и больше. У 39 402 из них источники разные.

Посчитать таких у себя можно одним запросом:

SELECT count(*)
FROM (
  SELECT user_id, ts, count(DISTINCT source) AS n,
         rank() OVER (PARTITION BY user_id ORDER BY ts) AS r
  FROM hits
  GROUP BY user_id, ts
) t
WHERE r = 1 AND n > 1;

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

В документации к SELECT про такие строки сказано, что строки, равные по всем выражениям ORDER BY, возвращаются «в порядке, зависящем от реализации». На деле порядок зависит от плана. Тот же запрос с разными настройками дал такие итоги по каналам:

                 work_mem = 4MB   work_mem = 256MB   индекс (user_id, ts)
email                    79778              79788                  79844
seo                      80202              80296                  80284
telegram                 79871              79728                  79833
vk_ads                   80133              80200                  80098
yandex_direct            80016              79988                  79941

При 4 МБ сортировка двух миллионов строк не помещается в память и уходит на диск, где Postgres пишет 65 МБ временных файлов и потом сливает отсортированные куски. При 256 МБ всё сортируется в памяти быстрой сортировкой. Строки с одинаковым ключом эти два алгоритма выдают в разном порядке.

По сравнению с вариантом на 4 МБ первый источник сменили 19 837 пользователей. С индексом по (user_id, ts) сортировки в плане нет совсем, строки идут в порядке индекса, и по сравнению с тем же вариантом источник сменился у 19 988 человек. DISTINCT ON (user_id) ... ORDER BY user_id, ts при тех же 4 МБ разошёлся с row_number() у 19 229 пользователей.

Про DISTINCT ON в документации есть предупреждение, что первая строка группы непредсказуема, если ORDER BY её однозначно не задаёт.

Итоги по каналам двигаются на десятки и сотни человек, и в недельном отчёте, где каналы и так гуляют от недели к неделе из‑за бюджетов, сезонности и креативов, такое движение никто не отличит от обычного шума.

Видно его становится при сравнении до и после изменения в базе. DBA поднял work_mem с 4 до 256 МБ, и у телеграма стало на 143 первых касания меньше, хотя отчёт никто не трогал. После VACUUM FULL или pg_repack физический порядок строк тоже другой, и отчёт, скорее всего, сдвинется по той же причине, но этого я уже не проверял.

Порядок делает однозначным второй ключ сортировки. Технически хватит уникального идентификатора события, ORDER BY ts, event_id. Тогда выбор между двумя одновременными касаниями будет зависеть от того, кто раньше получил номер из последовательности, и бизнес‑смысла в этом немного.

Если у маркетинга есть правило, какой канал считать первым при равном времени, его можно записать в самом запросе:

row_number() OVER (
  PARTITION BY user_id
  ORDER BY ts,
           CASE source WHEN 'yandex_direct' THEN 1 WHEN 'vk_ads' THEN 2 ELSE 3 END,
           event_id
)

С топами то же самое. ORDER BY revenue DESC LIMIT 10 при равной выручке у десятого и одиннадцатого клиента возвращает одного из них по выбору плана. С 13-й версии есть FETCH FIRST 10 ROWS WITH TIES. Он вернёт обоих, и строк в топе станет одиннадцать.

Плитки дашборда, которые не складываются

На дашборде выручки обычно есть плитка с итогом и рядом разбивка по каналам. Каждая плитка — отдельный запрос. Пока в таблицу никто не пишет, итог совпадает с суммой разбивки, а во время загрузки они расходятся на одну‑две пачки.

Проверял я это на таблице sales, куда фоновый процесс каждые 10 миллисекунд вставлял по 500 продаж на 1000 рублей. Параллельно другой процесс 20 раз подряд снимал итог и сумму разбивки:

SELECT sum(amount) FROM sales;
SELECT sum(s) FROM (SELECT source, sum(amount) AS s FROM sales GROUP BY source) x;

два запроса в автокоммите                  не сошлось 20 из 20, разница 500 000 – 1 000 000
оба в одной транзакции READ COMMITTED      не сошлось 20 из 20
оба в одной транзакции REPEATABLE READ     не сошлось  0 из 20

Вторая строка меня удивила, я ждал, что общей транзакции хватит. В READ COMMITTED каждый оператор получает свой снимок данных на момент своего начала, и транзакция их не объединяет.

Один снимок на всю транзакцию даёт REPEATABLE READ, и транзакция, которая только читает, ошибок сериализации на этом уровне не получает.

Цена у него другая. Пока транзакция открыта, она держит снимок, VACUUM не может убрать строки, удалённые после её начала, и долгий отчёт в REPEATABLE READ на нагруженной базе раздувает таблицы.

С BI‑системами это работает хуже. Плитки дашборда обычно уходят в базу параллельно, по разным соединениям из пула, и REPEATABLE READ в каждом из них даёт каждому свой снимок. Два соединения в REPEATABLE READ у меня разошлись все 20 раз из 20.

Свести их к одному снимку Postgres умеет с версии 9.2. Одно соединение экспортирует свой снимок, второе к нему подключается:

-- соединение A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT pg_export_snapshot();          -- '00000003-0000001B-1'

-- соединение B
BEGIN ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT '00000003-0000001B-1';

С экспортом снимка расхождений стало 0 из 20, даже когда между запросами я ставил паузу в 50 миллисекунд. На этом механизме работает параллельный pg_dump.

В настройках BI‑систем такого режима я не встречал, поэтому проще собрать итог и разбивку одним запросом:

SELECT GROUPING(source) AS is_total, source, sum(amount)
FROM sales
GROUP BY ROLLUP (source);

Строка с is_total = 1 содержит итог, и он посчитан из того же снимка, что и строки по каналам. GROUPING() нужен, чтобы итог не перепутался с каналом, у которого в source честный NULL. Обе плитки можно кормить из одного такого запроса, если BI умеет фильтровать результат на своей стороне.

Аналитику часто отправляют на реплику, чтобы не нагружать основную базу. Если реплик несколько, плитки одного дашборда попадают на разные. У каждой реплики своё отставание. Снимок на одной из них может оказаться на несколько секунд старше, чем на соседней, а перенести снимок на другой сервер нельзя.

Со стороны загрузки помогает транзакция на пачку. Если ETL вставляет пачку целиком в одной транзакции, половину пачки не увидит ни один отчёт. Плитки тогда расходятся на целое число пачек, и по размеру расхождения сразу понятно, что запросы попали между двумя коммитами загрузки.

Одно и то же число каждый раз

Во всех трёх историях отчёт опирается на то, чего SQL не фиксирует. Порядок слагаемых и порядок строк с равным ключом Postgres выбирает сам, по плану и по тому, какой процесс раньше освободился. Момент снимка задаёт уровень изоляции и время старта оператора.

Проверка «запустил дважды, совпало», которой обычно и ограничивается приёмка нового отчёта, ловит только часть этого, потому что work_mem, индексы и размер таблицы между двумя запусками в одну минуту не меняются.

Насколько хорошо вы знаете внутренние механизмы PostgreSQL и умеете учитывать их при разработке и администрировании? Пройдите тест, чтобы оценить свои знания.

Параллельная агрегация появилась в PostgreSQL 9.6 в 2016 году и была выключена по умолчанию. В 10-й версии max_parallel_workers_per_gather стал равен 2, и с осени 2017 года любой агрегат по большой таблице может уйти в воркеры без всякой просьбы пользователя.

До этого отчёт по double precision возвращал одно и то же неточное число при каждом запуске, и EXCEPT между двумя выгрузками ничего не находил.

А у вас в хранилище деньги лежат в numeric или во float, который когда‑то приехал из pandas?

Если вы работаете с PostgreSQL и отвечаете за сохранность и достоверность данных, важно учитывать не только корректность SQL-запросов, но и механизмы защиты самой базы от незаметного изменения записей.

Разобраться в одном из таких подходов поможет открытый урок:

• 23 ноября в 20:00. «Журнал, который нельзя тихо переписать: hash-chain и дерево Меркла в PostgreSQL». Записаться.

Другие материалы и открытые уроки по теме — в ноябрьском дайджесте OTUS.

Важное напоминание