Nạp 1 nghìn tỷ bản ghi dữ liệu thời tiết vào TimescaleDB

 (aliramadhan.me)
3 điểm bởi GN⁺ 2024-04-17 | 2 bình luận | Chia sẻ qua WhatsApp
  • Để phân tích nhanh các biến động thời tiết lịch sử trên toàn cầu, cần đưa dữ liệu tái phân tích ERA5 vào PostgreSQL/TimescaleDB; với các biến và lưới được chọn, quy mô vào khoảng 754 tỷ hàng
  • ERA5 cung cấp độ phân giải theo giờ từ năm 1940 và lưới vĩ độ/kinh độ 0,25 độ, nhưng trong cấu trúc NetCDF, việc trích xuất chuỗi thời gian dài hạn tại một điểm vẫn có thể mất 20–30 phút
  • insert từng hàng đơn lẻ chỉ đạt khoảng 3 nghìn hàng/giây, nên sẽ cần khoảng 8 năm để nạp toàn bộ; ngay cả insert nhiều hàng cũng chỉ ở mức 25 nghìn–30 nghìn hàng/giây theo psycopg3, tương đương gần 10 tháng
  • PostgreSQL copy và cursor.copy() của psycopg3 khác nhau tùy việc CSV đã được chuẩn bị sẵn hay chưa; tính cả overhead thì khoảng 100 nghìn hàng/giây, và còn có thể đẩy cao hơn bằng chạy song song cùng tinh chỉnh cấu hình
  • Khi nạp trực tiếp từ dataframe, cách phù hợp là copy thẳng vào hypertable bằng psycopg3; khi đã có sẵn CSV thì timescaledb-parallel-copy phù hợp hơn, và trên hệ thống này 12–16 worker có vẻ là điểm tối ưu cho song song hóa

Xây dựng kho dữ liệu thời tiết toàn cầu

  • Mục tiêu là xây dựng một kho dữ liệu có thể truy vấn dữ liệu thời tiết lịch sử trên toàn cầu để phân tích các tín hiệu biến đổi khí hậu đã thực sự xảy ra
  • Các phân tích ví dụ gồm Jakarta có thực sự nóng hơn hay bão xuất hiện thường xuyên hơn không, toàn bộ Chile có ấm hơn hay nhiều mây hơn không, và các thay đổi theo từng khu vực có dạng như thế nào
  • Để phân tích toàn cục nhanh, các truy vấn trên kho dữ liệu phải đủ nhanh, và bước đầu tiên là nạp khối lượng dữ liệu rất lớn vào cơ sở dữ liệu
  • Nền tảng được chọn là PostgreSQL; TimescaleDB là lựa chọn triển vọng để tăng tốc truy vấn theo thời gian, còn PostGIS có tiềm năng cho tăng tốc truy vấn không gian địa lý về sau

Dữ liệu tái phân tích ERA5 và quy mô 754 tỷ hàng

  • Thay vì dùng số đo thời tiết thực tế, bài viết sử dụng dữ liệu ERA5 climate reanalysis
    • Các quan trắc lịch sử có thể thưa thớt ở một số khu vực và thời điểm trong quá khứ
    • ERA5 là đầu ra của mô hình khí hậu được ràng buộc để khớp với quan trắc, và được dùng rộng rãi trong nghiên cứu thời tiết và khí hậu
  • ERA5 bao phủ toàn bộ Trái Đất ở độ phân giải 0,25 độ, với độ phân giải theo giờ từ năm 1940
    • Mỗi ảnh chụp thời gian có 727.080 giá trị cho mỗi biến
    • Có 1.038.240 điểm lưới, gồm 1.440 kinh độ và 721 vĩ độ bao gồm cả hai cực
    • Khi lập chỉ mục theo thời gian và vị trí, mỗi biến sẽ có 753.836.544.000 hàng, tức khoảng 754 tỷ hàng
  • Các biến được nạp gồm nhiệt độ, tốc độ gió 10m theo hướng đông-tây và bắc-nam, tổng lượng mây, lượng mưa và lượng tuyết rơi
  • Bảng dữ liệu có các cột time, location_id, latitude, longitude cùng các cột cho từng biến thời tiết
    • Việc giữ cả location_id lẫn cột vĩ độ/kinh độ là để benchmark truy vấn và chỉ mục về sau

Điểm mà cấu trúc file NetCDF trở nên chậm

  • ERA5 được phân phối dưới dạng file NetCDF, thường chứa dữ liệu theo từng ngày, tháng hoặc năm
  • Cấu trúc chunk theo thời gian giúp truy vấn dữ liệu tại một thời điểm cụ thể nhanh và đơn giản
  • Nhưng để xem mẫu theo thời gian, như chuỗi thời gian dài hạn tại một điểm, phải đọc rất nhiều file nên trở nên chậm
    • Ví dụ, việc trích xuất chuỗi thời gian nhiệt độ tại một vị trí có thể mất 20–30 phút
  • Các truy vấn không gian địa lý phức tạp, đặc biệt là truy vấn có thêm trục thời gian, vừa chậm vừa khó thực hiện
  • Các công cụ như xarray, dask, Pangeo có thể tăng tốc, nhưng quá trình này nhìn chung vẫn chậm

insert: từ từng hàng đơn lẻ đến nhiều hàng

  • Cách đơn giản nhất là dùng insert để thêm từng hàng một
  • insert từng hàng kéo theo nhiều loại chi phí
    • PostgreSQL phải phân tích câu lệnh, kiểm tra tên bảng và cột, rồi lập kế hoạch thực thi
    • Có thể cần khóa để đảm bảo tính toàn vẹn dữ liệu
    • Dữ liệu được ghi vào bộ đệm để phục vụ WAL (write-ahead logging)
    • Dữ liệu được chèn vào vùng đĩa thực của bảng
    • Khi transaction được commit, thay đổi mới được ghi nhận vĩnh viễn
  • Trong Python, bài viết benchmark ba cách chèn từng hàng
    • Dùng pandas df.to_sql() với chunksize=1
    • Dùng truy vấn tham số hóa của psycopg3
    • Dùng truy vấn tham số hóa của SQLAlchemy
  • Kết quả chèn từng hàng cho thấy psycopg3 nhỉnh hơn một chút và SQLAlchemy chậm nhất
    • Hypertable của TimescaleDB chậm hơn đôi chút so với bảng PostgreSQL thông thường
    • Hiệu năng cao nhất chỉ khoảng 3 nghìn hàng/giây, tức sẽ mất khoảng 8 năm để nạp toàn bộ dữ liệu
  • insert nhiều hàng đưa nhiều dòng vào một câu lệnh để giảm số lần round-trip mạng, chi phí parse và lập kế hoạch
    • psycopg3 nhanh nhất ở mức 25 nghìn–30 nghìn hàng/giây
    • pandas có thể chậm hơn vì cấu trúc chèn dựa trên dictionary thay vì tuple
    • SQLAlchemy có thể phát sinh thêm overhead từ quản lý session và lớp trừu tượng biểu thức SQL
    • Dù vậy, toàn bộ quá trình nạp vẫn cần khoảng 0,8 năm, tức gần 10 tháng

copy: con đường nạp dữ liệu hàng loạt của PostgreSQL

  • PostgreSQL copy là tính năng nạp dữ liệu hàng loạt bằng cách đọc các hàng từ file CSV hoặc nhị phân
  • Vì được tối ưu cho bulk load, nó giảm chi phí parse, lập kế hoạch và sử dụng WAL tốt hơn nên nhanh hơn insert nhiều hàng
  • Bài viết so sánh hai đường đi
    • Lưu dữ liệu NetCDF ra CSV rồi nạp bằng copy
    • Không tạo file CSV mà stream trực tiếp vào PostgreSQL bằng psycopg3 cursor.copy()
  • Khi file CSV đã sẵn sàng, copy có thể đạt tốc độ chèn gần 400 nghìn hàng/giây
  • Nếu tính cả overhead ghi file CSV hoặc tạo tuple, cả copy lẫn psycopg3 đều ở mức khoảng 100 nghìn hàng/giây, trong đó psycopg3 nhỉnh hơn một chút
  • Ngay cả ở tốc độ này, việc nạp toàn bộ dữ liệu vẫn cần khoảng 3 tháng

Tốc độ nạp duy trì và copy song song

  • Khi chèn rất nhiều hàng, có thể xuất hiện các nút thắt như ghi đĩa, cạnh tranh I/O giữa WAL và chèn bảng, autovacuum, hay checkpoint
  • Trong thử nghiệm nạp khoảng 772 triệu hàng qua 744 batch, với một worker đơn lẻ không thấy mức suy giảm tốc độ quá lớn
    • copy csv có các đợt giảm tốc thường xuyên và dễ biến động hơn
    • psycopg3 nhìn chung nhanh hơn
    • Chênh lệch giữa bảng thường và hypertable không lớn
  • Bài viết dùng joblib để chạy song song nhiều tác vụ copy hoặc nhiều cursor psycopg3
  • Chèn vào một bảng đơn lẻ không phải loại công việc song song hóa tốt, và hiệu năng nhìn chung chững lại sau 16 worker

pg_bulkload và timescaledb-parallel-copy

  • Ngoài PostgreSQL copy, bài viết còn benchmark pg_bulkload và timescaledb-parallel-copy
  • pg_bulkload có vẻ nhanh hơn ở cấu hình mặc định, nhưng mặc định nó bỏ qua shared buffers và bỏ luôn WAL logging, nên có thể không khôi phục được dữ liệu sau sự cố
  • Trong cùng điều kiện tắt fsync, timescaledb-parallel-copy với nhiều worker lại tốt hơn pg_bulkload
  • timescaledb-parallel-copy cho phép chỉ định số worker để chèn song song
    • Hiệu năng ban đầu tốt, nhưng trên hệ thống này nó chạm nút thắt trước mốc 100 triệu hàng, sau đó tốc độ chèn giảm mạnh rồi hồi phục theo dạng dao động
    • Tốc độ nạp duy trì ở bảng thường khoảng 600 nghìn–700 nghìn hàng/giây, còn hypertable khoảng 300 nghìn hàng/giây
  • pg_bulkload không cho chỉ định số worker, nhưng có tùy chọn writer=parallel để dùng nhiều luồng cho việc đọc, parse và ghi

Tinh chỉnh cấu hình PostgreSQL và đánh đổi về độ bền dữ liệu

  • Có thể đạt thêm hiệu năng bằng cách điều chỉnh các thiết lập không đảm bảo độ bền của PostgreSQL
  • Các thiết lập chính là tắt fsync để tránh flush xuống đĩa, và tắt full_page_writes để bỏ cơ chế bảo vệ trước partial page write
  • Các thiết lập này có thể làm rủi ro với tính toàn vẹn cơ sở dữ liệu khi xảy ra sự cố
  • Unlogged table không tạo WAL nên ghi nhanh hơn, nhưng có thể bị cắt mất dữ liệu khi khôi phục sau crash
    • Sau đó cần chuyển sang bảng logged thông thường, và quá trình này có thể chậm và chạy đơn luồng
    • Hypertable không thể là unlogged, nên nếu cần hypertable thì sẽ phải thêm bước chuyển đổi hoặc migrate

Lựa chọn cuối cùng: nạp trực tiếp vào hypertable

  • Nếu đích đến là hypertable, thì nạp trực tiếp vào hypertable nhanh hơn cách nạp vào bảng thường rồi chuyển đổi sang hypertable
  • Trong một thử nghiệm đơn giản với khoảng 772 triệu hàng được nạp bằng psycopg3 copy và 16 worker, việc chèn trực tiếp vào hypertable mất ít thời gian hơn so với chèn vào bảng thường rồi chuyển đổi
    • Trong trường hợp này, chèn trực tiếp vào hypertable chỉ mất khoảng 80% thời gian của phương án còn lại
    • Quá trình chuyển đổi và migrate không nhanh và có vẻ giống đơn luồng
  • Cách làm được khuyến nghị là
    • Nếu nạp trực tiếp từ dataframe, hãy dùng psycopg3 để copy thẳng vào hypertable
    • Nếu đã có sẵn file CSV, hãy dùng timescaledb-parallel-copy
    • Trên hệ thống này, 12–16 worker có vẻ là điểm hợp lý cho song song hóa

Kết luận benchmark tổng thể và thời gian cần thiết

  • Với một worker duy nhất và bật các thiết lập bảo vệ, trần tốc độ nạp duy trì tính cả overhead trên phần cứng này có vẻ vào khoảng 140 nghìn hàng/giây
  • Với nhiều worker, psycopg3 copy cursor có thể nâng tốc độ nạp duy trì lên khoảng 250 nghìn hàng/giây mà vẫn giữ các thiết lập bảo vệ
  • Quá trình chèn không song song hóa hiệu quả lắm, và vùng hợp lý có vẻ là 4–16 worker
  • Nếu chấp nhận rủi ro và tắt fsync, psycopg3 có thể duy trì khoảng 462 nghìn hàng/giây
  • Cần thận trọng vì pg_bulkload mặc định đã vô hiệu hóa fsync
  • Nếu duy trì được khoảng 462 nghìn hàng/giây, thì việc nạp khoảng 754 tỷ hàng sẽ mất khoảng 20 ngày

Mã nguồn và môi trường benchmark

  • Mã cho việc tải ERA5, tạo bảng, chèn/copy, benchmark và vẽ biểu đồ nằm tại timescaledb-insert-benchmarks
  • Mỗi benchmark đều khởi chạy một Docker container mới để giữ môi trường nhất quán
    • Không giữ lại storage giữa các Docker container
    • File NetCDF và CSV được đọc từ HDD
    • Cơ sở dữ liệu được lưu trên NVMe SSD
  • Cấu hình phần cứng
    • 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
  • Cấu hình phần mềm
    • Ubuntu 20.04, Linux kernel 5.15
    • PostgreSQL 15.5
    • TimescaleDB 2.13.0
    • pg_bulkload 3.1.20
  • Cấu hình PostgreSQL dùng các giá trị do timescaledb-tune khuyến nghị cho 250.57GB bộ nhớ và 48 CPU, còn trong benchmark thì kích thước WAL được điều chỉnh riêng

Bài viết liên quan

2 bình luận

 
jangsc0000 2024-04-18

Ý kiến trên GN+ đang dùng kính ngữ à..?
 
GN⁺ 2024-04-17
Các ý kiến trên Hacker News

  • Tôi đã làm khá nhiều về phân tích không gian địa lý trong công việc, và dữ liệu không gian địa lý tinh tế hơn tưởng nhiều vì phải hiểu chính xác hệ quy chiếu tọa độ (CRS) và phép chiếu dùng để trực quan hóa.
    Nếu không có hạ tầng mang metadata đi cùng dữ liệu một cách đúng đắn, CRS sẽ liên tục khiến bạn bất an.
    Tôi đã dùng các tính năng liên quan của AWS, Postgres/PostGIS, Spark/Databricks, Snowflake, Trino và ArcGIS, nhưng với các tác vụ không gian địa lý quy mô lớn, tôi thấy Google BigQuery vượt trội áp đảo.
    Một truy vấn trên PostGIS chạy trên EC2 m6a khổng lồ từng mất vài giờ và tốn khá nhiều chi phí, nhưng trên bậc miễn phí của BigQuery thì chưa đến 5 giây.
    Tôi dùng dữ liệu công khai của FEMA; Snowflake và các dịch vụ AWS bị chặn vì cột geometry vượt quá kích thước byte tối đa, còn Spark thì không có kiểu dữ liệu không gian địa lý và các extension mã nguồn mở cũng chưa ổn.
    Nếu chạy on-premise thì tình hình có thể khác, nhưng với mức 20TB, chi phí lưu trữ BigQuery nhiều khả năng cũng dưới $100/tháng, nên tôi không muốn tự vận hành làm gì.

    • Tôi đã trải qua quy trình tương tự khi xử lý pipeline với OSM toàn cầu và Whosonfirst; chi phí Google tăng lên tới $7k/tháng với Airflow + BigQuery, nên tôi thay bằng việc mua một lần phần cứng trị giá $7k.
      Điều này khả thi vì ban đầu tôi dùng chỉ mục H3 và toàn bộ dataset trung gian đều nằm trong bộ nhớ.
      Cấu hình là Mac Studio 128GB + Asahi Linux + file parquet mmap + DuckDB; tôi cũng chạy Airflow, và dùng Nix để tăng tốc build cho developer cũng như chạy các tác vụ Airflow của đội dữ liệu.
      GCP rất tốt khi miễn phí hoặc rẻ, nhưng đến một lúc nào đó nó có thể làm bạn bất ngờ bằng một hóa đơn lớn hơn, dù mức sử dụng không tăng.
    • Tôi muốn nghe thêm về phần Spark không có kiểu dữ liệu không gian địa lý và các package mã nguồn mở còn chưa ổn.
      Không biết bạn đã so sánh với Apache Sedona chưa, và cụ thể là thiếu những gì?
      Tôi đang làm ở Wherobots, công ty do các tác giả Apache Sedona thành lập, nên rất muốn nghe phản hồi.
      https://sedona.apache.org/latest/
      https://wherobots.com/
    • Nếu có thể thì mong bạn link dataset cụ thể.
      Tôi đồng ý rằng một số công cụ sẽ “vỡ” với các cột rất rộng, nhưng các hệ Postgres hướng cột khác có vẻ có thể hỗ trợ mà không gặp vấn đề.
      Hơi bất ngờ là có vẻ bạn không dùng Redshift, đối thủ trực tiếp của BigQuery; cột super của Redshift còn có thể đặt lớn hơn cả giới hạn tối đa của BigQuery.
      Tôi thường thấy mọi người phải tự rút ra một cách khó nhọc rằng rất khó đánh bại PostGIS.
      Việc Trino/Presto và Spark bị chững lại trong mảng này cũng đặc biệt đáng suy ngẫm.
    • Tôi đã chuyển một data warehouse khoảng 500GB từ Postgres RDS 8 lõi sang BigQuery; thời gian rebuild giảm từ 5 giờ xuống 11 phút, còn chi phí thì tương đương hoặc thấp hơn.
      Trên Postgres tôi đã cache một phần các bảng lớn, còn trên BigQuery thì dựng lại từ đầu, vậy mà vẫn như thế.
      Với công cụ có thể tự vận hành, tôi thích Postgres hơn, nhưng khó mà phản bác khi hiệu năng tốt hơn nhiều lần, ở mức một chữ số.
    • Tôi nghe rất nhiều lời khen về BigTable và BigQuery, tiếc là vẫn chưa có cơ hội dùng thử.

  • Bài viết thật sự rất hay.
    Tôi phụ trách DevRel ở Timescale, và rất vui khi thấy cộng đồng tạo ra một bài viết tốt như vậy.
    Một trong những lý do hypertable chậm hơn gần như chắc chắn là vì mặc định nó tạo chỉ mục trên cột timestamp.
    Bảng thông thường không có chỉ mục nên có thể đã nhanh hơn.
    Trong create_hypertable, dùng create_default_indexes=>false có thể bỏ qua việc tạo chỉ mục; hoặc cũng có thể xóa chỉ mục trước khi nạp dữ liệu.
    Cuối cùng thì bạn vẫn sẽ cần chỉ mục đó, nhưng với kiểu nạp hàng loạt như thế này, tốt hơn là nạp xong rồi tạo một lần.
    Tôi cũng tò mò HDD đọc dữ liệu chịu được đến đâu trong cấu hình có mức song song hóa cao như vậy.

    • Tôi không biết về create_default_indexes=>false cũng như việc hypertable mặc định tạo chỉ mục thời gian, và tôi sẽ thêm một chú thích giải thích phần này.
      Tôi cũng muốn benchmark thử việc insert khi không có chỉ mục thời gian rồi sau đó tạo chỉ mục thủ công.
      Có vẻ HDD vẫn ổn ngay cả với 32 worker.
      Khi xem mức sử dụng đĩa bằng btop, SSD nơi chạy Postgres trông gần với nút thắt cổ chai hơn HDD, nên tôi kết luận rằng đầu tư đổi SSD dùng cho Postgres sang loại nhanh hơn sẽ đáng hơn so với chuyển dữ liệu từ HDD sang SSD.

  • Không hiểu tại sao lại phải làm như vậy
    Hầu hết các bộ dữ liệu thời tiết/khí hậu, bao gồm ERA5, đều được cấu trúc rất chặt chẽ trên một lưới vĩ độ-kinh độ đều đặn
    Ngay cả khi chỉ trích xuất chuỗi thời gian cho một vị trí cụ thể, điểm mạnh của các bộ dữ liệu kiểu này nằm ở cấu trúc và ngữ cảnh không-thời gian vốn có; nếu mục tiêu không phải chỉ là trích xuất chuỗi thời gian điểm thì việc phá vỡ hoàn toàn cấu trúc đó không hợp lý lắm
    Thậm chí trong trường hợp chỉ lấy chuỗi thời gian điểm, ví dụ như chuỗi nhiệt độ bề mặt ở giữa đại dương thì hầu như chẳng có mấy dịp dùng đến, nên có lẽ bạn sẽ muốn cắt giảm dữ liệu khá mạnh tay
    Phần lớn các ứng dụng nghiên cứu/vận hành của những bộ dữ liệu như ERA5 có vẻ phù hợp hơn với các bản sao tối ưu cho đám mây nhưng vẫn giữ cấu trúc gốc, như ARCO-ERA5 của Google Public Datasets
    Những phiên bản này giữ nguyên cấu trúc gốc, đồng thời chia chunk để phù hợp với truy cập song song quy mô lớn trên lưu trữ đám mây
    Trong gần như mọi trường hợp tôi từng thấy trong sự nghiệp, chỉ một kho lưu trữ dựa trên Zarr với cách chunking thông thường đã đủ nhanh cho hầu hết các mục đích đáng quan tâm
    https://cloud.google.com/storage/docs/public-datasets/era5

    • Lý do chính là đây là dự án cá nhân nên tôi muốn thử làm tất cả trên server ở nhà mà không trả chi phí tài nguyên đám mây, đồng thời muốn học Postgres, TimescaleDB, rồi sau đó cả PostGIS
      Tuy vậy, như rabernat đã nói, việc trích xuất chuỗi thời gian dài từ bản sao trên đám mây cũng chậm
      Cuối cùng tôi cũng muốn chạy các truy vấn không-thời gian phức tạp, chẳng hạn tính percentile 99 của nhiệt độ mùa hè ở Chile giai đoạn 1940~1980
      Tôi không nghi ngờ việc bản sao đám mây có thể nhanh hơn, nhưng điều đó mâu thuẫn với ngân sách $0
    • Đúng, nhưng thực tế dữ liệu công khai Google ERA5 cũng gặp đúng cùng vấn đề chunking như bài viết mô tả
      Nó được tối ưu cho truy vấn không gian, không phải truy vấn chuỗi thời gian
      Tôi vừa chạy benchmark, và mất 20 phút để lấy chuỗi thời gian của một biến duy nhất tại một điểm
      Điều này cho thấy rất rõ rằng nếu mô hình sử dụng dự kiến là chuỗi thời gian thì cần chunking tối ưu cho chuỗi thời gian
    • Ước gì có ai đó làm việc ấy và dạy cả người khác cách dùng
      Một số phòng lab đã có pipeline dựa trên RDBMS với thuật toán và dữ liệu công khai đan xen vào nhau; không ai muốn triển khai lại, và cũng không có kinh phí để làm việc đó
      Cải tiến tốt nhất mà chúng tôi đạt được chỉ là chuyển từ MySQL cũ sang Postgres + PostGIS
      Timescale có lẽ cũng đã giúp ích
      Cũng có những lý do buộc phải chạy cục bộ, như quyền riêng tư, quyền truy cập cluster, và ngân sách

  • Bài hay
    Điều còn thiếu ở đây, theo tôi, là phân tích xem việc chuyển dữ liệu thời tiết sang cơ sở dữ liệu quan hệ mang lại gì
    Động lực là tăng tốc độ truy vấn, nhưng cần có một baseline để so sánh
    Với tư cách là maintainer của Xarray và Zarr, đồng thời là nhà sáng lập https://earthmover.io/, tôi khá quen thuộc với bức tranh công nghệ này; nếu chunk dữ liệu trong Zarr một cách phù hợp, chỉ cần giải pháp serverless + object storage cũng có thể xử lý truy vấn chuỗi thời gian của dữ liệu thời tiết với độ trễ dưới 1 giây
    Nhanh hơn rất nhiều so với 30 phút được nêu trong bài
    Xét đến độ khó của việc nạp dữ liệu trong bài này, rất đáng để đánh giá nghiêm túc những cách như vậy trước khi đi theo hướng RDBMS

    • Tôi đồng ý rằng lưu trong file Zarr được chunk phù hợp gần như chắc chắn sẽ nhanh hơn, cấu hình đơn giản hơn, và tốn ít dung lượng hơn
      Cũng có thể đặt một API phía trước để biến nó thành dạng như truy vấn
      Đúng là tôi chưa biện minh đủ cho cách tiếp cận RDBMS
      Lý do chính tôi chọn Postgres + Timescale là vì tôi muốn học chúng, và nghịch với dữ liệu ERA5 có vẻ thú vị nhất
      Có lẽ sức hấp dẫn còn nằm ở việc dữ liệu thời tiết đủ lớn để trở thành một thử thách
      Không có bằng chứng, nhưng tôi tò mò liệu TimescaleDB + PostGIS được tinh chỉnh và đánh index tốt có giúp ích cho các truy vấn không-thời gian phức tạp như percentile 99 của nhiệt độ mùa hè ở Chile giai đoạn 1940~1980 hay không
      Vì trong trường hợp này có thể phải đọc nhiều chunk Zarr
      Tôi cũng thích ý tưởng có một bảng riêng để cache các thống kê kiểu này, nhưng làm bằng Zarr cũng không quá khó
      Lần tới tôi định benchmark truy vấn và index, nên chắc sẽ biết thêm
    • Hơi lệch chủ đề một chút, nhưng tôi cũng quan tâm đến cùng lĩnh vực
      Có vẻ tồn tại một mâu thuẫn căn bản: chunk lớn thì tốt cho trực quan hóa vùng rộng và truy vấn lớn, còn chunk nhỏ thì tốt cho truy vấn theo điểm hoặc chuỗi thời gian
      Việc duy trì riêng các phiên bản chunking khác nhau cho những bộ dữ liệu địa không gian lớn như vậy là khả thi, nhưng không hiệu quả về chi phí lắm
      Tôi từng nghe nói kerchunk được dùng để lấy ưu điểm của cả hai phía, nhưng như vậy có vẻ sẽ mất các lựa chọn nén dữ liệu, và tôi nghĩ độ phức tạp cũng tăng lên khá nhiều
      Tôi thắc mắc nên cân bằng giữa nhiều use case như thế nào cho tốt

  • Tôi đã đóng góp cho tài liệu nạp dữ liệu hàng loạt của Postgres được tham chiếu ở đây; đó là một bài viết điểm qua khá tốt nhiều kỹ thuật
    Tôi từng làm khá nhiều việc kiểu này để tăng tốc nạp cơ sở dữ liệu OpenStreetMap, và bản cập nhật công khai gần nhất nằm ở https://www.youtube.com/watch?v=BCMnu7xay2Y
    Kể từ đó, nhờ tiến bộ phần cứng, cải tiến GIS trong PG15, và việc osm2pgsql áp dụng kỹ thuật middle-way-node-index-id-shift, thời gian nạp bộ planet đã giảm xuống dưới 4 giờ
    Nếu đề xuất với tác giả, thì một số thử nghiệm đang gián tiếp loại bỏ việc ghi WAL thông qua pg_bulkload và COPY
    Vào thời điểm Craig Ringer viết bài SO được liên kết, điều này chưa được tài liệu hóa, nhưng trong cấu hình có thể tắt hẳn WAL
    Tất nhiên nếu gặp sự cố, bạn sẽ mất bảng đang xử lý, và nếu công việc kéo dài vài tuần thì chuyện đó có thể xảy ra
    Nhưng với dữ liệu chuỗi thời gian, nếu thiết kế cấu trúc nạp hợp lý thì không khó để chỉ mất chunk cuối cùng
    Với nạp dữ liệu hàng loạt, dữ liệu WAL về cơ bản là không cần thiết
    Nếu gặp sự cố, chỉ cần dọn phần mép bên phải của dữ liệu đã nạp rồi bắt đầu lại
    Các thiết lập postgresql.conf tôi dùng để tắt WAL và các overhead khác như sau:
    wal_level = minimal
    max_wal_senders = 0
    synchronous_commit = off
    fsync = off
    full_page_writes = off
    autovacuum = off
    checkpoint_timeout = 60min
    Cuối cùng, để giảm công việc vacuum khi nạp theo các chunk lớn, thường tôi tắt autovacuum như trên và định kỳ chạy VACUUM FREEZE phía sau partition theo ngày hiện đang được nạp
    Đây là với PG thông thường, và nó cho phép bỏ qua một phần công việc mà cơ sở dữ liệu phải quan tâm trong giai đoạn trung gian khi transaction mới đã được ghi nhưng chưa hiển thị với tất cả mọi người

    • Tôi sẽ thử các thiết lập tắt WAL và các overhead khác để xem việc insert có nhanh hơn không
      Đặc biệt trong nạp theo chunk, thật vui khi nghe một chuyên gia nói rằng dữ liệu WAL không thực sự cần thiết cho nạp hàng loạt
      Tôi chưa có UPS, nhưng hy vọng có thể vượt qua khoảng 20 ngày nạp dữ liệu mà không mất điện; ngay cả trường hợp xấu nhất thì có lẽ chỉ cần tiếp tục là được
    • Tôi muốn biết có tài liệu nào để xem thêm về các cải tiến GIS trong PG15 không

  • Nếu OP là tác giả, có thể bạn cũng sẽ thấy thí nghiệm tương tự tôi làm khoảng 4 năm trước khá thú vị
    Cùng bộ dữ liệu, cùng mục tiêu lưu trữ, mục tiêu cũng tương tự
    https://rdrn.me/optimising-sql/
    Hướng khảo sát tương tự, nhưng tôi dùng Postgres thông thường thay vì Timescale; trong cấu hình của tôi, với giả định dữ liệu đã nằm trong bộ nhớ, sao chép dữ liệu nhị phân trực tiếp nhanh hơn COPY khoảng 3 lần

    • Giá mà tôi thấy bài này trước khi bắt đầu
      Tôi có ghi chú chân trang về lý do không thử binary COPY, về cơ bản là vì có người khác nói hiệu năng gây thất vọng
      Dù vậy có lẽ tôi vẫn nên tự thử
      https://aliramadhan.me/2024/03/31/trillion-rows.html#fn:copy-binary-note
    • Tôi đã đọc phần 1 và phần 2, bài viết rất thú vị
      Tôi cũng thích hình thức có chú thích bên lề
      Việc bạn cung cấp hàm ghi mảng cấu trúc numpy sang nhị phân Postgres rất hữu ích; trước đây tôi chưa tìm ra cách làm việc này

  • “Cơ sở dữ liệu quan hệ có phù hợp với dữ liệu thời tiết dạng lưới không? Không biết, nhưng cứ thử thì sẽ biết.”
    Tôi thích thái độ này
    Nó hoàn toàn trái ngược với phong cách “thực ra thì...” của các bài viết kỹ thuật chính thống khác, nên tôi rất thích
    Tôi cũng thích việc bài viết giữ chân độc giả đến tận cuối trong suốt hành trình đó

    • Có lẽ việc là người mới và không có lợi ích liên quan lại là một lợi thế
      Tôi đã tìm kiếm rất nhiều nhưng không tìm được câu trả lời dứt khoát cho use case của mình, nên quyết định tự chạy benchmark

  • Bài viết thú vị
    Đoạn “Nếu duy trì khoảng 462k insert/giây thì mất khoảng 20 ngày cho khoảng 754 tỷ hàng, nghe không tệ. Còn ngắn hơn thời gian tôi viết bài này.” buồn cười thật
    Tôi cũng đang nghiêng về việc viết các bài blog dài và sâu hơn, nên đồng cảm rằng nó tốn nhiều công sức hơn tưởng tượng

    • Một số benchmark mất hàng giờ, vài cái còn phải chạy lại, và tôi cũng học được nhiều điều trong quá trình đó

  • Nếu muốn vẽ biểu đồ chuỗi thời gian hoặc nhiều biểu đồ trực tiếp từ truy vấn SQL, qStudio là một SQL IDE miễn phí và hoạt động với nhiều DB, bao gồm TimescaleDB
    https://www.timestored.com/qstudio/database/timescale
    Tuyên bố miễn trừ: đây là công cụ tôi tạo ra

    • Tôi tò mò quy trình thêm hỗ trợ cơ sở dữ liệu khác vào qStudio như thế nào
      Tôi nghĩ có thể thêm hỗ trợ Timeplus
      Timeplus là cơ sở dữ liệu ưu tiên streaming dựa trên ClickHouse, và engine DB lõi Timeplus Proton là mã nguồn mở
      qStudio cũng là mã nguồn mở và được viết bằng Java, nên có vẻ hỗ trợ RDBMS mới sẽ cần JDBC driver
      Nếu đúng vậy, Timeplus Proton có một JDBC driver mã nguồn mở dựa trên driver ClickHouse, kèm các chỉnh sửa cho mục đích streaming
      https://www.timeplus.com/
      https://github.com/timeplus-io/proton
      https://github.com/timeseries/qstudio
      https://github.com/timeplus-io/proton-java-driver
    • Tôi chủ yếu dùng TablePlus và matplotlib qua psycopg3 để vẽ kết quả truy vấn, nhưng cái này có vẻ có thể dùng nhanh hơn
      Hiện tôi mới chỉ insert dữ liệu, nhưng sắp tới sẽ thử query và plotting

  • Tôi cũng dùng dữ liệu tái phân tích ERA5 và cần chuỗi thời gian nhanh
    Vì dữ liệu được xếp theo lưới [lat, lon] cho từng khoảng thời gian đã chọn, ví dụ dạng [dữ liệu theo giờ trong một tháng, lat, lon], nên nếu muốn lấy hơn 20 năm thì sẽ thành bài toán chuyển vị một ma trận khổng lồ
    Cách tôi làm là tải từng tệp netCDF xuống, chuyển vị, rồi đưa vào một tệp HDF 3D khổng lồ có cấu trúc [lat, lon, hour]
    Trên workstation của tôi, mất khoảng 30 phút để tạo dữ liệu 1 năm cho một biến, nhưng sau đó việc lấy một vị trí (lat, lon) đơn lẻ chỉ tính bằng mili giây
    Đây là cách ban đầu thì vất vả nhưng về lâu dài có lợi
    Đơn giản vậy thôi, nhưng tôi không phải chuyên gia cơ sở dữ liệu, chỉ là một nhà khí hậu học

    • Tuy đơn giản, nhưng rất có khả năng nhanh hơn cơ sở dữ liệu quan hệ và cũng hiệu quả hơn về dung lượng
      Có vẻ rabernat và open-meteo đã bình luận ở đây cũng dùng cách tương tự và cho rằng nó nhanh