Loading a trillion rows of weather data into TimescaleDB

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.