Loading a trillion rows of weather data into TimescaleDB

I went through a similar phase with a process that started with global OSM and Whosonfirst to process a pipeline. Google costs kept going up (7k a month with airflow + bigquery) and I was able to replace it with a one time $7k hardware purchase. We were able to do it since the process was using H3 indices early on and the resulting intermediate datasets all fit on ram.

System is a Mac Studio with 128GB + Asahi Linux + mmapped parquet files and DuckDB, it also runs airflow for us and with Nix can be used to accelerate developer builds and run the airflow tasks for the data team.

GCP is nice when it is free/cheap but they keep tabs on what you are doing and may surprise you at any point in time with ever higher bills without higher usage.

M6a is not even remotely enormous. Also were you using EBS?

Do you mind linking the specific dataset? I agree very wide columns break a lot of tools but other columnar postgres forks should support this no problem. It sounds like you didn't use Redshift, which I find surprising as it directly competes with BQ. Redshift has "super" columns that can be very large, even larger than the maximum supported by BigQuery.

I constantly see folks finding out the hard way that PostGIS is really hard to beat. The fact that Trini/Presto and Spark have languished here is particularly telling.

I’m glad to hear this first hand experience. I’m pretty sure that want to build and refine my own geospatial data horde.

I wonder if you think a longterm project is better rolling their own. The second priority is that I expect it all to be locally hosted.

Thanks for any considerations.

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!

Even more fun I bet would be if you're getting data with different reference spheroids.

Spark doesn't even have geospatial data types and the open source packages that add support leave a lot to be desired.

Could you say more about this? I'm curious if you've compared Apache Sedona [0] and what specifically you found lacking? I currently work at Wherobots [1], founded by the creators of Apache Sedona and would love to hear any feedback.

[0] https://sedona.apache.org/latest/

[1] https://wherobots.com/

We just ported our ~500gb data warehouse from an 8 core postgres rds instance to bigquery. Rebuilds went from 5 hours to 11 minutes and costs are about the same or lower. and that’s with caching of some very big tables in postgres and rebuilding from scratch in bq.

I much prefer postgres as a tool I can run myself but the order of magnitude performance benefits are hard to argue with.

I hear so much good things about BigTable / BigQuery, it's a shame I've had no opportunity to use it yet.

Our experience is that Clickhouse and Timescale are designed for different workloads, and that Timescale is optimized for many of the time-series workloads people use in production:

- https://www.timescale.com/blog/what-is-clickhouse-how-does-i...

Sidenote: Timescale _does_ provide columnar storage. I don't believe that the blog author focused on this as part of insert benchmarks:

- Timescale columnar storage: https://www.timescale.com/blog/building-columnar-compression...

- Timescale query vectorization: https://www.timescale.com/blog/teaching-postgres-new-tricks-...

Agreed, clickhouse is faster and has better features for this

Haha very cool use! Yeah reading up on TimescaleDB vs. Clickhouse it seems like columnar storage and Clickhouse will be faster and better compress the time series data. For now I'm sticking to TimescaleDB to learn Postgres and PostGIS, but might make a TimescaleDB vs. Clickhouse comparison when I switch!

Do you say that because its quicker to insert large batches of rows into Clickhouse, or because it's better in other ways?

(I'm currently inserting large batches of rows into MySQL and curious about Clickhouse...)

So click house is a column db. Any thoughts on if the performance would be a wash if you just pivoted the timescale hypertable and indexed the time + column on timescale?

Quick note to observe that all of the above while true becomes less of a practical issue as CPUs become faster vs i/o, which they have done and probably will keep doing.

Can it actually reuse the query plan? Couldn't that lead to a worse performing plan eventually? Say after inserting lots of data such that a different plan becomes a better option.

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.

I disagree that the security is a side benefit. It’s why most people choose to use parameterized queries.

True, but in fact, the Google ERA5 public data suffers from the exact chunking problem described in the post: it's optimized for spatial queries, not timeseries queries. I just ran a benchmark, and it took me 20 minutes to pull a timeseries of a single variable at a single point!

This highlights the needs for timeseries-optimized chunking if that is your anticipated usage pattern.

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.

That might be nice if someone would do it and teach others to use it. Some labs have an RDBMS-based pipeline with published algorithms and data that nobody wants to try to reimplement (and which nobody would be paid to do). About the best improvement we could get was moving from an ancient version of MySQL to Postgres + PostGIS. I think Timescale would have helped. There were other reasons also to run locally due to privacy, cluster access, funds etc.

Ok? And what’s your point in pointing out that?

ERA5 covers 1940 to present. That's well before the satellite era (and the earlier data absolutely has more quality issues) but there's nothing from 170 years ago.

Hypertable insert rates are faster and more predictable over time. Each individual insert might incur a small bit of extra overhead, but they scale forever since each temporal chunk is indexed separately vs a regular table where the entire index needs to fit in memory. This is a case where you can't make meaningful inferences from micro-benchmarks (they tested 20k rows, you probably need 200M to start seeing the diff)

Isn't data inserted into basically a normal Postgres table with hypertable extensions? I don't know the details of Timescale but that sounds like it would incur a cost of a normal Postgres insert, plus potentially extra work at insert time, plus extra work in the background to manage the hypertable.

Yeah I'm curious about this too. Been meaning to ask on the Timescale forums. My only guess is that there's some small extra overhead due to hypertable chunking.

I know Timescale has a blog post from 2017 claiming a 20x higher insert rate but that's for inserting into a table with an index. The general wisdom for loading huge amounts of data seems to be that you should insert into a table with no indexes then build them later though. So with no index, inserting into a hypertable seems a bit slower.

Timescale blog post: https://medium.com/timescale/timescaledb-vs-6a696248104e

Yes, this article is actually loading (order of magnitude) 10^12 rows.

I was very surprised to learn about this the first time. In USA English:

10^3 -> thousand

10^6 -> million

10^9 -> billion

10^12 -> trillion

10^15 -> quadrillion

10^18 -> quintillion

But there's another scale too! See this Wiki article for more

https://en.wikipedia.org/wiki/Names_of_large_numbers

The downvoters stand out as those who don't know. Thank you, amis!

This is a bit off-topic but I'm interested in the same space you are in.

There seems to be an inherent pull between large chunks (great for visualising large extents and larger queries) vs smaller chunks for point-based or timeseries queries. It's possible but not very cost-effective to maintain separately-chunked versions of these large geospatial datasets. I have heard of "kerchunk" being used to try and get the best of both, but then I _think_ you lose out on the option of compressing the data and it introduces quite a lot of complexity.

What are your thoughts on how to strike that balance between use cases?

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!

1. Yeah to me it seems very hard to saturate I/O with Postgres unless maybe you insert into an unlogged table. I guess there's quite a bit of overhead to get all the nice stability/consistency and crash-resistance.

2. That is a good point. I'm hoping TimescaleDB's compression helps here but yeah I don't think you'll ever get the database size below the data's original footprint.

Fair point, also worth noting is:

    The data is output from a climate model run that is constrained to match weather observations.

    So where we have lots of weather observations, ERA5 should match it closely.

    And where we do not have any weather observations, ERA5 will be physically consistent and should match the climatology, i.e. the simulated weather’s statistics should match reality.

    ERA5 covers the entire globe at 0.25 degree resolution, and stretches back in time to 1940 with hourly resolution. 

What I'd like to see in such tools are two things:

* Indications of actual observed data points (location time) .. so that the observed point mesh for a day in 1949 about the globe can be seen, and

* Interpolation for a modern day (with dense observed data points) from a reduced set of sparse points (just the ones, say, from that day in 1949) to get a sense of how the infill from sparse points matches the observed but unused points.

I'm not a climate skeptic in the least, global estimation of a coarse global mean from relatively sparse points is fine in my experience (I've done a lot of exploration geophysics for mineral | energy resource mapping) and a bit of interpolated wiggle in the model doesn't blow the big corase estimation .. but this question of interpolation from sparse data is one that always gets raised by skeptics.

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.

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...

Somewhat relevant (which I posted a while back):

https://climate.metoffice.cloud/

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)

Do you have more info on the GIS improvements in PG15?

What's the process for adding support for other databases to your tool qStudio?

I'm thinking perhaps you could add support for Timeplus [1]? Timeplus is a streaming-first database built on ClickHouse. The core DB engine Timeplus Proton is open source [2].

It seems that qStudio is open source [3] and written in Java and will need a JDBC driver to add support for a new RDBMS? If yes, Timeplus Proton has an open source JDBC driver [4] based on ClickHouse's driver but with modifications added for streaming use cases.

1: https://www.timeplus.com/

2: https://github.com/timeplus-io/proton

3: https://github.com/timeseries/qstudio

4: https://github.com/timeplus-io/proton-java-driver

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.

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.

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.

Hi, we updated our docs with the best options.

https://docs.timescale.com/self-hosted/latest/install/instal...

I'd personally recommend StackGres, it's a great project.

If I read correctly, they are using the ERA5 dataset [0]. It is freely available and can be downloaded through a Python API called cdsapi.

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

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

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!

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.

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.

When I see suggestions like this (totally reasonable hack!), I do have to wonder what happened to JDBC’s “addBatch()/executeBatch()”, introduced over 25 years ago.

https://docs.oracle.com/javase/8/docs/api/java/sql/PreparedS...

Did modern APIs & protocols simply fail to carry this evolutionary strand forward?

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.

Not likely, unless it's a set of parquet files already. Clickhouse would be a better bet.

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.