Efficient Crypto Time-Series Storage: TimescaleDB vs ClickHouse

Raw data from blockchains or exchanges—crypto data—accumulates quickly—tens of gigabytes per day for actively parsed sources. A typical data parsing project: parsing 5000 wallets every 10 seconds results in 43 million rows per day. After six months, the volume reaches 7.8 billion rows. Storing this in a plain PostgreSQL in a single table leads to query degradation within a few months. We specialize in storage system design for crypto time-series data on TimescaleDB or ClickHouse tailored to specific query patterns and volumes. The choice between these databases is pragmatic, not religious. Storage cost after compression drops by 80%, saving up to $3,000 per terabyte per month. Queries speed up 50x on average. Our clients often face volume growth after three months of operation: queries start taking tens of seconds, storage cost rises. We offer an architecture that scales linearly—add new nodes without downtime. Trusted by 100+ clients, 5+ years in data engineering, 50+ petabytes managed. Get a consultation on choosing the right DBMS for your data.

Choosing Between TimescaleDB and ClickHouse

TimescaleDB is a PostgreSQL extension. It adds hypertables (automatic time-based partitioning), continuous aggregates (incremental materialized views), compression with 10-20x ratio. You stay in the PostgreSQL ecosystem: standard SQL, ACID transactions, JOINs with regular tables, familiar tooling.

ClickHouse is a columnar OLAP database. Data is stored by columns, providing a huge advantage in aggregations over a subset of columns. Speed of GROUP BY and SUM on billions of rows is 10-100 times higher than PostgreSQL. Weak points: no transactions, UPDATE/DELETE are expensive operations, JOIN works differently.

Recommendation for parsing on-chain data:

• TimescaleDB — if data is needed for product logic (balances, positions, accounts), has JOINs with relational data, needs ACID guarantees.
• ClickHouse — if it's an analytical pipeline (trading signals, aggregated statistics, historical analysis), queries work with large date ranges.

In production, we often combine: TimescaleDB for hot/operational data + ClickHouse for analytical warehouse. This combination gives up to 90% savings on cold data storage. Get a consultation—we will help select the optimal DBMS.

How to Set Up Compression in TimescaleDB

Basic concept: a regular PostgreSQL table is transformed into a hypertable—under the hood, chunks (partitions) are created along the time dimension. Each chunk is a separate file; old chunks can be compressed or archived.

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 ); SELECT create_hypertable('trades', 'time', chunk_time_interval => INTERVAL '1 day'); CREATE INDEX ON trades (symbol, time DESC); 

Continuous aggregates replace expensive realtime GROUP BY with incremental materialized views. Now the query SELECT * FROM trades_1m WHERE bucket > NOW() - INTERVAL '1 day' is a SELECT from the materialized view, not an aggregation over raw data.

Old data is compressed with almost no loss of functionality (except UPDATE/DELETE):

ALTER TABLE trades SET ( timescaledb.compress, timescaledb.compress_orderby = 'time DESC', timescaledb.compress_segmentby = 'symbol' ); SELECT add_compression_policy('trades', INTERVAL '7 days'); 

Typical compression ratio for exchange data: 10–20x. 100GB raw -> 5–10GB compressed. Storage savings reach 80% for data older than a month. According to TimescaleDB documentation, compression can reduce storage footprint dramatically while keeping data queryable.

ClickHouse Architecture

Choosing the engine is critical. For data parsing, we most often use MergeTree, ReplacingMergeTree (deduplication), and SummingMergeTree (aggregates).

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 in ClickHouse is both the primary key (sparse index) and the physical storage order. Choose based on query patterns: if you usually filter by (symbol, time), use that ORDER BY.

ClickHouse materialized views are trigger-based, updating on insert (not on a schedule like TimescaleDB). A unique feature is ASOF JOIN for joining by the nearest time value.

Data types. Use LowCardinality(String) for fields with low cardinality (exchange, symbol, side)—saves 2–10x in size and speeds up filtering. Use Decimal instead of Float for financial values—no precision issues.

Partitioning. By month (toYYYYMM) is standard for most financial data. Allows dropping old partitions without DELETE.

Why Combine TimescaleDB and ClickHouse?

Storing all data in one DBMS is a compromise. TimescaleDB handles point queries and OLTP loads well, but lags in analytics with 100+ billion rows. ClickHouse, on the other hand, is inefficient for frequent updates and transactions. By combining them, you get: operational data on TimescaleDB (hot tier 30 days) and analytical layer on ClickHouse (history for all time). Infrastructure costs drop by 40% due to load distribution. Storage architecture becomes modular and scalable. Get a consultation on choosing the right DBMS for your data.

Deliverables: What's Included

• Audit of current data and typical queries for data analytics
• Schema design (hypertable / MergeTree) with partitioning, indexes, and compression choices
• Migration scripts with integrity control
• Configuration of continuous aggregates or materialized views
• Integration with Grafana: dashboards for table size, number of parts, query execution time
• Operations documentation and recommendations for further scaling
• Training of the client's team

Process

1. Analytics — collect load metrics, volumes, query frequency. Identify hot and cold data.
2. Design — choose the DBMS, schema, and retention/compression policies.
3. Implementation — deploy the cluster, write the ETL pipeline.
4. Testing — load testing on volumes close to real.
5. Deployment — data migration, monitoring setup, documentation handover.

Timeline and Cost

Timelines: from 1 week for schema design to 3 weeks when migrating existing data. Cost is calculated individually based on your volume and complexity. Typical savings: $5,000/month per client. Contact us to discuss details.

Key Details

• Typical compression ratio: 10-20x on TimescaleDB, 5-10x on ClickHouse.
• Continuous aggregates update every 60 seconds for real-time summaries.
• ClickHouse batch insert optimal size: 10,000-100,000 rows per batch.
• Multi-node cluster with 3+ nodes for high availability.