Мой PostgreSQL быстрее твоего и почти догнал ClickHouse

Мой знакомый - отличный архитектор, но полнейший сноб. У него в проде четыре разных базы данных. PostgreSQL для OLTP, в ClickHouse у него лежат данные OpenTelemetry и веб аналитики, Elasticsearch для полнотекстового поиска в приложении, а MongoDB - “так исторически сложилось”.

Между всем этим ETL, CDC вместе с дежурствами разработчиков в поисках где именно результаты не сходятся. Для него это все продуманная архитектура, а для меня излишняя сложностью в поддержке и разработке.

Недавно рассказал ему чем я занимался последнее время.

– Мне удалось вставить табличный движок MergeTree из ClickHouse в PostgreSQL табличным методом доступа, на ванильное ядро СУБД без форка с доступом к нему из векторизованного исполнителя запросов.

– Зачем? Просто возьми ClickHouse и не мучайся.

– Затем что мне нужны JOIN между множеством таблиц и согласованность данных в системе и одна привычная в разработке и поддержке платформа.

– Так не бывает. Либо надежно, либо быстро!

– А у меня получилось совместить лучшее из двух миров.

Векторизация уперлась в heap

– Подожди. Ты же недавно портировал Greenplum в расширение для постгреса, а потом написал векторный исполнитель поверх него. MergeTree-то тебе зачем?

– Векторизация быстро молотит столбцы, но захлебывается в момент считывания данных.

Предыстория этого началась когда я перенес Apache Cloudberry на PostgreSQL 19 в виде набора расширений и мне достался ORCA на PostgreSQL19 - стоимостный планировщик Greenplum. Сверху оптимизатора запросов разработал pg_vexec векторизованный исполнитель без единого патча ядра.

Его узлы VecScan, VecHashJoin, VecAgg и VecSort попадают в план запроса через штатные механизмы расширения СУБД и соревнуются с обычными узлами по стоимости. ORCA оценивает векторные узлы своей моделью стоимости. Внутри исполнитель обрабатывает батчи данных по 1024 строки в колоночном виде ядрами-вычислителями, привязанными к OID функции. Если специального ядра нет, выражение вычисляет штатный интерпретатор выражений постгреса прямо внутри узла.

Первый замер на ClickBench меня не порадовал, векторизация не сильно помогла в ускорении. На стандартном планировщике на heap pg_vexec уменьшил среднее геометрическое время выполнения запроса всего на 19% с 625 -> 506мс, а на ORCA на 18% с 1647 -> 1349мс.

– 18%, серьезно? И ради этого стоило городить векторизацию?

– Ну я запустил профилирование и понял что ядра функций занимали только 0-2% от общего времени исполнения запроса. В зависимости от запроса разбор широких строк съедал 51-86% тактов GROUP BY, проверки значения в хеш-таблице 5%. Еще 26-47% уходило на копирование страниц.

Если строка весит около 1.4КБ, на страницу помещается 5-6 строк и батч из 1024 строк размазан примерно по 180 страницам. Чтобы достать три колонки из 105 в таблице теста, heap честно поднимает страницы в память целиком. Табличный API постгреса отдает строки по одной через слот, а scan_begin() даже не принимает список нужных для запроса колонок.

Пропустить ненужные страницы тоже нечем: heap такой точки расширения не дает. Симуляция такого пропуска ускоряла те же запросы ClickBench в 8-100 раз (Q20 с 510 до 5.3мс). Но это была лишь симуляция.

– Так возьми колоночное хранилище. Их для PostgreSQL с десяток.

– Ты конечно советчик Капитан Очевидность) На немодифицированном PostgreSQL19 у vexec не нашлось ни одного источника, который бы отдавал данные поколоночно. Citus columnar и Hydra внутри колоночные, а наружу через tableAM API отдают те же самые слоты по одной строке. PAX и AOCO из гринплама живут только на пропатченном ядре порта постгреса: кроме расширений у порта есть и серия патчей ядра. Порт конечно на 16 сегментах gp_ao с ORCA + vexec выжимал 105мс но это шестнадцать сегментов против одного узла и все же форк ядра СУБД от которого хотелось уйти.

Так в моем плане появилась задача затащить движки таблиц семейства MergeTree и попытаться догнать ClickHouse на ClickBench.

Как уменьшал среднее время запроса от 715 до 49 миллисекунд

Первая же, еще неоптимизированная версия MergeTree вместе с pg_vexec сразу снизила среднее геометрическое время выполнения запроса с 715мс у heap без векторизации в замере 5 октября до 148мс. После чего началась обычная программистская жизнь: профилирование, гипотезы, прототипирование и откат изменений.

Автовекторизация GCC на -O2, с которым собирает PGXS, почти ничего не дала: векторизовался ровно один цикл и ни одного сравнения. После переписывания с помощью нейронки на SSE2 с movemask сравнение 1024 строк уже занимало 82нс вместо 426.

В коде, который решает, может ли выражение бросить ошибку не было ветки для T_TargetEntry. В итоге VecHashJoin читал строчного потомка по одной строке и TPC-DS Q2 из-за этого собирал 2153556 батчей из одной строки. Фикс в одну строку дал 1246мс вместо исходных 3447.

В режиме auto планировщик не ставил VecSort над узлом скана, а в режиме force стоимость VecSort проваливалась в минус: -46403! После фикса SELECT * FROM hits ORDER BY CounterID LIMIT 10 идет 67-76мс вместо исходных 2.1 секунды.

Скан сначала просит у источника данных колонки первого условия, а остальные дочитывает только для батчей, где после первого условия остались строки. В запросе Q23 в один поток это уменьшило время выполнения с 471 до 247мс.

Колонки с небольшим числом различных значений теперь приходят в pg_vexec в виде индексов в словаре LowCardinality, а выражения считаются один раз на каждое значение словаря. Q10 в один поток 85.6мс вместо 148.2.

Движок регулярных выражений PostgreSQL строит DFA заново при каждом вызове, а в Q28 его функция miss() съедала 36% тактов. Собственное ядро в векторизаторе плюс агрегация с разделами в общей памяти сделали время выполнения для Q28 более чем в пять раз меньше - с 1090 до 213мс. А по всему ClickBench сэкономили 16%.

Если честно, не все шаги вели к улучшениям. Таблица с LowCardinality, мелкими гранулами и без сжатия вдруг стала медленнее и заняла 11.8ГБ на диске вместо 4.8ГБ. Место съело отключенное сжатие, а замедление оказалось вовсе не в опциях: стенд перезапускал сервер до конца слияний, и запросы читали 35 частей вместо трех. Даже исходная таблица на 35 частях давала 106мс против 71. Самое большое ускорение для pg_vexec было при первоначальной работе с MergeTree, а дальше прогресс был медленным и кропотливым, с множеством развилок куда идти в проекте дальше.

Мне бы очередной колоночный велосипед

Я так и начинал со своего велосипеда, но быстро выбросил его на металлолом. MergeTree испытан годами в пользовательской аналитике под реальной нагрузкой и запросами бизнеса. Ну и конечно же в ClickBench невозможно победить без технологий, под которые этот тест писался: форматов данных ClickHouse, фоновой сортировки и слияний в хранилище.

Теперь в постгресе можно запустить ch_table_engines и получить хранилище, заточенное под анализ логов сервера:

-- postgresql.conf: shared_preload_libraries = 'ch_table_engines'
CREATE EXTENSION ch_table_engines;
CREATE TABLE hits (...) USING mergetree
    WITH (order_by = 'CounterID, EventDate, UserID', storage = 'pages');

– То есть ты просто слинковал ClickHouse с бэкэндом постгреса?

– Не просто. MergeTree отдельно не собирается, он часть dbms ClickHouse и тянет за собой интерпретатор, парсеры и функции. Из полутора сотен сабмодулей понадобилось лишь 28. На выходе после статической линковки получается libchte_engine.so. Взять готовый StorageMergeTree тоже нельзя с его многопоточной моделью одного серверного процесса. При попытке доступа второго процесса в свой каталог получаешь “Another server instance in same directory is already running” а постгрес на каждое соединение создает отдельный процесс. Поэтому понадобилась своя синяя изолента, чтобы примотать его к PostgreSQL.

Но толка от MergeTree в стандартном исполнителе постгреса мало. Без векторизованного исполнителя MergeTree проигрывал даже heap: под ORCA сумма запросов TPC-H SF1 61.8с против 17.2с у heap со штатным исполнителем, TPC-DS 380.5с против 58.8с. С pg_vexec те же запросы на MergeTree шли 6.9с и 48.6с. Без этой детали получалось колоночное хранилище, которое отдает данные построчно через слот, как медленный heap.

UPDATE не раздувает таблицу

– Ладно аналитика. Но признай что постгрес на обновлении данных при длительных транзакциях это боль. Каждый UPDATE новая версия строки, мертвые кортежи копятся и автовакуум пыхтит. Для нас в начале эксплуатации системы bloat таблиц был проблемой. Пришлось сокращать время транзакций и выносить аналитические запросы из постгреса.

– Признаю! Поэтому я и сделал свой iheap - табличный метод доступа PostgreSQL 19 который перезаписывает строку на месте, по тому же TID. Ее старая версия уходит в undo log, и собирает ее оттуда только тот читатель, чьему снимку она нужна.

Идейно похоже на проект zheap который много лет пытались довести до ядра, но так и забросили. Мне же удалось сделать подобное без патча ядра. В PostgreSQL 19 для реализации хватает штатных механизмов СУБД, от собственного менеджера ресурсов до горизонтов видимости.

-- postgresql.conf: shared_preload_libraries = 'pg_inplace_update_am'
CREATE EXTENSION pg_inplace_update_am;
CREATE TABLE accounts (id int PRIMARY KEY, balance int) USING iheap;
ALTER TABLE old_table SET ACCESS METHOD iheap;  -- перевести существующую

Undo живет в экстентах основного слоя самой таблицы, поэтому WAL, восстановление после сбоя, реплики, бэкапы и логическая репликация работают как у любой таблицы. Блокировки строк - тоже undo-записи, так что нет ни MultiXact ни заморозки. А чтобы обновление индексированной колонки не трогало остальные индексы, в расширении живет dmbtree форк btree из PG19, который умеет помечать старую запись удаленной: новая запись появляется только в индексах, чьи ключи изменились, а не во всех индексах таблицы.

Мой PostgreSQL

– Ну хорошо. И зачем все это если ClickHouse все равно быстрее работает?

– Затем что это отличная база данных для огромной таблицы метрик с сотнями колонок. А если в базе еще должны быть транзакционные данные из разных систем и десятки соединений между таблицами. Все можно получать в PostgreSQL, в единой транзакции. И длительная транзакция теперь не такая большая проблема при частых обновлениях: в iheap таблицы так не раздуваются, как в heap. Если у тебя не бигтех зачем тебе городить сложности в поддержке систем?

Мне удалось на своем ноутбуке в PostgreSQL19 запускать запросы ClickBench на 10 млн строк всего в 3 раза медленнее чем на ClickHouse с теми же данными на той же машине: 49.9мс против 16.8мс по среднему геометрическому. Ванильный постгрес при запусках был в 39 раз медленнее кликхауса. Получился свой набор расширений для PostgreSQL pg_vexec, ch_table_engines и pg_inplace_update_am и ни одного патча ядра. Хранилище в базе просто выбирается стандартным синтаксисом CREATE TABLE ... USING mergetree для метрик и широких таблиц, iheap для горячих обновлений без bloat таблиц и индексов. Реплики, pg_dump и MVCC работают как у обычного heap, потому что ими по-прежнему занимается PostgreSQL.

Специализированная база это сложности в разработке и поддержке, если масштаб данных не телеметрия Большого адронного коллайдера или Google. Старичок PostgreSQL еще может потягаться с современными базами данных в проектах из обычной жизни!