Loading a trillion rows of weather data into TimescaleDB

Original Article ↗ Hacker News Discussion ↗

Loading nearly a trillion rows of ERA5 weather data into TimescaleDB highlights both how far PostgreSQL-based time‑series databases can be pushed and where they struggle. Commenters dig into bulk‑ingest techniques, WAL and indexing trade‑offs, and Timescale’s compression, while repeatedly contrasting it with columnar systems like ClickHouse, BigQuery, and custom Zarr/HDF formats that may offer faster queries and better compression for large, mostly append‑only climate datasets. A recurring theme is that the “best” storage depends less on raw scale than on access patterns—operational time‑series APIs versus ad‑hoc geospatial and climatological analysis.

Hypertables, indexing, and bulk ingest performance

  • Several commenters question why Timescale hypertables ingest slower than plain tables.
  • Explanation: hypertables add overhead (chunk management, per-chunk index) and automatically create a timestamp index; the baseline table had no index.
  • Suggested fix: disable default indexes during bulk load and create them afterward.
  • Hypertables are argued to shine “over time” at large scale, not in micro‑benchmarks.

COPY, binary formats, WAL, and tuning

  • COPY is seen as CPU‑bound and hard to make truly I/O‑limited.
  • Some report modest gains from COPY BINARY, others large gains when writing binary directly from in‑memory structures. Results appear workload‑dependent.
  • Advanced tuning suggestions: temporarily disable WAL and related safeguards, autovacuum, and reduce checkpoints for one‑shot bulk loads, accepting crash‑recovery risk.

Row overhead, compression, and storage trade‑offs

  • Concern about significant per‑row overhead vs raw data size.
  • Timescale compression (segmenting by key, columnar storage, TOAST) is claimed to reach up to ~20x reduction in good cases by amortizing tuple overhead.
  • Some discuss narrowing types (e.g., storing scaled values as int2) but the author chose to rely on compression.

Is a relational database appropriate for ERA5?

  • Skepticism: ERA5 is a regular spatiotemporal grid; flattening to rows “destroys” structure and may be inefficient.
  • Alternative recommended: cloud‑optimized Zarr/NetCDF with time‑ or use‑case‑aware chunking, achieving sub‑second timeseries queries without heavy ingestion.
  • Counterpoint: public ERA5 replicas are often chunked for spatial access, yielding very slow time‑series extraction; custom time‑series chunking (including non‑RDBMS formats like custom HDF or proprietary layouts) can be extremely fast.
  • The author’s main motivation is learning Postgres/Timescale/PostGIS and staying within a $0 on‑prem budget.

Alternative databases and columnar systems

  • Multiple participants argue columnar engines (ClickHouse, BigQuery, DuckDB, VictoriaMetrics, others) can ingest and query trillions of time‑series rows far faster and with better compression than Postgres/Timescale, often with little tuning.
  • Benchmarks are cited where ClickHouse and VictoriaMetrics ingest hundreds of billions of samples in hours; some predict sub‑day loads for this workload.
  • Timescale representatives position it as an “operational” time‑series store (continuous streaming, recent‑data focus) vs warehouse‑style systems optimized for full‑dataset scans.

Cloud warehouses, costs, and on‑prem vs cloud

  • BigQuery is praised as exceptionally strong for large‑scale geospatial and weather analytics, often turning multi‑hour PostGIS jobs into seconds.
  • Serious concern about cost foot‑guns: frequent query refreshes and unpartitioned tables can drive big, surprising bills.
  • Some report successfully replacing expensive cloud stacks (e.g., BigQuery + Airflow) with one‑time hardware plus DuckDB/Parquet, especially when data fits in local NVMe/RAM.
  • Others stress that for “data hoarding” with infrequent queries, cheap object storage plus pay‑per‑query engines is attractive.

Geospatial and ERA5 data nuances

  • Thread emphasizes coordinate reference systems, projections, and geometry size limits as real practical problems.
  • ERA5 is a reanalysis product: model output constrained by observations, not pure observations. Users are urged to understand provenance, especially in earlier decades with sparse measurements.
  • Desire expressed for better metadata on where/when actual observations exist and how interpolation from sparse networks performs.

Security, parameterized queries, and query plans

  • Debate over why to use parameterized queries: some emphasize security (SQL injection), others performance (avoid text encoding, reuse plans).
  • Discussion notes that plan reuse is typically safe over short time windows; engines can replan periodically if data distribution changes.

Data access and tooling

  • ERA5 data sources and scripts to download subsets are shared; full dataset is ~petabytes, so most work with small (TB‑scale) slices.
  • Various tools are mentioned or recommended: PostGIS, qStudio for visualization, Open‑Meteo (ERA5‑backed API with custom time‑series‑optimized storage), and Xarray/Zarr for serverless workflows.