Memuat 1 Triliun Data Cuaca ke TimescaleDB

 (aliramadhan.me)
3 poin oleh GN⁺ 2024-04-17 | 2 komentar | Bagikan ke WhatsApp
  • Untuk menganalisis perubahan cuaca historis di seluruh dunia dengan cepat, data reanalisis ERA5 perlu dimasukkan ke PostgreSQL/TimescaleDB; berdasarkan variabel dan grid yang dipilih, skalanya sekitar 754 miliar baris
  • ERA5 menyediakan resolusi per jam dan grid lintang-bujur 0,25 derajat sejak 1940, tetapi dalam struktur NetCDF, mengekstrak deret waktu jangka panjang untuk satu titik saja bisa memakan waktu 20–30 menit
  • insert satu baris sekitar 3 ribu baris per detik, sehingga perlu sekitar 8 tahun untuk memuat seluruh data; insert multi-baris pun dengan psycopg3 mencapai 25 ribu–30 ribu baris per detik, atau sekitar 10 bulan
  • PostgreSQL copy dan psycopg3 cursor.copy() berbeda tergantung apakah CSV sudah disiapkan; termasuk overhead, kecepatannya sekitar 100 ribu baris per detik, dan bisa ditingkatkan lagi dengan paralelisasi serta penyesuaian konfigurasi
  • Saat memasukkan langsung dari dataframe, pilihan yang cocok adalah copy langsung ke hypertable dengan psycopg3; jika CSV sudah tersedia, timescaledb-parallel-copy cocok, dan pada sistem ini 12–16 worker tampak menjadi titik optimal untuk paralelisasi

Membangun data warehouse cuaca global

  • Tujuannya adalah membuat data warehouse yang dapat mengueri data cuaca historis global untuk menganalisis sinyal perubahan iklim yang sudah terjadi
  • Contoh analisis mencakup apakah Jakarta benar-benar menjadi lebih panas atau badai makin sering, apakah seluruh Chile menjadi lebih hangat atau lebih berawan, serta seperti apa bentuk perubahan di tiap wilayah
  • Untuk analisis global yang cepat, kueri data warehouse harus cepat, dan langkah pertama adalah memuat data dalam jumlah besar ke database
  • Basis yang digunakan adalah PostgreSQL; TimescaleDB adalah pilihan menjanjikan untuk mempercepat kueri berbasis waktu, sementara PostGIS menjanjikan untuk mempercepat kueri geospasial di masa depan

Data reanalisis ERA5 dan skala 754 miliar baris

  • Alih-alih observasi cuaca sebenarnya, digunakan data ERA5 climate reanalysis
    • Observasi historis bisa jarang tersedia untuk wilayah dan periode masa lalu tertentu
    • ERA5 adalah keluaran model iklim yang dibatasi agar sesuai dengan observasi, dan banyak digunakan dalam riset cuaca dan iklim
  • ERA5 mencakup seluruh Bumi dengan resolusi 0,25 derajat dan tersedia dengan resolusi per jam sejak 1940
    • Snapshot waktu berjumlah 727.080 per variabel
    • Titik grid berjumlah 1.038.240, terdiri dari 1.440 bujur dan 721 lintang termasuk kedua kutub
    • Jika diindeks berdasarkan waktu dan lokasi, jumlahnya menjadi 753.836.544.000 baris per variabel, atau sekitar 754 miliar baris
  • Variabel yang dimuat adalah suhu udara, kecepatan angin 10 m arah timur-barat dan utara-selatan, total tutupan awan, presipitasi, dan jumlah salju
  • Tabel memiliki kolom time, location_id, latitude, longitude, serta kolom untuk tiap variabel cuaca
    • Alasan menyertakan location_id bersama kolom lintang-bujur adalah untuk kueri dan benchmark indeks di kemudian hari

Titik lambat pada struktur file NetCDF

  • ERA5 didistribusikan sebagai file NetCDF, dan biasanya data berada dalam file harian, bulanan, atau tahunan
  • Struktur chunk berbasis waktu cepat dan sederhana untuk mengambil data pada waktu tertentu
  • Untuk melihat pola waktu seperti deret waktu jangka panjang di satu titik, banyak file harus dibaca sehingga menjadi lambat
    • Sebagai contoh, mengambil deret waktu suhu untuk satu lokasi memakan waktu 20–30 menit
  • Kueri geospasial kompleks, terutama yang mencakup sumbu waktu, lambat dan sulit dilakukan
  • Alat seperti xarray, dask, dan Pangeo dapat meningkatkan kecepatan, tetapi prosesnya tetap lambat

insert: dari satu baris hingga multi-baris

  • Cara paling sederhana adalah memasukkan satu baris demi satu baris dengan insert
  • insert satu baris membawa berbagai biaya
    • PostgreSQL mem-parse pernyataan, memvalidasi nama tabel dan kolom, serta membuat rencana eksekusi
    • Lock mungkin diperlukan untuk integritas data
    • Data dicatat ke buffer untuk WAL (write-ahead logging)
    • Data disisipkan ke area disk tabel yang sebenarnya
    • Saat transaksi di-commit, perubahan dibuat permanen
  • Tiga metode dibenchmark di Python untuk penyisipan satu baris
    • pandas df.to_sql() dengan chunksize=1
    • Kueri berparameter psycopg3
    • Kueri berparameter SQLAlchemy
  • Hasil penyisipan satu baris menunjukkan psycopg3 sedikit unggul dan SQLAlchemy paling lambat
    • Hypertable TimescaleDB sedikit lebih lambat daripada tabel PostgreSQL biasa
    • Performa terbaik sekitar 3 ribu baris per detik, yang berarti sekitar 8 tahun untuk memasukkan seluruh data
  • insert multi-baris memasukkan beberapa baris dalam satu pernyataan untuk mengurangi round trip jaringan, parsing, dan biaya perencanaan
    • psycopg3 paling cepat dengan 25 ribu–30 ribu baris per detik
    • pandas bisa lebih lambat karena struktur penyisipannya menggunakan dictionary, bukan tuple
    • SQLAlchemy bisa memiliki overhead tambahan seperti manajemen sesi dan abstraksi ekspresi SQL
    • Meski begitu, pemuatan penuh masih perlu sekitar 0,8 tahun, hampir 10 bulan

copy: jalur pemuatan massal PostgreSQL

  • PostgreSQL copy adalah fitur pemuatan massal yang membaca baris dari file CSV atau biner
  • Karena dioptimalkan untuk pemuatan massal dalam hal parsing, perencanaan, dan penggunaan WAL, ini lebih cepat daripada insert multi-baris
  • Dua jalur dibandingkan
    • Menyimpan data NetCDF sebagai CSV lalu memuatnya dengan copy
    • Streaming langsung ke PostgreSQL dengan psycopg3 cursor.copy() tanpa membuat file CSV
  • Jika file CSV sudah tersedia, copy menghasilkan kecepatan penyisipan hampir 400 ribu baris per detik
  • Jika overhead penulisan file CSV atau pembuatan tuple disertakan, baik copy maupun psycopg3 berada di sekitar 100 ribu baris per detik, dengan psycopg3 sedikit lebih cepat
  • Pada kecepatan ini pun, memuat seluruh data perlu sekitar 3 bulan

Laju pemuatan berkelanjutan dan copy paralel

  • Saat memasukkan banyak baris, bisa muncul bottleneck seperti penulisan disk, kompetisi I/O antara WAL dan penyisipan tabel, autovacuum, serta checkpoint
  • Dalam eksperimen yang memasukkan sekitar 772 juta baris dalam 744 batch, tidak terlihat penurunan kecepatan besar dengan satu worker
    • copy csv menunjukkan penurunan yang sering dan lebih rentan terhadap fluktuasi
    • psycopg3 umumnya lebih cepat
    • Perbedaan antara tabel biasa dan hypertable tidak besar
  • Beberapa pekerjaan copy atau cursor psycopg3 dijalankan paralel dengan joblib
  • Penyisipan ke satu tabel bukan pekerjaan yang mudah diparalelkan, dan performa umumnya mandek setelah 16 worker

pg_bulkload dan timescaledb-parallel-copy

  • Selain PostgreSQL copy, pg_bulkload dan timescaledb-parallel-copy dibenchmark
  • pg_bulkload tampak lebih cepat pada konfigurasi default, tetapi secara default ia melewati shared buffers dan melewati logging WAL, sehingga pemulihan data setelah crash bisa menjadi mustahil
  • Dalam kondisi yang sama dengan fsync dimatikan, timescaledb-parallel-copy yang menggunakan beberapa worker lebih baik daripada pg_bulkload
  • timescaledb-parallel-copy dapat melakukan penyisipan paralel dengan menentukan jumlah worker
    • Performa awalnya bagus, tetapi pada sistem ini bottleneck tercapai sebelum 100 juta baris, laju penyisipan turun tajam lalu pulih seperti gelombang
    • Laju pemuatan berkelanjutan sekitar 600 ribu–700 ribu baris per detik pada tabel biasa, dan sekitar 300 ribu baris per detik pada hypertable
  • pg_bulkload tidak memiliki penentuan jumlah worker, tetapi menggunakan beberapa thread untuk membaca, parsing, dan menulis melalui opsi writer=parallel

Penyesuaian konfigurasi PostgreSQL dan kompromi durabilitas

  • Performa tambahan dapat diperoleh dengan menyesuaikan konfigurasi non-durable PostgreSQL
  • Pengaturan utamanya adalah mematikan fsync untuk menghindari flush disk, dan mematikan full_page_writes untuk menghindari perlindungan terhadap partial page write
  • Pengaturan ini dapat membahayakan integritas database saat terjadi crash
  • Unlogged table tidak menghasilkan WAL sehingga penulisan lebih cepat, tetapi bisa dipotong saat pemulihan crash
    • Setelah itu harus dikonversi menjadi logged table biasa, dan proses ini lambat serta dapat berjalan single-threaded
    • Hypertable tidak bisa menjadi unlogged, jadi jika hypertable diperlukan, diperlukan konversi atau migrasi tambahan

Pilihan akhir: memasukkan langsung ke hypertable

  • Jika targetnya hypertable, memasukkan langsung ke hypertable lebih cepat daripada memasukkan ke tabel biasa lalu mengonversinya menjadi hypertable
  • Dalam tes sederhana yang memasukkan sekitar 772 juta baris dengan psycopg3 copy dan 16 worker, penyisipan langsung ke hypertable memakan waktu lebih sedikit daripada penyisipan ke tabel biasa lalu konversi
    • Dalam kasus ini, penyisipan langsung ke hypertable memakan sekitar 80% dari waktunya
    • Proses konversi dan migrasi tidak cepat dan tampak seperti single-threaded
  • Metode yang direkomendasikan adalah sebagai berikut
    • Jika memuat langsung dari dataframe, lakukan copy langsung ke hypertable dengan psycopg3
    • Jika file CSV sudah ada, gunakan timescaledb-parallel-copy
    • Pada sistem ini, 12–16 worker tampak menjadi titik optimal untuk paralelisasi

Kesimpulan benchmark keseluruhan dan waktu yang dibutuhkan

  • Dengan satu worker dan pengaturan proteksi aktif, batas atas laju pemuatan berkelanjutan termasuk overhead pada hardware ini tampak sekitar 140 ribu baris per detik
  • Dengan beberapa worker, laju pemuatan berkelanjutan dapat ditingkatkan hingga sekitar 250 ribu baris per detik sambil mempertahankan pengaturan proteksi menggunakan cursor psycopg3 copy
  • Proses penyisipan tidak memiliki efisiensi paralelisasi yang besar, dan 4–16 worker tampak sebagai rentang yang tepat
  • Jika bersedia mengambil risiko dan mematikan fsync, psycopg3 dapat mempertahankan sekitar 462 ribu baris per detik
  • pg_bulkload secara default menonaktifkan fsync, jadi perlu berhati-hati saat menggunakannya
  • Jika mempertahankan sekitar 462 ribu baris per detik, memuat sekitar 754 miliar baris akan memakan waktu sekitar 20 hari

Kode dan lingkungan benchmark

  • Kode untuk mengunduh ERA5, membuat tabel, melakukan insert dan copy, benchmark, serta membuat grafik tersedia di timescaledb-insert-benchmarks
  • Setiap benchmark menjalankan container Docker baru untuk menjaga lingkungan yang konsisten
    • Storage tidak dipertahankan antar-container Docker
    • File NetCDF dan CSV dibaca dari HDD
    • Database disimpan di NVMe SSD
  • Konfigurasi hardware
    • CPU: 2x 12-core Intel Xeon Silver 4214
    • RAM: 16x 16 GiB Samsung ECC DDR4 2666 MT/s
    • SSD: Intel 2 TB NVMe
    • HDD: Seagate Exos X16 14TB 7200 RPM
  • Konfigurasi software
    • Ubuntu 20.04, Linux kernel 5.15
    • PostgreSQL 15.5
    • TimescaleDB 2.13.0
    • pg_bulkload 3.1.20
  • Konfigurasi PostgreSQL menggunakan nilai yang direkomendasikan timescaledb-tune untuk memori 250,57GB dan 48 CPU, dan dalam benchmark ukuran WAL disesuaikan secara terpisah

Bacaan terkait

2 komentar

 
jangsc0000 2024-04-18

Komentar GN+ menggunakan bahasa formal, ya..?
 
GN⁺ 2024-04-17
Pendapat di Hacker News

  • Saya cukup banyak mengerjakan analisis geospasial untuk pekerjaan, dan data geospasial jauh lebih subtil daripada yang dibayangkan karena harus memahami sistem referensi koordinat (CRS) dan proyeksi visualisasi dengan tepat
    Kalau tidak ada infrastruktur yang membawa metadata dengan benar bersama datanya, CRS terus membuat waswas
    Saya sudah memakai fitur terkait di AWS, Postgres/PostGIS, Spark/Databricks, Snowflake, Trino, dan ArcGIS, tetapi untuk pekerjaan geospasial berskala besar, menurut saya Google BigQuery jauh lebih unggul
    Query yang di PostGIS pada EC2 m6a raksasa memakan waktu berjam-jam dan biaya besar, di tier gratis BigQuery selesai dalam kurang dari 5 detik
    Saya memakai data publik FEMA, dan layanan Snowflake serta AWS mentok karena kolom geometry melewati ukuran byte maksimum; Spark tidak punya tipe data geospasial, dan ekstensi open source-nya juga kurang memuaskan
    Kalau on-premise mungkin ceritanya berbeda, tetapi untuk 20TB biaya penyimpanan BigQuery kemungkinan besar di bawah $100 per bulan, jadi saya tidak terlalu ingin mengoperasikannya sendiri

    • Saya mengalami proses serupa saat menangani pipeline dengan OSM global dan Whosonfirst, dan biaya Google naik sampai $7k per bulan dengan Airflow + BigQuery, jadi saya menggantinya dengan sekali beli hardware senilai $7k
      Itu mungkin karena sejak awal saya memakai indeks H3 dan semua dataset antara muat ke memori
      Konfigurasinya 128GB Mac Studio + Asahi Linux + file parquet mmap + DuckDB; Airflow juga dijalankan, dan Nix dipakai untuk mempercepat build developer serta menjalankan pekerjaan Airflow tim data
      GCP bagus ketika gratis atau murah, tetapi pada suatu titik bisa mengejutkan dengan tagihan yang lebih besar meski pemakaian tidak bertambah
    • Saya ingin mendengar lebih lanjut soal Spark yang tidak punya tipe data geospasial dan paket open source-nya kurang memuaskan
      Penasaran apakah sudah dibandingkan dengan Apache Sedona, dan secara spesifik apa yang kurang
      Saya bekerja di Wherobots, yang didirikan oleh pembuat Apache Sedona, jadi ingin mendengar masukan
      https://sedona.apache.org/latest/
      https://wherobots.com/
    • Akan bagus kalau bisa menautkan dataset konkretnya
      Saya setuju bahwa kolom yang sangat lebar merusak banyak tool, tetapi turunan Postgres kolumnar lain sepertinya bisa mendukungnya tanpa masalah
      Saya agak heran sepertinya tidak memakai Redshift, yang bersaing langsung dengan BigQuery, dan kolom super Redshift bisa disetel lebih besar daripada batas maksimum BigQuery
      Saya sering melihat orang-orang menyadari dengan susah payah bahwa PostGIS sulit dikalahkan
      Mandeknya Trino/Presto dan Spark di area ini juga sangat memberi pelajaran
    • Saya memindahkan data warehouse sekitar 500GB dari Postgres RDS 8-core ke BigQuery; waktu rebuild turun dari 5 jam menjadi 11 menit, dan biayanya mirip atau lebih rendah
      Di Postgres, sebagian tabel besar di-cache, sedangkan di BigQuery dibuat ulang dari awal, tetapi hasilnya tetap begitu
      Untuk tool yang bisa dioperasikan sendiri, saya lebih suka Postgres, tetapi sulit membantah peningkatan performa lebih dari beberapa kali lipat
    • Saya benar-benar sering mendengar hal bagus tentang BigTable dan BigQuery, sayang belum punya kesempatan mencobanya

  • Tulisan yang sangat keren
    Saya menangani DevRel di Timescale, dan senang melihat komunitas membuat tulisan sebagus ini
    Salah satu alasan hypertable lebih lambat hampir pasti karena secara default ia membuat indeks pada kolom timestamp
    Tabel biasa tidak punya indeks, jadi mungkin lebih cepat
    Dengan create_hypertable, Anda bisa memakai create_default_indexes=>false untuk melewati pembuatan indeks, atau menghapus indeks sebelum memasukkan data
    Pada akhirnya indeks itu akan dibutuhkan, tetapi untuk pemuatan batch seperti ini lebih baik dibuat sekaligus setelah proses loading selesai
    Saya juga penasaran seberapa kuat HDD yang membaca data dalam konfigurasi dengan paralelisme tinggi

    • Saya tidak tahu soal create_default_indexes=>false dan bahwa hypertable secara default membuat indeks waktu, dan akan menambahkan catatan yang menjelaskan bagian ini
      Saya juga ingin mencoba benchmark: memasukkan data tanpa indeks waktu lalu membuat indeks secara manual
      Bahkan dengan 32 worker, HDD sepertinya baik-baik saja
      Saat melihat penggunaan disk dengan btop, SSD tempat Postgres berjalan tampak lebih mendekati bottleneck daripada HDD, jadi saya menyimpulkan investasi yang lebih baik adalah mengganti SSD untuk Postgres dengan yang lebih cepat, bukan memindahkan data dari HDD ke SSD

  • Saya tidak paham kenapa harus melakukan itu
    Sebagian besar dataset cuaca dan iklim, termasuk ERA5, sangat terstruktur di atas kisi lintang-bujur yang teratur
    Bahkan jika hanya mengambil deret waktu untuk lokasi tertentu, kekuatan dataset seperti ini ada pada struktur dan konteks ruang-waktu yang melekat, dan jika tujuannya bukan sekadar mengekstrak deret waktu titik, tidak terlalu masuk akal untuk merusak struktur itu sepenuhnya
    Bahkan dalam kasus hanya mengambil deret waktu titik pun, misalnya deret waktu suhu permukaan di tengah laut hampir tidak akan pernah dipakai, jadi orang mungkin ingin memangkas datanya dengan cukup agresif
    Untuk sebagian besar pemanfaatan riset dan operasional dataset seperti ERA5, tampaknya lebih cocok memakai replika yang dioptimalkan untuk cloud dan mempertahankan struktur asli, seperti ARCO-ERA5 di Google Public Datasets
    Versi seperti ini mempertahankan struktur asli sambil membagi chunk agar sesuai untuk akses paralel skala besar dari penyimpanan cloud
    Hampir di semua kasus yang pernah saya lihat sepanjang karier saya, arsip berbasis Zarr dengan chunking umum saja sudah cukup cepat untuk sebagian besar penggunaan yang layak diperhatikan
    https://cloud.google.com/storage/docs/public-datasets/era5

    • Alasan utamanya karena ini proyek pribadi, saya ingin mencoba semuanya di server rumah tanpa membayar biaya resource cloud, dan ingin belajar Postgres, TimescaleDB, lalu nantinya PostGIS
      Namun seperti yang dikatakan rabernat, mengambil deret waktu panjang dari replika cloud juga lambat
      Pada akhirnya saya juga ingin menjalankan query ruang-waktu yang kompleks, seperti menghitung persentil ke-99 suhu musim panas di Chile pada 1940–1980
      Saya tidak meragukan bahwa replika cloud bisa lebih cepat, tetapi itu bertabrakan dengan anggaran $0
    • Benar, tetapi data publik Google ERA5 sendiri sebenarnya mengalami masalah chunking yang persis sama seperti yang dijelaskan dalam tulisan
      Dioptimalkan untuk query spasial, bukan untuk query deret waktu
      Saya baru saja menjalankan benchmark, dan mengambil deret waktu satu variabel di satu titik memakan waktu 20 menit
      Ini menunjukkan dengan baik bahwa jika pola penggunaan yang diharapkan adalah deret waktu, maka chunking yang dioptimalkan untuk deret waktu memang diperlukan
    • Akan bagus kalau ada yang melakukannya dan juga mengajari orang lain cara memakainya
      Beberapa laboratorium sudah punya pipeline berbasis RDBMS yang menautkan algoritme dan data yang sudah dipublikasikan, tidak ada yang mau mengimplementasikannya ulang, dan tidak ada biaya untuk melakukan pekerjaan itu
      Perbaikan terbaik yang kami dapatkan hanyalah pindah dari MySQL lama ke Postgres + PostGIS
      Timescale mungkin juga akan membantu
      Ada juga alasan untuk menjalankannya secara lokal, seperti privasi, akses cluster, dan anggaran

  • Tulisan yang bagus
    Menurut saya yang hilang di sini adalah analisis tentang apa yang didapat dengan memindahkan data cuaca ke basis data relasional
    Motivasinya adalah meningkatkan kecepatan query, tetapi perlu ada baseline-nya
    Sebagai maintainer Xarray dan Zarr serta pendiri https://earthmover.io/, saya cukup akrab dengan lanskap teknologi ini, dan jika data di Zarr di-chunk dengan tepat, query deret waktu untuk data cuaca bisa ditangani dengan latensi di bawah 1 detik hanya menggunakan solusi serverless + object storage
    Jauh lebih cepat daripada 30 menit yang disebut dalam tulisan
    Mengingat tingkat kesulitan pemuatan data yang dijelaskan di tulisan ini, solusi seperti itu layak dievaluasi dengan serius sebelum mengambil jalur RDBMS

    • Saya setuju bahwa menyimpannya dalam file Zarr yang di-chunk dengan tepat hampir pasti lebih cepat, lebih sederhana disiapkan, dan memakai lebih sedikit ruang
      Bisa juga diberi API di depannya agar terasa seperti query
      Benar juga bahwa pendekatan RDBMS belum cukup dibenarkan
      Alasan utama saya memilih Postgres + Timescale adalah karena ingin mempelajarinya, dan bermain-main dengan data ERA5 tampak paling menyenangkan
      Mungkin daya tariknya juga karena data cuaca cukup besar untuk menjadi tantangan
      Tanpa dasar bukti, saya penasaran apakah TimescaleDB + PostGIS yang dituning dan diindeks dengan baik akan membantu untuk query ruang-waktu kompleks seperti persentil ke-99 suhu musim panas di Chile pada 1940–1980
      Dalam kasus ini, mungkin perlu membaca banyak chunk Zarr
      Saya juga suka ide membuat tabel terpisah untuk men-cache statistik seperti ini, tetapi dengan Zarr pun hal itu tidak terlalu sulit
      Berikutnya saya berencana menjalankan benchmark query dan indeks, jadi mungkin akan tahu lebih banyak
    • Sedikit keluar topik, tetapi saya tertarik pada bidang yang sama
      Sepertinya ada ketegangan mendasar: chunk besar bagus untuk visualisasi area luas dan query besar, sedangkan chunk kecil bagus untuk query berbasis titik atau deret waktu
      Menjaga dataset geospasial besar seperti ini dalam beberapa versi chunking yang berbeda memang mungkin, tetapi tidak terlalu efisien dari sisi biaya
      Saya pernah mendengar kerchunk dipakai untuk mendapatkan keunggulan keduanya, tetapi dengan begitu sepertinya kita kehilangan pilihan kompresi data dan kompleksitasnya juga meningkat cukup banyak
      Saya penasaran bagaimana sebaiknya menyeimbangkan berbagai kasus penggunaan ini

  • Saya ikut berkontribusi pada dokumen pemuatan massal Postgres yang dirujuk di sini; tulisannya merangkum berbagai teknik dengan baik
    Saya cukup sering melakukan pekerjaan seperti ini untuk mempercepat pemuatan database OpenStreetMap, dan pengumuman pembaruan publik terakhir ada di https://www.youtube.com/watch?v=BCMnu7xay2Y
    Setelah itu, berkat kemajuan hardware, peningkatan GIS di PG15, serta adopsi teknik middle-way-node-index-id-shift di osm2pgsql, waktu pemuatan set planet turun hingga di bawah 4 jam
    Saran saya untuk penulis: beberapa eksperimen secara tidak langsung menghilangkan penulisan WAL lewat pg_bulkload dan COPY
    Saat Craig Ringer menulis posting SO yang ditautkan itu, hal ini belum terdokumentasi, tetapi di konfigurasi WAL bisa dimatikan begitu saja
    Tentu saja, jika terjadi crash, tabel yang sedang diproses akan hilang, dan kalau pekerjaannya memakan waktu berminggu-minggu, hal seperti itu bisa saja terjadi
    Namun untuk data deret waktu, jika struktur loading dirancang dengan baik, tidak sulit membuatnya hanya kehilangan chunk terakhir
    Untuk pemuatan massal, data WAL pada dasarnya tidak diperlukan
    Jika crash, bersihkan ujung kanan data yang sudah dimuat lalu mulai lagi
    Pengaturan postgresql.conf yang saya pakai untuk mematikan WAL dan overhead lain adalah sebagai berikut:
    wal_level = minimal
    max_wal_senders = 0
    synchronous_commit = off
    fsync = off
    full_page_writes = off
    autovacuum = off
    checkpoint_timeout = 60min
    Terakhir, untuk mengurangi pekerjaan vacuum saat memuat dalam chunk besar, biasanya autovacuum dimatikan seperti di atas, lalu VACUUM FREEZE dijalankan secara berkala di belakang partisi tanggal yang sedang dimuat
    Ini untuk PG biasa, dan membuat database melewati sebagian pekerjaan yang mengurusi tahap perantara ketika transaksi baru sudah ditulis tetapi belum terlihat oleh semua orang

    • Saya akan mencoba pengaturan untuk mematikan WAL dan overhead lain, lalu melihat apakah insert menjadi lebih cepat
      Khususnya untuk pemuatan per chunk, senang mendengar dari seorang pakar bahwa data WAL memang tidak wajib untuk pemuatan massal
      Saya belum punya UPS, tetapi berharap bisa melewati sekitar 20 hari yang dibutuhkan untuk memuat data tanpa listrik padam; dalam kasus terburuk pun sepertinya tinggal melanjutkan saja
    • Saya penasaran apakah ada materi lain untuk melihat lebih jauh peningkatan GIS di PG15

  • Kalau OP adalah penulisnya, eksperimen serupa yang saya lakukan sekitar 4 tahun lalu mungkin juga menarik
    Dataset yang sama, target yang sama, tujuan yang mirip
    https://rdrn.me/optimising-sql/
    Alur investigasinya mirip, tetapi saya memakai Postgres biasa, bukan Timescale; dalam konfigurasi saya, dengan asumsi data sudah ada di memori, penyalinan langsung data biner sekitar 3 kali lebih cepat daripada COPY

    • Andai saya melihat ini sebelum mulai, pasti bagus
      Saya menuliskan alasan tidak mencoba binary COPY di catatan kaki; pada dasarnya karena ada orang lain yang mengatakan performanya mengecewakan
      Meski begitu, sepertinya saya tetap harus mencobanya sendiri
      https://aliramadhan.me/2024/03/31/trillion-rows.html#fn:copy-binary-note
    • Saya sudah membaca bagian 1 dan 2, tulisannya menarik
      Format dengan catatan di margin juga bagus
      Fungsi yang disediakan untuk menulis structured array numpy ke biner Postgres sangat membantu; sebelumnya saya tidak berhasil mencari tahu bagian itu

  • “Apakah database relasional memang cocok untuk data cuaca berbentuk grid? Tidak tahu, tapi kita akan tahu kalau dicoba.”
    Saya suka sikap ini
    Rasanya kebalikan total dari gaya “sebenarnya begini lho” di banyak tulisan teknologi arus utama, jadi saya menyukainya
    Saya juga suka bagaimana pembaca tetap tertarik sampai akhir selama mengikuti perjalanannya

    • Bisa jadi menjadi pemula dan tidak punya kepentingan apa pun justru merupakan kelebihan
      Saya sudah banyak mencari, tetapi tidak menemukan jawaban pasti untuk use case saya, jadi saya memutuskan menjalankan benchmark sendiri

  • Tulisan yang menarik
    Bagian “dengan insert berkelanjutan sekitar 462k per detik, sekitar 754 miliar baris akan memakan waktu sekitar 20 hari, rasanya tidak buruk. Lebih singkat daripada waktu yang dibutuhkan untuk menulis artikel ini.” itu lucu
    Saya juga makin condong menulis posting blog yang lebih panjang dan mendalam, jadi saya paham bahwa ternyata usahanya jauh lebih besar dari yang dibayangkan

    • Sebagian benchmark memakan waktu berjam-jam, beberapa harus dijalankan ulang, dan banyak juga yang dipelajari selama proses itu

  • Jika ingin menggambar chart deret waktu atau beberapa chart langsung dari query SQL, qStudio adalah SQL IDE gratis dan bekerja dengan berbagai DB termasuk TimescaleDB
    https://www.timestored.com/qstudio/database/timescale
    Disclaimer: saya yang membuat alat ini

    • Saya penasaran bagaimana prosedur menambahkan dukungan database lain ke qStudio
      Saya pikir mungkin dukungan Timeplus bisa ditambahkan
      Timeplus adalah database streaming-first berbasis ClickHouse, dan Timeplus Proton, engine DB intinya, bersifat open source
      qStudio juga open source dan ditulis dalam Java, jadi untuk dukungan RDBMS baru sepertinya diperlukan driver JDBC
      Jika benar, Timeplus Proton memiliki driver JDBC open source berbasis driver ClickHouse dengan modifikasi untuk kebutuhan streaming
      https://www.timeplus.com/
      https://github.com/timeplus-io/proton
      https://github.com/timeseries/qstudio
      https://github.com/timeplus-io/proton-java-driver
    • Saya terutama memakai TablePlus dan matplotlib lewat psycopg3 untuk menggambar hasil query, tetapi ini tampaknya bisa dipakai lebih cepat
      Untuk saat ini saya baru memasukkan data, tetapi saya akan segera mencoba query dan plotting

  • Saya juga memakai data reanalisis ERA5 dan membutuhkan deret waktu yang cepat
    Karena datanya datang sebagai kisi [lat, lon] yang ditumpuk berdasarkan periode yang dipilih, misalnya dalam bentuk [data per jam untuk satu bulan, lat, lon], jika ingin lebih dari 20 tahun ini menjadi masalah transposisi matriks raksasa
    Cara yang saya pakai adalah mengunduh tiap file netCDF, mentransposenya, lalu memasukkannya ke file HDF 3D raksasa yang tersusun sebagai [lat, lon, hour]
    Di workstation saya, membuat data satu tahun untuk satu variabel memakan waktu sekitar 30 menit, tetapi setelah itu mengambil satu lokasi (lat, lon) hanya butuh hitungan milidetik
    Ini pendekatan yang bersusah-payah di awal tetapi menguntungkan dalam jangka panjang
    Sederhana, tapi saya bukan ahli database, hanya seorang klimatolog

    • Sederhana, tetapi kemungkinan lebih cepat dan lebih efisien ruang dibanding database relasional
      rabernat dan open-meteo yang berkomentar di sini juga tampaknya memakai pendekatan serupa dan menganggapnya cepat