VXI 筆記

量化研究資料怎麼長期保存？PostgreSQL PITR 與 R2 歸檔的取捨

一年後想重新執行今天的一次量化研究，究竟需要還原什麼？

GitHub 上可能還有當時的策略程式碼，研究資料庫也留著一個 run_id。但行情供應商可能已經修訂歷史資料，當年的 API 原始回應也可能早已清掉。程式能跑完，不代表還能得到同一份結果。對長期累積信號與因子快照的 Python/SQL 研究系統來說，我更在意每次決策實際使用的輸入、程式碼與資訊可用時間，能否重新組合。

早期方案是讓 PostgreSQL 保留研究索引、信號和 outcome，將大型 raw_json 送到 Cloudflare R2，確認歸檔後再裁剪資料庫。但難點不在 gzip，也不在怎麼呼叫 S3 SDK，而是這份複雜度何時值得投入、為何要先建立 manifest，以及裁剪後怎麼證明還能還原舊研究。

我曾用 Python 標準函式庫做過一套離線實驗：SQLite 記憶體資料庫模擬研究紀錄，MemoryObjectStore 模擬物件儲存。它檢查了幾個防止誤刪的狀態條件，但沒有測試真正的 PostgreSQL/R2 故障，也沒有多個 worker 的併發驗證。下文將這些測試結果與文件推導的架構建議分開。

什麼時候只用 PostgreSQL 就夠了

如果個人專案的研究資料增長還不大、很少需要重播歷史執行，我會先把 PostgreSQL 的備份、實際還原演練、策略版本和參數紀錄做好。多維護一個 R2 bucket、manifest、對帳 worker 和相關故障處理，未必划算。

不能忽略現成的 PostgreSQL PITR（時間點還原）。透過 base backup 和持續封存的 WAL，可以把資料庫叢集還原到備份涵蓋的指定時間點。只要保存期限足夠長，且當年的研究輸入確實存在 PG，就能在隔離的資料庫中找回。

差別是復原粒度。PITR 主要還原整個資料庫叢集；研究人員問的是「給我 run_id=35 當時使用的原始行情」。為了一筆實驗還原舊叢集、保存多年的 WAL，並想辦法和今天的程式碼對接，可能比從 R2 取回一個不可變的資料包麻煩許多。PITR 也不會自動保存 Python 依賴、交易日曆或從未寫入資料庫的外部 API 回應。

我的判斷是：PITR 處理資料庫災難復原，依 run 保存的不可變資料包處理研究追溯。它們可以並存，也可能根本不需要後者。

我不想隨口訂一個「超過 100GB 就上 R2」的門檻。先用 PostgreSQL 的關聯大小函式觀察 heap、TOAST 和索引。以下是建議的唯讀 SQL，尚未在本專案的實際 PostgreSQL 環境執行：

SELECT
  pg_size_pretty(pg_relation_size('quant_research.signal_snapshot')) AS heap,
  pg_size_pretty(pg_table_size('quant_research.signal_snapshot')) AS table_including_toast,
  pg_size_pretty(pg_total_relation_size('quant_research.signal_snapshot')) AS total_with_indexes;

再把每日新增原始載荷、保存年限、查詢頻率、備份/WAL 增長和實際還原時間納入評估。當冷資料已經明顯拖累資料庫維護，而且確實需要逐次研究還原時，再引入 R2。

TOAST 與 MVCC，讓原地裁剪沒有想像中單純

PostgreSQL 本來就有 TOAST。當整個資料列太寬，通常接近 2KB 的門檻時，系統可能壓縮可變長欄位，必要時將內容移到主表之外的 TOAST 表；文件提到行外指標在磁碟上的長度是 18 位元組。

但「超過 2KB 的 JSONB 一定會放到行外」並不精確。還要看整列長度、壓縮結果和欄位的 storage strategy。如果一般查詢根本不載入 raw_json，TOAST 可能已經幫忙避掉不少負擔，不能因為欄位很大就急著拆表。

舊設計的風險來自 UPDATE：把 raw_json 換成摘要，MVCC 仍要建立新資料列版本、產生 WAL，後續再清理過期版本；被替換的 TOAST 資料也可能需要回收。大量更新歷史資料可能造成額外寫入、膨脹和鎖競爭，但不能斷言每次更新都會嚴重膨脹。

PostgreSQL VACUUM 文件也區分內部可重用空間與作業系統實際收回的磁碟空間。一般 VACUUM 多半是讓空間再次可用；VACUUM FULL 則會重寫資料表，有機會回收更多實體空間，但鎖和維護代價更高。部分尾端空間可以截斷，因此「物理檔案永遠不會變小」同樣不對。

如果是新系統，我會優先評估垂直拆表：

signal_snapshot
  snapshot_id | run_id | signal_time | score | raw_hash | archive_key

signal_raw_payload
  snapshot_id | payload_version | raw_json | captured_at

常用的信號欄位留在 signal_snapshot，完整載荷存到 signal_raw_payload。封存驗證後，只清理後者，不必大量更新主表。但垂直拆表不會讓 DELETE 跳過 MVCC/VACUUM。

如果資料自然依月份或批次到期，還可以考慮分區。官方 Partitioning 指南說明了移除整個到期分區比逐列刪除更有效率的原因。但必須先確定分區內每一筆資料都已驗證歸檔、符合保存期限與刪除權限。只要有一筆還不能刪，就不能直接 DROP 整個分區。

現有大表不應為了新架構立即搬遷。先測實際 heap/TOAST、WAL 和查詢成本，再決定保留 TOAST、垂直拆表或建立依刪除生命週期設計的分區。

為何採用 manifest-first，而不是先上傳物件

兩種順序都說得通：

Upload-first:
package → PUT object → GET/verify → commit PG manifest

Manifest-first:
commit PENDING manifest + expected digest → PUT object
→ GET/verify → mark VERIFIED in PG

Upload-first 不會產生「有 PENDING 紀錄但物件還不存在」的狀態。代價是另一個故障窗口：物件上傳成功，PG manifest 還沒寫入就當機，遠端留下孤兒物件。日後還原它，需要命名、歸屬和清理規則。

離線原型選擇 manifest-first，是想先保存可查詢、具唯一身份的歸檔意圖：run_id / revision / expected_sha256 在寫入遠端前就固定。上傳失敗時，對帳程序仍知道原本承諾保存哪一份內容。不過 PENDING 不是分散式鎖，也不保證兩個工作程序不會同時寫入。

這個選擇確實在實驗程式裡暴露一個漏洞。當上傳被拒絕，manifest 留在 PENDING，物件卻不存在；第一版對帳只檢查遠端物件，因此永遠無法修復。後來改成：只有仍能讀取完整原始資料，而且重新打包的 SHA-256 與長度都符合先前 manifest，才允許重傳。這是合成實驗中真正修過的邏輯，不是正式環境事故：

recovered = pack(self._source_rows(manifest["run_id"]))
if digest(recovered) != manifest["sha256"] or len(recovered) != manifest["byte_size"]:
    raise ArchiveError("pending manifest source changed; cannot re-upload")
self.store.put_if_absent(manifest["object_key"], recovered)

真正多 worker 的部署，還得有 lease 與 fencing token。假設 A 領到租約後卡在網路，租約過期由 B 接手；A 甦醒時不該還能用舊權限更新 PG。可以透過條件式 UPDATE ... RETURNING 搶占工作，並用遞增的 lease_epoch 擋掉過期持有者。以下是尚未在 PostgreSQL 執行的設計示意：

UPDATE archive_manifest
SET lease_owner = $2,
    lease_epoch = lease_epoch + 1,
    locked_until = clock_timestamp() + interval '5 minutes',
    status = 'RECONCILING'
WHERE identity = $1
  AND status IN ('PENDING', 'RECONCILING')
  AND (locked_until IS NULL OR locked_until < clock_timestamp())
RETURNING lease_epoch, expected_sha256, object_key;

PUT/GET 期間不應長時間佔住 PG 交易。驗證完成後，工作程序必須以領到的 lease_owner + lease_epoch 條件式更新狀態；舊 epoch 不能提交。五分鐘只是範例，不是通用門檻。

Lease 只能管 PG 這端的狀態，無法阻止過期 worker 已發出的遠端寫入。物件端仍須使用內容定址、不可變 key、禁止不同位元組覆寫同一身份的條件寫入，以及衝突後的完整對帳。沒有真正併發與網路故障測試前，我只能將其稱為設計方案。

R2 的版本身份、ETag 與物件 Metadata

Cloudflare 的 R2 S3 相容表列出 PutObject、HeadObject、GetObject 等操作，但目前 GetBucketVersioning 與 PutBucketVersioning 仍標示為未實作。不能假設 R2 有 AWS S3 原生的版本歷史功能。

我會用明確的不可變 key，例如 raw/v1/run=35/revision=2/sha256=...jsonl.gz。新載荷建立新 revision，不在原 key 上偷偷覆寫。保留策略還要協調 Bucket Locks 和 Object Lifecycle，不能把 R2 Bucket Locks 視為完整相容 AWS S3 Object Lock。

依 R2 S3 擴充說明，x-amz-meta-* 能映射到物件自訂 Metadata。可以把壓縮位元組 SHA-256、列數、schema 版本放進去，讓 HEAD 做低成本的初步對帳。以下是未在真實 R2 執行的 boto3 單次 PutObject 示意：

# gzip_bytes and expected_sha256 must refer to the same immutable input
s3.put_object(
    Bucket=bucket,
    Key=object_key,
    Body=gzip_bytes,
    Metadata={
        "sha256": expected_sha256,
        "row-count": str(row_count),
        "schema-version": "1",
    },
    IfNoneMatch="*",
)
head = s3.head_object(Bucket=bucket, Key=object_key)
assert head["Metadata"]["sha256"] == expected_sha256

但 HEAD 讀到的 SHA 是上傳者宣稱的字串，並不代表伺服端重新計算過檔案摘要。允許裁剪前仍需完整 GET、重新計算壓縮檔 SHA-256、解壓縮、核對 schema、列數和紀錄身份。離線 fixture 曾故意讓 HEAD 顯示存在，但 GET 回傳損毀內容，裁剪會被拒絕。

Multipart 也要另外驗收。分段上傳的 ETag 不能當完整 SHA-256，也不能假定單次 PutObject 的條件寫入語意直接適用於 CompleteMultipartUpload。實際 SDK 如何切分、abort、重試、防覆寫，必須在有權限的 R2 測試 bucket 上確認。

行情修訂，其實是雙時態資料問題

存下舊位元組還不夠。回測也必須知道：在當時的決策時間點，哪些資料已經可以使用？

這涉及 bitemporal 的兩個維度。市場事件時間（event/valid time）描述資料屬於哪個交易時段；observed_at 或 ingested_at 描述研究系統實際何時收到某個版本。資料供應商的 source_published_at 最好另存，因為供應商已發佈不代表你的系統已接收到。

合成資料中，同一交易日期先在 10 月 2 日收到 close=100，10 月 5 日才收到修正的 104：

as_of（UTC）當時可見 close
2026-10-01 09:00尚無紀錄
2026-10-03 00:00100
2026-10-06 00:00104

實驗只證明這個簡單離線選擇器不會讓晚到修訂穿越到更早的讀取時間。真實供應商的撤銷、更正優先順序、拆股、退市和成分股歷史，都還沒經過驗證。

正式研究 API 應要求傳入 as_of_utc、dataset_revision 或不可變 snapshot_id。歷史回放少了 cutoff，寧可拒絕請求，也不要偷偷使用今天的最新版。即時看盤則可以有獨立的 latest 視圖。若同一事件與觀測時點出現衝突，須依供應商版本/更正序列判定或隔離，不能單純以最後寫入資料庫者為準。

全部保存成 UTC 也不代表交易 cutoff 沒問題。美股常規收盤是交易所當地 16:00，America/New_York 會切換 EDT/EST；如果硬編固定 UTC 收盤時間，一年中有一段會錯。應依具版本的交易所日曆、IANA 時區和實際交易 session 產生決策截止事件，再轉為 UTC，並檢查資料何時真正進入系統。

例如 16:00:00 做決策，但正式收盤價到 16:00:02 才收到，前者就不該預先用到那根已完成的 bar。提早收盤、停牌與盤後更正，都會影響資料的可用範圍。復權因子、公司行動與指數成分名單，也需要各自的版本來源。之前的 因子有效性驗證 是從 signal-time snapshot 的角度碰到同一條時間界線。

離線測試並沒有證明一年後可以完整重跑

我真正執行的是 28 個離線合成回歸測試，28 個通過。它們涵蓋重複封存、模擬上傳失敗、物件缺失或損毀時拒絕裁剪、PENDING 修復、保存期/確認開關與簡單 as_of 修訂。測試的是程式自己實作的狀態路徑和注入的錯誤，並非網路分區、真正 PG 鎖、worker 競態或 R2 multipart 的實證。

兩筆 10.5 和 15.5 的合成資料，裁剪前、從歸檔取回、還原後的平均值都是 13.0。它只是 gzip、雜湊和最小恢復路徑的 smoke test，既不能證明複雜回測可重現，也沒有量到磁碟節省。

原始測試輸出：

$ python3 verify.py --output results
Ran 28 tests in 0.017s

OK
CASE_SUMMARY {"ran": 28, "passes": 28, "failures": 0, "errors": 0}

完整 fixture 仍放在私有 Content Lab 儲存庫，不是公開可下載的專案。不能把使用者看不到的私有 GitHub 連結當作一鍵重現證據。

更重要的缺口是完整的 reproduction_manifest。目前實驗記了玩具範例的物件雜湊、schema、計算函式、參數、Python 版本與 UTC，但沒有在另一台機器還原歷史 PostgreSQL 資料、checkout 舊策略 commit、重建依賴、載入當年交易日曆，再跑完整 factor/outcome 鏈路。文章開頭「一年後重跑」的要求，目前還未被驗證。

正式驗收時，我會要求：

  1. 固定原始資料的版本、物件 SHA-256、供應商修訂/as_of 和當年的 signal snapshot。
  2. 固定策略 Git commit、設定雜湊、依賴鎖檔或容器 digest、交易所日曆版本、時區及 outcome 定義。
  3. 用獨立 PostgreSQL 測真實交易、TOAST、並發 lease 和受控刪除；用授權 R2 測試 bucket 測單次及 multipart、斷線重試、條件寫入和完整 GET。
  4. 在全新環境還原舊 run，重新執行凍結版本的程式碼，逐項比對信號、決策與結果，而不只檢查退出碼是不是 0。

在真正的 PG/R2 還原和業務查詢完成驗收前，我仍會讓生產環境的自動 raw_json 裁剪維持關閉。多保留一些資料不是最糟的事；清理成功後過了幾個月，才發現無法解釋舊決策用的輸入，才是最難補救的問題。

Quant & Markets quant postgresql cloudflare-r2 data-engineering reproducibility