TimescaleDB에 1조 개의 기상 데이터 로딩

 (aliramadhan.me)
3P by GN⁺ | ★ favorite | 댓글 2개
  • 전 세계 과거 기상 변화를 빠르게 분석하려면 ERA5 재분석 데이터를 PostgreSQL/TimescaleDB에 넣어야 하며, 선택한 변수와 격자 기준으로 약 7,540억 행 규모가 됨
  • ERA5는 1940년부터 시간당 해상도와 0.25도 위경도 격자를 제공하지만, NetCDF 구조에서는 한 지점의 장기 시계열 추출도 20~30분이 걸릴 수 있음
  • 단일 행 insert는 초당 약 3천 행이라 전체 적재에 약 8년이 필요하고, 다중 행 insert도 psycopg3 기준 초당 2.5만~3만 행으로 약 10개월 수준임
  • PostgreSQL copy와 psycopg3 cursor.copy()는 CSV 준비 여부에 따라 차이가 나며, 오버헤드 포함 기준 약 초당 10만 행에서 병렬화와 설정 조정으로 더 끌어올릴 수 있음
  • 데이터프레임에서 바로 넣을 때는 psycopg3로 hypertable에 직접 copy, CSV가 이미 있을 때는 timescaledb-parallel-copy가 적합하며, 이 시스템에서는 12~16 워커가 병렬화의 적정 지점으로 보임

전 세계 기상 데이터 웨어하우스 만들기

  • 목표는 전 세계 과거 기상 데이터를 쿼리해 이미 발생한 기후 변화 신호를 분석할 수 있는 데이터 웨어하우스를 만드는 것임
  • 예시 분석은 Jakarta가 실제로 더워졌거나 폭풍이 잦아졌는지, Chile 전체가 더 따뜻해졌거나 구름이 많아졌는지, 지역별 변화가 어떤 형태인지 등을 다룸
  • 빠른 전역 분석을 하려면 데이터 웨어하우스 쿼리가 빨라야 하고, 첫 단계는 대량 데이터를 데이터베이스에 적재하는 일임
  • PostgreSQL을 기반으로 쓰고, 시간 기반 쿼리 가속에는 TimescaleDB, 향후 지리공간 쿼리 가속에는 PostGIS가 유망한 선택지임

ERA5 재분석 데이터와 7,540억 행 규모

  • 실제 기상 관측값 대신 ERA5 climate reanalysis 데이터를 사용함
    • 과거 관측값은 특정 지역과 과거 시점에서 희소할 수 있음
    • ERA5는 관측값에 맞도록 제약된 기후 모델 출력이며, 기상·기후 연구에서 널리 사용됨
  • ERA5는 전 지구를 0.25도 해상도로 덮고, 1940년부터 시간당 해상도로 제공됨
    • 시간 스냅샷은 변수당 727,080개
    • 격자점은 1,038,240개이며, 경도 1,440개와 양극을 포함한 위도 721개로 구성됨
    • 시간과 위치로 인덱싱하면 변수당 753,836,544,000행, 약 7,540억 행이 됨
  • 적재 대상 변수는 기온, 동서·남북 10m 풍속, 총운량, 강수량, 적설량임
  • 테이블에는 time, location_id, latitude, longitude와 각 기상 변수 컬럼을 둠
    • location_id와 위경도 컬럼을 함께 둔 이유는 이후 쿼리와 인덱스 벤치마크를 위한 것임

NetCDF 파일 구조가 느려지는 지점

  • ERA5는 NetCDF 파일로 배포되며, 보통 하루·한 달·1년 단위 파일에 데이터가 들어 있음
  • 시간 기준 청크 구조는 특정 시점 데이터를 조회하기에는 빠르고 간단함
  • 한 지점의 장기 시계열처럼 시간 패턴을 보려면 많은 파일을 읽어야 해서 느려짐
    • 예시로 한 위치의 기온 시계열을 뽑는 데 20~30분이 걸림
  • 복잡한 지리공간 쿼리, 특히 시간축을 포함한 쿼리는 느리고 수행하기 어려움
  • xarray, dask, Pangeo 같은 도구가 속도를 높일 수 있지만 여전히 느린 과정으로 남음

insert: 단일 행에서 다중 행까지

  • 가장 단순한 방식은 insert로 한 행씩 넣는 것임
  • 단일 행 insert는 여러 비용을 동반함
    • PostgreSQL이 문장을 파싱하고 테이블·컬럼 이름을 검증하며 실행 계획을 세움
    • 데이터 무결성을 위해 잠금이 필요할 수 있음
    • WAL(write-ahead logging)을 위해 데이터를 버퍼에 기록함
    • 실제 테이블 디스크 영역에 데이터를 삽입함
    • 트랜잭션 커밋 시 변경이 영구화됨
  • Python에서 단일 행 삽입을 위해 세 가지 방식을 벤치마크함
    • pandas df.to_sql()에 chunksize=1 사용
    • psycopg3의 매개변수화 쿼리 사용
    • SQLAlchemy의 매개변수화 쿼리 사용
  • 단일 행 삽입 결과는 psycopg3가 약간 앞서고 SQLAlchemy가 가장 느림
    • TimescaleDB hypertable은 일반 PostgreSQL 테이블보다 약간 느림
    • 최고 성능은 초당 약 3천 행이며, 전체 데이터를 넣는 데 약 8년이 걸리는 수준임
  • 다중 행 insert는 한 문장에 여러 행을 넣어 네트워크 왕복, 파싱, 계획 비용을 줄임
    • psycopg3가 초당 2.5만~3만 행으로 가장 빠름
    • pandas는 딕셔너리를 사용해 삽입하는 구조가 튜플보다 느릴 수 있음
    • SQLAlchemy는 세션 관리와 SQL 표현식 추상화 같은 추가 오버헤드가 있을 수 있음
    • 그래도 전체 적재에는 약 0.8년, 거의 10개월이 필요함

copy: PostgreSQL의 대량 적재 경로

  • PostgreSQL copy는 CSV 또는 바이너리 파일에서 행을 읽어 넣는 대량 적재 기능임
  • 대량 적재를 전제로 파싱, 계획, WAL 사용을 최적화하므로 다중 행 insert보다 빠름
  • 두 가지 경로를 비교함
    • NetCDF 데이터를 CSV로 저장한 뒤 copy로 적재
    • CSV 파일을 만들지 않고 psycopg3 cursor.copy()로 PostgreSQL에 직접 스트리밍
  • CSV 파일이 이미 준비되어 있으면 copy는 초당 약 40만 행에 가까운 삽입 속도를 냄
  • CSV 파일 쓰기나 튜플 생성 오버헤드를 포함하면 copy와 psycopg3 모두 약 초당 10만 행 수준이며, psycopg3가 약간 더 빠름
  • 이 속도에서도 전체 데이터 적재에는 약 3개월이 필요함

지속 적재율과 병렬 copy

  • 많은 행을 넣을 때는 디스크 쓰기, WAL과 테이블 삽입의 I/O 경쟁, autovacuum, checkpoint 같은 병목이 생길 수 있음
  • 약 7억7,200만 행을 744개 배치로 넣은 실험에서는 단일 워커 기준 큰 속도 저하가 보이지 않음
    • copy csv는 빈번한 하락이 나타나며 변동에 더 취약함
    • psycopg3는 대체로 더 빠름
    • 일반 테이블과 hypertable 간 차이는 크지 않음
  • joblib으로 여러 copy 작업 또는 psycopg3 cursor를 병렬 실행함
  • 단일 테이블 삽입은 병렬화가 잘 되는 작업이 아니며, 성능은 대체로 16 워커 이후 정체됨

pg_bulkload와 timescaledb-parallel-copy

  • PostgreSQL copy 외에 pg_bulkload와 timescaledb-parallel-copy를 벤치마크함
  • pg_bulkload는 기본 설정에서 더 빨라 보이지만, 기본적으로 shared buffers를 우회하고 WAL 로깅을 건너뛰므로 충돌 이후 데이터 복구가 불가능할 수 있음
  • fsync를 끈 동일 조건에서는 여러 워커를 쓰는 timescaledb-parallel-copy가 pg_bulkload보다 나음
  • timescaledb-parallel-copy는 워커 수를 지정해 병렬 삽입할 수 있음
    • 초기 성능은 좋지만, 이 시스템에서는 1억 행 이전에 병목에 도달해 삽입률이 급락한 뒤 파동처럼 회복됨
    • 지속 적재율은 일반 테이블에서 약 초당 60만~70만 행, hypertable에서 약 초당 30만 행 수준임
  • pg_bulkload는 워커 수 지정은 없지만 writer=parallel 옵션으로 읽기, 파싱, 쓰기에 여러 스레드를 사용함

PostgreSQL 설정 조정과 내구성 절충

  • 추가 성능은 PostgreSQL의 비내구성 설정을 조정해 얻을 수 있음
  • 주요 설정은 fsync를 꺼서 디스크 flush를 피하고, full_page_writes를 꺼서 partial page write 방어를 피하는 것임
  • 이 설정들은 충돌 시 데이터베이스 무결성을 위험하게 만들 수 있음
  • unlogged table은 WAL을 생성하지 않아 쓰기가 빠르지만, 충돌 복구 시 잘릴 수 있음
    • 이후 일반 logged table로 변환해야 하며 이 과정은 느리고 단일 스레드로 진행될 수 있음
    • hypertable은 unlogged가 될 수 없으므로, hypertable이 필요하면 추가 변환·마이그레이션이 필요함

최종 선택: hypertable에 직접 넣기

  • 목표가 hypertable이라면 일반 테이블에 넣은 뒤 hypertable로 변환하는 방식보다 hypertable에 직접 넣는 방식이 더 빠름
  • 약 7억7,200만 행을 psycopg3 copy와 16 워커로 넣은 간단한 테스트에서 hypertable 직접 삽입은 일반 테이블 삽입 후 변환보다 시간이 적게 걸림
    • 이 경우 hypertable 직접 삽입은 대략 80%의 시간이 걸림
    • 변환·마이그레이션 과정은 빠르지 않고 단일 스레드처럼 보임
  • 권장 방식은 다음과 같음
    • 데이터프레임에서 바로 적재한다면 psycopg3로 hypertable에 직접 copy
    • CSV 파일이 이미 있다면 timescaledb-parallel-copy 사용
    • 병렬화는 이 시스템에서 12~16 워커가 적정 지점으로 보임

전체 벤치마크 결론과 소요 시간

  • 보호 설정을 켠 단일 워커 기준, 오버헤드 포함 지속 적재율의 상한은 이 하드웨어에서 약 초당 14만 행으로 보임
  • 여러 워커를 쓰면 psycopg3 copy cursor로 보호 설정을 유지한 채 약 초당 25만 행까지 지속 적재율을 높일 수 있음
  • 삽입 프로세스는 병렬화 효율이 크지 않으며, 4~16 워커가 적정 구간으로 보임
  • 위험을 감수하고 fsync를 끄면 psycopg3로 약 초당 46.2만 행을 지속할 수 있음
  • pg_bulkload는 기본적으로 fsync를 비활성화하므로 사용 시 주의가 필요함
  • 약 초당 46.2만 행을 지속하면 약 7,540억 행 적재에 약 20일이 걸림

코드와 벤치마크 환경

  • ERA5 다운로드, 테이블 생성, 삽입·copy, 벤치마크, 그래프 생성 코드는 timescaledb-insert-benchmarks에 있음
  • 각 벤치마크마다 새 Docker 컨테이너를 띄워 일관된 환경을 유지함
    • Docker 컨테이너 간 스토리지는 유지하지 않음
    • 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 메모리와 48 CPU 기준으로 추천한 값을 사용하고, 벤치마크에서는 WAL 크기를 별도로 조정함

함께 보면 좋은 글 β

jangsc0000   [-]

GN+의견이 존댓말이네요..?

답변달기
GN⁺   [-]
Hacker News 의견들

  • 업무로 지리공간 분석을 꽤 해봤는데, 지리공간 데이터는 좌표 기준계(CRS)와 시각화 투영법을 정확히 이해해야 해서 생각보다 훨씬 미묘함
    메타데이터를 데이터와 함께 제대로 운반하는 인프라가 없으면 CRS 때문에 계속 불안해짐
    AWS의 관련 기능, Postgres/PostGIS, Spark/Databricks, Snowflake, Trino, ArcGIS를 다 써봤지만, 대규모 지리공간 작업에는 Google BigQuery가 압도적으로 좋다고 봄
    거대한 m6a EC2의 PostGIS에서 몇 시간 걸리고 비용도 많이 들던 쿼리가 BigQuery 무료 티어에서 5초도 안 걸렸음
    FEMA 공개 데이터를 썼는데, Snowflake와 AWS 서비스들은 geometry 열이 최대 바이트 크기를 넘어서 막혔고, Spark는 지리공간 자료형도 없으며 오픈소스 확장도 아쉬웠음
    온프레미스라면 사정이 다를 수 있지만, 20TB 기준 BigQuery 저장 비용도 월 $100 미만일 가능성이 커서 굳이 직접 운영하고 싶지는 않음

    • 글로벌 OSM과 Whosonfirst로 파이프라인을 처리하면서 비슷한 과정을 겪었고, Google 비용이 Airflow + BigQuery로 월 $7k까지 올라가서 한 번에 $7k짜리 하드웨어 구매로 대체했음
      초기에 H3 인덱스를 사용했고 중간 데이터셋이 모두 메모리에 올라갔기 때문에 가능했음
      구성은 128GB Mac Studio + Asahi Linux + mmap parquet 파일 + DuckDB이며, Airflow도 돌리고 Nix로 개발자 빌드 가속과 데이터팀 Airflow 작업 실행에도 씀
      GCP는 무료이거나 쌀 때는 좋지만, 사용량이 늘지 않아도 어느 순간 더 큰 청구서로 놀라게 할 수 있음
    • Spark에 지리공간 자료형이 없고 오픈소스 패키지도 아쉽다는 부분을 더 듣고 싶음
      Apache Sedona와 비교해봤는지, 구체적으로 뭐가 부족했는지 궁금함
      Apache Sedona 제작자들이 세운 Wherobots에서 일하고 있어서 피드백을 듣고 싶음
      https://sedona.apache.org/latest/
      https://wherobots.com/
    • 구체적인 데이터셋을 링크해줄 수 있으면 좋겠음
      아주 넓은 열이 많은 도구를 깨뜨린다는 데는 동의하지만, 다른 열 지향 Postgres 계열은 문제없이 지원할 수 있을 것 같음
      BigQuery와 직접 경쟁하는 Redshift를 안 쓴 것 같아 의외이고, Redshift의 super 열은 BigQuery 최대치보다도 더 크게 잡을 수 있음
      PostGIS를 이기기 어렵다는 걸 사람들이 계속 어렵게 깨닫는 걸 자주 봄
      Trino/Presto와 Spark가 이 영역에서 정체된 것도 특히 시사하는 바가 큼
    • 약 500GB 데이터 웨어하우스를 8코어 Postgres RDS에서 BigQuery로 옮겼는데, 재구축 시간이 5시간에서 11분으로 줄었고 비용은 비슷하거나 더 낮아졌음
      Postgres에서는 큰 테이블 일부를 캐싱했고 BigQuery에서는 처음부터 다시 만들었는데도 그랬음
      직접 운영할 수 있는 도구로는 Postgres를 더 좋아하지만, 성능이 한 자릿수 배 이상 좋아지는 건 반박하기 어렵다
    • BigTable과 BigQuery에 대한 좋은 얘기를 정말 많이 듣는데, 아직 써볼 기회가 없어서 아쉬움

  • 정말 멋진 글임
    Timescale에서 DevRel을 맡고 있는데, 커뮤니티에서 이렇게 잘 쓴 글을 만드는 걸 보면 좋음
    hypertable이 더 느렸던 이유 중 하나는 거의 확실히 timestamp 열에 기본으로 인덱스를 만들기 때문이라고 봄
    일반 테이블에는 인덱스가 없어서 더 빨랐을 가능성이 있음
    create_hypertable에서 create_default_indexes=>false를 쓰면 인덱스 생성을 건너뛸 수 있고, 데이터를 넣기 전에 인덱스를 삭제해도 됨
    결국 그 인덱스는 필요하겠지만, 이런 일괄 적재에서는 적재 후 한 번에 만드는 편이 좋음
    또 병렬성이 높은 구성에서 데이터를 읽는 HDD가 얼마나 버티는지도 궁금함

    • create_default_indexes=>false와 hypertable이 기본으로 시간 인덱스를 만든다는 걸 몰랐고, 이 부분을 설명하는 주석을 추가하겠음
      시간 인덱스 없이 삽입한 다음 수동으로 인덱스를 만드는 벤치마크도 해보고 싶음
      작업자 32개에서도 HDD는 괜찮았던 것 같음
      btop으로 디스크 사용량을 봤을 때 Postgres가 올라간 SSD가 HDD보다 병목에 가까워 보여서, 데이터를 HDD에서 SSD로 옮기기보다 Postgres용 SSD를 더 빠른 걸로 바꾸는 편이 나은 투자라고 결론냈음

  • 왜 굳이 그렇게 하는지 모르겠음
    ERA5를 포함한 대부분의 날씨·기후 데이터셋은 규칙적인 위도-경도 격자 위에 고도로 구조화되어 있음
    특정 위치의 시계열만 뽑더라도, 이런 데이터셋의 강점은 내재된 시공간 구조와 맥락에 있고, 점 시계열만 추출할 목적이 아니라면 그 구조를 완전히 깨뜨리는 건 별로 말이 안 됨
    심지어 점 시계열만 뽑는 경우에도, 예를 들어 바다 한가운데 표면 온도 시계열 같은 건 쓸 일이 거의 없으니 데이터를 꽤 과감히 줄이고 싶을 것임
    ERA5 같은 데이터셋의 연구·운영 활용 대부분은 Google Public Datasets의 ARCO-ERA5 같은 원본 구조를 보존한 클라우드 최적화 복제본을 쓰는 편이 더 적합해 보임
    이런 버전은 원본 구조를 유지하면서 클라우드 저장소에서 대규모 병렬 접근에 맞게 청크를 나눔
    내 경력에서 본 거의 모든 경우, Zarr 기반으로 일반적인 청킹을 한 아카이브만으로도 관심 가질 만한 대부분의 용도에는 충분히 빠름
    https://cloud.google.com/storage/docs/public-datasets/era5

    • 주된 이유는 개인 프로젝트라서 클라우드 자원 비용을 내지 않고 집 서버에서 전부 해보고 싶었고, Postgres, TimescaleDB, 나중에는 PostGIS까지 배우고 싶었기 때문임
      다만 rabernat가 말했듯 클라우드 복제본에서 긴 시계열을 뽑는 것도 느림
      결국 1940~1980년 칠레 여름 기온의 99백분위수를 계산하는 식의 복잡한 시공간 쿼리도 하고 싶음
      클라우드 복제본이 더 빠를 수 있다는 건 의심하지 않지만, 예산 $0과는 충돌함
    • 맞지만, 실제로 Google ERA5 공개 데이터도 글에서 설명한 것과 정확히 같은 청킹 문제를 겪음
      공간 쿼리에 최적화되어 있고 시계열 쿼리에는 최적화되어 있지 않음
      방금 벤치마크를 돌렸는데, 한 지점의 단일 변수 시계열을 가져오는 데 20분이 걸렸음
      예상 사용 패턴이 시계열이라면 시계열 최적화 청킹이 필요하다는 걸 잘 보여줌
    • 누군가 그걸 해주고 다른 사람에게 쓰는 법까지 가르쳐주면 좋겠음
      어떤 연구실들은 이미 공개된 알고리즘과 데이터가 얽힌 RDBMS 기반 파이프라인을 갖고 있고, 아무도 다시 구현하려 하지 않으며 그 일을 할 비용도 없음
      우리가 얻은 최선의 개선은 오래된 MySQL에서 Postgres + PostGIS로 옮기는 정도였음
      Timescale도 도움이 됐을 것 같음
      프라이버시, 클러스터 접근, 예산 같은 이유로 로컬에서 돌려야 하는 이유도 있었음

  • 좋은 글임
    여기서 빠진 건 날씨 데이터를 관계형 데이터베이스로 옮겨서 무엇을 얻는지에 대한 분석이라고 봄
    동기는 쿼리 속도를 높이는 것인데, 기준선이 무엇인지가 필요함
    Xarray와 Zarr 유지보수자이자 https://earthmover.io/ 창업자로서 이 기술 지형에 꽤 익숙한데, Zarr에서 데이터를 적절히 청킹하면 서버리스 솔루션 + 객체 저장소만으로도 날씨 데이터의 시계열 쿼리를 1초 미만 지연으로 처리할 수 있음
    글에서 말한 30분보다 훨씬 빠름
    이 글에 나온 데이터 적재 난이도를 생각하면, RDBMS 경로로 가기 전에 그런 해법을 진지하게 평가할 가치가 있음

    • 적절히 청킹한 Zarr 파일에 저장하는 편이 거의 확실히 더 빠르고, 설정도 단순하고, 공간도 덜 쓸 것이라는 데 동의함
      앞에 API를 붙여서 쿼리처럼 만들 수도 있음
      RDBMS 접근을 충분히 정당화하지 않았다는 점도 맞음
      Postgres + Timescale로 간 주된 이유는 그것들을 배우고 싶었고, ERA5 데이터로 놀아보는 게 가장 재미있어 보였기 때문임
      날씨 데이터가 도전이 될 만큼 크다는 매력도 있었던 듯함
      근거는 없지만, 잘 튜닝하고 인덱싱한 TimescaleDB + PostGIS는 1940~1980년 칠레 여름 기온의 99백분위수 같은 복잡한 시공간 쿼리에서 도움이 될지 궁금함
      이 경우 여러 Zarr 청크를 읽어야 할 수 있기 때문임
      이런 통계를 캐시하는 별도 테이블을 두는 아이디어도 마음에 들지만, Zarr로도 그렇게 어렵지는 않음
      다음에는 쿼리와 인덱스를 벤치마크할 예정이라 더 알게 될 것 같음
    • 약간 주제에서 벗어나지만 같은 영역에 관심이 있음
      큰 청크는 넓은 영역 시각화와 큰 쿼리에 좋고, 작은 청크는 점 기반 또는 시계열 쿼리에 좋다는 근본적인 긴장이 있는 것 같음
      이런 대형 지리공간 데이터셋을 서로 다른 청킹 버전으로 따로 유지하는 건 가능하지만 비용 효율은 별로임
      둘의 장점을 얻으려는 용도로 kerchunk가 쓰인다는 얘기를 들었지만, 그러면 데이터 압축 선택지를 잃는 것 같고 복잡도도 꽤 늘어난다고 생각함
      여러 사용 사례 사이에서 균형을 어떻게 잡는 게 좋을지 궁금함

  • 여기서 참조한 Postgres 대량 적재 문서에 기여했는데, 여러 기법을 잘 훑은 글임
    OpenStreetMap 데이터베이스 적재를 빠르게 하려고 이런 작업을 꽤 해봤고, 마지막 공개 업데이트 발표는 https://www.youtube.com/watch?v=BCMnu7xay2Y에 있음
    그 뒤로 하드웨어 발전, PG15의 GIS 개선, osm2pgsql의 middle-way-node-index-id-shift 기법 채택 덕분에 planet 세트 적재 시간이 4시간 아래로 내려갔음
    저자에게 제안하자면, 일부 실험은 pg_bulkload와 COPY를 통해 간접적으로 WAL 쓰기를 제거하고 있음
    Craig Ringer가 링크된 SO 글을 쓸 당시에는 문서화되어 있지 않았지만, 설정에서 WAL을 그냥 끌 수 있음
    물론 충돌이 나면 진행 중인 테이블을 잃고, 작업이 몇 주 걸리면 그런 일이 생길 수 있음
    하지만 시계열 데이터는 로딩 구조를 잘 잡으면 마지막 청크만 잃게 만들기 어렵지 않음
    대량 적재에는 WAL 데이터가 사실상 필요하지 않음
    충돌하면 적재된 데이터의 오른쪽 끝을 정리하고 다시 시작하면 됨
    WAL과 기타 오버헤드를 끄려고 쓰는 postgresql.conf 설정은 다음과 같음:
    wal_level = minimal
    max_wal_senders = 0
    synchronous_commit = off
    fsync = off
    full_page_writes = off
    autovacuum = off
    checkpoint_timeout = 60min
    마지막으로 큰 청크로 적재할 때 vacuum 작업을 줄이려면 보통 위처럼 autovacuum을 끄고, 현재 적재 중인 날짜 파티션 뒤에서 주기적으로 VACUUM FREEZE를 실행함
    일반 PG 기준이며, 새 트랜잭션이 쓰였지만 아직 모두에게 보이지 않는 중간 단계를 데이터베이스가 신경 쓰는 작업 일부를 건너뛰게 해줌

    • WAL과 다른 오버헤드를 끄는 설정을 시도해보고 삽입이 더 빨라지는지 보겠음
      특히 청크 단위 적재에서는 WAL 데이터가 대량 적재에 꼭 필요하지 않다는 말을 전문가에게 들으니 반가움
      UPS가 아직 없지만 정전 없이 데이터 적재에 걸리는 약 20일을 넘길 수 있기를 바라고, 최악의 경우에도 그냥 이어서 하면 될 것 같음
    • PG15의 GIS 개선에 대해 더 볼 수 있는 자료가 있는지 궁금함

  • OP가 저자라면, 4년쯤 전에 내가 했던 비슷한 실험도 흥미로울 수 있음
    같은 데이터셋, 같은 대상, 비슷한 목표였음
    https://rdrn.me/optimising-sql/
    비슷한 조사 흐름이지만 Timescale 대신 일반 Postgres를 썼고, 내 구성에서는 데이터가 이미 메모리에 있다는 가정하에 바이너리 데이터 직접 복사로 COPY보다 약 3배 더 빨라졌음

    • 시작하기 전에 이걸 봤으면 좋았을 것 같음
      바이너리 COPY를 시도하지 않은 이유를 각주로 남겼는데, 기본적으로 다른 사람이 성능이 실망스럽다고 했기 때문임
      그래도 직접 해봐야 할 것 같음
      https://aliramadhan.me/2024/03/31/trillion-rows.html#fn:copy-binary-note
    • 1부와 2부를 읽어봤고, 글이 재미있었음
      여백 주석을 넣은 형식도 좋음
      numpy 구조화 배열을 Postgres 바이너리로 쓰는 함수를 제공해줘서 도움이 됐고, 전에는 이걸 알아내지 못했음

  • “격자형 날씨 데이터에 관계형 데이터베이스가 적합하긴 한가? 모르겠지만 해보면 알겠지.”
    이 태도가 좋음
    다른 주류 기술 글들의 “사실은 말이죠” 스타일과 정반대라서 마음에 듦
    여정을 따라가는 동안 독자를 끝까지 붙잡아두는 점도 좋음

    • 초보라서 이해관계가 없다는 게 장점일 수도 있음
      많이 검색했지만 내 사용 사례에 대한 결정적인 답을 찾지 못해서, 직접 벤치마크를 돌려보기로 했음

  • 흥미로운 글임
    “지속적으로 초당 약 462k 삽입이면 약 7540억 행에 약 20일이 걸리는데 나쁘지 않은 듯. 이 글 쓰는 데 걸린 시간보다 짧다.”라는 부분이 웃김
    나도 더 길고 깊이 있는 블로그 글을 쓰는 쪽으로 기울고 있어서, 생각보다 많은 노력이 든다는 데 공감함

    • 벤치마크 일부가 몇 시간씩 걸렸고 몇 번은 다시 돌려야 했으며, 그 과정에서 배우는 것도 많았음

  • SQL 쿼리에서 바로 시계열 차트나 여러 차트를 그리고 싶다면 qStudio가 무료 SQL IDE이고 TimescaleDB를 포함해 여러 DB와 동작함
    https://www.timestored.com/qstudio/database/timescale
    면책: 내가 만든 도구임

    • qStudio에 다른 데이터베이스 지원을 추가하는 절차가 궁금함
      Timeplus 지원을 추가할 수 있지 않을까 생각함
      Timeplus는 ClickHouse 기반의 스트리밍 우선 데이터베이스이고, 핵심 DB 엔진인 Timeplus Proton은 오픈소스임
      qStudio도 오픈소스이고 Java로 작성되어 있으니 새 RDBMS 지원에는 JDBC 드라이버가 필요해 보임
      맞다면 Timeplus Proton에는 ClickHouse 드라이버 기반에 스트리밍 용도 수정을 더한 오픈소스 JDBC 드라이버가 있음
      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 경유로 써서 쿼리 결과를 그렸는데, 이건 더 빠르게 쓸 수 있어 보임
      아직은 데이터 삽입만 했지만 곧 쿼리와 플로팅을 해볼 예정임

  • 나도 ERA5 재분석 데이터를 쓰고 빠른 시계열이 필요함
    데이터가 선택한 기간별로 쌓인 [lat, lon] 격자, 예를 들어 [한 달치 시간별 데이터, lat, lon] 형태로 오기 때문에 20년 이상을 원하면 거대한 행렬 전치 문제가 됨
    내가 하는 방식은 각 netCDF 파일을 내려받고, 전치한 다음 [lat, lon, hour]로 구성된 거대한 3D HDF 파일에 넣는 것임
    내 워크스테이션에서는 변수 하나의 1년치를 만드는 데 약 30분이 걸리지만, 이후 단일 (lat, lon) 위치를 가져오는 건 밀리초 단위임
    처음에는 고생하고 장기적으로 이득을 보는 방식임
    단순하지만, 나는 데이터베이스 전문가가 아니라 기후학자일 뿐임

    • 단순하지만 관계형 데이터베이스보다 더 빠르고 공간 효율도 좋을 가능성이 큼
      여기서 댓글 단 rabernat와 open-meteo도 비슷한 방식을 쓰고 빠르다고 보는 것 같음
답변달기