The post discusses the process of building a weather data warehouse using PostgreSQL and TimescaleDB. It explores the need for historical weather data, the ERA5 climate reanalysis product, and the challenges of loading large amounts of data into a database. The post compares different insertion methods, including single-row inserts, multi-valued inserts, and the copy statement. It also evaluates the performance of external tools like pg_bulkload and timescaledb-parallel-copy. The conclusion suggests using psycopg3 to directly copy data into a hypertable or using timescaledb-parallel-copy if CSV files are available. The post provides detailed benchmarks and considerations for achieving optimal insert rates and loading times.
Table of contents
What are we even doing?The insert statementThe copy statementToolsTweaking Postgres settingsSo what’s the best method?AppendicesFootnotes28 Impressions