PostgreSQL или ClickHouse: когда добавлять вторую базу

← назад к разделу

Почти каждый проект начинается с PostgreSQL — и это правильно. Но рано или поздно приходит момент: аналитические запросы начинают тормозить, дашборды грузятся минутами, а команда говорит «давайте прикрутим ClickHouse». Разберём, когда это действительно нужно, а когда — лишняя сложность.

Почему PostgreSQL вообще начинает тормозить на аналитике

PostgreSQL хранит данные строками. Это удобно: если нужна одна запись — прочитали одну строку. Если нужно обновить поле — нашли строку, поменяли.

Но аналитический запрос — «суммарная выручка по регионам за два года» — заставляет PostgreSQL читать строки целиком, даже если для ответа нужны три поля из двадцати. На миллионах строк это медленно и нагружает диск.

Правда, «целиком» тут с двумя оговорками, и обе в пользу PostgreSQL. Длинные значения — описания, большой текст, JSON — база держит не в самой строке, а в отдельной служебной таблице, и если колонку не спросили, туда никто не пойдёт. А если все нужные поля уместились в один индекс, PostgreSQL умеет ответить прямо по индексу, не заглядывая в таблицу вовсе. Так что разрыв между строковым и колоночным хранением на практике меньше, чем звучит в теории, — и это ровно та причина, по которой сначала пробуют индексы, а уже потом вторую базу.

ClickHouse хранит данные по столбцам: для того же запроса он прочитает только нужные, пропустив остальные. Плюс данные в столбцах хорошо сжимаются — однотипные значения рядом. Результат: аналитические агрегаты на миллиардах строк за секунды.

На маленькой программе видно обе стороны сделки: ответ одинаковый, объём прочитанного — разный.

import java.util.LinkedHashMap;
import java.util.Map;

public class StorageCost {
    public static void main(String[] args) {
        String[] header = {"id", "region", "amount", "status", "created"};
        String[][] rows = {{"7", "msk", "120", "paid", "04-01"},
                {"8", "spb", "340", "new", "04-01"},
                {"9", "msk", "90", "paid", "04-02"},
                {"10", "spb", "55", "paid", "04-03"}};
        Map<String, Integer> revenue = new LinkedHashMap<>();
        for (String[] row : rows) {
            revenue.merge(row[1], Integer.parseInt(row[2]), Integer::sum);
        }
        System.out.println("sum(amount) GROUP BY region -> " + revenue);
        System.out.println("прочитано ячеек: строками " + rows.length * header.length
                + ", колонками " + rows.length * 2);
        System.out.println("точечный SELECT * WHERE id = 9: строками 1 чтение, колонками "
                + header.length);
    }
}

package main

import (
	"fmt"
	"strconv"
	"strings"
)

func main() {
	header := []string{"id", "region", "amount", "status", "created"}
	rows := [][]string{{"7", "msk", "120", "paid", "04-01"}, {"8", "spb", "340", "new", "04-01"}, {"9", "msk", "90", "paid", "04-02"}, {"10", "spb", "55", "paid", "04-03"}}
	var regions []string
	revenue := map[string]int{}
	for _, row := range rows {
		amount, _ := strconv.Atoi(row[2])
		if _, ok := revenue[row[1]]; !ok {
			regions = append(regions, row[1])
		}
		revenue[row[1]] += amount
	}
	var parts []string
	for _, region := range regions {
		parts = append(parts, fmt.Sprintf("%s=%d", region, revenue[region]))
	}
	fmt.Println("sum(amount) GROUP BY region -> {" + strings.Join(parts, ", ") + "}")
	fmt.Printf("прочитано ячеек: строками %d, колонками %d\n", len(rows)*len(header), len(rows)*2)
	fmt.Printf("точечный SELECT * WHERE id = 9: строками 1 чтение, колонками %d\n", len(header))
}

const header = ['id', 'region', 'amount', 'status', 'created'];
const rows = [['7', 'msk', '120', 'paid', '04-01'], ['8', 'spb', '340', 'new', '04-01'],
  ['9', 'msk', '90', 'paid', '04-02'], ['10', 'spb', '55', 'paid', '04-03']];
const revenue = new Map();
for (const row of rows) revenue.set(row[1], (revenue.get(row[1]) ?? 0) + Number(row[2]));
console.log('sum(amount) GROUP BY region -> {' + [...revenue].map(([k, v]) => `${k}=${v}`).join(', ') + '}');
console.log(`прочитано ячеек: строками ${rows.length * header.length}, колонками ${rows.length * 2}`);
console.log(`точечный SELECT * WHERE id = 9: строками 1 чтение, колонками ${header.length}`);

header = ["id", "region", "amount", "status", "created"]
rows = [["7", "msk", "120", "paid", "04-01"], ["8", "spb", "340", "new", "04-01"],
        ["9", "msk", "90", "paid", "04-02"], ["10", "spb", "55", "paid", "04-03"]]
revenue: dict[str, int] = {}
for row in rows:
    revenue[row[1]] = revenue.get(row[1], 0) + int(row[2])
print("sum(amount) GROUP BY region -> {" + ", ".join(f"{k}={v}" for k, v in revenue.items()) + "}")
print(f"прочитано ячеек: строками {len(rows) * len(header)}, колонками {len(rows) * 2}")
print(f"точечный SELECT * WHERE id = 9: строками 1 чтение, колонками {len(header)}")

Последняя строка вывода и есть вторая сторона: точечный запрос строковое хранение отдаёт одним чтением, а колоночному приходится собирать запись из пяти отдельных мест.

Поэтому это не повод заменять PostgreSQL. ClickHouse плохо работает с точечными запросами («дай мне конкретный заказ по ID»), обновляет и удаляет отдельные записи неохотно и не поддерживает полноценные транзакции. «Неохотно» — не «никак»: удалить строки можно облегчённой командой DELETE FROM, а обновление по ключу закрывает движок ReplacingMergeTree, который при слиянии оставляет последнюю версию записи. Просто оба приёма заметно дороже привычного UPDATE и работают не мгновенно, так что строить на них домен нельзя. Вопрос звучит не «что выбрать», а «когда добавить вторую базу рядом».

Какие запросы идут в PostgreSQL, а какие — в ClickHouse

PostgreSQL — база под поток коротких операций, OLTP (Online Transaction Processing): добавить заказ, найти пользователя по email, поменять статус, удалить запись, и всё это в транзакциях, где несколько операций идут как одно целое, со связями между таблицами через JOIN.

ClickHouse — база под вопросы ко всей истории сразу, OLAP (Online Analytical Processing): агрегаты по огромным объёмам: GROUP BY регион/месяц/категория; сканирование сотен миллионов и миллиардов строк за секунды; хранение событий и журналов годами при минимальной стоимости; параллельные запросы от аналитиков и BI-инструментов.

Разницу видно на двух запросах к одной таблице. Первый — типичный OLAP: считает агрегаты за весь период, конкретная строка в ответе не нужна.

живой пример

SELECT status,
       count(*) AS orders,
       round(sum(total_amount), 2) AS revenue
FROM orders
GROUP BY status
ORDER BY revenue DESC;

Второй — типичный OLTP: нужна одна запись, целиком и прямо сейчас.

живой пример

SELECT id, customer_id, status, total_amount
FROM orders
WHERE id = 'ord-09';

Первый читает две-три колонки по всей таблице — его и стоит уносить в ClickHouse. Второй достаёт строку по индексу и остаётся в PostgreSQL.

Когда PostgreSQL справляется сам

Сначала стоит убедиться, что PostgreSQL действительно упёрся в потолок.

Несколько десятков миллионов строк — для PostgreSQL это немного. Правильные индексы и партиционирование по дате решают большинство задач.

Отчёты строит сам сервис и только несколько фиксированных запросов — тут хватит PostgreSQL с репликой для чтения: она принимает аналитику и не мешает основной базе.

Данные меняются — заказы, балансы, остатки. ClickHouse создан для событий, которые вставили и больше не трогают; правки задним числом в нём болезненны.

Команда небольшая, и некому обслуживать второе хранилище с данными. Это честный аргумент отложить ClickHouse — конвейер данных, мониторинг, резервные копии — всё это новые обязанности.

И есть ступенька между «поставим индексы» и «поднимаем ClickHouse», о которой часто забывают: посчитать тяжёлые агрегаты заранее и хранить готовыми. В PostgreSQL для этого есть материализованные представления и обычные таблицы с предагрегатами, которые обновляют по расписанию. Дашборд перестаёт пересчитывать выручку за два года на каждое открытие и читает уже посчитанные несколько тысяч строк. Для фиксированного набора отчётов этого хватает надолго.

Признаки, что пора переходить к двум базам

Несколько сигналов, что PostgreSQL начинает мешать:

Объём данных. Аналитические таблицы перевалили за 50–100 миллионов строк или ежедневно прирастают на миллионы. Отчёты занимают минуты.

Характер запросов. Не списки записей с фильтрами, а агрегаты за месяц или год: воронки, перцентили, уникальные пользователи.

Аналитики мешают проду. Команда данных или BI-инструменты запускают произвольные запросы, которые нагружают базу и замедляют продуктовых пользователей.

Долгая история. Нужно хранить события годами: клики, просмотры, статусы. ClickHouse сжимает такие данные в 5–20 раз, и длинная история перестаёт дорого стоить.

Свежесть данных не критична. Дашборды с данными «на пять минут назад» устраивают всех потребителей. ClickHouse получает данные с небольшим лагом через пайплайн, мгновенной синхронизации нет.

Про железо и деньги стоит сказать порядком величин, иначе «вторая база» звучит бесплатно. ClickHouse хорошо сжимает: типичная аналитическая таблица занимает в разы меньше, чем те же данные в PostgreSQL (сжатие в пять-десять раз на однородных колонках — обычное дело), поэтому диска нужно меньше, а не больше. Зато он любит процессор и память: аналитический запрос честно читает и считает миллионы строк, и один отчёт может занять все ядра. Ориентир для старта: отдельная машина с несколькими ядрами и десятками гигабайт памяти обслуживает десятки миллиардов строк в узких запросах — то есть железо сопоставимо с одним сервером базы, а не с кластером.

Дороже железа обходится конвейер: его надо написать, следить за отставанием, разбирать пропуски и переливать периоды. Это постоянная работа человека, и именно она, а не стоимость сервера, определяет, стоит ли вторая база одного отчёта.

Как данные попадают из PostgreSQL в ClickHouse

Нельзя просто «писать в две базы» — это рецепт рассинхронизации.

Начинать при этом стоит с самого дешёвого, что закрывает задачу.

Самый простой путь — пакетная выгрузка по расписанию: раз в час (или раз в сутки) забрать из PostgreSQL новые строки по времени создания и вставить их в ClickHouse. Годится, когда данные только добавляются и свежесть «на час назад» устраивает. У ClickHouse для этого есть и встроенные средства: движок таблиц PostgreSQL умеет читать чужую таблицу напрямую, а MaterializedPostgreSQL — сам вычитывать изменения из журнала репликации. Никакой отдельной очереди при этом не нужно.

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

1. Сервис пишет только в PostgreSQL. Изменения фиксируются в outbox-таблице или через CDC (Change Data Capture).
2. Отдельный компонент читает изменения из PostgreSQL и публикует их в Kafka: это либо Debezium, который слушает журнал репликации, либо ваш собственный сервис, разбирающий таблицу исходящих сообщений.
3. Потребитель читает из Kafka и пишет в ClickHouse пакетами.

Лаг такого пайплайна — обычно секунды или минуты. Зато данные согласованы: если запись прошла в PostgreSQL, она рано или поздно окажется в ClickHouse. Подробности на примере Java — в статье про интеграцию с сервисом; в Go, Node и Python схема та же, меняется только клиент (clickhouse-go, @clickhouse/client, clickhouse-connect).

Схема в ClickHouse при этом другая, чем в PostgreSQL: денормализованные таблицы, события, предагрегаты — то, что ускоряет аналитические запросы.

SQL там свой

Ожидание «это тот же SQL, команда разберётся» — источник сюрпризов на первой неделе. Язык похож, а поведение отличается в местах, которые важны.

Обновлений и удалений в привычном смысле нет. ALTER TABLE … UPDATE и … DELETE существуют, но это асинхронные операции перезаписи куска данных, а не изменение строки: они выполняются в фоне, могут идти минутами и не годятся для потока. Правильный приём — не менять, а дописывать новую версию строки и брать последнюю при чтении.

Дубли — норма, и с ними надо жить. Движок, который сворачивает версии по ключу, делает это когда-нибудь (при слиянии куска), а не сразу. Поэтому запрос без FINAL может вернуть две версии одной строки, а FINAL — сворачивает на лету и стоит заметно дороже. Альтернатива — агрегировать при чтении (argMax по времени версии), и обычно она дешевле.

Транзакций нет. Вставка одного куска атомарна, дальше — ничего: несколько вставок в разные таблицы не образуют операции, откатить их нельзя. Отсюда правило конвейера: все данные, которые должны попасть вместе, пишут одной вставкой в одну таблицу.

Соединения ведут себя иначе. По умолчанию правая сторона соединения целиком загружается в память на каждом узле; большое соединение двух больших таблиц — самый простой способ упасть по памяти. Поэтому справочники держат словарями (специальный вид хранения с поиском по ключу), а факты денормализуют в широкую таблицу. Соединение «как в PostgreSQL» тут не бесплатная операция, а решение с последствиями.

Вставка должна быть пачками. Тысяча вставок по одной строке создаёт тысячу кусков данных и заваливает фоновые слияния — база начинает отказывать с ошибкой про слишком много частей. Правило: вставлять пачками по десятки тысяч строк, не чаще раза в секунду, или пользоваться асинхронной вставкой на стороне сервера.

Вывод: перед переходом полезно, чтобы кто-то в команде прочитал документацию по движкам таблиц и по FINAL — иначе первые две недели уйдут на удивление.

Обновляемые измерения: клиент сменил регион

Прямое следствие совета «денормализуйте»: регион клиента, записанный в миллион строк заказов, при смене региона оказывается неверным в миллионе строк. В реляционной схеме это одна правка в справочнике, здесь — задача.

Три рабочих подхода.

Словарь вместо колонки. Регион не записывают в факты, а держат справочником-словарём и подставляют при чтении: dictGet('regions', 'name', customer_id). Тогда смена региона — обновление словаря, и все запросы сразу отвечают по-новому. Цена: словарь должен влезать в память каждого узла, поэтому годится для десятков тысяч записей, а не для миллионов. Это самый частый ответ.

Историчность как данность. Часто «регион на момент заказа» — это правильный ответ, а не ошибка: отчёт за прошлый год не должен меняться от того, что клиент переехал. Тогда денормализованное значение оставляют как есть и явно называют его историческим — и заодно избавляются от проблемы.

Перезапись затронутых кусков. Если история не нужна и словарь не подходит, остаётся пересчитать: ALTER TABLE … UPDATE region = … WHERE customer_id = … асинхронно, или (надёжнее для больших объёмов) перелить затронутый период заново из источника. Это ремонт, а не штатная операция, и планируют его как задачу.

Выбирают по вопросу «какой ответ правильный»: если отчёт должен показывать текущий регион — словарь; если регион на момент события — историчность; если исторические данные оказались неверными — перезапись.

Сверка: как убедиться, что данные сошлись

Конвейер без сверки — это конвейер, который однажды молча потерял час данных. Минимальный набор проверок, который ставят вместе с ним.

Счётчики по окнам. Число строк за каждый закрытый час в источнике против числа строк в приёмнике. Расхождение — сигнал, и ноль строк за час — самый важный случай (значит, поток встал).

Контрольные суммы по деньгам. Для финансовых данных сверяют не только количество, но и сумму: sum(total_amount) за час в обеих базах. Совпадение количества при расхождении суммы означает, что приехали не те строки.

Отставание. Метрика «сколько времени назад приехала последняя строка» с оповещением на порог. Это то, что видно раньше всего при поломке.

Что делать при пропуске. Заранее должна существовать операция «перелить период заново», и она обязана быть идемпотентной: повторная заливка того же часа не должна создавать дубли. Достигается это либо движком, сворачивающим версии по ключу, либо удалением партиции за этот период перед повторной заливкой (ALTER TABLE … DROP PARTITION) — второе проще и надёжнее, если данные разбиты по дням.

Порядок действий при частичных сигналах. Признаки «пора переходить» редко срабатывают все сразу, и это нормально. Порядок такой: сначала выжать из PostgreSQL то, что даётся дёшево, — материализованные представления и предагрегаты под конкретные отчёты, реплика на чтение для аналитики, партиционирование больших таблиц, колоночное сжатие расширением. Потом, если отчёты всё равно не укладываются в бюджет, поднять ClickHouse рядом и перевести в него один самый тяжёлый отчёт, оставив остальные на месте. И только после того, как этот один отчёт заработал и конвейер оказался стабильным, переносить остальные. Так решение проверяется на одном сюжете, а не всей аналитикой сразу.

Типичные ошибки

«Заменить PostgreSQL на ClickHouse». Перенести в ClickHouse заказы, балансы и остатки — и обнаружить, что нет транзакций, дешёвых обновлений и точечных чтений.

Слишком рано. Развернуть шардированный кластер ClickHouse под пять миллионов строк, которые PostgreSQL прожевал бы с одним индексом.

Писать в обе базы прямо из кода. Два вызова подряд — в PostgreSQL и ClickHouse — внутри одного действия. Если второй упадёт, данные разойдутся.

Перенести нормализованную схему как есть. JOIN в ClickHouse есть, но обходится дороже, чем в PostgreSQL. Схема проектируется от аналитических запросов: денормализация, события, агрегаты.

Принять реплику PostgreSQL за OLAP. Реплика снимает нагрузку чтения с основной базы, но не делает строчное хранение колоночным. Тяжёлый запрос за два года будет так же медленным: реплика — промежуточный шаг, не конечная точка.

Глубже: временные ряды: TimescaleDB, Prometheus и InfluxDBрасширенное

Метрики, показания датчиков, курсы, события с отметкой времени. В этой статье и в соседних их каждый раз отправляют в ClickHouse или Cassandra, а у класса задач есть свои движки, и выбор между ними проще, чем кажется.

У временного ряда три свойства, под которые заточены эти движки. Записи приходят почти по порядку времени и никогда не обновляются. Запрос почти всегда берёт диапазон по времени и считает по нему агрегат: среднее за минуту, максимум за час, последнее значение. И старые данные нужны грубее свежих: за сегодня по секундам, за год по часам. Отсюда три механики: секционирование по времени, чтобы удалять старое целыми кусками; непрерывные агрегаты, когда база сама ведёт часовые и дневные сводки по мере вставки; и политики хранения с прореживанием, когда сырые данные живут неделю, а сводки годами.

TimescaleDB это расширение PostgreSQL: обычная таблица объявляется гипертаблицей, SELECT create_hypertable('readings', 'ts'), и база сама режет её на куски по времени. Дальше CREATE MATERIALIZED VIEW ... WITH (timescaledb.continuous) для сводок, add_retention_policy('readings', INTERVAL '30 days') для удаления старого и сжатие старых кусков в колоночный вид, которое даёт десятикратную экономию места. Плюс в том, что это тот же PostgreSQL: те же транзакции, JOIN с таблицами устройств и клиентов, тот же драйвер и та же резервная копия. Берут его, когда ряды это часть бизнес-модели (показания приборов клиента, история цен товара) и PostgreSQL уже есть.

Prometheus это не база общего назначения, а система мониторинга со своим хранилищем: он сам опрашивает сервисы по HTTP, хранит метрики с метками, отвечает на PromQL и по умолчанию держит пятнадцать дней. Писать в него бизнес-события из приложения нельзя, для долгого хранения к нему ставят Thanos, Mimir или VictoriaMetrics. Его место это метрики инфраструктуры и приложений, о которых говорит раздел наблюдаемости.

InfluxDB рождён для рядов, принимает строчный протокол по HTTP, умеет те же политики хранения и прореживание, но это отдельная база со своим языком запросов и своей эксплуатацией, и третья версия переписала движок заново. Разумна там, где сбор идёт с устройств, а бизнес-данных рядом нет.

Правило выбора. Метрики сервисов и серверов это Prometheus, и его не заменяют базой. Ряды, привязанные к сущностям продукта, при живом PostgreSQL это TimescaleDB. Миллиарды событий с произвольной аналитикой поверх это ClickHouse, где ORDER BY (device_id, ts), TTL и AggregatingMergeTree дают те же три механики, но без транзакций и JOIN с основной базой. Cassandra под ряды берут только там, где главное требование это запись из нескольких дата-центров без остановки.

Коротко

• PostgreSQL хранит строками — быстро для точечных операций. ClickHouse хранит столбцами — быстро для аналитических агрегатов.
• ClickHouse не заменяет PostgreSQL, а дополняет: PostgreSQL держит домен и транзакции, ClickHouse — события и аналитику.
• Пора задуматься о ClickHouse, если аналитические таблицы в сотнях миллионов строк, запросы — агрегаты за длинный период, аналитики мешают проду.
• Данные передаются либо пакетной выгрузкой по расписанию (проще, и этого часто достаточно), либо пайплайном через очередь (outbox → Kafka → потребитель) — но никогда двойной записью из кода.
• Схема в ClickHouse строится от аналитических запросов, а не копируется из PostgreSQL.
• Держать две базы — это новые операционные обязанности. Если команда маленькая, лучше дольше оставаться в PostgreSQL с правильными индексами и репликой.
• Временные ряды: метрики сервисов в Prometheus, ряды рядом с бизнес-данными в TimescaleDB поверх того же PostgreSQL, миллиарды событий с аналитикой в ClickHouse; общие механики это секции по времени, непрерывные агрегаты и политики хранения.
• SQL в ClickHouse свой: обновлений в привычном смысле нет, дубли сворачиваются когда-нибудь (FINAL дорог), транзакций нет, соединения грузят правую сторону в память, а вставлять надо пачками.
• Обновляемое измерение решают словарём с подстановкой при чтении, признанием историчности или перезаливкой периода — по вопросу «какой ответ правильный».
• Конвейер ставят вместе со сверкой: счётчики и суммы по закрытым часам, метрика отставания и идемпотентная операция «перелить период заново».

Что почитать дальше

• Как устроен ClickHouse: колоночное хранение, MergeTree и OLAP — почему он быстрый и чем за это платят.
• ClickHouse из Java и Spring Boot: подключение, запись и чтение — outbox, CDC и потребитель на примере Java; клиенты для Go, Node и Python перечислены в статье про моделирование.
• PostgreSQL или MongoDB: как выбрать базу данных — развилка про основное хранилище.
• Поиск: PostgreSQL FTS или Elasticsearch — та же логика для поисковой нагрузки.