TimescaleDB में 1 ट्रिलियन मौसम डेटा लोड करना

 (aliramadhan.me)
3 पॉइंट द्वारा GN⁺ 2024-04-17 | 2 टिप्पणियां | WhatsApp पर शेयर करें
  • दुनिया भर के ऐतिहासिक मौसम बदलावों का तेज़ी से विश्लेषण करने के लिए ERA5 reanalysis डेटा को PostgreSQL/TimescaleDB में डालना ज़रूरी है, और चुने गए variables व grid के आधार पर इसका आकार लगभग 754 अरब rows बनता है
  • ERA5 1940 से प्रति-घंटा resolution और 0.25-degree latitude/longitude grid देता है, लेकिन NetCDF संरचना में किसी एक बिंदु की लंबी time series extraction में भी 20–30 मिनट लग सकते हैं
  • single-row insert लगभग 3 हज़ार rows प्रति सेकंड तक पहुँचता है, इसलिए पूरा load करने में लगभग 8 साल लगेंगे; multi-row insert भी psycopg3 के साथ 25–30 हज़ार rows/second पर लगभग 10 महीने लेता है
  • PostgreSQL copy और psycopg3 cursor.copy() में CSV पहले से तैयार होने या न होने के आधार पर फ़र्क पड़ता है, और overhead सहित यह लगभग 100 हज़ार rows/second तक पहुँचता है, जिसे parallelization और tuning से और बढ़ाया जा सकता है
  • अगर डेटा सीधे dataframe से डालना हो तो psycopg3 से hypertable में direct copy सबसे उपयुक्त है; अगर CSV पहले से मौजूद हो तो timescaledb-parallel-copy बेहतर है, और इस सिस्टम में 12–16 workers parallelization का अच्छा संतुलन लगते हैं

वैश्विक मौसम डेटा warehouse बनाना

  • लक्ष्य ऐसा data warehouse बनाना है जिसमें दुनिया भर के ऐतिहासिक मौसम डेटा को query करके पहले से हो चुके climate change signals का विश्लेषण किया जा सके
  • उदाहरण के तौर पर यह देखा जा सकता है कि Jakarta वास्तव में ज़्यादा गर्म हुआ है या तूफ़ान ज़्यादा आने लगे हैं, Chile कुल मिलाकर ज़्यादा गर्म हुआ है या बादल बढ़े हैं, और अलग-अलग क्षेत्रों में बदलाव किस रूप में दिख रहे हैं
  • तेज़ global analysis के लिए data warehouse queries का तेज़ होना ज़रूरी है, और पहला कदम भारी मात्रा के डेटा को database में load करना है
  • आधार के रूप में PostgreSQL इस्तेमाल किया गया है, time-based query acceleration के लिए TimescaleDB और आगे geospatial queries को तेज़ करने के लिए PostGIS एक promising विकल्प है

ERA5 reanalysis डेटा और 754 अरब rows का पैमाना

  • वास्तविक मौसम observations की जगह ERA5 climate reanalysis डेटा इस्तेमाल किया गया है
    • ऐतिहासिक observations कुछ क्षेत्रों और समय बिंदुओं पर sparse हो सकते हैं
    • ERA5 observations के अनुरूप constrained climate model output है, और मौसम व climate research में व्यापक रूप से इस्तेमाल होता है
  • ERA5 पूरी पृथ्वी को 0.25-degree resolution पर cover करता है और 1940 से hourly resolution में उपलब्ध है
    • हर time snapshot में प्रति variable 727,080 values होती हैं
    • grid points की संख्या 1,038,240 है, जिसमें 1,440 longitudes और poles सहित 721 latitudes शामिल हैं
    • time और location के आधार पर index करने पर प्रति variable 753,836,544,000 rows, यानी लगभग 754 अरब rows बनती हैं
  • load किए जाने वाले variables हैं: temperature, east-west और north-south 10m wind speed, total cloud cover, precipitation, और snowfall
  • table में time, location_id, latitude, longitude और हर weather variable के columns रखे गए हैं
    • location_id को latitude/longitude columns के साथ रखने का कारण आगे queries और indexes की benchmark testing करना है

NetCDF file structure कहाँ धीमी पड़ती है

  • ERA5 NetCDF files में distribute होता है, और डेटा आमतौर पर daily, monthly, या yearly files में रखा जाता है
  • time-based chunk structure किसी specific समय का डेटा देखने के लिए तेज़ और सरल है
  • लेकिन किसी एक location की लंबी time series जैसे समय-आधारित pattern देखने हों, तो बहुत-सी files पढ़नी पड़ती हैं, इसलिए यह धीमा हो जाता है
    • उदाहरण के लिए, एक location की temperature time series निकालने में 20–30 मिनट लग सकते हैं
  • complex geospatial queries, खासकर time dimension वाली queries, धीमी और चलाने में कठिन हो जाती हैं
  • xarray, dask, Pangeo जैसे tools गति बढ़ा सकते हैं, लेकिन प्रक्रिया फिर भी धीमी रहती है

insert: single-row से multi-row तक

  • सबसे सरल तरीका insert से rows को एक-एक करके डालना है
  • single-row insert के साथ कई तरह की लागत जुड़ी होती है
    • PostgreSQL statement को parse करता है, table/column names validate करता है, और execution plan बनाता है
    • data integrity के लिए lock की ज़रूरत पड़ सकती है
    • WAL(write-ahead logging) के लिए data buffer में लिखा जाता है
    • फिर actual table disk area में data insert किया जाता है
    • transaction commit होने पर बदलाव स्थायी बनते हैं
  • Python में single-row insertion के लिए तीन तरीकों का benchmark किया गया
    • pandas df.to_sql() के साथ chunksize=1
    • psycopg3 की parameterized query
    • SQLAlchemy की parameterized query
  • single-row insertion में psycopg3 थोड़ा आगे रहा और SQLAlchemy सबसे धीमा था
    • TimescaleDB hypertable सामान्य PostgreSQL table से थोड़ा धीमा था
    • सर्वोत्तम प्रदर्शन लगभग 3 हज़ार rows/second रहा, यानी पूरा डेटा डालने में लगभग 8 साल लगेंगे
  • multi-row insert एक statement में कई rows डालकर network round trips, parsing और planning cost घटाता है
    • psycopg3 लगभग 25–30 हज़ार rows/second के साथ सबसे तेज़ था
    • pandas में dictionary-आधारित insertion, tuple की तुलना में धीमा हो सकता है
    • SQLAlchemy में session management और SQL expression abstraction जैसे अतिरिक्त overhead हो सकते हैं
    • फिर भी पूरा load करने में लगभग 0.8 साल, यानी क़रीब 10 महीने लगते हैं

copy: PostgreSQL का bulk loading path

  • PostgreSQL copy CSV या binary file से rows पढ़कर डालने की bulk loading सुविधा है
  • क्योंकि यह bulk loading के लिए optimized है, इसलिए parsing, planning और WAL usage को multi-row insert से बेहतर तरीके से संभालता है
  • दो paths की तुलना की गई
    • NetCDF डेटा को पहले CSV में save करके फिर copy से load करना
    • CSV file बनाए बिना psycopg3 cursor.copy() के जरिए सीधे PostgreSQL में stream करना
  • अगर CSV file पहले से तैयार हो, तो copy लगभग 400 हज़ार rows/second के insertion rate तक पहुँच सकता है
  • लेकिन CSV file लिखने या tuple generation का overhead जोड़ने पर copy और psycopg3 दोनों लगभग 100 हज़ार rows/second तक आते हैं, जिसमें psycopg3 थोड़ा तेज़ है
  • इस गति पर भी पूरा डेटा load करने में लगभग 3 महीने लगते हैं

sustained ingest rate और parallel copy

  • बहुत-सी rows डालते समय disk writes, WAL और table inserts के बीच I/O contention, autovacuum, और checkpoint जैसी bottlenecks आ सकती हैं
  • लगभग 77.2 करोड़ rows को 744 batches में डालने वाले experiment में single worker पर बहुत बड़ी slowdown नहीं दिखी
    • copy csv में बार-बार गिरावट दिखी और यह उतार-चढ़ाव के प्रति अधिक संवेदनशील था
    • psycopg3 अधिकांश समय तेज़ रहा
    • सामान्य table और hypertable के बीच फ़र्क बड़ा नहीं था
  • joblib के साथ कई copy jobs या psycopg3 cursors को parallel चलाया गया
  • एक ही table में insertion ऐसा काम नहीं है जो बहुत अच्छी तरह parallelize हो, और प्रदर्शन आमतौर पर 16 workers के बाद plateau हो जाता है

pg_bulkload और timescaledb-parallel-copy

  • PostgreSQL copy के अलावा pg_bulkload और timescaledb-parallel-copy का भी benchmark किया गया
  • pg_bulkload default settings में तेज़ दिखता है, लेकिन यह default रूप से shared buffers को bypass करता है और WAL logging को skip करता है, इसलिए crash के बाद data recovery संभव न हो सकती है
  • fsync बंद होने की समान condition में multiple workers वाला timescaledb-parallel-copy, pg_bulkload से बेहतर रहा
  • timescaledb-parallel-copy में workers की संख्या देकर parallel insertion किया जा सकता है
    • शुरुआती performance अच्छी थी, लेकिन इस सिस्टम में 100 million rows से पहले ही bottleneck आ गया, insertion rate तेज़ी से गिरा और फिर लहरों की तरह recover हुआ
    • sustained ingest rate सामान्य table में लगभग 600–700 हज़ार rows/second और hypertable में लगभग 300 हज़ार rows/second रहा
  • pg_bulkload में workers की संख्या सीधे नहीं दी जाती, लेकिन writer=parallel option के जरिए reading, parsing, और writing में multiple threads का उपयोग किया जाता है

PostgreSQL tuning और durability trade-offs

  • अतिरिक्त performance PostgreSQL की non-durable settings को adjust करके हासिल की जा सकती है
  • मुख्य settings हैं fsync को बंद करना ताकि disk flush न हो, और full_page_writes को बंद करना ताकि partial page write protection न हो
  • ये settings crash की स्थिति में database integrity को जोखिम में डाल सकती हैं
  • unlogged table WAL generate नहीं करता, इसलिए writes तेज़ होती हैं, लेकिन crash recovery के दौरान truncate हो सकता है
    • बाद में इसे सामान्य logged table में बदलना पड़ता है और यह प्रक्रिया धीमी तथा single-threaded हो सकती है
    • hypertable unlogged नहीं हो सकता, इसलिए अगर hypertable चाहिए तो अतिरिक्त conversion या migration करना होगा

अंतिम चयन: सीधे hypertable में डालना

  • अगर अंतिम लक्ष्य hypertable है, तो पहले सामान्य table में load करके फिर hypertable में convert करने की बजाय सीधे hypertable में load करना तेज़ है
  • लगभग 77.2 करोड़ rows को psycopg3 copy और 16 workers के साथ डालने वाले एक सरल test में hypertable direct insertion, सामान्य table insertion के बाद conversion से कम समय में पूरा हुआ
    • इस मामले में hypertable direct insertion को लगभग 80% समय लगा
    • conversion/migration प्रक्रिया तेज़ नहीं थी और single-threaded जैसी लगी
  • अनुशंसित तरीका यह है
    • अगर dataframes से सीधे load करना हो, तो psycopg3 के साथ hypertable में direct copy
    • अगर CSV files पहले से हों, तो timescaledb-parallel-copy का उपयोग करें
    • इस सिस्टम में parallelization के लिए 12–16 workers उपयुक्त लगते हैं

कुल benchmark निष्कर्ष और समय अनुमान

  • protection settings चालू होने पर single worker के साथ, overhead सहित sustained ingest rate की ऊपरी सीमा इस hardware पर लगभग 140 हज़ार rows/second दिखती है
  • कई workers के साथ psycopg3 copy cursor का उपयोग करके protection settings बनाए रखते हुए sustained ingest rate लगभग 250 हज़ार rows/second तक बढ़ाई जा सकती है
  • insertion process बहुत कुशलता से parallelize नहीं होती, और 4–16 workers का दायरा उपयुक्त दिखता है
  • अगर जोखिम स्वीकार कर fsync बंद किया जाए, तो psycopg3 के साथ लगभग 462 हज़ार rows/second की sustained rate मिल सकती है
  • pg_bulkload default रूप से fsync को disable करता है, इसलिए इसका उपयोग सावधानी से करना चाहिए
  • लगभग 462 हज़ार rows/second बनाए रखने पर करीब 754 अरब rows load करने में लगभग 20 दिन लगेंगे

code और benchmark environment

  • ERA5 download, table creation, insert/copy, benchmark, और graph generation का code timescaledb-insert-benchmarks में उपलब्ध है
  • हर benchmark के लिए एक नया Docker container शुरू किया गया ताकि environment consistent रहे
    • Docker containers के बीच storage preserve नहीं की गई
    • NetCDF और CSV files HDD से पढ़ी गईं
    • database NVMe SSD पर store किया गया
  • hardware configuration
    • 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
  • software configuration
    • Ubuntu 20.04, Linux kernel 5.15
    • PostgreSQL 15.5
    • TimescaleDB 2.13.0
    • pg_bulkload 3.1.20
  • PostgreSQL settings में timescaledb-tune द्वारा 250.57GB memory और 48 CPU के आधार पर सुझाए गए values का उपयोग किया गया, और benchmarks में WAL size को अलग से adjust किया गया

संबंधित पढ़ाई

2 टिप्पणियां

 
jangsc0000 2024-04-18

GN+ की राय में सम्मानसूचक भाषा है..?
 
GN⁺ 2024-04-17
Hacker News की रायें

  • काम के सिलसिले में मैंने geospatial analysis काफी किया है, और geospatial data उम्मीद से कहीं ज़्यादा सूक्ष्म होता है, क्योंकि coordinate reference system (CRS) और visualization projection को ठीक से समझना पड़ता है
    अगर metadata को data के साथ सही ढंग से ले जाने वाला infrastructure न हो, तो CRS की वजह से लगातार बेचैनी बनी रहती है
    AWS की संबंधित सुविधाएँ, Postgres/PostGIS, Spark/Databricks, Snowflake, Trino, ArcGIS—सब इस्तेमाल किए हैं, लेकिन बड़े पैमाने के geospatial काम के लिए मुझे Google BigQuery साफ तौर पर बेहतर लगता है
    विशाल m6a EC2 पर PostGIS में जिन queries में कई घंटे लगते थे और लागत भी काफी आती थी, वे BigQuery के free tier में 5 सेकंड से भी कम में हो गईं
    मैंने FEMA public data इस्तेमाल किया था; Snowflake और AWS services geometry column के maximum byte size से आगे निकलने पर अटक गए, Spark में geospatial data type ही नहीं है और open-source extensions भी कमजोर लगे
    on-premises हो तो स्थिति अलग हो सकती है, लेकिन 20TB के हिसाब से BigQuery storage cost भी शायद $100/माह से कम रहेगी, इसलिए मैं इसे खुद operate नहीं करना चाहूँगा

    • global OSM और Whosonfirst के साथ pipeline चलाते हुए मैंने भी कुछ ऐसा ही अनुभव किया, और Google की cost Airflow + BigQuery के साथ महीने में $7k तक पहुँच गई, इसलिए उसे एक बार में $7k के hardware खरीद से replace कर दिया
      शुरुआत में H3 index इस्तेमाल किया था और intermediate datasets पूरी तरह memory में आ जाते थे, इसलिए यह संभव हुआ
      setup था 128GB Mac Studio + Asahi Linux + mmap parquet files + DuckDB; Airflow भी चलाया, और Nix का इस्तेमाल developer builds तेज करने और data team की Airflow jobs चलाने में भी किया
      GCP जब free या सस्ता हो तो अच्छा है, लेकिन usage न बढ़े तब भी किसी मोड़ पर बड़ा bill देकर चौंका सकता है
    • Spark में geospatial data type नहीं है और open-source packages कमजोर हैं—इस हिस्से के बारे में और सुनना चाहूँगा
      क्या आपने Apache Sedona से तुलना की थी, और具体 तौर पर क्या कमी लगी, यह जानना चाहूँगा
      मैं Wherobots में काम करता हूँ, जिसे Apache Sedona के creators ने शुरू किया है, इसलिए feedback सुनना चाहता हूँ
      https://sedona.apache.org/latest/
      https://wherobots.com/
    • अगर specific dataset का link दे सकें तो अच्छा होगा
      मैं मानता हूँ कि बहुत चौड़े columns कई tools को तोड़ देते हैं, लेकिन दूसरे column-oriented Postgres derivatives शायद बिना समस्या support कर पाएँगे
      हैरानी है कि आपने BigQuery के सीधे competitor Redshift को इस्तेमाल नहीं किया; Redshift का super column BigQuery की maximum limit से भी बड़ा रखा जा सकता है
      मैंने अक्सर देखा है कि लोग मुश्किल तरीके से यह समझते हैं कि PostGIS को हराना आसान नहीं है
      Trino/Presto और Spark का इस क्षेत्र में ठहर जाना भी खास तौर पर बहुत कुछ बताता है
    • करीब 500GB data warehouse को 8-core Postgres RDS से BigQuery पर move किया; rebuild time 5 घंटे से घटकर 11 मिनट हो गया और cost लगभग समान या कम रही
      Postgres में बड़े tables के कुछ हिस्से cache किए थे और BigQuery में शुरुआत से फिर से बनाया था, फिर भी ऐसा हुआ
      self-host किए जा सकने वाले tools में मुझे Postgres ज़्यादा पसंद है, लेकिन performance का एक digit multiple से भी ज़्यादा बेहतर होना नकारना मुश्किल है
    • BigTable और BigQuery के बारे में बहुत अच्छी बातें सुनता हूँ, लेकिन अभी तक इन्हें आज़माने का मौका नहीं मिला, इसका अफसोस है

  • वाकई शानदार लेख है
    मैं Timescale में DevRel संभालता हूँ, और community से ऐसा अच्छी तरह लिखा गया लेख देखना अच्छा लगता है
    hypertable के धीमे होने की एक वजह लगभग निश्चित रूप से यह है कि वह timestamp column पर default रूप से index बनाता है
    सामान्य table में index नहीं था, इसलिए वह तेज रहा होगा
    create_hypertable में create_default_indexes=>false इस्तेमाल करें तो index creation skip कर सकते हैं, और data डालने से पहले index delete भी किया जा सकता है
    आखिरकार वह index चाहिए होगा, लेकिन ऐसे bulk load में loading के बाद उसे एक साथ बनाना बेहतर रहता है
    यह भी जानना चाहूँगा कि इतनी high-parallelism configuration में data पढ़ने वाला HDD कितना संभाल पाया

    • मुझे नहीं पता था कि create_default_indexes=>false होता है और hypertable default रूप से time index बनाता है; इस हिस्से को समझाने वाली comment जोड़ूँगा
      time index के बिना insert करके फिर manually index बनाने का benchmark भी करना चाहूँगा
      32 workers पर भी HDD ठीक लग रहा था
      btop में disk usage देखते समय लगा कि Postgres वाला SSD, HDD की तुलना में bottleneck के ज़्यादा करीब था, इसलिए मैंने निष्कर्ष निकाला कि data को HDD से SSD पर ले जाने की बजाय Postgres के लिए SSD को तेज वाले से बदलना बेहतर investment होगा

  • समझ नहीं आता कि इसे ऐसा करने की ज़रूरत ही क्यों है
    ERA5 सहित ज़्यादातर weather और climate datasets नियमित latitude-longitude grid पर काफ़ी structured होते हैं
    भले ही आप किसी खास location की time series ही निकाल रहे हों, ऐसे datasets की ताकत उनकी अंतर्निहित spatiotemporal structure और context में होती है; और अगर मकसद सिर्फ़ point time series निकालना नहीं है, तो उस structure को पूरी तरह तोड़ देना ज़्यादा समझदारी नहीं लगता
    यहां तक कि केवल point time series निकालने के मामले में भी, उदाहरण के लिए समुद्र के बीचोंबीच surface temperature time series जैसी चीज़ शायद ही कभी काम आएगी, इसलिए आप data को काफी आक्रामक तरीके से घटाना चाहेंगे
    ERA5 जैसे datasets के research और operational उपयोगों में ज़्यादातर मामलों के लिए Google Public Datasets के ARCO-ERA5 जैसे मूल structure को बचाए रखने वाले cloud-optimized replicas ज़्यादा उपयुक्त लगते हैं
    ऐसे versions मूल structure बनाए रखते हुए cloud storage में बड़े पैमाने पर parallel access के लिए chunks में बांटते हैं
    अपने career में मैंने लगभग हर मामले में देखा है कि Zarr-based सामान्य chunking वाला archive ही दिलचस्प अधिकतर use cases के लिए पर्याप्त तेज़ होता है
    https://cloud.google.com/storage/docs/public-datasets/era5

    • मुख्य वजह यह है कि यह personal project था, इसलिए cloud resources का खर्च दिए बिना home server पर सब कुछ आज़माना चाहता था, और Postgres, TimescaleDB, बाद में PostGIS तक सीखना चाहता था
      हालांकि rabernat ने जैसा कहा, cloud replica से लंबी time series निकालना भी धीमा है
      आखिरकार मैं 1940~1980 के Chile summer temperatures के 99th percentile की गणना जैसे जटिल spatiotemporal queries भी करना चाहता हूं
      मुझे शक नहीं कि cloud replica तेज़ हो सकता है, लेकिन यह $0 budget से टकराता है
    • सही है, लेकिन असल में Google ERA5 public data भी ठीक वही chunking problem झेलता है जो लेख में बताई गई है
      यह spatial queries के लिए optimized है, time series queries के लिए नहीं
      मैंने अभी benchmark चलाया, और एक point की single variable time series लाने में 20 मिनट लगे
      यह अच्छी तरह दिखाता है कि अगर expected usage pattern time series है, तो time-series-optimized chunking ज़रूरी है
    • अच्छा होगा अगर कोई यह कर दे और दूसरों को इसे इस्तेमाल करना भी सिखा दे
      कुछ labs के पास पहले से public algorithms और data से जुड़ी RDBMS-based pipelines हैं, और कोई भी उन्हें फिर से implement नहीं करना चाहता, न ही उसके लिए budget है
      हमें मिला सबसे अच्छा सुधार बस पुराने MySQL से Postgres + PostGIS पर shift करना था
      Timescale भी शायद मददगार होता
      privacy, cluster access, budget जैसी वजहों से local पर चलाने के कारण भी थे

  • अच्छा लेख है
    मेरे हिसाब से यहां जो चीज़ छूट गई है, वह यह analysis है कि weather data को relational database में ले जाने से हासिल क्या होता है
    motivation query speed बढ़ाना है, लेकिन baseline क्या है, यह चाहिए
    Xarray और Zarr maintainer तथा https://earthmover.io/ के founder के रूप में मैं इस technology landscape से काफी परिचित हूं; Zarr में data को सही तरह chunk किया जाए तो serverless solution + object storage भर से weather data की time series queries sub-second latency में handle की जा सकती हैं
    लेख में बताए गए 30 मिनट से यह बहुत तेज़ है
    इस लेख में दिखी data loading की कठिनाई को देखते हुए, RDBMS route पर जाने से पहले ऐसे solution का गंभीरता से मूल्यांकन करना उचित होगा

    • मैं सहमत हूं कि सही तरह chunk की हुई Zarr file में store करना लगभग निश्चित रूप से तेज़ होगा, setup भी सरल होगा, और space भी कम लेगा
      आगे API लगाकर उसे query जैसा बनाया जा सकता है
      यह भी सही है कि RDBMS approach को पर्याप्त रूप से justify नहीं किया गया
      Postgres + Timescale पर जाने की मुख्य वजह यह थी कि मैं इन्हें सीखना चाहता था, और ERA5 data के साथ experiment करना सबसे मज़ेदार लग रहा था
      weather data इतना बड़ा है कि challenge बन सके, इसमें भी शायद आकर्षण था
      मेरे पास evidence नहीं है, लेकिन यह जानने की उत्सुकता है कि अच्छी तरह tuned और indexed TimescaleDB + PostGIS, 1940~1980 के Chile summer temperatures के 99th percentile जैसी जटिल spatiotemporal queries में मदद करेगा या नहीं
      क्योंकि इस case में कई Zarr chunks पढ़ने पड़ सकते हैं
      ऐसे statistics cache करने के लिए अलग table रखने का idea भी मुझे पसंद है, हालांकि Zarr से भी यह इतना मुश्किल नहीं है
      अगली बार queries और indexes का benchmark करने वाला हूं, तो शायद और पता चलेगा
    • थोड़ा विषय से हटकर है, लेकिन मेरी रुचि भी इसी area में है
      लगता है कि एक fundamental tension है: बड़े chunks wide-area visualization और large queries के लिए अच्छे हैं, जबकि छोटे chunks point-based या time series queries के लिए अच्छे हैं
      ऐसे बड़े geospatial datasets को अलग-अलग chunking versions में अलग से रखना संभव है, लेकिन cost-efficient नहीं है
      सुना है कि दोनों के फायदे पाने के लिए kerchunk इस्तेमाल किया जाता है, लेकिन ऐसा करने पर data compression choices खो जाती हैं और complexity भी काफी बढ़ जाती है, ऐसा मुझे लगता है
      कई use cases के बीच balance कैसे रखना बेहतर होगा, यह जानने की उत्सुकता है

  • यहां संदर्भित Postgres bulk load दस्तावेज़ में मैंने योगदान दिया था; यह कई तकनीकों का अच्छा अवलोकन करता है
    OpenStreetMap डेटाबेस की loading तेज़ करने के लिए मैंने इस तरह का काम काफ़ी किया है, और आख़िरी सार्वजनिक update presentation https://www.youtube.com/watch?v=BCMnu7xay2Y पर है
    उसके बाद hardware में सुधार, PG15 के GIS improvements, और osm2pgsql द्वारा middle-way-node-index-id-shift तकनीक अपनाने की वजह से planet set की loading time 4 घंटे से नीचे आ गई
    लेखक के लिए एक सुझाव: कुछ experiments pg_bulkload और COPY के ज़रिए अप्रत्यक्ष रूप से WAL writes को हटा रहे हैं
    Craig Ringer ने linked SO post लिखी थी, उस समय यह documented नहीं था, लेकिन settings में WAL को सीधे बंद किया जा सकता है
    बेशक crash हुआ तो current table खो जाएगी, और अगर job में कई हफ्ते लगें तो ऐसा हो सकता है
    लेकिन time-series data में loading structure सही बनाया जाए तो सिर्फ़ आख़िरी chunk खोना मुश्किल नहीं है
    bulk load के लिए WAL data असल में ज़रूरी नहीं होता
    crash हो तो loaded data के right edge को clean up करके फिर से शुरू कर दें
    WAL और अन्य overhead बंद करने के लिए मैं ये postgresql.conf settings इस्तेमाल करता हूं:
    wal_level = minimal
    max_wal_senders = 0
    synchronous_commit = off
    fsync = off
    full_page_writes = off
    autovacuum = off
    checkpoint_timeout = 60min
    आखिर में, बड़े chunks में load करते समय vacuum work कम करने के लिए आम तौर पर ऊपर की तरह autovacuum बंद करता हूं, और अभी load हो रहे date partition के पीछे-पीछे periodic रूप से VACUUM FREEZE चलाता हूं
    यह सामान्य PG के लिए है, और database को उन कुछ कामों को skip करने देता है जिनमें वह इस intermediate stage की परवाह करता है कि नए transactions लिखे जा चुके हैं लेकिन अभी सबको visible नहीं हैं

    • WAL और दूसरे overhead बंद करने वाली settings आज़माकर देखूंगा कि inserts तेज़ होते हैं या नहीं
      खासकर chunk-based loading में bulk load के लिए WAL data सचमुच ज़रूरी नहीं है—यह किसी expert से सुनकर अच्छा लगा
      मेरे पास अभी UPS नहीं है, लेकिन उम्मीद है कि data load में लगने वाले करीब 20 दिन बिना power cut के निकल जाएंगे; worst case में भी शायद बस आगे से continue कर दूंगा
    • PG15 के GIS improvements के बारे में और पढ़ने के लिए कोई material है क्या, यह जानना चाहूंगा

  • अगर OP ही लेखक हैं, तो लगभग 4 साल पहले किया गया मेरा मिलता-जुलता experiment भी दिलचस्प हो सकता है
    वही dataset, वही target, और मिलते-जुलते goals थे
    https://rdrn.me/optimising-sql/
    investigation flow काफ़ी मिलता-जुलता है, लेकिन Timescale की जगह plain Postgres इस्तेमाल किया था, और मेरी configuration में data पहले से memory में है यह मानकर binary data की direct copy से COPY की तुलना में करीब 3x तेज़ी मिली

    • काश शुरू करने से पहले यह देख लिया होता
      मैंने footnote में लिखा है कि binary COPY क्यों try नहीं किया; मूल वजह यह थी कि किसी और ने इसका performance निराशाजनक बताया था
      फिर भी लगता है इसे खुद try करना चाहिए
      https://aliramadhan.me/2024/03/31/trillion-rows.html#fn:copy-binary-note
    • मैंने part 1 और part 2 पढ़े, लेख मज़ेदार था
      margin notes वाला format भी अच्छा लगा
      numpy structured arrays को Postgres binary में लिखने वाला function देने से मदद मिली; पहले मैं यह figure out नहीं कर पाया था

  • “क्या gridded weather data के लिए relational database सही fit है? पता नहीं, लेकिन करके देखेंगे तो पता चलेगा।”
    यह attitude अच्छा है
    दूसरे mainstream tech articles के “असल बात तो यह है” वाले style से बिल्कुल उल्टा है, इसलिए पसंद आया
    journey के दौरान reader को आखिर तक बांधे रखने वाली बात भी अच्छी है

    • beginner होने की वजह से कोई vested interest नहीं है, यह फायदा भी हो सकता है
      मैंने काफी search किया लेकिन अपने use case के लिए कोई निर्णायक जवाब नहीं मिला, इसलिए खुद benchmark चलाने का फैसला किया

  • दिलचस्प लेख है
    “लगातार करीब 462k inserts/sec हो तो लगभग 754 billion rows में करीब 20 दिन लगेंगे, जो बुरा नहीं लगता। यह post लिखने में लगे समय से कम है।” वाला हिस्सा मज़ेदार है
    मैं भी लंबे और गहरे blog posts लिखने की तरफ झुक रहा हूं, इसलिए इस बात से सहमत हूं कि इसमें उम्मीद से ज्यादा मेहनत लगती है

    • benchmarks में से कुछ कई-कई घंटे चले और कुछ को दोबारा चलाना पड़ा, और इस process में बहुत कुछ सीखने को मिला

  • अगर आप SQL query से सीधे time-series chart या कई charts बनाना चाहते हैं, तो qStudio एक free SQL IDE है और TimescaleDB समेत कई DBs के साथ काम करता है
    https://www.timestored.com/qstudio/database/timescale
    disclaimer: यह tool मैंने बनाया है

    • qStudio में किसी और database का support जोड़ने की प्रक्रिया के बारे में जानना चाहूंगा
      सोच रहा हूं कि शायद Timeplus support जोड़ा जा सके
      Timeplus, ClickHouse-based streaming-first database है, और इसका core DB engine Timeplus Proton open source है
      qStudio भी open source है और Java में लिखा है, इसलिए नए RDBMS support के लिए JDBC driver चाहिए लगता है
      अगर सही समझ रहा हूं, तो Timeplus Proton के पास ClickHouse driver पर आधारित, streaming use case के लिए modifications वाला open-source JDBC driver है
      https://www.timeplus.com/
      https://github.com/timeplus-io/proton
      https://github.com/timeseries/qstudio
      https://github.com/timeplus-io/proton-java-driver
    • मैं मुख्य रूप से TablePlus और matplotlib को psycopg3 के ज़रिए इस्तेमाल करके query results plot करता रहा हूं, लेकिन यह ज्यादा quick-to-use लगता है
      अभी तक केवल data insertion किया है, लेकिन जल्द ही queries और plotting try करूंगा

  • मैं भी ERA5 reanalysis data इस्तेमाल करता/करती हूँ और तेज़ time series की ज़रूरत होती है
    डेटा चुनी गई अवधि के हिसाब से जमा हुए [lat, lon] grid के रूप में आता है, जैसे [एक महीने का hourly data, lat, lon], इसलिए अगर 20 साल से ज़्यादा चाहिए तो यह एक बहुत बड़ी matrix transpose समस्या बन जाती है
    मेरा तरीका है कि हर netCDF file डाउनलोड करता/करती हूँ, उसे transpose करता/करती हूँ, फिर [lat, lon, hour] से बने एक विशाल 3D HDF file में डाल देता/देती हूँ
    मेरे workstation पर एक variable का 1 साल का data बनाने में करीब 30 मिनट लगते हैं, लेकिन उसके बाद किसी single (lat, lon) location को fetch करना milliseconds में हो जाता है
    यह पहले मेहनत करके लंबे समय में फायदा लेने वाला तरीका है
    तरीका सरल है, लेकिन मैं database expert नहीं, बस एक climatologist हूँ

    • सरल है, लेकिन relational database से ज़्यादा तेज़ और space-efficient होने की संभावना भी अधिक है
      लगता है कि यहाँ comment करने वाले rabernat और open-meteo भी मिलते-जुलते तरीके इस्तेमाल करते हैं और उसे तेज़ मानते हैं