Розробка системи зберігання граббінг-даних (TimescaleDB, ClickHouse)

Розробка системи зберігання парсинг-даних (TimescaleDB, ClickHouse)

Сирі дані з блокчейну або бирж накопиченням швидко — десятки гігабайт в день для активно парсимих джерел. Зберігати це у звичайному PostgreSQL в одній таблиці означає деградацію запитів через кілька місяців. Вибір між TimescaleDB та ClickHouse — це вибір між двома принципово різними моделями зберігання та різними паттернами запитів. Обидві технології правильні у своєму контексті.

TimescaleDB vs ClickHouse: коли що

TimescaleDB — розширення PostgreSQL. Додає hypertables (автоматичне партиціонування по часу), continuous aggregates (інкрементальні матеріалізовані представлення), compression. Ви залишаєтесь в PostgreSQL-екосистемі: стандартний SQL, ACID транзакції, JOIN з звичайними таблицями, знайома інструментарій.

ClickHouse — колоночна OLAP база даних. Дані зберігаються по стовпцях, що дає величезний виграш при агрегаціях по підмножині колонок. Швидкість GROUP BY та SUM на мільярдах рядків — на порядок вище PostgreSQL. Слаба сторона: немає транзакцій, UPDATE/DELETE — дорогі операції, JOIN працює інакше.

Рекомендація для парсинга on-chain даних:

• TimescaleDB — якщо дані потребуються для продуктової логіки (балансы, позиції, рахунки), є JOIN з реляційними даними, потребують ACID-гарантій
• ClickHouse — якщо це аналітичний конвеєр (торговельні сигнали, агрегована статистика, історичний аналіз), запити працюють з великими діапазонами дат

В production часто комбінують: TimescaleDB для гарячих/операційних даних + ClickHouse для аналітичного warehouse.

Архітектура TimescaleDB

Hypertables

Базова концепція: звичайна таблиця PostgreSQL перетворюється на hypertable — під капотом створюються чанки (partitions) по часовому вимірюванню. Кожен чанк — окремий файл, старі чанки можна компресувати або архівувати.

-- Створюємо таблицю
CREATE TABLE trades (
  time        TIMESTAMPTZ NOT NULL,
  exchange    TEXT NOT NULL,
  symbol      TEXT NOT NULL,
  price       NUMERIC(20, 8) NOT NULL,
  volume      NUMERIC(20, 8) NOT NULL,
  side        CHAR(4) NOT NULL  -- 'buy' | 'sell'
);

-- Перетворюємо на hypertable з чанками по 1 дню
SELECT create_hypertable('trades', 'time', chunk_time_interval => INTERVAL '1 day');

-- Індекси per chunk (TimescaleDB створює автоматично на time)
CREATE INDEX ON trades (symbol, time DESC);

Розмір чанку — ключовий параметр. Правило: кожен чанк повинен поміститися в пам'яті для bulk insert (зазвичай 1-7 днів для біржевих даних).

Continuous Aggregates

Замінюють дорогі realtime GROUP BY на інкрементальні матеріалізовані представлення:

-- Агрегат OHLCV по хвилинам
CREATE MATERIALIZED VIEW trades_1m
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 minute', time) AS bucket,
  symbol,
  exchange,
  first(price, time)  AS open,
  max(price)          AS high,
  min(price)          AS low,
  last(price, time)   AS close,
  sum(volume)         AS volume,
  count(*)            AS trade_count
FROM trades
GROUP BY bucket, symbol, exchange
WITH NO DATA;

-- Політика оновлення: оновлювати агрегат кожні 1 хвилину, починаючи з 2 хвилин тому
SELECT add_continuous_aggregate_policy('trades_1m',
  start_offset => INTERVAL '2 minutes',
  end_offset   => INTERVAL '1 minute',
  schedule_interval => INTERVAL '1 minute'
);

Тепер запит SELECT * FROM trades_1m WHERE bucket > NOW() - INTERVAL '1 day' — це SELECT з матеріалізованого представлення, не агрегація по raw даним.

Compression

Старі дані компресуються майже без втрати функціональності (крім UPDATE/DELETE):

-- Включаємо компресію з сортуванням по symbol+time (оптимально для наших запитів)
ALTER TABLE trades SET (
  timescaledb.compress,
  timescaledb.compress_orderby = 'time DESC',
  timescaledb.compress_segmentby = 'symbol'
);

-- Автоматично компресувати чанки старші за 7 днів
SELECT add_compression_policy('trades', INTERVAL '7 days');

Типова ступінь стиснення для біржевих даних: 10-20x. 100GB raw → 5-10GB compressed.

Retention policy

-- Автоматично видаляти дані старші за 2 роки
SELECT add_retention_policy('trades', INTERVAL '2 years');

-- Або: перемістити старі чанки на дешеве сховище (tablespace на HDD)
SELECT add_tiering_policy('trades', INTERVAL '30 days');

Архітектура ClickHouse

MergeTree engines

Вибір engine — критичний момент. Для парсинг-даних частіше використовуються:

MergeTree — базовий, без спеціальних властивостей:

CREATE TABLE trades
(
    time      DateTime64(3),
    exchange  LowCardinality(String),
    symbol    LowCardinality(String),
    price     Decimal(20, 8),
    volume    Decimal(20, 8),
    side      Enum8('buy' = 1, 'sell' = 2)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(time)
ORDER BY (symbol, exchange, time);

ORDER BY у ClickHouse — це одночасно первинний ключ (sparse index) та фізичний порядок зберігання. Вибирайте за паттернами запитів: якщо частіше фільтруєте по (symbol, time) — саме такий ORDER BY.

ReplacingMergeTree — для дедупликації при insert (потрібно для надійного re-processing):

CREATE TABLE balances
(
    time      DateTime64(3),
    address   String,
    token     LowCardinality(String),
    balance   Decimal(38, 18),
    block_number UInt64,
    _version  UInt64  -- зазвичай block_number або timestamp для dedup
)
ENGINE = ReplacingMergeTree(_version)
PARTITION BY toYYYYMM(time)
ORDER BY (address, token, time);

При дублюючихся вставках (same primary key) — зберігається запис з максимальним _version. Dедупликація відбувається при merge, не негайно — для точних запитів потрібен FINAL modifier.

SummingMergeTree — для агрегатів (обсяги, суми):

CREATE TABLE volume_by_hour
(
    hour     DateTime,
    symbol   LowCardinality(String),
    volume   Decimal(20, 8),
    count    UInt64
)
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(hour)
ORDER BY (symbol, hour);

Batch insert

ClickHouse оптимізований для вставки батчами. Частих дрібних вставок — анти-паттерн:

import clickhouse_connect

client = clickhouse_connect.get_client(host='localhost')

# Правильно: батч мінімум 1000-10000 рядків
def flush_buffer(rows: list[dict]):
    if len(rows) < 1000:
        return  # накопиченням дальше
    
    client.insert(
        'trades',
        [[r['time'], r['exchange'], r['symbol'], r['price'], r['volume']] for r in rows],
        column_names=['time', 'exchange', 'symbol', 'price', 'volume']
    )
    rows.clear()

Мінімальний рекомендуємий батч: 1000 рядків. Оптимальний: 10,000-100,000 рядків. Частота: не частіше 1-2 разів в секунду на таблицю.

Materialized Views у ClickHouse

ClickHouse materialized views — триггерні, оновлюються при insert (не по розписанню як у TimescaleDB):

-- Materialized view для агрегації OHLCV
CREATE MATERIALIZED VIEW trades_1h_mv
TO trades_1h  -- цільова таблиця
AS
SELECT
    toStartOfHour(time) AS hour,
    symbol,
    exchange,
    argMin(price, time)  AS open,
    max(price)           AS high,
    min(price)           AS low,
    argMax(price, time)  AS close,
    sum(volume)          AS volume
FROM trades
GROUP BY hour, symbol, exchange;

ASOF JOIN для часових даних

Унікальна функція ClickHouse для часових даних — джойн по найближчому значенню:

-- Присоєднати ціну активу до кожної ликвідації (найближча ціна до события)
SELECT 
    l.time,
    l.symbol,
    l.quantity_usd,
    p.price AS price_at_liquidation
FROM liquidations l
ASOF LEFT JOIN prices p
    ON l.symbol = p.symbol AND l.time >= p.time;

Загальні практики

Типи даних. Використовуйте LowCardinality(String) для полів з малою кардинальністю (exchange, symbol, side) — економія 2-10x по розміру та прискорення фільтрації. Decimal замість Float для фінансових значень — жодних проблем з точністю.

Партиціонування. По місяцям (toYYYYMM) — стандарт для більшості фінансових даних. Дозволяє дропати старі партиції без DELETE.

Мониторинг. Ключові метрики:

• Розмір таблиць та партицій
• Кількість parts (у ClickHouse: багато маленьких parts = проблема)
• Час виконання медленних запитів
• Insert latency при пиковій нагрузці

Стек

Проектування схеми + настройка TimescaleDB або ClickHouse для конкретного обсягу та паттернів запитів: 1-2 тижні. Якщо включає міграцію існуючих даних — плюс тиждень.