Database or Parquet Store?
Postgres, TimescaleDB, ClickHouse or plain Parquet files for market data? We benchmark all four on 4.8M BTC/USDT candles: write time, read time and storage, and when relations are worth the cost.
When I was working in asset management, my colleague didn't use a database: he kept all his data in CSV files. I didn't understand why he did that. I thought it wasn't good, that it didn't scale, etc. Today, I'm building my own crypto data provider, and guess what: I also keep my data in files, Parquet files... I need to apologize to my colleague.
Why a database is nice
First, let's see why a database can be a very nice thing. With Postgres or others, you can make links between tables. For example, here is how the tables of my database look:
As you can see, each row of the OHLCV table holds an id for its exchange, its interval, its assets, and so on, instead of repeating their names. The database guarantees these links are valid.
But the downside of a database is that it's harder to set up than Parquet files: you have to think about the architecture of all the tables beforehand.
With Parquet files, there are no links between elements, no tables, no schema to design upfront. It seems simpler, but if you aren't organized, your file store quickly becomes a real mess.
Performance
Now let's look at the performance. We'll compare two relational databases (Postgres and TimescaleDB), ClickHouse, and Parquet files. ClickHouse is a database too, but without relations between tables, and like Parquet it stores the data by columns.
The data is the full Binance BTC/USDT history, from August 2017 to October 2026, in daily, hourly and minute candles: up to 4.8 million rows.
Write time
Read time
The write time of the relational databases is much bigger than for Parquet files (and ClickHouse), and the gap grows with the amount of data: writing the 4.8M minute candles takes 21 s in Postgres against 1.2 s as Parquet. Same for the read time: reading the full history takes 3.4 s from Postgres, 70 ms from Parquet. So Parquet files (and ClickHouse) are much faster to read and write than relational databases.
Storage
What about the storage?
Same observation: Postgres takes up to 5× more space than Parquet files and ClickHouse, while TimescaleDB with compression gets much closer. So if you need to store a lot of data, you'll want Parquet or ClickHouse.
Parquet files can also live almost anywhere, including object storage like S3 or Cloudflare R2, while a relational database needs to sit on the SSD or a volume of your server. The downside of ClickHouse is that you need a server running it, while Parquet is just files.
Side by side
| TimescaleDB | Postgres | ClickHouse | Parquet files | |
|---|---|---|---|---|
| Links between tables | Same as Postgres | Foreign keys, joins, constraints | Joins, but no foreign keys | None, you organize the folders yourself |
| Setup | Same as Postgres | Design all the tables first | Design all the tables first | Just write a file |
| Change the structure | Migrations | Migrations | Migrations | Rewrite the files |
| Add a few new rows | Fast | Fast | Slow, prefers big batches | Rewrite the whole file |
| Write full history (BTC/USDT 1m, 4.8M rows) | 52 s | 21 s | 1.9 s | 1.2 s |
| Read full history | 3.4 s | 3.4 s | 0.9 s | 70 ms |
| Storage | 223 MB | 754 MB | 189 MB | 139 MB |
| Where it lives | Server + disk | Server + disk | Server + disk or S3 | Anywhere, no server: disk, object storage |
Conclusion
A relational database is great if you need to make links between your data. The cost is a harder first implementation, and poor performance compared to Parquet files and ClickHouse.
If you don't need links between your tables, if it's just simple data, or if you have to reorganize your data again and again, Parquet files or ClickHouse will serve you better. Be careful to stay organized anyway, or you'll end up with tons of files and a new reader and writer for every new project...
At Angolin, we went hybrid: a database for hot data, so we can use websockets and stay fast and reactive, then we archive the data in Parquet files for performance.
Angolin serves the full BTC history at 1d, 1h and 1m granularity, and the historical universe of each exchange, delisted pairs included, to avoid survivorship bias.
