Order History Storage: Schema, Optimization, Pipeline

After a year of active trading, you notice: queries to order history slow down, full table scans take minutes, and generating a P&L report for the last quarter is painful. We design a storage that solves this problem once and for all. It handles 10,000 events per second and answers analytical queries in milliseconds. This is the foundation for execution quality analysis, backtesting, commission calculation, tax reporting, and trading strategy audits.

How to Choose a Database Schema for Orders?

An order in a trading system is not just a record "buy 1 BTC at 50000". The full model includes several event types. Storing these events separately (Event sourcing) gives full reproducibility: you can always restore the state of any order at any point in time. For time series of orders, TimescaleDB or ClickHouse are optimal.

TimescaleDB is a good choice if you already use PostgreSQL. It automatically partitions tables by time (hypertables), supports continuous aggregates and compression policies. Below is an example schema for an order events table.

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'); 

How to Optimize Queries to Order History?

Restoring order state is a frequent operation. Instead of recomputing from events every time, maintain a materialized table orders with the current state. Update this table on each new event via a trigger or application-side logic.

Analytical queries typically aggregate by strategy, instrument, and period. Example P&L query by strategy:

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 CURRENT_DATE - INTERVAL '1 year' AND CURRENT_DATE AND status = 'FILLED' GROUP BY strategy_id, symbol ORDER BY realized_pnl DESC; 

TimescaleDB continuous aggregates allow pre-computing these aggregations and updating them incrementally.

Storing Fills Separately

For detailed execution quality analysis, it is critical to store individual fills separately from orders. This allows calculating execution VWAP, comparing with mid-price at execution time (market impact), and analyzing maker/taker ratio by strategy.

Retention Policies and Archiving

Hot data is stored without compression for maximum write and read speed. Older data is compressed. TimescaleDB compression achieves 10–20x size reduction for time series with repeating values. Data older than 2 years can be exported to Parquet files on S3 using pg_parquet or a custom ETL, preserving historical analysis capability via Athena or ClickHouse.

Ingestion Pipeline

High-frequency writes require batching. Instead of INSERT per event, use COPY for bulk inserts — 10–50x faster. Accumulate events in memory (100ms or 1000 events) and write with a single COPY. Unlogged tables for intermediate buffer avoid WAL writes, significantly speeding up inserts. Connection pooling via PgBouncer allows serving thousands of clients.

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'); 

Monitoring and Alerts

Key metrics for storage monitoring:

Order loss is a critical incident. The system must have a reconciliation mechanism: periodically compare local history with exchange data via REST API and fill gaps.

Replication and Fault Tolerance

The production storage runs PostgreSQL streaming replication: primary for writes, replica for analytical queries. On primary failure, failover through Patroni with automatic switchover. RPO with proper synchronous_commit settings is zero. TimescaleDB Documentation recommends this configuration for critical systems. Our team has 10 years in blockchain development and has implemented similar solutions for funds with $1B+ turnover. With over 5 years on the market and 50+ successfully delivered projects, we guarantee a robust and scalable solution.

What's Included in the Work

• Documentation of the data schema and pipeline architecture.
• Code for TimescaleDB/ClickHouse schema, triggers, compression policies.
• Configured ingestion pipeline with batching and connection pooling.
• Migration and deployment scripts (CI/CD).
• Monitoring dashboards (Grafana + Prometheus).
• Runbook and training for your team (2–3 sessions).
• Post-release support for 2 weeks.

How We Develop the Storage

1. Load analysis — profile existing traffic, determine RPS and typical queries.
2. Schema design — choose between TimescaleDB and ClickHouse, design hypertables and indexes.
3. Pipeline implementation — configure batching, connection pooling, unlogged tables.
4. Compression and retention setup — define policies for hot and cold data.
5. Replication and monitoring — deploy Patroni, configure alerts and dashboards.
6. Load testing — simulate 50,000 events/s and verify p99 latency.
7. Documentation and training — hand over code, schemas, and runbook to your team.

Project Assessment

We'll assess your project for free within 2 business days. We'll send architecture recommendations and a quote in person-months. Contact us — we'll discuss your use cases and help design a reliable storage that won't let you down. Typical cost savings of 40% on storage costs compared to traditional solutions. For a mid-sized trading firm, this translates to annual savings of over $45,000. Get a consultation today.