Если вы строите продукт на VDS и разрываетесь между PostgreSQL и ClickHouse, вы не одиноки. Каждая СУБД решает свою задачу блестяще, но по-разному. PostgreSQL — универсальная транзакционная база (OLTP) с сильным SQL и строгими гарантиями. ClickHouse — столбцовая аналитическая СУБД (OLAP), выдающая интерактивные отчёты по миллиардам строк. Вопрос не «что лучше», а «где какая система приносит максимум пользы» и как их корректно эксплуатировать в условиях VDS.
OLTP против OLAP: коротко о главном
OLTP (онлайн-обработка транзакций) — это короткие, предсказуемые запросы: авторизация пользователя, запись заказа, изменение статуса. Главные метрики — задержка и согласованность. PostgreSQL с его MVCC, транзакциями, блокировками и богатой экосистемой — естественный выбор.
OLAP (онлайн-аналитика) — это сканирование огромных массивов, агрегации, группировки, срезы и отчёты. Ключевые метрики — пропускная способность чтения и компрессия. ClickHouse с колоночным хранением и векторными вычислениями закрывает эту нишу.
Правильная стратегия чаще всего звучит так: «OLTP в PostgreSQL; аналитические витрины и отчёты — в ClickHouse». Связь — через CDC, стриминг или периодические ETL.
Хранение и формат данных (storage)
PostgreSQL хранит строки по-строчно (row-store). Это идеально для OLTP: вставки и обновления локальны, индексные поиски быстры, транзакции изолированы. Индексы B-tree, GIN, BRIN, GiST позволяют покрывать широкий спектр паттернов доступа. Но массовые аналитические сканы построчного формата уступают колонночному по компрессии и пропускной способности.
ClickHouse хранит данные колонками (column-store) в таблицах семейства MergeTree. Благодаря колоночной компрессии и векторизации чтение миллиарда строк по двум-трём колонкам занимает секунды. Однако частые точечные обновления и удаление отдельных строк дороже: архитектура ориентирована на вставки партиями и последующие слияния партиций.
Компрессия и пропускная способность
ClickHouse выигрывает в компрессии: в реальных кейсах экономия 5–15 раз против «сырых» данных, часто 2–4 раза против построчных форматов. Это снижает I/O и стоимость хранения. PostgreSQL опирается на индексы и планы запросов; компрессия в классическом виде отсутствует, зато есть расширения и сжатие TOAST для больших значений.
Модель согласованности и транзакции
PostgreSQL — MVCC, полноценные ACID-транзакции, блокировки, триггеры, внешние ключи, сериализуемая изоляция. Это стандарт де-факто для критичных к консистентности систем.
ClickHouse — ориентирован на неизменяемые наборы данных с последующими мержами. Вставки атомарны на уровне партиций/частей, но классических многозапросных транзакций нет (за редкими исключениями специфичных движков). Для аналитики это нормально: данные поступают батчами, а чтение идёт в режиме «почти-онлайн».
Индексы и ускорение запросов
PostgreSQL:
- B-tree для точных и диапазонных запросов.
- GIN — полнотекст и JSONB-ключи/значения.
- BRIN — «огромные» таблицы, где данные физически коррелируют с ключом (минимальный размер индекса).
- Аналитика через планировщик: CTE, оконные функции, материализованные представления.
ClickHouse:
- Сортировка по
ORDER BYи партиционирование задают физическую локализацию. - Primary key — это скорее «ключ сортировки» для data-skipping индексов, а не B-tree.
- Data skipping индексы (minmax, bloom_filter и др.) позволяют пропускать большие куски файлов.
- Материализованные представления и предварительные агрегации снижают латентность сложных отчётов.
SQL-совместимость и функции
PostgreSQL — очень полный SQL: подзапросы, оконные функции, recursive CTE, богатая работа с JSONB, геоданными, массивами, сложными типами и триггерами. Для OLTP-логики и сложных связей это критично.
ClickHouse сильно прокачал SQL-совместимость: полноценные JOIN, оконные функции, подзапросы. Однако его сильная сторона — агрегации, rollup, approximate-алгоритмы, массивные JOIN и обработка полу-структурированных данных (например, JSON) на лету со скоростью аналитического движка.
Репликация и масштабирование
PostgreSQL:
- Физическая потоковая репликация для read scaling и HA, настройка синхронности по нужным гарантиям.
- Логическая репликация и публикации для частичных потоков и миграций.
- Шардинг — через сторонние решения или расширения; требует дисциплины схемы и приложения.
ClickHouse:
ReplicatedMergeTreeиDistributedтаблицы для репликации и распределённых запросов.- Горизонтальное масштабирование заложено в архитектуре: шардируйте по ключу, храните реплики на разных узлах.
- Кворумные вставки, механизм мерджей и пересборки партиций обеспечивают устойчивость аналитических кластеров.
Бэкапы и восстановление
PostgreSQL: стратегический минимум — регулярные base backup и архивирование WAL для PITR. Это даёт возможность отката к любому моменту времени. Важно контролировать размер архива и RPO/RTO.
ClickHouse: поддерживает ALTER TABLE ... FREEZE для создания «снэпшотов» частей, а в новых версиях — механизмы BACKUP/RESTORE в различные хранилища. PITR в классическом понимании сложнее, зато восстановление больших объёмов обычно быстрее из-за колоночного формата и независимости частей.
Производительность на VDS: что важно учесть
Виртуализация добавляет свою динамику: «соседи», общий пул CPU/IO, политика гипервизора. Классические ошибки — недоучёт steal time, заниженные лимиты IOPS, невнимание к NUMA и профилям CPU.
- CPU: ClickHouse любит много ядер и высокую частоту, PostgreSQL — быстрые одиночные ядра для латентности транзакций и умеренный параллелизм.
- RAM: PostgreSQL выигрывает от большого effective cache (файловый кеш +
shared_buffers), ClickHouse — от RAM под промежуточные агрегации и join. - Storage: для аналитики NVMe часто must-have, для OLTP — низкая латентность и предсказуемые IOPS важнее пиковой пропускной способности.
- Network: распределённые запросы ClickHouse чувствительны к задержкам и пропускной способности между шардами/репликами.
Файловые системы и тюнинг
Для PostgreSQL традиционно хорош ext4 или XFS, отключение «грязных» опций монтирования, корректные значения vm.dirty_* и аккуратный выбор размеров WAL-сегментов. Для ClickHouse — файловая система с хорошей производительностью на больших последовательных чтениях и мелких метаданных, корректная настройка aio и read_ahead_kb.
Методика бенчмарков (benchmarks)
Сравнение ClickHouse и PostgreSQL прямыми «гонками» без контекста некорректно: разные нагрузки и цели. Но есть полезные микротесты, чтобы понять границы и спроектировать инфраструктуру.
- OLTP: латентность коротких транзакций, производительность индексов, конкуренция за блокировки, эффективность autovacuum.
- OLAP: пропускная способность сканов, скорость агрегаций, влияние партиционирования, стоимость JOIN больших таблиц.
- Смешанные сценарии: влияние массовых INSERT в ClickHouse на читающих клиентов, влияние VACUUM/ANALYZE на PostgreSQL при пиковых нагрузках.
Минимальные команды для старта стенда:
# PostgreSQL: инициализация теста OLTP
pgbench -i -s 50
pgbench -c 32 -j 8 -T 60 -P 5
# ClickHouse: синтетика чтения
clickhouse-benchmark --query "SELECT count() FROM table WHERE event_date >= toDate('2025-01-01')" --concurrency 16 --iterations 5
Советы по чистоте эксперимента:
- Зафиксируйте версию СУБД и конфигурацию.
- Разделите диск данных и логов, если возможно.
- Прогрейте кеши одинаково, измеряйте «холодные» и «тёплые» прогоны.
- Следите за
steal time, нагрузкой I/O и очередями на блочном устройстве.
Быстрый старт на VDS: установка и базовый тюнинг
PostgreSQL
# Установка (пример для Debian/Ubuntu)
sudo apt update
sudo apt install postgresql
# Быстрый тюнинг конфигурации (примерные значения)
# postgresql.conf
shared_buffers = 4GB
work_mem = 64MB
effective_cache_size = 12GB
maintenance_work_mem = 512MB
wal_compression = on
max_wal_size = 4GB
random_page_cost = 1.1
autovacuum = on
autovacuum_vacuum_cost_limit = 2000
# Практика: отдельный tablespace, если есть второй диск
Набор метрик для наблюдения: pg_stat_statements, состояние блокировок, прогресс вакуума, рост WAL, фрагментация горячих таблиц, эффективность индексов (idx-only scans), доля seq scan.
ClickHouse
# Установка (пример для Debian/Ubuntu)
sudo apt update
sudo apt install clickhouse-server clickhouse-client
# Базовая правка конфигурации (фрагмент config.xml, экранированный)
<yandex>
<max_threads>16</max_threads>
<max_memory_usage>8589934592</max_memory_usage> <!-- 8GB -->
<background_pool_size>16</background_pool_size>
<mark_cache_size>536870912</mark_cache_size> <!-- 512MB -->
<path>/var/lib/clickhouse/</path>
</yandex>
# Таблица с партиционированием по дате и сортировкой по ключу
clickhouse-client --query "CREATE TABLE IF NOT EXISTS events (
event_date Date,
user_id UInt64,
action String,
value Float64
) ENGINE = MergeTree PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, user_id);"
Следите за состоянием мерджей (system.merges), очередями фоновых задач, уровнем компрессии и использованием кешей (mark cache, uncompressed cache). Для больших вставок лучше использовать батчи и сжатие клиента.
Схемы интеграции: как жить вместе
На практике часто используется связка: приложение пишет в PostgreSQL, а затем данные потоково или батчами перегружаются в ClickHouse для отчётности и аналитики. Это позволяет:
- Сохранить строгую консистентность транзакций в OLTP.
- Снять тяжёлую отчётную нагрузку с PostgreSQL.
- Получить быстрые дашборды и витрины, которые обновляются почти в реальном времени.
Транспорт — на ваш выбор: CDC, очереди, периодические выгрузки. Важно обеспечить идемпотентность загрузки и репликации, а также фиксировать «водоразделы» по времени или смещениям.
Управление затратами на VDS
Стоимость складывается из CPU, RAM и storage. Для PostgreSQL опасны «узкие» диски: экономия на IOPS может обойтись таймаутами и ростом латентности. Для ClickHouse диск — тоже король, но благодаря компрессии и последовательным чтениям важно раскрыть потенциал NVMe и не упереться в сеть при распределённых запросах.
- Горизонтальный масштаб ClickHouse позволяет держать «холодные» партиции на более дешёвом носителе, «горячие» — на NVMe.
- PostgreSQL лучше масштабировать вертикально до разумных пределов и добавлять реплики для чтения.
- Размер RAM подбирайте под рабочее множество запросов и кеш (особенно для PostgreSQL).
Если часть чтений можно кэшировать на уровне приложения, рассмотрите Redis/Memcached для снижения нагрузки на БД: подробно в материале кэш Redis и Memcached для PHP-проектов.
Наблюдение и эксплуатация
PostgreSQL: регулярно проверяйте pg_stat_activity, pg_stat_all_tables, очереди автовакаума, рост «мёртвых» строк, эффективность индексов, горячие таблицы и индексы. Планируйте VACUUM FULL только при реальной необходимости (локи!), приоритезируйте pg_repack или аккуратные миграции.
ClickHouse: контролируйте скорость мерджей и количество активных частей на партицию, своевременно чистите старые партиции (TTL), измеряйте скорость сканов и влияние ORDER BY на пропускную способность. Проверяйте распределение данных по шардом и равномерность нагрузки.
Типичные анти-паттерны
- Сложные отчётные запросы по миллионам строк в PostgreSQL «в проде», без материализованных витрин — путь к латентности и блокировкам.
- Частые точечные UPDATE/DELETE в ClickHouse — дорого; используйте батчи, версии строк, пере-заливку партиций и TTL для мягкого удаления.
- Отсутствие партиционирования в ClickHouse — потери в скорости мерджей и сложности с ретеншном.
- Недонастройка autovacuum в PostgreSQL — раздувание таблиц и падение производительности.
Практические рецепты: запросы и полезные приёмы
PostgreSQL: ускорение отчётов
-- Материализованное представление
CREATE MATERIALIZED VIEW mv_daily AS
SELECT date_trunc('day', created_at) AS d, count(*) AS c
FROM orders
GROUP BY 1;
-- Расписание освежения
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily;
-- Индекс для ускорения агрегации по дню
CREATE INDEX ON orders (date_trunc('day', created_at));
Не забывайте о грамотных покрытиях индексов и о планировании времени REFRESH вне пиковых окон.
ClickHouse: предагрегации
-- Материализованное представление в ClickHouse
CREATE MATERIALIZED VIEW mv_events_daily
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date)
AS
SELECT event_date, count() AS c
FROM events
GROUP BY event_date;
Предагрегации резко снижают среднюю латентность запросов дашбордов и отчётов, особенно при интерактивной фильтрации.
Чек-лист выбора
- Нужны строгие транзакции, внешние ключи, сложная OLTP-логика? Берите PostgreSQL.
- Нужны молниеносные отчёты по огромным табличкам с компрессией и быстрой агрегацией? Берите ClickHouse.
- Нужна универсальность и один контур? PostgreSQL выдержит многое, но аналитика может потребовать витрин и предагрегаций.
- Готовы оперировать двумя СУБД? Возьмите связку: OLTP в PostgreSQL, аналитика в ClickHouse.
Резюме
PostgreSQL и ClickHouse — не конкуренты, а партнёры. В среде VDS их сильные стороны раскрываются при грамотной схеме данных, аккуратном тюнинге диска и памяти, дисциплине бэкапов и осмысленных бенчмарках. Делайте OLTP там, где важны транзакции и низкая латентность, и отдавайте массивную аналитику столбцовому движку. Так вы получите предсказуемую производительность сегодня и масштабируемость завтра.
