Розробка сховища історичних даних ордерів

Розробка сховища історичних даних ордерів

Історія ордерів — це не просто архів. Це первинне джерело даних для аналізу ефективності виконання, бектестингу, розрахунку комісій, податкової звітності та аудиту торговельних стратегій. Правильно спроектоване сховище повинне забезпечувати високу швидкість запису в real-time та аналітичні запити за багатовимірними зрізами за роками даних.

Структура даних ордера

Ордер у торговельній системі — це не просто запис "купив 1 BTC по 50000". Повна модель включає кілька типів подій:

Order Lifecycle Events:

• ORDER_CREATED — первинне створення з параметрами
• ORDER_ACCEPTED — біржа підтвердила отримання
• ORDER_PARTIALLY_FILLED — часткове виконання
• ORDER_FILLED — повне виконання
• ORDER_CANCELLED — відмена (з причиною)
• ORDER_REJECTED — відмова біржі (з кодом помилки)
• ORDER_EXPIRED — вичерпав таймаут

Зберігання цих подій окремо (event sourcing) забезпечує повну воспроізводимість: ви завжди можете відновити стан будь-якого ордера в будь-який момент часу.

Схема бази даних

Для часових рядів ордерів оптимально використовувати TimescaleDB (розширення PostgreSQL) або ClickHouse.

TimescaleDB — хороший вибір, якщо вже використовуєте PostgreSQL. Автоматично партиціонує таблиці за часом (hypertables), підтримує безперервні агрегації (continuous aggregates) та політики компресії.

CREATE TABLE order_events (
    event_id        UUID DEFAULT gen_random_uuid(),
    event_time      TIMESTAMPTZ NOT NULL,
    order_id        UUID NOT NULL,
    exchange        VARCHAR(32) NOT NULL,
    symbol          VARCHAR(32) NOT NULL,
    event_type      VARCHAR(32) NOT NULL,
    side            VARCHAR(8),
    order_type      VARCHAR(16),
    price           NUMERIC(24, 8),
    quantity        NUMERIC(24, 8),
    filled_qty      NUMERIC(24, 8),
    avg_fill_price  NUMERIC(24, 8),
    commission      NUMERIC(24, 8),
    commission_asset VARCHAR(16),
    client_order_id VARCHAR(64),
    strategy_id     VARCHAR(64),
    metadata        JSONB
);

SELECT create_hypertable('order_events', 'event_time',
    chunk_time_interval => INTERVAL '1 day');

Індекси для типових паттернів запитів:

CREATE INDEX ON order_events (order_id, event_time DESC);
CREATE INDEX ON order_events (exchange, symbol, event_time DESC);
CREATE INDEX ON order_events (strategy_id, event_time DESC);
CREATE INDEX ON order_events USING GIN (metadata);

Паттерни запитів та оптимізація

Відновлення стану ордера — часта операція. Замість повторного обчислення з подій щоразу, підтримуємо матеріалізовану таблицю orders з поточним станом:

CREATE TABLE orders (
    order_id        UUID PRIMARY KEY,
    exchange        VARCHAR(32) NOT NULL,
    symbol          VARCHAR(32) NOT NULL,
    status          VARCHAR(32) NOT NULL,
    side            VARCHAR(8) NOT NULL,
    order_type      VARCHAR(16) NOT NULL,
    price           NUMERIC(24, 8),
    quantity        NUMERIC(24, 8),
    filled_qty      NUMERIC(24, 8) DEFAULT 0,
    avg_fill_price  NUMERIC(24, 8),
    total_commission NUMERIC(24, 8) DEFAULT 0,
    strategy_id     VARCHAR(64),
    created_at      TIMESTAMPTZ NOT NULL,
    updated_at      TIMESTAMPTZ NOT NULL
);

Оновлення цієї таблиці відбувається при кожній новій події через тригер або логіку на стороні додатку.

Аналітичні запити — зазвичай це агрегації за стратегією, інструментом, періодом:

-- P&L за стратегіями за період
SELECT
    strategy_id,
    symbol,
    SUM(CASE WHEN side = 'BUY' THEN -filled_qty * avg_fill_price ELSE filled_qty * avg_fill_price END) as realized_pnl,
    SUM(total_commission) as total_fees,
    COUNT(*) as order_count
FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'
  AND status = 'FILLED'
GROUP BY strategy_id, symbol
ORDER BY realized_pnl DESC;

TimescaleDB continuous aggregates дозволяють предвичислити ці агрегації та оновлювати їх інкрементально:

CREATE MATERIALIZED VIEW orders_daily_summary
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 day', created_at) AS day,
    exchange,
    symbol,
    strategy_id,
    COUNT(*) AS order_count,
    SUM(filled_qty * avg_fill_price) AS volume,
    SUM(total_commission) AS fees
FROM orders
WHERE status = 'FILLED'
GROUP BY 1, 2, 3, 4;

Зберігання fills окремо

Для детального аналізу якості виконання критично зберігати individual fills (часткові виконання) окремо від ордерів:

CREATE TABLE order_fills (
    fill_id         UUID DEFAULT gen_random_uuid(),
    fill_time       TIMESTAMPTZ NOT NULL,
    order_id        UUID NOT NULL REFERENCES orders(order_id),
    exchange        VARCHAR(32) NOT NULL,
    symbol          VARCHAR(32) NOT NULL,
    price           NUMERIC(24, 8) NOT NULL,
    quantity        NUMERIC(24, 8) NOT NULL,
    commission      NUMERIC(24, 8),
    commission_asset VARCHAR(16),
    is_maker        BOOLEAN,
    trade_id        VARCHAR(64)
);

SELECT create_hypertable('order_fills', 'fill_time',
    chunk_time_interval => INTERVAL '1 day');

Це дозволяє розраховувати VWAP виконання, порівнювати з mid-price на момент виконання (market impact), аналізувати maker/taker ratio за стратегією.

Політики утримання та архівування

Гарячі дані (останні 30 днів) зберігаються без компресії для максимальної швидкості запису та читання. Старіші дані компресуються:

ALTER TABLE order_events SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'exchange, symbol',
    timescaledb.compress_orderby = 'event_time DESC'
);

SELECT add_compression_policy('order_events',
    INTERVAL '30 days');

Компресія TimescaleDB забезпечує 10–20x зменшення розміру для часових рядів з повторюваними значеннями. На практиці таблиця order_events об'ємом 100GB стискається до 5–10GB.

Дані старше 2 років можна експортувати в Parquet-файли на S3 за допомогою pg_parquet або власного ETL, збереживши можливість історичного аналізу через Athena або ClickHouse.

Pipeline ingestion

Високочастотний запис вимагає batching. Замість INSERT на кожну подію:

COPY для bulk inserts — у 10–50x швидше ніж окремі INSERTs. Накопичуємо події в пам'яті (100ms або 1000 подій, що станеться раніше) і записуємо одним COPY.

Unlogged tables для проміжного буфера — WAL не пишеться, швидкість запису значно вища. Після агрегації дані переносяться в основну logged таблицю.

Connection pooling через PgBouncer — торговельні системи відкривають багато коротких з'єднань. PgBouncer у transaction mode дозволяє обслуговувати тисячі клієнтів через невеликий пул реальних з'єднань PostgreSQL.

Моніторинг та алерти

Ключові метрики для моніторингу сховища:

Втрата ордерів — це завжди критичний інцидент. Система повинна мати механізм reconciliation: періодично порівнювати локальну історію з даними біржі через REST API та заповнювати прогалини.

Репліація та відмовостійкість

Production-сховище працює в режимі streaming replication PostgreSQL: primary для записів, replica для аналітичних запитів. При падінні primary — failover через Patroni або PgBouncer з автоматичним переключенням. RPO (Recovery Point Objective) при правильній настройці synchronous_commit — нульовий.