Developing a tick data storage system: where the stream breaks
Tick data storage breaks not at peak load, but on load asymmetry: the quote stream grows in steps — at session opens, when statistics are released — while the write pipeline remains steady and single-point.
As soon as the incoming rate exceeds the throughput of the ingestion buffer, the source starts discarding messages, and gaps in recording appear in the archive.
The insidious part is that they are not visible immediately: the discrepancy surfaces later, when candles are built from the archive or a trading strategy is run.
Write latency usually hides in one place — when the accumulated buffer is flushed to disk. If data ingestion and writing happen in the same thread, every disk synchronization stops the ingestion of new quotes, and the larger the batch, the longer the pause.
Decoupling ingestion and writing through a queue and batch flushing helps, but any such decoupling has a threshold: beyond it, the queue grows faster than it is drained, and losses return.
The third failure point is archive fragmentation. While there are only a few instruments and days, files open randomly and quickly; when the count runs into millions of segments, search overhead begins to exceed useful read time.
Partitioning by dates and tickers removes part of the problem, but requires an honest compression and deletion policy, otherwise the volume grows faster than the data itself.
A homegrown archive almost always started with one task: one thread, one instrument, one venue. Each new source, growth in history depth, or recovery requirement adds another branch of code without a common reliability model — the system loses predictability. We examine how this is addressed at each stage of the pipeline.
What dedicated storage delivers: effect in measurable metrics
Dedicated storage changes not the convenience of working with the archive, but measurable indicators: how many trades actually made it to record, how quickly a historical interval is served, and how fast a node returns to service after a failure. These are the metrics we put into acceptance.
Completeness of historical data. On flat files, a connection loss or process restart leaves gaps that are discovered only during strategy reconciliation.
The writing loop with a local buffer and write confirmation pushes the stream through after reconnection, and duplicates are filtered out by a composite key — instrument, timestamp, trade number.
History retrieval speed. Columnar storage on ClickHouse with partitioning by date and instrument reads only the needed columns: candles of any interval are calculated on the fly, without scanning thousands of files. A historical interval is served in seconds, and strategy recalculation stops being a nightly task.
Recovery time and registry integrity. A replica is brought up from a partition copy, the last day is rolled forward from the write log — the unavailability window shrinks from hours to minutes.
For each partition, the checksum and trade counters match: a discrepancy is visible before the data goes to the consumer, not after incident investigation.
Instead of “works or not,” acceptance measures the share of recovered gaps, response time for a historical interval, and node startup time. Thus the quote archive stops being a source of surprises.
Options for organizing a quotes archive: comparison of modes
The flow of trades arrives continuously, while history queries are arbitrary: restore the order book state at a point in time, recalculate an indicator, reconcile execution with a broker report.
One storage mode for all scenarios usually cannot handle it: fast writes come at the expense of queries, and convenient analytical tables handle individual events poorly. At the start, one chooses between three schemes, and each breaks under its own load profile.
| Option | When it fits | Limitations |
|---|---|---|
| Columnar storage | Analytical queries by time range and instrument, recalculation of aggregates. Columnar compression together with time partitioning yields multiple-fold volume savings on history over years | Single inserts are expensive — batch loading is needed. Without time partitioning, deleting old data turns into manual work |
| Streaming log | Ingestion with minimal latency, state restoration, reprocessing during incident analysis. Preserves events in arrival order | Queries with filters on many fields require rebuilding into a separate store. Long-term retention is possible only with history compression and segment rotation |
| Hybrid scheme | Both real-time ingestion and heavy analytics are needed: the log records the stream, and a loader transfers it into columnar partitions | More expensive to maintain: reconciliation, idempotent loading, and monitoring of loader lag |
The choice is determined by the load profile: the write-to-read ratio, the depth of history kept “hot,” and recovery requirements after a failure.
For a quotes archive with a predominance of analytics, a columnar store with time partitioning is sufficient; if the system also trades, the hybrid covers both scenarios without losing events.
Implementation stages: from stream audit to handover to operations
The tick stream does not tolerate approximate solutions: records arrive unevenly, peaks are several times above average, and the source periodically emits duplicates and backdated data.
That is why we start not with storage selection but with a survey of the current environment — and only then move to the data schema and validation on real volumes.
- Storage environment audit. We build a map of sources: frequency, format, points where records are lost and duplicates form. The output is a report with bottlenecks and a volume growth forecast.
- Data schema. We define time partitioning, the sorting key, field bit width, and compression. These decisions affect both query speed and occupied volume: in ClickHouse, the hot layer returns per-instrument history with tick precision, while old data goes to the cold archive.
- Configuring ingestion and writing. We build a pipeline with a buffer between the source and storage: it absorbs peaks, and redelivery does not create duplicates. We separately test connection loss and source restart.
- Load testing. We reproduce the historical stream at production rate and measure write latency and response time for typical queries. We test node failure: queries must not be interrupted.
- Data reconciliation. Checksums by period, reconciliation with the source, restore from backup with timing. We bring the discrepancy in record count to zero.
- Handover to operations. We hand over configurations, the placement schema, retention and cleanup policies, on-call instructions, and stream monitoring dashboards. Routine operations are performed by the customer's team itself.
Deliverables: configurations, storage schemas, and operational procedures
We hand over not only a working pipeline, but also everything needed to operate it independently. Below is the minimum set that remains with the customer's team after handover: without it, the very first failure in stream writing turns into manual archive forensics.
- Configuration of the write and read services — buffering parameters, batch size, retry timeouts, concurrent connection limits. The values are moved into a separate configuration layer: they can be changed without rebuilding.
- Storage schema description — keys and sort order, time-based partitioning, retention of hot and cold partitions, compaction rules. The schema is tied to the format of the incoming stream, so when the source changes it is clear exactly what needs to be rewritten.
- Storage documentation — a map of tables and service fields, the purpose of each partition, and typical queries for reconciling volumes and finding gaps in the data.
- Access rules — roles for services and people are separate: write, read within one's own group of instruments, full access, administrative operations. Granting rights does not require editing configs on the servers.
- Maintenance procedures — the frequency of integrity checks, the order of partition compaction, actions to take when writing falls behind, alert thresholds, and who responds to them.
- Backup scheme — what is copied, how often, where the copies are stored, how their usability is verified, and how long restoration takes on a clean environment.
- Instructions for typical operations — adding a new instrument to the stream, rolling back to the previous schema state, exporting a historical slice for analytics.
The set is put together so that a new engineer on the customer's team can deploy a stand and reproduce the process from the document without contacting us. If the task involves migrating or expanding the stream, describe it and we will come back with an estimate and a work plan.
Case: quote registry without gaps and with fast querying
Before: gaps in the stream and hour-long reports
Quotes for forty instruments were written to a single table on a single server, with inserts going one record at a time.
During high-volatility hours the ingestion buffer overflowed and some ticks were lost: over the quarter, gaps accumulated in three instruments, and there was no way to recover them — the source stream was not stored anywhere.
Building a report on hourly candles and the volume-weighted average price took 40–60 minutes, because the aggregate was recalculated across the entire history every time.
After: a continuous archive and queries in seconds
The archive was split into two layers: hot — in a columnar analytical DBMS with partitions by day and instrument, cold — Parquet in object storage with partitioning by date and symbol.
Ingestion was made idempotent by the key "instrument + timestamp + source": re-delivery no longer creates duplicates, and a reconciliation pass against the exchange archive Deribit closes holes in the records.
What shifted in the metrics: tick loss — from a fraction of a percent to zero on the reconciliation window; a daily report — 40–60 minutes → 20–30 seconds; re-exporting a year of history — almost a day → 15–20 minutes; the archive size with columnar compression and deduplication shrank sixfold while preserving full depth.
How does a columnar archive differ from a streaming log?
A columnar archive and a streaming log are different classes of storage, and the choice between them is not based on data volume, but on what is done with the data afterward.
The archive lays out a tick by columns — timestamp, price, volume, trade side — and compresses each separately: a query over a date range and a narrow set of fields reads only the required columns and does not touch the rest.
The log is built the opposite way. Records are appended strictly in arrival order, acknowledgment does not wait for indexing, and after a failure the state is restored by replaying up to the last confirmed position.
The price for this is reading: a month-long slice from the log has to be assembled by traversing it in full.
| Option | When it fits | Limitations |
|---|---|---|
| Columnar archive | Historical analytics: date ranges, aggregates, a narrow set of fields | Appending in small batches is more expensive — each fragment requires compaction; point updates are slow |
| Streaming log | Ingesting a continuous stream of ticks, recovery after failure, serving a “hot” window | Volume grows linearly, a long-period slice is not built from it — transfer to the archive is needed |
Recovery separates the approaches most strongly: the log provides a rollback point for any confirmed record, while the archive provides only segments and snapshots that lag behind the latest tick.
In practice, both are kept: the log ingests the stream and serves the last minutes, while a background transfer compacts it into columnar segments.
The choice of a tick data storage system comes down to three numbers — history depth, acceptable read latency, and the share of write operations in the overall workload profile.
Technical doubts: answering frequently asked questions
Before starting the development of a tick data storage system, engineers check four things: what happens to records when a node fails, how the archive grows, whether analysts will get raw data without exports, and whether the storage will coexist with the current environment. We answer directly.
What happens to data if a node fails during a write?
The stream is written into immutable segments: a segment is first fixed on disk and only then becomes visible to readers. An incomplete segment after restart is recognized and discarded, and the missing part is recovered by re-ingestion.
Duplicates at the boundary are eliminated by the key "instrument + timestamp", so replay does not corrupt history.
How fast does the archive grow and what limits it? Volume is calculated in advance — based on the number of instruments, update frequency, and history depth, so expansion is planned before space runs out.
Savings come from compression of similar fields and separation of hot and cold layers: fresh records on fast media, history on cheap ones.
How do analysts get access without interfering with writes? Reading is offloaded to a separate environment: queries go to replicas and the data mart layer, so a month-long query does not slow down data ingestion.
The analyst works through a SQL-compatible interface, without intermediate file exports; permissions are delimited by instruments and date ranges.
Will the current environment need to be rebuilt? We connect to existing message brokers, scheduler, and monitoring, transfer history from current files and tables with consistency checks.
If part of the environment remains on the previous stack, the exchange is built so that existing consumers continue to read data without rewriting.
Let's discuss the task: what we need from your side and where to start
Let's show by example: a client's archive of tick data for Ethereum and spot pairs was stored as a single array, so a query for a narrow interval scanned the entire volume. A monthly report took hours to assemble, and after a failure the data had to be re-uploaded from scratch.
We split the stream into "date + instrument" partitions, enabled columnar compression and precomputed aggregates — interval sampling now fits within seconds, the archive volume shrank severalfold, and node recovery takes minutes.
To come back with an estimate and work plan, a minimal set of inputs is enough for us:
- list of networks and instruments for which tick data is required;
- required history depth and granularity: individual trades or aggregates by interval;
- read scenarios — analytics, reports, exports, internal services via API;
- acceptable gaps and the reconciliation rule with the source;
- where we host it: your servers or cloud, what access is needed and what data policy applies;
- timeline constraints and regulatory storage requirements.
In response, we send an estimate and work plan: partition and key schema, the procedure for initial loading and appending the stream, the procedure for migrating from the current storage without stopping collection, and acceptance criteria. Describe the task — we'll come back with an estimate and a plan.