TimescaleDB’ye 1 trilyon hava durumu verisi yüklemek

 (aliramadhan.me)
3 puan yazan GN⁺ 2024-04-17 | 2 yorum | WhatsApp'ta paylaş
  • Dünya genelindeki geçmiş hava durumu değişimlerini hızlıca analiz etmek için ERA5 yeniden analiz verilerini PostgreSQL/TimescaleDB’ye koymak gerekiyor; seçilen değişkenler ve ızgara bazında ölçek yaklaşık 754 milyar satıra ulaşıyor
  • ERA5, 1940’tan itibaren saatlik çözünürlük ve 0,25 derecelik enlem-boylam ızgarası sağlıyor; ancak NetCDF yapısında tek bir noktanın uzun dönemli zaman serisi çıkarımı bile 20-30 dakika sürebiliyor
  • Tek satırlık insert saniyede yaklaşık 3 bin satır işliyor; bu da tüm yükleme için yaklaşık 8 yıl gerektiriyor. Çok satırlı insert de psycopg3 bazında saniyede 25 bin-30 bin satır ile yaklaşık 10 ay düzeyinde kalıyor
  • PostgreSQL copy ve psycopg3 cursor.copy(), CSV’nin hazır olup olmamasına göre farklılık gösteriyor; ek yük dahil edildiğinde yaklaşık saniyede 100 bin satır seviyesinden paralelleştirme ve ayar optimizasyonlarıyla daha da yukarı çıkarılabiliyor
  • Veri çerçevesinden doğrudan yüklerken psycopg3 ile hypertable’a doğrudan copy, CSV zaten mevcutsa timescaledb-parallel-copy uygun; bu sistemde 12-16 worker paralelleştirme için makul nokta gibi görünüyor

Dünya geneli hava durumu veri ambarı oluşturmak

  • Amaç, dünya genelindeki geçmiş hava durumu verilerini sorgulayarak halihazırda gerçekleşmiş iklim değişikliği sinyallerini analiz edebilecek bir veri ambarı oluşturmak
  • Örnek analizler Jakarta’nın gerçekten daha sıcak hale gelip gelmediğini veya fırtınaların sıklaşıp sıklaşmadığını, Şili’nin tamamının daha sıcak ya da daha bulutlu olup olmadığını ve bölgesel değişimlerin nasıl bir biçim aldığını ele alıyor
  • Hızlı küresel analiz için veri ambarı sorgularının hızlı olması gerekiyor; ilk adım ise büyük miktarda veriyi veritabanına yüklemek
  • Temel olarak PostgreSQL kullanılıyor; zaman tabanlı sorguları hızlandırmak için TimescaleDB, gelecekte jeo-uzamsal sorguları hızlandırmak için ise PostGIS güçlü adaylar

ERA5 yeniden analiz verisi ve 754 milyar satır ölçeği

  • Gerçek meteorolojik gözlemler yerine ERA5 climate reanalysis verisi kullanılıyor
    • Geçmiş gözlemler belirli bölgelerde ve geçmiş zamanlarda seyrek olabilir
    • ERA5, gözlemlerle uyumlu olacak şekilde kısıtlanmış iklim modeli çıktısıdır ve meteoroloji ile iklim araştırmalarında yaygın kullanılır
  • ERA5, tüm dünyayı 0,25 derece çözünürlükle kapsar ve 1940’tan itibaren saatlik çözünürlükte sunulur
    • Zaman anlık görüntüleri değişken başına 727.080 adettir
    • Izgara noktaları 1.038.240 adettir; 1.440 boylam ve kutuplar dahil 721 enlemden oluşur
    • Zaman ve konuma göre indekslendiğinde değişken başına 753.836.544.000 satır, yani yaklaşık 754 milyar satır eder
  • Yüklenecek değişkenler sıcaklık, doğu-batı ve kuzey-güney yönlü 10 m rüzgâr hızı, toplam bulutluluk, yağış ve kar miktarıdır
  • Tabloda time, location_id, latitude, longitude ve her hava durumu değişkeni için sütunlar bulunur
    • location_id ile enlem-boylam sütunlarının birlikte tutulmasının nedeni, ilerideki sorgular ve indeks benchmark’larıdır

NetCDF dosya yapısının yavaşladığı noktalar

  • ERA5, NetCDF dosyaları olarak dağıtılır; veriler genellikle günlük, aylık veya yıllık dosyalarda yer alır
  • Zaman bazlı chunk yapısı, belirli bir andaki veriyi sorgulamak için hızlı ve basittir
  • Tek bir noktanın uzun dönemli zaman serisi gibi zaman örüntülerini görmek için çok sayıda dosya okumak gerektiğinden yavaşlar
    • Örneğin tek bir konumun sıcaklık zaman serisini çıkarmak 20-30 dakika sürer
  • Karmaşık jeo-uzamsal sorgular, özellikle zaman eksenini de içeren sorgular, yavaş ve yürütmesi zordur
  • xarray, dask, Pangeo gibi araçlar hızı artırabilir ancak süreç yine de yavaş kalır

insert: tek satırdan çok satıra

  • En basit yöntem, insert ile satırları tek tek eklemektir
  • Tek satırlık insert çeşitli maliyetler getirir
    • PostgreSQL ifadeyi ayrıştırır, tablo ve sütun adlarını doğrular, yürütme planı oluşturur
    • Veri bütünlüğü için kilitler gerekebilir
    • WAL (write-ahead logging) için veriyi tampona yazar
    • Veriyi gerçek tablo disk alanına ekler
    • İşlem commit edildiğinde değişiklik kalıcı hale gelir
  • Python’da tek satırlık ekleme için üç yöntem benchmark edildi
    • pandas df.to_sql() ile chunksize=1 kullanımı
    • psycopg3’ün parametreli sorgusu
    • SQLAlchemy’nin parametreli sorgusu
  • Tek satırlık ekleme sonuçlarında psycopg3 biraz önde, SQLAlchemy ise en yavaş
    • TimescaleDB hypertable, normal PostgreSQL tablosundan biraz daha yavaş
    • En iyi performans saniyede yaklaşık 3 bin satır; bu da tüm veriyi yüklemek için yaklaşık 8 yıl anlamına geliyor
  • Çok satırlı insert, tek ifadeye birden fazla satır koyarak ağ gidiş-gelişlerini, ayrıştırma ve planlama maliyetlerini azaltır
    • psycopg3 saniyede 25 bin-30 bin satırla en hızlısı
    • pandas’ın sözlük kullanarak ekleme yapan yapısı tuple’a göre daha yavaş olabilir
    • SQLAlchemy’de oturum yönetimi ve SQL ifade soyutlaması gibi ek yükler olabilir
    • Yine de tüm yükleme yaklaşık 0,8 yıl, neredeyse 10 ay gerektirir

copy: PostgreSQL’in toplu yükleme yolu

  • PostgreSQL copy, CSV veya ikili dosyalardan satır okuyup yükleyen bir toplu yükleme özelliğidir
  • Toplu yükleme varsayımıyla ayrıştırma, planlama ve WAL kullanımını optimize ettiği için çok satırlı insertten daha hızlıdır
  • İki yol karşılaştırılıyor
    • NetCDF verilerini CSV olarak kaydedip ardından copy ile yüklemek
    • CSV dosyası oluşturmadan psycopg3 cursor.copy() ile doğrudan PostgreSQL’e stream etmek
  • CSV dosyası zaten hazırsa copy, saniyede yaklaşık 400 bin satıra yakın ekleme hızına ulaşır
  • CSV dosyası yazma veya tuple oluşturma ek yükleri dahil edildiğinde hem copy hem de psycopg3 yaklaşık saniyede 100 bin satır düzeyindedir; psycopg3 biraz daha hızlıdır
  • Bu hızda bile tüm veriyi yüklemek yaklaşık 3 ay sürer

Sürekli yükleme oranı ve paralel copy

  • Çok sayıda satır yüklerken disk yazma, WAL ile tablo ekleme arasındaki I/O rekabeti, autovacuum ve checkpoint gibi darboğazlar oluşabilir
  • Yaklaşık 772 milyon satırın 744 batch halinde yüklendiği deneyde, tek worker bazında büyük bir hız düşüşü görülmedi
    • copy csv sık düşüşler gösteriyor ve dalgalanmalara daha açık
    • psycopg3 genel olarak daha hızlı
    • Normal tablo ile hypertable arasındaki fark büyük değil
  • joblib ile birden fazla copy işi veya psycopg3 cursor’ı paralel çalıştırılıyor
  • Tek bir tabloya ekleme, paralelleştirmesi çok iyi olan bir iş değildir; performans genel olarak 16 worker sonrasında plato yapıyor

pg_bulkload ve timescaledb-parallel-copy

  • PostgreSQL copy dışında pg_bulkload ve timescaledb-parallel-copy benchmark edildi
  • pg_bulkload varsayılan ayarlarda daha hızlı görünüyor; ancak varsayılan olarak shared buffers’ı atlıyor ve WAL logging’i pas geçtiği için çökme sonrasında veri kurtarma mümkün olmayabilir
  • fsync kapatılmış aynı koşullarda, birden fazla worker kullanan timescaledb-parallel-copy pg_bulkload’dan daha iyi
  • timescaledb-parallel-copy, worker sayısını belirleyerek paralel ekleme yapabilir
    • İlk performans iyi; ancak bu sistemde 100 milyon satırdan önce darboğaza ulaşıyor, ekleme oranı sert biçimde düşüyor ve sonra dalga gibi toparlanıyor
    • Sürekli yükleme oranı normal tabloda saniyede yaklaşık 600 bin-700 bin satır, hypertable’da ise saniyede yaklaşık 300 bin satır düzeyinde
  • pg_bulkload’da worker sayısı belirtme yoktur; ancak writer=parallel seçeneğiyle okuma, ayrıştırma ve yazma için birden fazla thread kullanır

PostgreSQL ayarları ve dayanıklılık ödünleşimi

  • Ek performans, PostgreSQL’in dayanıksız ayarlarını değiştirerek elde edilebilir
  • Ana ayarlar, disk flush’ını önlemek için fsync’i kapatmak ve partial page write korumasından kaçınmak için full_page_writes’ı kapatmaktır
  • Bu ayarlar çökme durumunda veritabanı bütünlüğünü riske atabilir
  • Unlogged table WAL oluşturmadığı için yazma hızlıdır; ancak çökme kurtarması sırasında kırpılabilir
    • Sonrasında normal logged table’a dönüştürülmesi gerekir; bu süreç yavaş ve tek thread’li ilerleyebilir
    • Hypertable unlogged olamaz; bu nedenle hypertable gerekiyorsa ek dönüştürme ve migrasyon gerekir

Nihai seçim: doğrudan hypertable’a yüklemek

  • Hedef hypertable ise, normal tabloya yükleyip sonra hypertable’a dönüştürmek yerine doğrudan hypertable’a yüklemek daha hızlıdır
  • Yaklaşık 772 milyon satırın psycopg3 copy ve 16 worker ile yüklendiği basit testte, hypertable’a doğrudan ekleme normal tabloya ekleyip dönüştürmekten daha az zaman aldı
    • Bu durumda hypertable’a doğrudan ekleme kabaca sürenin %80’ini aldı
    • Dönüştürme ve migrasyon süreci hızlı değil ve tek thread’li gibi görünüyor
  • Önerilen yöntem şöyle
    • Veri çerçevesinden doğrudan yüklüyorsanız psycopg3 ile hypertable’a doğrudan copy
    • CSV dosyası zaten varsa timescaledb-parallel-copy kullanın
    • Paralelleştirme için bu sistemde 12-16 worker makul nokta gibi görünüyor

Genel benchmark sonucu ve gereken süre

  • Koruma ayarları açıkken tek worker bazında, ek yük dahil sürekli yükleme oranının üst sınırı bu donanımda yaklaşık saniyede 140 bin satır gibi görünüyor
  • Birden fazla worker kullanıldığında psycopg3 copy cursor ile koruma ayarları korunarak sürekli yükleme oranı yaklaşık saniyede 250 bin satıra çıkarılabiliyor
  • Ekleme sürecinde paralelleştirme verimi yüksek değil; 4-16 worker makul aralık gibi görünüyor
  • Risk alıp fsync kapatılırsa psycopg3 ile yaklaşık saniyede 462 bin satır sürdürülebiliyor
  • pg_bulkload varsayılan olarak fsync’i devre dışı bıraktığından kullanırken dikkat gerekiyor
  • Saniyede yaklaşık 462 bin satır sürdürülebilirse yaklaşık 754 milyar satırın yüklenmesi yaklaşık 20 gün sürer

Kod ve benchmark ortamı

  • ERA5 indirme, tablo oluşturma, insert/copy, benchmark ve grafik üretme kodu timescaledb-insert-benchmarks deposunda bulunuyor
  • Her benchmark için tutarlı ortamı korumak üzere yeni Docker container başlatılıyor
    • Docker container’ları arasında depolama korunmuyor
    • NetCDF ve CSV dosyaları HDD’den okunuyor
    • Veritabanı NVMe SSD’de saklanıyor
  • Donanım yapılandırması
    • 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
  • Yazılım yapılandırması
    • Ubuntu 20.04, Linux kernel 5.15
    • PostgreSQL 15.5
    • TimescaleDB 2.13.0
    • pg_bulkload 3.1.20
  • PostgreSQL ayarlarında timescaledb-tuneun 250.57GB bellek ve 48 CPU bazında önerdiği değerler kullanılıyor; benchmark’larda WAL boyutu ayrıca ayarlanıyor

İlgili okumalar

2 yorum

 
jangsc0000 2024-04-18

GN+ yorumları resmî hitapla yazılmış gibi duruyor..?
 
GN⁺ 2024-04-17
Hacker News görüşleri

  • İş gereği coğrafi uzamsal analiz epey yaptım; coğrafi uzamsal veri, koordinat referans sistemini (CRS) ve görselleştirme projeksiyonlarını doğru anlamayı gerektirdiği için sanılandan çok daha nüanslı
    Metaveriyi veriyle birlikte düzgün taşıyan bir altyapı yoksa CRS yüzünden sürekli tedirgin oluyorsunuz
    AWS’in ilgili özelliklerini, Postgres/PostGIS’i, Spark/Databricks’i, Snowflake’i, Trino’yu ve ArcGIS’i kullandım; ama büyük ölçekli coğrafi uzamsal işlerde Google BigQuery’nin açık ara en iyi olduğunu düşünüyorum
    Devasa bir m6a EC2 üzerindeki PostGIS’te saatler süren ve pahalıya mal olan bir sorgu, BigQuery ücretsiz katmanında 5 saniyeden kısa sürdü
    FEMA açık verilerini kullandım; Snowflake ve AWS servisleri geometry sütunu maksimum bayt boyutunu aştığı için takıldı, Spark’ta coğrafi uzamsal veri tipi yok ve açık kaynak uzantıları da yetersizdi
    On-premise ise durum farklı olabilir, ama 20 TB için BigQuery depolama maliyeti de büyük olasılıkla ayda $100’ın altında olacağından bunu kendim işletmek istemem

    • Global OSM ve Whosonfirst ile pipeline işlerken benzer bir süreç yaşadım; Google maliyeti Airflow + BigQuery ile ayda $7k’a kadar çıktı, ben de bunu tek seferlik $7k’lık donanım alımıyla değiştirdim
      Başta H3 indeksleri kullandığım ve ara veri kümelerinin tamamı belleğe sığdığı için bu mümkündü
      Kurulum 128 GB Mac Studio + Asahi Linux + mmap parquet dosyaları + DuckDB; Airflow da çalıştırıyor, ayrıca Nix’i geliştirici build’lerini hızlandırmak ve veri ekibinin Airflow işlerini çalıştırmak için kullanıyorum
      GCP ücretsizken ya da ucuzken iyi, ama kullanım artmasa bile bir noktada daha büyük bir faturayla sizi şaşırtabilir
    • Spark’ta coğrafi uzamsal veri tipi olmadığı ve açık kaynak paketlerin yetersiz kaldığı kısmını daha fazla duymak isterim
      Apache Sedona ile karşılaştırdınız mı, özellikle ne eksikti merak ediyorum
      Apache Sedona’yı yapanların kurduğu Wherobots’ta çalışıyorum, geri bildirim duymak isterim
      https://sedona.apache.org/latest/
      https://wherobots.com/
    • Belirli veri kümesine link verebilirseniz iyi olur
      Çok geniş sütunların pek çok aracı bozduğu konusunda hemfikirim, ama diğer sütun yönelimli Postgres türevleri bunu sorunsuz destekleyebilir gibi geliyor
      BigQuery ile doğrudan rekabet eden Redshift’i kullanmamış olmanız şaşırtıcı; Redshift’in super sütunu BigQuery maksimumundan bile daha büyük ayarlanabiliyor
      İnsanların PostGIS’i yenmenin zor olduğunu tekrar tekrar zahmetli şekilde fark ettiğini sıkça görüyorum
      Trino/Presto ve Spark’ın bu alanda duraksamış olması da özellikle anlamlı
    • Yaklaşık 500 GB’lık veri ambarını 8 çekirdekli Postgres RDS’den BigQuery’ye taşıdım; yeniden oluşturma süresi 5 saatten 11 dakikaya düştü ve maliyet benzer ya da daha düşüktü
      Postgres’te büyük tabloların bir kısmını cache’liyordum, BigQuery’de ise her şeyi sıfırdan oluşturuyordum; buna rağmen böyle oldu
      Kendi işletilebilen araçlar arasında Postgres’i daha çok seviyorum, ama performansın tek haneli katlar mertebesinde daha iyi olmasına karşı çıkmak zor
    • BigTable ve BigQuery hakkında gerçekten çok iyi şeyler duyuyorum; hâlâ deneme fırsatım olmadığı için üzülüyorum

  • Gerçekten harika bir yazı
    Timescale’de DevRel’den sorumluyum; topluluğun böyle iyi yazılmış içerikler üretmesini görmek güzel
    Hypertable’ın daha yavaş olmasının nedenlerinden biri neredeyse kesin olarak timestamp sütununa varsayılan olarak indeks oluşturması bence
    Normal tabloda indeks olmadığı için daha hızlı olmuş olabilir
    create_hypertable içinde create_default_indexes=>false kullanırsanız indeks oluşturmayı atlayabilirsiniz; veriyi yüklemeden önce indeksi silmek de mümkün
    Sonunda o indekse ihtiyaç duyacaksınız, ama bu tür toplu yüklemelerde yüklemeden sonra tek seferde oluşturmak daha iyi
    Ayrıca yüksek paralellikli bir kurulumda verinin okunduğu HDD’nin ne kadar dayanabildiğini de merak ediyorum

    • create_default_indexes=>false seçeneğini ve hypertable’ın varsayılan olarak zaman indeksi oluşturduğunu bilmiyordum; bunu açıklayan bir not ekleyeceğim
      Zaman indeksi olmadan ekleme yapıp ardından manuel indeks oluşturduğum bir benchmark da denemek isterim
      32 worker’da bile HDD iyi gibiydi
      btop ile disk kullanımına baktığımda Postgres’in bulunduğu SSD, HDD’den daha çok darboğaza yakın görünüyordu; bu yüzden veriyi HDD’den SSD’ye taşımak yerine Postgres için kullanılan SSD’yi daha hızlı bir modelle değiştirmenin daha iyi bir yatırım olduğu sonucuna vardım

  • Bunu neden özellikle böyle yaptıklarını anlamıyorum
    ERA5 dahil çoğu hava durumu ve iklim veri kümesi, düzenli bir enlem-boylam ızgarası üzerinde son derece yapılandırılmış durumda
    Belirli bir konumun yalnızca zaman serisini çıkarsanız bile, bu tür veri kümelerinin gücü içerdikleri uzamsal-zamansal yapı ve bağlamda; amaç yalnızca nokta zaman serileri çıkarmak değilse bu yapıyı tamamen bozmak pek mantıklı değil
    Hatta yalnızca nokta zaman serileri çıkarırken bile, örneğin okyanusun ortasındaki yüzey sıcaklığı zaman serisi gibi şeylerin pek kullanılmayacağı için veriyi epey agresif biçimde azaltmak istersiniz
    ERA5 gibi veri kümelerinin araştırma ve operasyonel kullanımlarının çoğu için, Google Public Datasets’teki ARCO-ERA5 gibi özgün yapıyı koruyan, buluta optimize edilmiş kopyaları kullanmak daha uygun görünüyor
    Bu sürümler, özgün yapıyı korurken bulut depolamada büyük ölçekli paralel erişime uygun şekilde parçalara ayırır
    Kariyerimde gördüğüm neredeyse tüm durumlarda, Zarr tabanlı ve genel amaçlı chunking yapılmış bir arşiv bile ilgi çekebilecek çoğu kullanım için yeterince hızlıydı
    https://cloud.google.com/storage/docs/public-datasets/era5

    • Ana neden bunun kişisel bir proje olmasıydı; bulut kaynaklarına para ödemeden her şeyi ev sunucumda denemek, Postgres, TimescaleDB ve sonrasında PostGIS öğrenmek istedim
      Bununla birlikte rabernat’ın söylediği gibi, bulut kopyalarından uzun zaman serileri çıkarmak da yavaş
      Sonunda 1940–1980 yılları arasında Şili yaz sıcaklıklarının 99. yüzdelik dilimini hesaplamak gibi karmaşık uzamsal-zamansal sorgular da yapmak istiyorum
      Bulut kopyasının daha hızlı olabileceğinden şüphem yok, ama bu $0 bütçeyle çelişiyor
    • Doğru, ama gerçekte Google ERA5 açık verisi de yazıda anlatılanla tamamen aynı chunking sorununu yaşıyor
      Uzamsal sorgular için optimize edilmiş, zaman serisi sorguları için değil
      Az önce benchmark çalıştırdım; tek bir noktanın tek değişkenli zaman serisini almak 20 dakika sürdü
      Beklenen kullanım deseni zaman serisiyse zaman serisine optimize edilmiş chunking gerektiğini gayet iyi gösteriyor
    • Keşke biri bunu yapıp başkalarına nasıl kullanılacağını da öğretse
      Bazı laboratuvarların, zaten yayımlanmış algoritmalar ve verilerle iç içe geçmiş RDBMS tabanlı pipeline’ları var; kimse bunları yeniden uygulamak istemiyor ve bunu yapacak bütçe de yok
      Elde ettiğimiz en iyi iyileştirme, eski MySQL’den Postgres + PostGIS’e geçmek oldu
      Timescale de muhtemelen yardımcı olurdu
      Gizlilik, kümeye erişim ve bütçe gibi nedenlerle yerelde çalıştırmamız gereken durumlar da vardı

  • Güzel yazı
    Burada eksik olan şeyin, hava durumu verilerini ilişkisel veritabanına taşıyarak ne kazanıldığına dair bir analiz olduğunu düşünüyorum
    Motivasyon sorgu hızını artırmak, ama bunun için bir temel çizgiye ihtiyaç var
    Xarray ve Zarr bakımcısı ve https://earthmover.io/ kurucusu olarak bu teknoloji alanına epey aşinayım; Zarr’da veriyi uygun şekilde chunk ederseniz, yalnızca sunucusuz bir çözüm + nesne depolama ile hava durumu verilerinde zaman serisi sorgularını 1 saniyenin altında gecikmeyle işleyebilirsiniz
    Yazıda bahsedilen 30 dakikadan çok daha hızlı
    Bu yazıdaki veri yükleme zorluğu düşünüldüğünde, RDBMS yoluna girmeden önce böyle bir çözümü ciddi biçimde değerlendirmeye değer

    • Uygun şekilde chunk edilmiş Zarr dosyasında saklamanın neredeyse kesinlikle daha hızlı, kurulumunun daha basit ve daha az yer kaplayacağına katılıyorum
      Önüne bir API koyup sorgu gibi de gösterebilirsiniz
      RDBMS yaklaşımını yeterince gerekçelendirmediğim de doğru
      Postgres + Timescale’e gitmemin ana nedeni bunları öğrenmek istemem ve ERA5 verileriyle oynamanın en eğlenceli seçenek gibi görünmesiydi
      Hava durumu verilerinin meydan okuyacak kadar büyük olması da muhtemelen cazip gelmişti
      Elimde kanıt yok, ama iyi ayarlanmış ve indekslenmiş TimescaleDB + PostGIS’in 1940–1980 yılları arasında Şili yaz sıcaklıklarının 99. yüzdelik dilimi gibi karmaşık uzamsal-zamansal sorgularda yardımcı olup olmayacağını merak ediyorum
      Çünkü bu durumda birden fazla Zarr chunk’ı okumak gerekebilir
      Bu tür istatistikleri cache’leyen ayrı tablolar tutma fikrini de seviyorum, ama Zarr ile de bu çok zor değil
      Bir sonraki adımda sorguları ve indeksleri benchmark edeceğim, sanırım o zaman daha fazlasını öğreneceğim
    • Konudan biraz sapıyor ama aynı alana ilgi duyuyorum
      Büyük chunk’ların geniş alan görselleştirmeleri ve büyük sorgular için, küçük chunk’ların ise nokta tabanlı veya zaman serisi sorguları için iyi olması gibi temel bir gerilim var gibi görünüyor
      Bu tür büyük jeo-uzamsal veri kümelerini farklı chunking sürümleriyle ayrı ayrı tutmak mümkün, ama maliyet açısından pek verimli değil
      İkisinin avantajını elde etmek için kerchunk kullanıldığını duydum, ancak bu durumda veri sıkıştırma seçeneklerini kaybediyor gibi oluyorsunuz ve karmaşıklığın da epey arttığını düşünüyorum
      Farklı kullanım senaryoları arasında dengeyi en iyi nasıl kurmak gerektiğini merak ediyorum

  • Burada atıfta bulunulan Postgres toplu yükleme dokümantasyonuna katkıda bulunmuştum; çeşitli teknikleri iyi tarayan bir yazı
    OpenStreetMap veritabanı yüklemesini hızlandırmak için bu tür işleri epey yaptım; son herkese açık güncelleme sunumu https://www.youtube.com/watch?v=BCMnu7xay2Y adresinde
    O zamandan beri donanım gelişmeleri, PG15’in GIS iyileştirmeleri ve osm2pgsql’in middle-way-node-index-id-shift tekniğini benimsemesi sayesinde planet seti yükleme süresi 4 saatin altına indi
    Yazara önerim şu: Bazı deneyler pg_bulkload ve COPY üzerinden dolaylı olarak WAL yazımını ortadan kaldırıyor
    Craig Ringer bağlantı verilen SO yazısını yazdığı sırada belgelenmemişti, ama ayarlardan WAL’ı doğrudan kapatabilirsiniz
    Elbette bir çökme olursa işlemdeki tabloyu kaybedersiniz; iş birkaç hafta sürüyorsa bu yaşanabilir
    Ama zaman serisi verilerinde yükleme yapısını iyi kurarsanız yalnızca son chunk’ı kaybedecek hale getirmek zor değil
    Toplu yüklemede WAL verisine fiilen ihtiyaç yok
    Çökme olursa yüklenmiş verinin sağ ucunu temizleyip yeniden başlarsınız
    WAL ve diğer ek yükleri kapatmak için kullandığım postgresql.conf ayarları şöyle:
    wal_level = minimal
    max_wal_senders = 0
    synchronous_commit = off
    fsync = off
    full_page_writes = off
    autovacuum = off
    checkpoint_timeout = 60min
    Son olarak büyük chunk’lar halinde yüklerken vacuum işini azaltmak için genelde yukarıdaki gibi autovacuum’u kapatır, o anda yüklenen tarih partition’ının arkasından düzenli olarak VACUUM FREEZE çalıştırırım
    Bu genel PG için geçerli; yeni transaction’ların yazıldığı ama henüz herkese görünür olmadığı ara aşamalarla ilgili veritabanının önem verdiği işlerin bir kısmını atlamayı sağlar

    • WAL ve diğer ek yükleri kapatan ayarları deneyip insert’lerin hızlanıp hızlanmadığına bakacağım
      Özellikle chunk bazlı yüklemede WAL verisinin toplu yükleme için şart olmadığını bir uzmandan duymak sevindirici
      Henüz UPS’im yok ama elektrik kesilmeden veri yüklemenin süreceği yaklaşık 20 günü aşabilmeyi umuyorum; en kötü durumda da sanırım sadece kaldığım yerden devam ederim
    • PG15’in GIS iyileştirmeleri hakkında daha fazla bakabileceğim kaynak olup olmadığını merak ediyorum

  • OP yazarsa, yaklaşık 4 yıl önce yaptığım benzer bir deney de ilginç gelebilir
    Aynı veri seti, aynı hedef, benzer amaçlar vardı
    https://rdrn.me/optimising-sql/
    Benzer bir araştırma akışı; ama Timescale yerine düz Postgres kullandım ve benim kurulumumda verinin zaten bellekte olduğu varsayımıyla binary veriyi doğrudan kopyalama, COPY’den yaklaşık 3 kat hızlı oldu

    • Keşke başlamadan önce bunu görmüş olsaydım
      Binary COPY’yi denememe nedenimi dipnot olarak yazdım; temelde başka birinin performansın hayal kırıklığı yarattığını söylemesiydi
      Yine de kendim denemem gerekecek gibi
      https://aliramadhan.me/2024/03/31/trillion-rows.html#fn:copy-binary-note
      1. ve 2. bölümleri okudum; yazı keyifliydi
        Kenar notları eklenmiş formatı da sevdim
        numpy structured array’leri Postgres binary’ye yazan bir fonksiyon sağlamanız yardımcı oldu; bunu daha önce çözememiştim

  • “Izgara biçimli hava durumu verileri için ilişkisel veritabanı gerçekten uygun mu? Bilmiyorum, ama deneyince görürüz.”
    Bu tavrı seviyorum
    Diğer ana akım teknik yazılardaki “aslında şöyle” tarzının tam zıddı olduğu için hoşuma gidiyor
    Yolculuğu takip ederken okuyucuyu sonuna kadar tutması da güzel

    • Acemi olup işin içinde bir çıkarım olmaması avantaj da olabilir
      Çok aradım ama kullanım senaryom için kesin bir cevap bulamadım; bu yüzden kendim benchmark çalıştırmaya karar verdim

  • İlginç bir yazı
    “Sürekli olarak saniyede yaklaşık 462k insert ile yaklaşık 754 milyar satır 20 gün kadar sürüyor; fena değil gibi. Bu yazıyı yazmamdan daha kısa.” kısmı komik
    Ben de daha uzun ve derin blog yazıları yazma tarafına kayıyorum; beklediğimden çok daha fazla emek istediği konusunda katılıyorum

    • Benchmark’ların bir kısmı saatler sürdü ve birkaçını tekrar çalıştırmam gerekti; bu süreçte de çok şey öğrendim

  • SQL sorgusundan doğrudan zaman serisi grafiği veya birden fazla grafik çizmek istiyorsanız qStudio ücretsiz bir SQL IDE’si ve TimescaleDB dahil çeşitli DB’lerle çalışıyor
    https://www.timestored.com/qstudio/database/timescale
    Açıklama: Bu aracı ben yaptım

    • qStudio’ya başka veritabanı desteği ekleme süreci nasıl, merak ediyorum
      Timeplus desteği eklenebilir mi diye düşünüyorum
      Timeplus, ClickHouse tabanlı, streaming öncelikli bir veritabanı; çekirdek DB motoru Timeplus Proton ise açık kaynak
      qStudio da açık kaynak ve Java ile yazılmış olduğundan yeni bir RDBMS desteği için JDBC driver gerekiyor gibi görünüyor
      Doğruysa Timeplus Proton’un, ClickHouse driver’ını temel alan ve streaming kullanımı için değişiklikler eklenmiş açık kaynak bir JDBC driver’ı var
      https://www.timeplus.com/
      https://github.com/timeplus-io/proton
      https://github.com/timeseries/qstudio
      https://github.com/timeplus-io/proton-java-driver
    • Çoğunlukla sorgu sonuçlarını çizmek için psycopg3 üzerinden TablePlus ve matplotlib kullanıyordum; bu daha hızlı kullanılabilir görünüyor
      Şimdilik yalnızca veri insert ettim, ama yakında sorgu ve plotting deneyeceğim

  • Ben de ERA5 yeniden analiz verilerini kullanıyorum ve hızlı zaman serilerine ihtiyacım var
    Veri, seçilen dönemlere göre biriken [lat, lon] ızgarası olarak, örneğin [bir aylık saatlik veri, lat, lon] biçiminde geldiği için 20 yıldan fazlasını isterseniz devasa bir matris transpozisyonu sorununa dönüşüyor
    Benim yöntemim, her netCDF dosyasını indirip transpoze ettikten sonra [lat, lon, hour] şeklinde yapılandırılmış devasa bir 3D HDF dosyasına koymak
    İş istasyonumda tek bir değişkenin 1 yıllık verisini oluşturmak yaklaşık 30 dakika sürüyor, ama sonrasında tek bir (lat, lon) konumunu almak milisaniyeler düzeyinde
    Başta zahmetli, uzun vadede kazançlı bir yöntem
    Basit ama ben bir veritabanı uzmanı değil, yalnızca bir iklim bilimciyim

    • Basit olsa da ilişkisel veritabanından daha hızlı ve alan açısından daha verimli olma ihtimali yüksek
      Burada yorum yapan rabernat ve open-meteo da benzer bir yöntem kullanıyor ve bunun hızlı olduğunu düşünüyor gibi görünüyor