Hacker News Reader

PolarizedPoutinon Ask HN: Share your personal website

I recently redesigned my personal website to mimic Earth’s layers and also started blogging. Thanks for putting this together, I love browsing through unique personal websites!

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

Thank you for reading and for the links!

I'm trying out Clickhouse for the next post. Definitely excited for sub 24 hour data loading!

I haven't heard of VictoriaMetrics but that's some impressive performance. Will check it out!

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

The data is publicly available!

The data is freely available from the Climate Change Service [1] which has a nice API but download speeds can be a bit slow. You'll have to sign up for this.

NCAR's Research Data Archive [2] provides some of the data (as pre-generated NetCDF files) but at higher download speeds. No signup necessary.

It's not super well documented but I hosted the Python scripts I used to download the data on the accompanying GitHub repository [3].

[1]: https://cds.climate.copernicus.eu/cdsapp#!/dataset/reanalysi...

[2]: https://rda.ucar.edu/datasets/ds633-0/

[3]: https://github.com/ali-ramadhan/timescaledb-insert-benchmark...

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

What do you mean by the heat distribution of energy? Do you mean like how much heat is received from the sun at a particular location and particular time? If so, then temperature is only the result of this plus other factors. Factors like cloud cover influence how much radiation is received at the surface.

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

GraphCast was trained on this exact same data!

From https://deepmind.google/discover/blog/graphcast-ai-model-for...

> Crucially, GraphCast and traditional approaches go hand-in-hand: we trained GraphCast on four decades of weather reanalysis data, from the ECMWF’s ERA5 dataset.

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

The full dataset is quite huge (~9 petabytes and growing) out of which I'm using just ~8 terabytes.

The data is freely available from the Climate Change Service [1] which has a nice API but download speeds can be a bit slow.

NCAR's Research Data Archive [2] provides some of the data (as pre-generated NetCDF files) but at higher download speeds.

It's not super well documented but I hosted the Python scripts I used to download the data on the accompanying GitHub repository [3].

[1]: https://cds.climate.copernicus.eu/cdsapp#!/dataset/reanalysi...

[2]: https://rda.ucar.edu/datasets/ds633-0/

[3]: https://github.com/ali-ramadhan/timescaledb-insert-benchmark...

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

The full dataset is quite huge (~9 petabytes and growing) out of which I'm using just ~8 terabytes. Still quite big to upload.

The data is freely available from the Climate Change Service [1] which has a nice API but download speeds can be a bit slow.

NCAR's Research Data Archive [2] provides some of the data (as pre-generated NetCDF files) but at higher download speeds.

It's not super well documented but I hosted the Python scripts I used to download the data on the accompanying GitHub repository [3].

[1]: https://cds.climate.copernicus.eu/cdsapp#!/dataset/reanalysi...

[2]: https://rda.ucar.edu/datasets/ds633-0/

[3]: https://github.com/ali-ramadhan/timescaledb-insert-benchmark...

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

Yeah I was thinking about this and hoped that Postgres had a `float2` data type but `int2` would have to work. I could scale the numbers to fit them into 2 bytes with minimal loss of precision, but decided I'd rather take the storage space hit since TimescaleDB promises good compression. Still haven't measured this though haha.

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

It's a good question! It's true that the output is massive, I believe ~9 petabytes and growing. But running the model is super expensive. It runs on ECMWF's supercomputer. Not sure how many cores but I would guesstimate in the 10k - 100k core range just for one instance. So computing the data on the fly is super expensive, probably much more expensive than just storing it.

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

I'm hoping to compare TimescaleDB and Clickhouse to see how big the difference is for different queries! My impression is that TimescaleDB gives you some columnar features, but maybe Clickhouse is a true columnar database.

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

Had a read through parts 1 and 2, thank you for the engaging reads! Love how you've formatted your posts with the margin notes too. Thank you for providing the function to write numpy structured arrays to Postgres binary, I couldn't figure this out before.

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

Thank you for reading through and for your feedback! Excited to try your settings to disable the WAL and other overhead and see if I get even faster inserts.

Also glad to hear an expert say that WAL data isn't really necessary for bulk loading, especially with chunks. I should get through the ~20 days it takes to load data without a power outage haha (no UPS yet :() but it sounds like even in the worst case I can just resume.

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

Hoping to find out when I compare TimescaleDB vs. Clickhouse!

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

Haha simplistic but probably faster and more space-efficient than a relational database. Sounds like rabernat and open-meteo who commented here do something similar to you and find it fast as well!

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

Thank you for mentioning paleoclimatology! Not sure if what I'm doing with ERA5 data is that rigorous haha, but one of my favorite plots is https://commons.wikimedia.org/wiki/File:All_palaeotemps.svg (still have it hung up in my office haha)

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

The data is definitely sparse in the earlier decades, especially over unpopulated areas and in developing nations, due to the lack of regularly recorded weather observations (sensors, balloons, etc.). Especially since weather satellites started collecting global data, coverage has been better though. And weather observing stations have proliferated since too.

Totally agree that it would be cool to see when and where the weather observations are that are informing the climate model. A quick search didn't turn up this dataset, but it may be somewhere. I'll see if I can find this data.

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

Yes you are correct of course. ERA5 is climate model output constrained to match weather observations, not actual observations, which I do note in the post.

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

Are you suggesting compressing the data using like Fourier series or wavelets? I know some of the variables have sharp gradients, especially variables like precipitation, which probably wouldn't compress super well. And some applications of the ERA5 dataset, e.g. closing heat or moisture budgets by accounting for every bit of it, cannot be done if the data has been compressed since you lose some data. Curious if this is what you had in mind or something else.

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

Thank you! I've relied on that USGS Projects manual multiple times haha. Working with satellite data some of it was in somewhat obscure projections and the manual always told you how to convert back to latitude-longitude.

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

Thanks for the link on the trillion row challenge, interesting read! I'm looking at queries and indexes next and I'm hoping to include Clickhouse in that comparison.

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

Thank you for the link! I've mostly used TablePlus (not free I think) and matplotlib (via psycopg3) to plot data returned by queries, but this looks like it'll be faster to use. I've only inserted data so far haha but will be querying and plotting soon.

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

Haha thank you for reading and glad you found it engaging! Maybe it's the benefit of being a beginner and not having any skin in the game. I did a lot of searching but couldn't find any conclusive answers for my use case so figured I may as well run the benchmarks myself.

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

Thanks for pointing out the status of ERA5 on GCP and AWS.

I'll have to try the binning and compare it with listing all the values and finding the 99th percentile! But yeah the spatial part of that query might be the tougher part.

And yeah I also considered whether to use ERA5 or ERA5-Land. I don't remember if ERA5-Land was missing a variable or I just wanted to look at some ocean grid cells, but I decided to start with vanilla ERA5.

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

Hey Ryan and thank you for the feedback!

I agree that storing the data is appropriately chunked Zarr files is almost surely going to be faster, simpler to set up, and take up less space. Could even put up an API in front of it to get "queries".

I also agree that I haven't motivated the RDBMS approach much. This is mainly because I took this approach with Postgres + Timescale since I wanted to learn to work with them, and playing around with ERA5 data seemed like the most fun way. Maybe it's the allure of weather data being big enough to pose a challenge here.

I don't have anything to back this up but I wonder if the RDBMS approach, with properly tuned and indexed TimescaleDB + PostGIS (non-trivial to set up), can speed up complex spatio-temporal queries, e.g. computing the 99th percentile of summer temperatures in Chile from 1940-1980, in case many different Zarr chunks have to be read to find this data. I like the idea of setting up different tables to cache these kinds of statistics, but it's not that hard to do with Zarr either.

I'm benchmarking queries and indexes next so I might know more then!

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

The main reason why was that it's a personal project and I wanted to do everything on my home server so that I wouldn't have to pay for cloud resources, and so that I could learn Postgres, TimescaleDB, and eventuallly PostGIS.

But as rabernat pointed out in his comment, pulling out a long time series from the cloud replica is also slow. And I know I eventually want to perform complex spatio-temporal queries, e.g. computing the 99% percentile of summer temperatures in Chile from 1940-1980.

I don't doubt that a cloud replica can be faster, but it's at odds with my budget of $0 haha.

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

Thank you for reading and for your kind words!

Ah I did not know about the `create_default_indexes=>false` and that a time index is created by default for hypertables. I'll add a note to explain this! Also curious to benchmark inserting without the time index then creating it manually.

Even with 32 workers I think the HDD was fine. I did monitor disk usage through btop and the SSD that Postgres lived on seemed to be more of a bottleneck than the HDD. So my conclusion was that a faster SSD for Postgres would be a better investment than moving the data from HDD to SSD.

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

Wish I saw this before I started haha! I left a footnote about why I didn't try binary copy (basically someone else found its performance disappointing) but it sounds like I should give it a try.

footnote: https://aliramadhan.me/2024/03/31/trillion-rows.html#fn:copy...

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

Thank you for reading through it thoroughly and pointing this out! I'm still new and learning Postgres so this is good to know. I will update the post.

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

Thank you for the insights! Yeah I'm still not sure how Postgres/PostGIS will scale for me, but good to know that BigQuery does this nicely.

This is not something I'm productionizing (at least not yet?) and I'm giving myself zero budget since it's a side project, but if/when I do I'll definitely look into BigQuery!

PolarizedPoutinon Loading a trillion rows of weather data into TimescaleDB

Thank you! Yeah haha some of the benchmarks took several hours (and a few re-runs) and there was a lot of learning done along the way.