โหลดข้อมูลสภาพอากาศ 1 ล้านล้านแถวเข้าสู่ TimescaleDB

 (aliramadhan.me)
3 คะแนน โดย GN⁺ 2024-04-17 | 2 ความคิดเห็น | แชร์ทาง WhatsApp
  • หากต้องการวิเคราะห์การเปลี่ยนแปลงสภาพอากาศในอดีตทั่วโลกได้อย่างรวดเร็ว จำเป็นต้องนำข้อมูลรีอะนาไลซิส ERA5 เข้าไปไว้ใน PostgreSQL/TimescaleDB และเมื่ออิงตามตัวแปรและกริดที่เลือก จะมีขนาดประมาณ 754 พันล้านแถว
  • ERA5 ให้ข้อมูลตั้งแต่ปี 1940 ด้วยความละเอียดรายชั่วโมงและกริดละติจูด-ลองจิจูด 0.25 องศา แต่ในโครงสร้าง NetCDF การ ดึง time series ระยะยาวของจุดเดียวก็อาจใช้เวลา 20–30 นาที
  • insert แบบแถวเดียวทำได้ราว 3 พันแถวต่อวินาที จึงต้องใช้เวลาประมาณ 8 ปีในการโหลดทั้งหมด ส่วน insert หลายแถวตามเกณฑ์ของ psycopg3 ทำได้ 25,000–30,000 แถวต่อวินาที หรือราว 10 เดือน
  • PostgreSQL copy และ psycopg3 cursor.copy() มีความต่างกันตามว่ามีการเตรียม CSV ไว้แล้วหรือไม่ โดยเมื่อรวมโอเวอร์เฮดแล้วจะอยู่ที่ราว 100,000 แถวต่อวินาที และยังดันต่อได้อีกด้วยการรันแบบขนานและการปรับตั้งค่า
  • หากใส่ข้อมูลจากดาต้าเฟรมโดยตรง ควรใช้ copy ด้วย psycopg3 ลง hypertable โดยตรง แต่ถ้ามีไฟล์ CSV อยู่แล้ว timescaledb-parallel-copy จะเหมาะกว่า และในระบบนี้ 12–16 workers ดูเป็นจุดที่เหมาะสมสำหรับการทำงานแบบขนาน

สร้างคลังข้อมูลสภาพอากาศทั่วโลก

  • เป้าหมายคือสร้างคลังข้อมูลสำหรับ query ข้อมูลสภาพอากาศในอดีตทั่วโลก เพื่อวิเคราะห์ สัญญาณการเปลี่ยนแปลงสภาพภูมิอากาศ ที่เกิดขึ้นแล้ว
  • ตัวอย่างการวิเคราะห์ เช่น จาการ์ตาร้อนขึ้นจริงหรือพายุถี่ขึ้นหรือไม่, ทั้งประเทศชิลีอุ่นขึ้นหรือมีเมฆมากขึ้นหรือไม่, และการเปลี่ยนแปลงในแต่ละภูมิภาคมีลักษณะอย่างไร
  • หากต้องการวิเคราะห์ระดับโลกได้เร็ว query ของคลังข้อมูลก็ต้องเร็วด้วย และขั้นตอนแรกคือการโหลดข้อมูลจำนวนมหาศาลเข้าสู่ฐานข้อมูล
  • ระบบนี้ใช้ PostgreSQL เป็นฐาน โดยมี TimescaleDB เป็นตัวเลือกที่น่าสนใจสำหรับเร่ง query ตามเวลา และในอนาคต PostGIS ก็น่าสนใจสำหรับเร่ง query เชิงภูมิสารสนเทศ

ข้อมูลรีอะนาไลซิส ERA5 และขนาด 754 พันล้านแถว

  • ใช้ข้อมูล ERA5 climate reanalysis แทนค่าการสังเกตสภาพอากาศจริง
    • ข้อมูลสังเกตย้อนหลังอาจมีน้อยในบางพื้นที่และบางช่วงเวลา
    • ERA5 คือผลลัพธ์จากแบบจำลองสภาพภูมิอากาศที่ถูกจำกัดให้สอดคล้องกับข้อมูลสังเกต และถูกใช้อย่างกว้างขวางในงานวิจัยด้านอุตุนิยมวิทยาและภูมิอากาศ
  • ERA5 ครอบคลุมทั้งโลกที่ความละเอียด 0.25 องศา และให้ข้อมูลรายชั่วโมงตั้งแต่ปี 1940
    • สแนปช็อตต่อเวลามี 727,080 ค่าในแต่ละตัวแปร
    • จุดกริดมี 1,038,240 จุด ประกอบด้วยลองจิจูด 1,440 ค่า และละติจูด 721 ค่า รวมขั้วโลกด้วย
    • เมื่อนำมาทำดัชนีตามเวลาและตำแหน่ง จะได้ 753,836,544,000 แถวต่อหนึ่งตัวแปร หรือราว 754 พันล้านแถว
  • ตัวแปรที่จะโหลด ได้แก่ อุณหภูมิ, ความเร็วลม 10 เมตรในแนวตะวันออก-ตะวันตกและเหนือ-ใต้, ปริมาณเมฆรวม, ปริมาณฝน และปริมาณหิมะสะสม
  • ในตารางจะมีคอลัมน์ time, location_id, latitude, longitude และคอลัมน์ของตัวแปรสภาพอากาศแต่ละตัว
    • ที่เก็บทั้ง location_id และคอลัมน์ละติจูด-ลองจิจูดไว้พร้อมกัน เพราะต้องการใช้สำหรับ benchmark query และดัชนีในภายหลัง

จุดที่โครงสร้างไฟล์ NetCDF ทำให้ช้าลง

  • ERA5 ถูกแจกจ่ายเป็นไฟล์ NetCDF โดยทั่วไปข้อมูลจะถูกเก็บแยกเป็นไฟล์รายวัน รายเดือน หรือรายปี
  • โครงสร้างแบบ chunk ตามเวลาเหมาะกับการเรียกดูข้อมูล ณ ช่วงเวลาหนึ่ง เพราะเร็วและเรียบง่าย
  • แต่ถ้าต้องการดูรูปแบบตามเวลา เช่น time series ระยะยาวของจุดใดจุดหนึ่ง จะต้องอ่านหลายไฟล์มาก จึงช้า
    • ตัวอย่างเช่น การดึง time series อุณหภูมิของตำแหน่งหนึ่งอาจใช้เวลา 20–30 นาที
  • query เชิงภูมิสารสนเทศที่ซับซ้อน โดยเฉพาะ query ที่มีแกนเวลา มักช้าและทำได้ยาก
  • เครื่องมืออย่าง xarray, dask, Pangeo ช่วยเพิ่มความเร็วได้ แต่กระบวนการก็ยังช้าอยู่ดี

insert: จากแถวเดียวไปสู่หลายแถว

  • วิธีที่ง่ายที่สุดคือใช้ insert เพื่อใส่ทีละแถว
  • insert แบบแถวเดียวมีต้นทุนหลายอย่าง
    • PostgreSQL ต้อง parse คำสั่ง ตรวจสอบชื่อตารางและคอลัมน์ และสร้าง execution plan
    • อาจต้องมี lock เพื่อรักษาความถูกต้องของข้อมูล
    • ต้องเขียนข้อมูลลงบัฟเฟอร์สำหรับ WAL (write-ahead logging)
    • ต้องแทรกข้อมูลลงพื้นที่ดิสก์ของตารางจริง
    • เมื่อ commit ธุรกรรม การเปลี่ยนแปลงจึงจะถูกทำให้ถาวร
  • ใน Python มีการ benchmark 3 วิธีสำหรับการแทรกแถวเดียว
    • ใช้ pandas df.to_sql() พร้อม chunksize=1
    • ใช้ parameterized query ของ psycopg3
    • ใช้ parameterized query ของ SQLAlchemy
  • ผลการแทรกแถวเดียวพบว่า psycopg3 เร็วกว่าเล็กน้อย และ SQLAlchemy ช้าที่สุด
    • hypertable ของ TimescaleDB ช้ากว่าตาราง PostgreSQL ปกติเล็กน้อย
    • ประสิทธิภาพสูงสุดอยู่ที่ราว 3 พันแถวต่อวินาที ซึ่งหมายความว่าต้องใช้เวลาประมาณ 8 ปีในการใส่ข้อมูลทั้งหมด
  • insert แบบหลายแถวจะใส่หลายแถวในคำสั่งเดียว เพื่อลดต้นทุนจาก network round-trip, การ parse และการวางแผน
    • psycopg3 เร็วที่สุดที่ 25,000–30,000 แถวต่อวินาที
    • pandas อาจช้ากว่าเพราะโครงสร้างการแทรกใช้ dictionary แทน tuple
    • SQLAlchemy อาจมีโอเวอร์เฮดเพิ่มจากการจัดการ session และ abstraction ของ SQL expression
    • ถึงอย่างนั้นการโหลดทั้งหมดก็ยังต้องใช้เวลาราว 0.8 ปี หรือเกือบ 10 เดือน

copy: เส้นทางโหลดข้อมูลจำนวนมากของ PostgreSQL

  • PostgreSQL copy เป็นความสามารถสำหรับโหลดข้อมูลจำนวนมากโดยอ่านแถวจากไฟล์ CSV หรือไบนารี
  • เพราะออกแบบมาสำหรับการโหลดข้อมูลจำนวนมาก จึงปรับ parse, การวางแผน และการใช้ WAL ให้เหมาะสม ทำให้เร็วกว่าการ insert หลายแถว
  • มีการเปรียบเทียบ 2 เส้นทาง
    • บันทึกข้อมูล NetCDF ออกมาเป็น CSV แล้วค่อยโหลดด้วย copy
    • ไม่สร้างไฟล์ CSV แต่สตรีมตรงเข้า PostgreSQL ด้วย psycopg3 cursor.copy()
  • หากมีไฟล์ CSV เตรียมไว้อยู่แล้ว copy จะทำความเร็วการแทรกได้เกือบ 400,000 แถวต่อวินาที
  • แต่เมื่อรวมโอเวอร์เฮดจากการเขียนไฟล์ CSV หรือการสร้าง tuple แล้ว ทั้ง copy และ psycopg3 จะอยู่ที่ประมาณ 100,000 แถวต่อวินาที โดย psycopg3 เร็วกว่าเล็กน้อย
  • แม้ที่ความเร็วนี้ การโหลดข้อมูลทั้งหมดก็ยังต้องใช้เวลาประมาณ 3 เดือน

อัตราการโหลดต่อเนื่องและ copy แบบขนาน

  • เมื่อใส่ข้อมูลจำนวนมาก จะเกิดคอขวดได้จากการเขียนดิสก์, การแย่ง I/O ระหว่าง WAL กับการแทรกลงตาราง, autovacuum และ checkpoint
  • ในการทดลองใส่ข้อมูลราว 772 ล้านแถวเป็น 744 ชุดงาน ไม่พบการชะลอตัวหนักสำหรับกรณี worker เดียว
    • copy csv มีการตกลงของความเร็วบ่อยกว่าและไวต่อความผันผวนมากกว่า
    • psycopg3 โดยรวมเร็วกว่า
    • ความต่างระหว่างตารางปกติกับ hypertable ไม่มาก
  • ใช้ joblib เพื่อรันงาน copy หลายชุดหรือรัน psycopg3 cursor หลายตัวแบบขนาน
  • การแทรกลงตารางเดียวไม่ใช่งานที่ขยายแบบขนานได้ดีนัก และประสิทธิภาพมัก เริ่มตันหลัง 16 workers

pg_bulkload และ timescaledb-parallel-copy

  • นอกจาก PostgreSQL copy แล้ว ยัง benchmark pg_bulkload และ timescaledb-parallel-copy ด้วย
  • pg_bulkload ดูเหมือนจะเร็วกว่าในการตั้งค่าปริยาย แต่ค่าเริ่มต้นของมันจะข้าม shared buffers และข้าม WAL logging ทำให้หลังเกิด crash อาจกู้ข้อมูลไม่ได้
  • เมื่อทดสอบภายใต้เงื่อนไขเดียวกันโดยปิด fsync พบว่า timescaledb-parallel-copy ที่ใช้หลาย workers ทำได้ดีกว่า pg_bulkload
  • timescaledb-parallel-copy สามารถระบุจำนวน workers เพื่อแทรกแบบขนานได้
    • ช่วงแรกประสิทธิภาพดี แต่ในระบบนี้จะชนคอขวดก่อนถึง 100 ล้านแถว ทำให้อัตราการแทรกลดฮวบแล้วค่อยฟื้นกลับมาเป็นคลื่น
    • อัตราการโหลดต่อเนื่องอยู่ที่ประมาณ 600,000–700,000 แถวต่อวินาทีสำหรับตารางปกติ และราว 300,000 แถวต่อวินาทีสำหรับ hypertable
  • pg_bulkload แม้จะไม่กำหนดจำนวน workers โดยตรง แต่มีตัวเลือก writer=parallel เพื่อใช้หลายเธรดกับการอ่าน การ parse และการเขียน

การปรับตั้งค่า PostgreSQL และการแลกความทนทาน

  • ประสิทธิภาพเพิ่มเติมสามารถได้มาจากการปรับ การตั้งค่าที่ไม่รับประกันความทนทาน ของ PostgreSQL
  • ค่าหลักคือการปิด fsync เพื่อลดการ flush ลงดิสก์ และปิด full_page_writes เพื่อลดการป้องกัน partial page write
  • การตั้งค่าเหล่านี้อาจทำให้ความสมบูรณ์ของฐานข้อมูลตกอยู่ในความเสี่ยงหากเกิด crash
  • unlogged table ไม่สร้าง WAL จึงเขียนได้เร็วกว่า แต่หลังการกู้คืนจาก crash อาจถูกตัดข้อมูลทิ้งได้
    • หลังจากนั้นต้องแปลงกลับเป็น logged table ปกติ ซึ่งกระบวนการนี้ช้าและอาจทำงานแบบเธรดเดียว
    • hypertable ไม่สามารถเป็น unlogged ได้ ดังนั้นหากต้องการ hypertable จะต้องมีขั้นตอนแปลงหรือย้ายข้อมูลเพิ่ม

ตัวเลือกสุดท้าย: ใส่ลง hypertable โดยตรง

  • หากเป้าหมายคือ hypertable การใส่ข้อมูลลง hypertable โดยตรงจะเร็วกว่าวิธีใส่ลงตารางปกติก่อนแล้วค่อยแปลงเป็น hypertable
  • จากการทดสอบแบบง่ายด้วยข้อมูลราว 772 ล้านแถว โดยใช้ psycopg3 copy และ 16 workers การแทรกลง hypertable โดยตรงใช้เวลาน้อยกว่าการแทรกลงตารางปกติแล้วค่อยแปลง
    • ในกรณีนี้ การแทรกลง hypertable โดยตรงใช้เวลาประมาณ 80% ของอีกวิธี
    • กระบวนการแปลงและ migration ไม่ได้เร็ว และดูเหมือนทำงานแบบเธรดเดียว
  • วิธีที่แนะนำมีดังนี้
    • หากโหลดจากดาต้าเฟรมโดยตรง ให้ใช้ psycopg3 copy ลง hypertable โดยตรง
    • หากมีไฟล์ CSV อยู่แล้ว ให้ใช้ timescaledb-parallel-copy
    • สำหรับการทำงานแบบขนาน ในระบบนี้ 12–16 workers ดูเป็นจุดที่เหมาะสม

สรุปผล benchmark และเวลาที่ต้องใช้ทั้งหมด

  • ในกรณี worker เดียวและเปิดการตั้งค่าป้องกันข้อมูลไว้ อัตราการโหลดต่อเนื่องสูงสุดเมื่อรวมโอเวอร์เฮดบนฮาร์ดแวร์นี้อยู่ที่ประมาณ 140,000 แถวต่อวินาที
  • หากใช้หลาย workers จะสามารถดันอัตราการโหลดต่อเนื่องด้วย psycopg3 copy cursor ไปได้ถึงราว 250,000 แถวต่อวินาที โดยยังคงการตั้งค่าป้องกันข้อมูลไว้
  • กระบวนการแทรกไม่ได้ขยายแบบขนานได้มากนัก และช่วงที่เหมาะสมอยู่ที่ 4–16 workers
  • หากยอมรับความเสี่ยงและปิด fsync จะสามารถทำต่อเนื่องได้ราว 462,000 แถวต่อวินาที ด้วย psycopg3
  • pg_bulkload ปิด fsync โดยปริยาย จึงต้องใช้อย่างระมัดระวัง
  • หากทำได้ต่อเนื่องที่ประมาณ 462,000 แถวต่อวินาที การโหลดข้อมูลราว 754 พันล้านแถวจะใช้เวลาประมาณ 20 วัน

โค้ดและสภาพแวดล้อม benchmark

  • โค้ดสำหรับดาวน์โหลด ERA5, สร้างตาราง, แทรกข้อมูลและ copy, ทำ benchmark และสร้างกราฟ อยู่ที่ timescaledb-insert-benchmarks
  • ในแต่ละ benchmark จะเปิด Docker container ใหม่เพื่อคงสภาพแวดล้อมให้สม่ำเสมอ
    • ไม่มีการคง storage ข้ามระหว่าง Docker container
    • อ่านไฟล์ NetCDF และ CSV จาก HDD
    • ฐานข้อมูลเก็บไว้บน NVMe SSD
  • สเปกฮาร์ดแวร์
    • 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
  • ซอฟต์แวร์ที่ใช้
    • Ubuntu 20.04, Linux kernel 5.15
    • PostgreSQL 15.5
    • TimescaleDB 2.13.0
    • pg_bulkload 3.1.20
  • การตั้งค่า PostgreSQL ใช้ค่าที่ timescaledb-tune แนะนำสำหรับหน่วยความจำ 250.57GB และ CPU 48 ตัว และในการ benchmark มีการปรับขนาด WAL แยกต่างหาก

บทความที่เกี่ยวข้อง

2 ความคิดเห็น

 
jangsc0000 2024-04-18

ความเห็นของ GN+ เป็นภาษาสุภาพเหรอครับ..?
 
GN⁺ 2024-04-17
ความคิดเห็นบน Hacker News

  • เคยทำงานด้านการวิเคราะห์เชิงภูมิสารสนเทศมาพอสมควร และข้อมูลภูมิสารสนเทศมีความละเอียดอ่อนกว่าที่คิดมาก เพราะต้องเข้าใจทั้งระบบพิกัดอ้างอิง (CRS) และการฉายภาพเพื่อการแสดงผลให้ถูกต้อง
    ถ้าไม่มีโครงสร้างพื้นฐานที่พาเมทาดาทาไปพร้อมกับข้อมูลอย่างเหมาะสม ก็จะรู้สึกไม่มั่นใจกับเรื่อง CRS อยู่ตลอด
    เคยใช้มาหมดแล้วทั้งฟีเจอร์ที่เกี่ยวข้องของ AWS, Postgres/PostGIS, Spark/Databricks, Snowflake, Trino และ ArcGIS แต่สำหรับงานภูมิสารสนเทศขนาดใหญ่ มองว่า Google BigQuery เหนือกว่าชัดเจน
    คิวรีที่เคยใช้เวลาหลายชั่วโมงและมีค่าใช้จ่ายสูงบน PostGIS ที่รันบน EC2 m6a ขนาดใหญ่ กลับใช้เวลาไม่ถึง 5 วินาทีบน BigQuery free tier
    ตอนนั้นใช้ข้อมูลสาธารณะของ FEMA โดย Snowflake และบริการของ AWS ติดปัญหาเพราะคอลัมน์ geometry มีขนาดเกินไบต์สูงสุดที่รองรับ, Spark ก็ไม่มีชนิดข้อมูลภูมิสารสนเทศ และส่วนขยายโอเพนซอร์สก็ยังน่าผิดหวัง
    ถ้าเป็น on-premises สถานการณ์อาจต่างออกไป แต่ที่ขนาด 20TB ค่าเก็บข้อมูลของ BigQuery ก็น่าจะต่ำกว่า $100 ต่อเดือนอยู่ดี เลยไม่ค่อยอยากดูแลระบบเอง

    • เคยผ่านประสบการณ์คล้ายกันตอนทำ pipeline กับ Global OSM และ Whosonfirst แล้วค่าใช้จ่ายฝั่ง Google ขึ้นไปถึง $7k ต่อเดือนสำหรับ Airflow + BigQuery เลยเปลี่ยนมาเป็นซื้อฮาร์ดแวร์ราคา $7k ครั้งเดียวแทน
      ตอนแรกใช้ดัชนี H3 และเพราะชุดข้อมูลระหว่างทางทั้งหมดขึ้นมาอยู่ในหน่วยความจำได้ จึงทำแบบนั้นได้
      สแตกที่ใช้คือ 128GB Mac Studio + Asahi Linux + ไฟล์ parquet แบบ mmap + DuckDB และยังใช้รัน Airflow รวมถึงใช้ Nix เพื่อเร่งบิลด์สำหรับนักพัฒนาและรันงาน Airflow ของทีมข้อมูลด้วย
      GCP นั้นดีมากตอนที่ยังฟรีหรือราคาถูก แต่ถึงการใช้งานจะไม่ได้เพิ่มขึ้น สักวันหนึ่งก็อาจทำให้ตกใจกับบิลที่ใหญ่ขึ้นได้
    • อยากฟังเพิ่มเติมตรงที่บอกว่า Spark ไม่มีชนิดข้อมูลภูมิสารสนเทศ และแพ็กเกจโอเพนซอร์สก็ยังน่าผิดหวัง
      เคยลองเทียบกับ Apache Sedona ไหม และมีอะไรที่รู้สึกว่ายังขาดไปบ้าง
      ทำงานอยู่ที่ Wherobots ซึ่งก่อตั้งโดยผู้สร้าง Apache Sedona เลยอยากฟังฟีดแบ็ก
      https://sedona.apache.org/latest/
      https://wherobots.com/
    • ถ้าลิงก์ชุดข้อมูลที่เจาะจงมาได้ก็น่าจะดี
      เห็นด้วยว่าค่าที่กว้างมากอาจทำให้หลายเครื่องมือพังได้ แต่คิดว่าระบบแบบ Postgres เชิงคอลัมน์ตัวอื่น ๆ น่าจะรองรับได้โดยไม่มีปัญหา
      แปลกใจที่เหมือนไม่ได้ลอง Redshift ซึ่งแข่งกับ BigQuery โดยตรง และคอลัมน์ super ของ Redshift ก็รองรับขนาดได้ใหญ่กว่าค่าสูงสุดของ BigQuery เสียอีก
      มักเห็นผู้คนค่อย ๆ เรียนรู้ด้วยความยากลำบากว่าเอาชนะ PostGIS ได้ยากแค่ไหน
      การที่ Trino/Presto และ Spark แทบไม่คืบหน้าในด้านนี้ก็ชวนให้คิดมากเป็นพิเศษ
    • ย้าย คลังข้อมูลขนาดประมาณ 500GB จาก Postgres RDS 8 คอร์ไป BigQuery แล้วเวลา rebuild ลดจาก 5 ชั่วโมงเหลือ 11 นาที และค่าใช้จ่ายก็ใกล้เคียงกันหรือถูกลงด้วยซ้ำ
      บน Postgres มีการแคชบางส่วนของตารางใหญ่ แต่บน BigQuery สร้างใหม่ตั้งแต่ต้นทั้งหมดก็ยังได้ผลแบบนั้น
      สำหรับเครื่องมือที่ดูแลเองได้ยังชอบ Postgres มากกว่า แต่พอประสิทธิภาพดีขึ้นเกินระดับหลายเท่าตัวแบบเลขหลักเดียว ก็เถียงได้ยากจริง ๆ
    • ได้ยินเรื่องดี ๆ เกี่ยวกับ BigTable และ BigQuery เยอะมากจริง ๆ แต่เสียดายที่ยังไม่เคยมีโอกาสได้ลองใช้

  • เป็นบทความที่ยอดเยี่ยมมาก
    ผมดูแลงาน DevRel ที่ Timescale และชอบมากเวลาได้เห็นบทความจากคอมมูนิตี้ที่เขียนออกมาดีแบบนี้
    หนึ่งในเหตุผลที่ hypertable ช้ากว่า น่าจะเป็นเพราะมันสร้างดัชนีบนคอลัมน์ timestamp ให้โดยอัตโนมัติเกือบแน่นอน
    ส่วนตารางปกติไม่มีดัชนี จึงอาจเร็วกว่า
    ใน create_hypertable สามารถใช้ create_default_indexes=>false เพื่อข้ามการสร้างดัชนีได้ หรือจะลบดัชนีก่อนใส่ข้อมูลก็ได้
    สุดท้ายยังไงก็น่าจะต้องใช้ดัชนีนั้นอยู่ดี แต่สำหรับการโหลดข้อมูลแบบเป็นชุด การค่อยสร้างทีเดียวหลังโหลดเสร็จน่าจะดีกว่า
    แล้วก็สงสัยเหมือนกันว่า HDD ที่ใช้อ่านข้อมูลจะรับมือไหวแค่ไหนในระบบที่มี parallelism สูง

    • ไม่รู้มาก่อนว่า create_default_indexes=>false และ hypertable จะสร้าง ดัชนีเวลา โดยอัตโนมัติ จะเพิ่มหมายเหตุอธิบายส่วนนี้เข้าไป
      อยากลองทำเบนช์มาร์กแบบแทรกข้อมูลโดยไม่มีดัชนีเวลา แล้วค่อยสร้างดัชนีเองภายหลังด้วย
      แม้จะมี worker 32 ตัว HDD ก็ดูเหมือนจะรับไหว
      ตอนดูการใช้งานดิสก์ด้วย btop กลับรู้สึกว่า SSD ที่ติดตั้ง Postgres อยู่ใกล้จะเป็นคอขวดมากกว่า HDD เลยสรุปว่าการอัปเกรด SSD สำหรับ Postgres ให้เร็วขึ้นน่าจะคุ้มกว่าย้ายข้อมูลจาก HDD ไป SSD

  • ไม่ค่อยเข้าใจว่าทำไมต้องทำแบบนั้นด้วย
    ชุดข้อมูลสภาพอากาศ·ภูมิอากาศส่วนใหญ่รวมถึง ERA5 มีโครงสร้างสูงมากอยู่บนกริดละติจูด-ลองจิจูดที่สม่ำเสมอ
    แม้จะดึงออกมาแค่อนุกรมเวลาของตำแหน่งใดตำแหน่งหนึ่ง จุดแข็งของชุดข้อมูลแบบนี้ก็อยู่ที่โครงสร้างและบริบทเชิงพื้นที่-เวลาที่มีอยู่ในตัว และถ้าไม่ได้มีเป้าหมายแค่สกัดอนุกรมเวลารายจุด การทำลายโครงสร้างนั้นทิ้งทั้งหมดก็ดูไม่ค่อยสมเหตุสมผล
    ต่อให้เป็นกรณีที่ดึงเฉพาะอนุกรมเวลารายจุดจริง ๆ เช่น อนุกรมเวลาอุณหภูมิผิวน้ำกลางมหาสมุทร ก็มักแทบไม่มีโอกาสได้ใช้งาน ดังนั้นคงอยากลดข้อมูลลงอย่างค่อนข้างหนัก
    การใช้งานชุดข้อมูลอย่าง ERA5 ทั้งด้านวิจัยและงานปฏิบัติการส่วนใหญ่ ดูเหมือนจะเหมาะกับการใช้สำเนาที่ปรับให้เหมาะกับคลาวด์และยังคงโครงสร้างต้นฉบับไว้ เช่น ARCO-ERA5 ของ Google Public Datasets มากกว่า
    เวอร์ชันแบบนี้ยังรักษาโครงสร้างต้นฉบับไว้ ขณะเดียวกันก็แบ่งชังก์ให้เหมาะกับการเข้าถึงแบบขนานขนาดใหญ่บนคลาวด์สตอเรจ
    แทบทุกกรณีที่ผมเคยเห็นมาในสายอาชีพ แค่อาร์ไคฟ์ที่ทำชังก์แบบทั่วไปบนฐานของ Zarr ก็เร็วพอแล้วสำหรับการใช้งานส่วนใหญ่ที่น่าสนใจ
    https://cloud.google.com/storage/docs/public-datasets/era5

    • เหตุผลหลักคือมันเป็นโปรเจ็กต์ส่วนตัว เลยอยากลองทำทุกอย่างบนเซิร์ฟเวอร์ที่บ้านโดยไม่ต้องจ่ายค่าทรัพยากรคลาวด์ และก็อยากเรียนรู้ Postgres, TimescaleDB และภายหลังรวมถึง PostGIS ด้วย
      แต่ก็อย่างที่ rabernat พูดไว้ การดึงอนุกรมเวลายาว ๆ จากสำเนาบนคลาวด์ก็ยังช้า
      สุดท้ายแล้วผมก็อยากทำคิวรีเชิงพื้นที่-เวลาที่ซับซ้อนด้วย เช่น คำนวณเปอร์เซ็นไทล์ที่ 99 ของอุณหภูมิฤดูร้อนในชิลีช่วงปี 1940~1980
      ผมไม่สงสัยเลยว่าสำเนาบนคลาวด์อาจเร็วกว่า แต่ก็ขัดกับงบประมาณ $0
    • ใช่ แต่จริง ๆ แล้วข้อมูลสาธารณะ Google ERA5 เองก็เจอปัญหาการทำชังก์แบบเดียวกับที่อธิบายในบทความเป๊ะ
      มันถูกปรับให้เหมาะกับคิวรีเชิงพื้นที่ และไม่ได้ปรับให้เหมาะกับคิวรีอนุกรมเวลา
      ผมเพิ่งรันเบนช์มาร์กไป และใช้เวลา 20 นาทีในการดึงอนุกรมเวลาของตัวแปรเดียว ณ จุดเดียว
      มันแสดงให้เห็นชัดเจนว่าถ้าแพตเทิร์นการใช้งานที่คาดไว้คืออนุกรมเวลา ก็จำเป็นต้องทำชังก์ให้เหมาะกับอนุกรมเวลา
    • คงดีถ้ามีใครทำสิ่งนั้นให้ แล้วสอนคนอื่นให้ใช้มันด้วย
      ห้องแล็บบางแห่งมีไปป์ไลน์ที่อิง RDBMS ซึ่งผูกพันกับอัลกอริทึมและข้อมูลที่เปิดเผยอยู่แล้ว ไม่มีใครอยากมาเขียนใหม่ และก็ไม่มีงบสำหรับงานนั้น
      การปรับปรุงที่ดีที่สุดที่เราได้มาก็คือย้ายจาก MySQL รุ่นเก่าไปเป็น Postgres + PostGIS
      Timescale ก็น่าจะช่วยได้เหมือนกัน
      ยังมีเหตุผลด้วยว่าทำไมต้องรันในเครื่อง เช่น เรื่องความเป็นส่วนตัว การเข้าถึงคลัสเตอร์ และงบประมาณ

  • เป็นบทความที่ดี
    สิ่งที่ขาดไปตรงนี้ ในความเห็นผม คือการวิเคราะห์ว่าการย้ายข้อมูลอากาศไปไว้ในฐานข้อมูลเชิงสัมพันธ์จะได้อะไรกลับมา
    แรงจูงใจคือทำให้คิวรีเร็วขึ้น ดังนั้นจึงต้องรู้ว่าค่าฐานเปรียบเทียบคืออะไร
    ในฐานะผู้ดูแล Xarray และ Zarr และผู้ก่อตั้ง https://earthmover.io/ ผมค่อนข้างคุ้นเคยกับภูมิทัศน์เทคโนโลยีนี้ และถ้าทำชังก์ข้อมูลใน Zarr อย่างเหมาะสม ก็สามารถรองรับคิวรีอนุกรมเวลาของข้อมูลอากาศด้วยโซลูชันแบบ serverless + object storage เพียงอย่างเดียวได้ที่ latency ต่ำกว่า 1 วินาที
    ซึ่งเร็วกว่าที่บทความบอกไว้ว่า 30 นาทีมาก
    เมื่อคิดถึงความยากของการนำเข้าข้อมูลตามที่กล่าวในบทความ ก็คุ้มที่จะประเมินแนวทางแบบนั้นอย่างจริงจังก่อนจะไปสาย RDBMS

    • เห็นด้วยว่าการเก็บไว้ในไฟล์ Zarrที่ทำชังก์อย่างเหมาะสมน่าจะเร็วกว่าเกือบแน่นอน ตั้งค่าง่ายกว่า และใช้พื้นที่น้อยกว่า
      จะเอา API มาครอบด้านหน้าจนดูเหมือนเป็นคิวรีก็ได้
      และก็จริงด้วยว่าผมยังให้เหตุผลสนับสนุนแนวทาง RDBMS ได้ไม่พอ
      เหตุผลหลักที่ผมไปทาง Postgres + Timescale คืออยากเรียนรู้มัน และการเล่นกับข้อมูล ERA5 ก็ดูน่าสนุกที่สุด
      ขนาดของข้อมูลอากาศที่ใหญ่พอจะเป็นความท้าทายก็ดูจะมีเสน่ห์อยู่เหมือนกัน
      ผมไม่มีหลักฐาน แต่ก็สงสัยว่า TimescaleDB + PostGIS ที่จูนและทำดัชนีอย่างดี จะช่วยกับคิวรีเชิงพื้นที่-เวลาที่ซับซ้อนอย่างเปอร์เซ็นไทล์ที่ 99 ของอุณหภูมิฤดูร้อนในชิลีช่วงปี 1940~1980 ได้ไหม
      เพราะในกรณีนี้อาจต้องอ่าน Zarr หลายชังก์
      ผมก็ชอบไอเดียการมีตารางแยกไว้แคชสถิติพวกนี้ แต่กับ Zarr เองก็ดูไม่ได้ทำยากขนาดนั้น
      ครั้งหน้าผมตั้งใจจะเบนช์มาร์กคิวรีและดัชนี ก็น่าจะได้รู้อะไรมากขึ้น
    • อาจจะนอกประเด็นไปนิด แต่ผมสนใจโดเมนเดียวกันนี้อยู่
      ดูเหมือนว่าจะมีความตึงเครียดเชิงพื้นฐานที่ว่า ชังก์ใหญ่เหมาะกับการแสดงภาพพื้นที่กว้างและคิวรีขนาดใหญ่ ส่วนชังก์เล็กเหมาะกับคิวรีแบบอิงจุดหรืออนุกรมเวลา
      การเก็บชุดข้อมูลภูมิสารสนเทศขนาดใหญ่แบบนี้แยกเป็นหลายเวอร์ชันที่ทำชังก์ต่างกันก็พอทำได้ แต่ไม่ค่อยคุ้มค่าใช้จ่าย
      เคยได้ยินว่า kerchunk ถูกใช้เพื่อพยายามเอาข้อดีของทั้งสองแบบ แต่ผมรู้สึกว่ามันทำให้เสียทางเลือกเรื่องการบีบอัดข้อมูลไป และยังเพิ่มความซับซ้อนพอสมควร
      ผมสงสัยว่าควรหาจุดสมดุลระหว่างหลายกรณีการใช้งานแบบนี้อย่างไรดี

  • ฉันเคยมีส่วนร่วมกับ เอกสารการโหลดข้อมูลจำนวนมากของ Postgres ที่อ้างถึงตรงนี้ ซึ่งเป็นบทความที่รวบรวมเทคนิคหลายอย่างไว้ได้ดี
    ฉันเคยทำงานแนวนี้อยู่พอสมควรเพื่อเร่งการโหลดฐานข้อมูล OpenStreetMap และการอัปเดตสาธารณะครั้งสุดท้ายอยู่ที่ https://www.youtube.com/watch?v=BCMnu7xay2Y
    หลังจากนั้น ด้วยพัฒนาการของฮาร์ดแวร์, การปรับปรุง GIS ใน PG15 และการที่ osm2pgsql นำเทคนิค middle-way-node-index-id-shift มาใช้ ทำให้เวลาโหลดชุดข้อมูล planet ลดลงมาต่ำกว่า 4 ชั่วโมง
    ถ้าจะเสนอแนะผู้เขียน การทดลองบางส่วนกำลังกำจัดการเขียน WAL ทางอ้อมผ่าน pg_bulkload และ COPY
    ตอนที่ Craig Ringer เขียนโพสต์ SO ที่ลิงก์ไว้นั้นยังไม่มีการบันทึกไว้ในเอกสาร แต่จริง ๆ แล้ว สามารถปิด WAL ได้เลย ในการตั้งค่า
    แน่นอนว่าถ้าเกิด crash ก็จะเสียตารางที่กำลังทำอยู่ และถ้างานกินเวลาหลายสัปดาห์ เรื่องแบบนั้นก็อาจเกิดขึ้นได้
    แต่สำหรับข้อมูลอนุกรมเวลา ถ้าออกแบบโครงสร้างการโหลดดี ๆ ก็ไม่ยากที่จะทำให้เสียแค่ chunk สุดท้าย
    สำหรับการโหลดข้อมูลจำนวนมาก จริง ๆ แล้วแทบไม่จำเป็นต้องมีข้อมูล WAL
    ถ้า crash ก็แค่เก็บกวาดด้านขวาสุดของข้อมูลที่โหลดไว้แล้ว แล้วเริ่มใหม่
    ค่าตั้งใน postgresql.conf ที่ใช้เพื่อปิด WAL และ overhead อื่น ๆ มีดังนี้:
    wal_level = minimal
    max_wal_senders = 0
    synchronous_commit = off
    fsync = off
    full_page_writes = off
    autovacuum = off
    checkpoint_timeout = 60min
    สุดท้าย เวลาจะโหลดเป็น chunk ใหญ่ ๆ เพื่อลดงาน vacuum โดยทั่วไปก็จะปิด autovacuum แบบข้างบน และรัน VACUUM FREEZE เป็นระยะ ๆ กับพาร์ทิชันวันที่ที่อยู่ถัดจากพาร์ทิชันที่กำลังโหลดอยู่
    นี่เป็นแนวทางของ PG ปกติ และช่วยให้ฐานข้อมูลข้ามงานบางส่วนที่เกี่ยวกับการดูแลสถานะกึ่งกลางซึ่งมีธุรกรรมใหม่ถูกเขียนแล้วแต่ยังมองเห็นได้ไม่ครบทุกคน

    • ฉันจะลองตั้งค่าเพื่อปิด WAL และ overhead อื่น ๆ ดู แล้วดูว่าการ insert จะเร็วขึ้นไหม
      โดยเฉพาะกับการโหลดเป็นระดับ chunk การได้ยินจากผู้เชี่ยวชาญว่าข้อมูล WAL ไม่จำเป็นนักสำหรับการโหลดข้อมูลจำนวนมากนี่เป็นข่าวดีมาก
      ตอนนี้ยังไม่มี UPS แต่หวังว่าจะผ่านเวลาประมาณ 20 วัน ที่ใช้โหลดข้อมูลไปได้โดยไม่ไฟดับ และในกรณีเลวร้ายที่สุดก็น่าจะโหลดต่อจากเดิมได้
    • ฉันสงสัยว่ามีแหล่งข้อมูลให้อ่านต่อเกี่ยวกับการปรับปรุง GIS ใน PG15 หรือไม่

  • ถ้า OP คือผู้เขียน การทดลองคล้าย ๆ กันที่ฉันทำไว้เมื่อราว 4 ปีก่อนอาจน่าสนใจด้วย
    เป็นชุดข้อมูลเดียวกัน เป้าหมายเดียวกัน และวัตถุประสงค์คล้ายกัน
    https://rdrn.me/optimising-sql/
    ลำดับการสำรวจก็คล้ายกัน แต่ฉันใช้ Postgres ปกติแทน Timescale และในการตั้งค่าของฉัน ถ้าสมมติว่าข้อมูลอยู่ในหน่วยความจำอยู่แล้ว การ คัดลอกข้อมูลไบนารีโดยตรง เร็วกว่า COPY ราว 3 เท่า

    • น่าเสียดายที่ฉันไม่ได้เห็นอันนี้ก่อนเริ่ม
      ฉันทิ้งเชิงอรรถไว้ถึงเหตุผลที่ไม่ได้ลอง binary COPY ซึ่งหลัก ๆ คือมีคนอื่นบอกว่าประสิทธิภาพน่าผิดหวัง
      แต่ถึงอย่างนั้นก็ดูเหมือนว่าควรลองด้วยตัวเอง
      https://aliramadhan.me/2024/03/31/trillion-rows.html#fn:copy-binary-note
    • ฉันอ่านตอนที่ 1 และ 2 แล้ว สนุกดี
      ฉันชอบรูปแบบที่มีหมายเหตุไว้ตรงขอบ
      และการที่คุณให้ฟังก์ชันสำหรับเขียน structured array ของ numpy เป็นไบนารีของ Postgres ก็ช่วยได้มาก เพราะก่อนหน้านี้ฉันหาวิธีนี้ไม่เจอ

  • “ฐานข้อมูลเชิงสัมพันธ์เหมาะกับข้อมูลสภาพอากาศแบบกริดจริงหรือ? ไม่รู้เหมือนกัน แต่ลองทำดูก็เดี๋ยวรู้”
    ฉันชอบท่าทีแบบนี้
    มันตรงกันข้ามกับสไตล์ “ความจริงก็คือ” ของบทความสายเทคกระแสหลักอื่น ๆ เลย เลยยิ่งถูกใจ
    และยังชอบที่มันดึงผู้อ่านให้อยู่กับเรื่องราวได้ตลอดการเดินทาง

    • การเป็นมือใหม่และไม่มีส่วนได้ส่วนเสียอาจเป็นข้อดี
      ฉันค้นมามากแล้วแต่หาคำตอบชี้ขาดสำหรับกรณีใช้งานของตัวเองไม่ได้ เลยตัดสินใจรัน benchmark เอง

  • เป็นบทความที่น่าสนใจ
    ประโยคที่ว่า “ถ้า insert ได้ต่อเนื่องที่ประมาณ 462k ครั้งต่อวินาที ก็จะใช้เวลาราว 20 วันสำหรับประมาณ 754 พันล้านแถว ซึ่งถือว่าไม่เลว แถมยังสั้นกว่าเวลาที่ใช้เขียนบทความนี้” ทำให้ฉันขำ
    ฉันเองก็เริ่มเอนเอียงไปทางการเขียนบล็อกโพสต์ที่ยาวและลึกขึ้นเหมือนกัน เลยเข้าใจดีว่ามันใช้แรงมากกว่าที่คิด

    • benchmark บางส่วนใช้เวลาหลายชั่วโมง และบางอันต้องรันใหม่หลายรอบ ซึ่งระหว่างนั้นก็ได้เรียนรู้อะไรเยอะมาก

  • ถ้าคุณอยากวาดกราฟอนุกรมเวลาหรือกราฟหลายแบบได้ตรงจาก SQL query เลย qStudio เป็น SQL IDE ฟรีที่ทำงานได้กับหลายฐานข้อมูลรวมถึง TimescaleDB
    https://www.timestored.com/qstudio/database/timescale
    ข้อชี้แจง: ฉันเป็นคนสร้างเครื่องมือนี้เอง

    • ฉันสงสัยว่ากระบวนการเพิ่มการรองรับฐานข้อมูลอื่นให้ qStudio ทำอย่างไร
      ฉันคิดว่าอาจเพิ่มการรองรับ Timeplus ได้
      Timeplus เป็นฐานข้อมูลแบบสตรีมมิงที่สร้างบน ClickHouse และเอนจินฐานข้อมูลหลัก Timeplus Proton ก็เป็นโอเพนซอร์ส
      qStudio ก็เป็นโอเพนซอร์สและเขียนด้วย Java ดังนั้นดูเหมือนว่าการรองรับ RDBMS ใหม่คงต้องใช้ JDBC driver
      ถ้าเข้าใจถูก Timeplus Proton มี JDBC driver แบบโอเพนซอร์สที่ต่อยอดจาก ClickHouse 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 แต่นี่ดูเหมือนจะใช้งานได้เร็วกว่า
      ตอนนี้ฉันยังทำแค่การ insert ข้อมูล แต่เดี๋ยวจะลอง query และ plotting เร็ว ๆ นี้

  • ผมก็ใช้ ข้อมูลวิเคราะห์ย้อนหลัง ERA5 และต้องการ time series ที่รวดเร็ว
    ข้อมูลมาในรูปกริด [lat, lon] ที่สะสมตามช่วงเวลาที่เลือก เช่น รูปแบบ [ข้อมูลรายชั่วโมงของหนึ่งเดือน, lat, lon] ดังนั้นถ้าต้องการมากกว่า 20 ปี มันจะกลายเป็นปัญหา transpose เมทริกซ์ขนาดมหึมา
    วิธีที่ผมทำคือดาวน์โหลดไฟล์ netCDF แต่ละไฟล์ จากนั้น transpose แล้วใส่ลงในไฟล์ HDF แบบ 3D ขนาดใหญ่ที่จัดเป็น [lat, lon, hour]
    บนเวิร์กสเตชันของผม การสร้างข้อมูล 1 ปีสำหรับตัวแปรหนึ่งตัวใช้เวลาประมาณ 30 นาที แต่หลังจากนั้นการดึงตำแหน่ง (lat, lon) เพียงจุดเดียวทำได้ในระดับมิลลิวินาที
    เป็นวิธีที่ลำบากตอนต้นแต่คุ้มค่าในระยะยาว
    มันเรียบง่ายดี แต่ผมไม่ใช่ผู้เชี่ยวชาญด้านฐานข้อมูล เป็นแค่นักภูมิอากาศวิทยาเท่านั้น

    • ถึงจะเรียบง่าย แต่ก็น่าจะเร็วกว่าและใช้พื้นที่ได้มีประสิทธิภาพกว่าฐานข้อมูลเชิงสัมพันธ์
      ดูเหมือนทั้ง rabernat ที่คอมเมนต์ที่นี่และ open-meteo ก็ใช้วิธีคล้ายกันและมองว่ามันเร็ว