Contents
pg_nanotime 測試報告
這份文件整理到目前為止所有的測試：回歸測試、與 timestamp9 的行為比較、TimescaleDB 相容性、效能基準測試、遷移與並行測試。文中數字全部取自實際執行的紀錄，沒有估算值。只跑過一次的數字會標明，雜訊較大的也會說明。測試日期都是 2026-10-23（Asia/Taipei）。
1. 測試環境
| 項目 | 內容 |
|---|---|
| 機器 | 共享 KVM 虛擬機，8 vCPU Intel Xeon（時鐘源 kvm-clock），15 GiB RAM |
| 作業系統 | Debian 13，Linux 6.12 |
| PostgreSQL | 17.11 與 18.6（PGDG 套件，gcc 14.2.0） |
| TimescaleDB | 2.31.0（Timescale 官方套件） |
| timestamp9 | 1.4.0，commit 2d689f8，CMake 建置（只裝在 PG 18） |
| 編譯 | PGXS，PG 17 與 18 都是 0 警告 |
| pg_nanotime commit | Phase 1：4fa53d5、5d4e08f；Phase 2：0df79e0、da6916d、b99b2f7；Phase 3：eddc1f3（文件更新在其後的 commit） |
| 核心 TAI 偏移 | 0 秒（這台機器沒有設定，見 §8） |
這是共享虛擬機，同一個測試重跑一次常有 10–20% 的差距，偶爾會出現明顯的異常值。只差幾個百分點的結果請視為打平。
2. 回歸測試（pg_regress）
執行方式
make PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config
sudo make install PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config
make installcheck PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config PGHOST=/tmp PGPORT=5432 PGUSER=postgres
# 加跑 TimescaleDB 測試（伺服器需要已安裝 TimescaleDB，並已加入 shared_preload_libraries）：
make installcheck WITH_TIMESCALEDB=1 ...
.so 檔依 PG 版本編譯，換版本測試前要先 make clean 再重新編譯、安裝。
各測試檔涵蓋範圍
| 測試 | 內容 |
|---|---|
basic |
儲存格式、範圍上下界與越界錯誤、1677/2262 以外與 1970 以前的值、比較、聚合 |
io |
1/9/10 位小數、ISO T/Z、多個時區、與 timestamptz ISO 輸出一致、負值、text 與 binary 往返（COPY） |
casts |
timestamptz floor 取整、infinity 報錯、bigint 極值與 ±1 ns 越界、隱式轉型被拒 |
clocks |
clock_ns/now_ns/statement_ns 的易變性、交易內不變、now_ns() >= now()、作為 DEFAULT |
index |
B-tree 範圍與排序（含兩端極值）、hash 等值與 hash join、BRIN |
compact |
8 B nanotimestamptz8：範圍、越界錯誤訊息、轉型、索引、時鐘函數 |
interval |
nanointerval I/O、單位、ISO 格式、溢位、運算子、與 interval/bigint 轉型 |
arith |
ts − ts、ts ± nanointerval、ts ± interval（DST）、溢位 |
bucket |
time_bucket_ns（1970 以前、origin、夾到最小值）、date_trunc、extract_ns |
notz |
nanotimestamp：I/O、DST 缺口、索引、轉型（與核心 timestamp/timestamptz 的 DST 行為比對）、timezone()、算術 |
migrate |
遷移輔助函數：運算式、可疑值計數、dry_run、預設值處理、逐位元比對、索引重建、失敗時不變更 |
monotonic |
clock_ns_monotonic：20 萬次呼叫嚴格遞增、模擬時鐘倒退 2 秒、監控計數、權限、nanotime.clock_source |
global |
clock_ns_global：有 preload 時（global.out）測 20 萬次嚴格遞增；沒有 preload 時（global_1.out）測錯誤訊息 |
timescale（選用） |
分區映射的飽和、ns()、nanotime_now_int8、16 B 與 8 B hypertable、排除 chunk、drop_chunks、保留策略 |
結果
| 階段 | PG 17.11 | PG 18.6 |
|---|---|---|
| Phase 1（5 個測試） | — | 連續 3 次 5/5 通過 |
| Phase 1 加入 8 B 型別（6 個） | 3 次 6/6 | 3 次 6/6 |
| Phase 2 第一批（9 個） | 3 次 9/9 | 3 次 9/9 |
| Phase 2 完成（11 個） | 3 次 11/11 | 3 次 11/11 |
| Phase 3（14 個，含 timescale，有 preload） | 3 次 14/14 | 3 次 14/14 |
global_1.out（沒有 preload）只在 PG 18 實際產生並比對過。
過程中發現並已修正的問題：
- -9223372036854775808 ns 轉 bigint 時，中間值會誤報溢位。
- 測試把文件寫的「4713 BC」誤當下界，實際的公曆輸出是 4714-11-24 BC。
- 兩台伺服器共用 /tmp 下同名的 COPY 檔，造成權限衝突（環境問題）。
- SQL 函式名 interval 是保留字。
- 整數字面值乘 nanointerval 有歧義，已加上 int4 版本的運算子。
- 遷移程序原本會丟掉欄位的預設值，現在改為保留，無法轉換時報錯並附提示。
3. timestamp9 1.4.0 行為紀錄
這是設計 pg_nanotime 時對 timestamp9 現況所做的實測紀錄，用來決定新型別的範圍檢查與取整規則。腳本：bench/phase0/timestamp9_boundaries.sql，session 時區為 UTC。
| 指令 | 輸出 | 說明 |
|---|---|---|
(-9223372036854775808)::bigint::timestamp9 |
1677-09-21 00:12:43.145224192 +0000 | 下界 |
9223372036854775807::bigint::timestamp9 |
2262-04-11 23:47:16.854775807 +0000 | 上界 |
(-1)::bigint::timestamp9 |
1969-12-31 23:59:59.999999999 +0000 | 1970 年以前可正常使用 |
'1677-09-21 00:12:43.145224191 +0000'::timestamp9 |
2262-04-11 23:47:16.854775807 +0000 | 超出範圍的值會繞回 |
'2262-04-11 23:47:16.854775808 +0000'::timestamp9 |
1677-09-21 00:12:43.145224192 +0000 | 同上 |
'1500-01-01 00:00:00 +0000'::timestamp9 |
2084-07-20 23:34:33.709551616 +0000 | 同上，從 timestamptz 或 date 轉入時也一樣 |
'3000-01-01 00:00:00 +0000'::timestamp9 |
1830-11-23 00:50:52.580896768 +0000 | 同上 |
(-1)::bigint::timestamp9::timestamptz |
1970-01-01 00:00:00+00 | 轉成微秒時往 0 取整 |
'1900-01-01 00:00:00.000000001 +0000'::timestamp9::timestamptz |
1900-01-01 00:00:00.000001+00 | 同上（1970 年以前會往上進位） |
(-1)::bigint::timestamp9::date |
1970-01-01 | 同上 |
9223372036854775807::bigint::timestamp9 + interval '1 microsecond' |
1677-09-21 00:12:43.145225191 +0000 | 加減運算超出範圍時會繞回 |
timestamp9 - timestamp9 |
ERROR: operator does not exist | 沒有差值運算子 |
| 函數名稱含 now、clock、current 的函數 | 0 個（共 25 個函數） | 沒有產生目前時間的函數 |
timestamp9 的範圍其實涵蓋 1970 年以前；它 README 中「最小值 1970」的說法與實測不符。
pg_nanotime 的對應做法：所有輸入、轉型、算術超出範圍時都會報 out of range 錯誤（ERRCODE_DATETIME_VALUE_OUT_OF_RANGE）；轉成微秒或日期時一律用 floor（往負無限大取整）。
4. TimescaleDB 相容性
timestamp9 作為對照組（TimescaleDB 2.31.0、PG 18.6）
| 項目 | 結果 |
|---|---|
| 不加分區函數直接建 hypertable | 可以，被視為整數維度 |
| 1970 年以前的 chunk、排除 chunk、壓縮 | 正常 |
time_bucket(bigint, t) |
函數不存在；time_bucket(..., t::bigint) 可用 |
| 連續聚合 | 不支援（time bucket 必須直接引用維度欄位；有自訂分區函數時也不支援） |
set_integer_now_func |
必須設分區函數才可以 |
drop_chunks、保留策略 |
參數只能用 bigint |
pg_nanotime 型別
| 項目 | nanotimestamptz8（8 B） |
nanotimestamptz / nanotimestamp（16 B） |
|---|---|---|
| 建立 hypertable | 不需分區函數就可以（by_range('t', ns('1 day'))） |
要用 partition_func => 'nanotime_part'（無時區版本用 nanotime_part_notz） |
| 排除 chunk（壓縮前後） | 只掃描 1 個 chunk | 只掃描 1 個 chunk |
| 1677/2262 以外的值 | 寫入時就會報錯（型別範圍） | 映射飽和到 int64 兩端，落入兩端的 chunk，查詢正常 |
set_integer_now_func(nanotime_now_int8) |
需要 partition_func => 'nanotime_part8'（恆等映射）；沒有的話報 custom time function not supported |
可以 |
drop_chunks、add_retention_policy |
可以，參數用 ns('...') 或 nanotime_part8(...) |
可以 |
| 壓縮 | 可以（ColumnarScan 仍能排除 chunk） | 可以（以 ns16 原型測試） |
time_bucket |
不接受此型別；請改用 time_bucket_ns 或 time_bucket(bigint, t::bigint) |
同左 |
| 連續聚合 | 不支援 | 不支援 |
需要連續聚合時，請把時間另外存成 bigint 欄位。完整用法見 TIMESCALEDB.md。
分區函數邊界（ns16 原型）：輸入低於 1677 下界 1 ns 時輸出 INT64_MIN，高於 2262 上界 1 ns 時輸出 INT64_MAX。西元 1 年與 9999 年分別飽和到兩端。各 100 萬筆隨機值排序後比對，遞減次數都是 0。正式版的 nanotime_part 在 timescale 回歸測試中驗證了相同的邊界。
5. 效能基準測試
設定：PG 18.6，shared_buffers 2GB、maintenance_work_mem 1GB，關閉平行查詢，unlogged 表 (t, v int)。10M 列取 3 次乾淨執行的中位數；50M 列只跑 1 次。腳本：bench/phase0/bench_types.sh。表中的 ns16 是與正式版佈局相同的原型。
10M 列
| 項目 | bigint | timestamptz | timestamp9 | ns16 | uuid |
|---|---|---|---|---|---|
| 表大小 (MiB) | 422.3 | 422.3 | 422.3 | 497.6 | 497.6 |
| B-tree 大小 (MiB) | 214.2 | 203.3 | 214.2 | 300.8 | 300.8 |
| BRIN 大小 (KiB) | 32 | 32 | 不支援 | 40 | 40 |
| 批次 INSERT (ms) | 4387 | 7214 | 4360 | 4944 | 8615 |
| 建 B-tree (ms) | 2008 | 2232 | 1746 | 1947 | 2222 |
| 建 BRIN (ms) | 881 | 897 | 不支援 | 920 | 997 |
| ORDER BY 全表 (ms) | 741 | 765 | 733 | 880 | 1109 |
| 點查詢 (ms，200 次中位數) | 0.074 | 0.078 | 0.109 | 0.091 | 0.083 |
| 1% 範圍掃描 B-tree (ms) | 11.21 | 11.92 | 11.62 | 11.55 | 12.06 |
| 1% 範圍掃描 BRIN (ms) | 13.06 | 13.16 | 不支援 | 13.92 | 13.87 |
50M 列（只跑 1 次）
| 項目 | bigint | timestamptz | timestamp9 | ns16 | uuid |
|---|---|---|---|---|---|
| 表大小 (MiB) | 2111.5 | 2111.5 | 2111.5 | 2488.0 | 2488.0 |
| B-tree 大小 (MiB) | 1071.1 | 1016.4 | 1071.1 | 1504.1 | 1504.1 |
| BRIN 大小 (KiB) | 88 | 88 | 不支援 | 136 | 136 |
| 批次 INSERT (ms) | 288883（異常值） | 35833 | 20998 | 26198 | 41796 |
| 建 B-tree (ms) | 13774 | 12670 | 12249 | 13716 | 13562 |
| ORDER BY 全表 (ms) | 4067 | 3949 | 4227 | 4821 | 4788 |
| 1% 範圍掃描 B-tree (ms) | 58.3 | 74.6 | 61.0 | 59.3 | 57.7 |
注意事項：
- INSERT 量測的是 INSERT ... SELECT 加上從 bigint 轉型的時間，不是 COPY。
- 50M 列 bigint 的 INSERT 時間明顯異常（可能剛好遇到 checkpoint 或前一輪的清理），不應採用。
- 結論：16 B 的主要代價是空間，表約大 18%，B-tree 約大 40%。寫入與查詢的差距都在雜訊範圍內，排序約慢 15–20%。
時鐘函數（每次呼叫的額外成本）
扣除 count(1) 基準後的值。Phase 0 是在原型上量測（5M 次、5 次中位數）；Phase 3 用正式版，腳本是 bench/phase3/cost.sh。
| 函數 | Phase 0 (ns) | Phase 3：PG 17.11 (ns) | Phase 3：PG 18.6 (ns) |
|---|---|---|---|
clock_timestamp() |
19.2 | 20.2 | 47.6（明顯偏離，視為雜訊） |
clock_ns8()（8 B） |
26.0（bigint 原型） | 19.6 | 19.2 |
clock_ns8_monotonic() |
— | 24.8 | 20.2 |
clock_ns8_global()（單一連線） |
— | 23.6 | 16.5 |
clock_ns()（16 B） |
36.8 | 38.1 | 39.5 |
clock_ns_monotonic() |
— | 35.5 | 31.3 |
clock_ns_global()（單一連線） |
— | 40.5 | 39.0 |
單一連線時，monotonic 與 global 的額外成本都在雜訊範圍內。16 B 版本因為要配置記憶體（palloc），每次呼叫大約多 15–20 ns。
6. 16 B 佈局比較與 COPY
ns16 = {int64 sec, int64 nsec} 對照 ns128 = 單一 __int128 奈秒，兩者都是 16 B、傳參考。腳本：bench/phase0/bench_copy_layout.sh。
| 10M 列（兩輪，各取 3–5 次中位數） | ns16 | ns128 |
|---|---|---|
| 表 / B-tree / BRIN 大小 | 497.6 MiB / 300.8 MiB / 40 KiB | 相同 |
| 建 B-tree (ms) | 1793 / 2009 | 1783 / 1885 |
| 建 BRIN (ms) | 933 / 938 | 1098 / 928 |
| ORDER BY 全表 (ms) | 556 / 583 | 625 / 571 |
| 1% 範圍 B-tree (ms) | 11.10 / 12.71 | 13.80 / 11.87 |
| 1% 範圍 BRIN (ms) | 13.31 / 14.49 | 14.72 / 13.97 |
| 1% 範圍全表掃描 (ms) | 337.5 / 346.6 | 384.6 / 352.5 |
兩輪的快慢方向不一致，屬於雜訊。最後選擇 sec+nsec，理由是：可攜到 MSVC、沒有對齊問題、text I/O 較簡單。
| 10M 列 COPY FROM（3 次中位數，ms） | text | binary | text 檔大小 (MB) |
|---|---|---|---|
| bigint | 2236 | 1728 | 278.9 |
| timestamptz | 5196 | 2170 | 377.6 |
| timestamp9 | 6616 | 1578 | 438.9 |
| ns16 | 2940 / 2639 | 1926 / 2022 | 288.9 |
| ns128 | 2366 / 2536 | 1927 / 1886 | 278.9 |
原型的 text 格式（sec.nnnnnnnnn 或整數奈秒）與正式版的 ISO 8601 格式不同，所以 ns16/ns128 的 text COPY 數字只能當作下限。timestamp9 的 text COPY 較慢，是因為它要解析完整的日期字串。
7. 遷移測試
真實的 timestamp9 1.4.0 資料表（PG 18.6）：
- 200,000 列一般資料，另加 4 個邊界值：INT64_MIN（1677）、1969-12-31 23:59:59.999999999、繞回後的 1500-01-01（顯示為 2084-07-20 23:34:33.709551616）、INT64_MAX（2262）。
- 建了 B-tree、unique (t, id)、hash 三個索引。
| 步驟 | 結果 |
|---|---|
CALL nanotime_migrate_column('trades','t') |
報錯：舊預設值（timestamp9 常數）無法轉換，並提示使用 new_default |
加上 new_default => 'clock_ns()' |
成功，兩次執行分別約 531 ms 與 505 ms；3 個索引自動重建 |
| 逐列比對 | 200,004/200,004 列的 ns 值與文字輸出完全相同 |
| 之後的查詢計畫 | 範圍查詢使用 Index Scan |
再轉成 nanotimestamptz8 |
200,004 列全部相同，索引與預設值保留 |
繞回損毀無法可靠偵測。 timestamp9 繞回後的值仍是合法時刻。nanotime_suspect_count(tbl, col, lo, hi) 只能統計落在預期時間窗外的列數。用預設窗 1990–2100 時抓到 3 列（1677、1969、2262），繞回後的 2084 值因為落在窗內，沒有被抓到。窗要依資料實際的時間跨度設定。
回歸測試 migrate 在 PG 17 上用名為 timestamp9 的 bigint domain 模擬，因為 PG 17 沒有安裝 timestamp9。
8. Phase 3：嚴格遞增時鐘的並行測試
腳本：bench/phase3/concurrency.sh <port> 64 15。條件：pgbench 64 個連線、8 個執行緒、15 秒。每個交易插入一列，同時呼叫 clock_ns8_global()、clock_ns8_monotonic()、clock_ns8()。
| 項目 | PG 17 第 1 次 | PG 17 第 2 次 | PG 18 第 1 次 | PG 18 第 2 次 |
|---|---|---|---|---|
| 列數 | 3,033,533 | 2,590,012 | 2,494,094 | 2,390,116 |
clock_ns8_global() 重複值 |
0 | 0 | 0 | 0 |
| 各連線內不遞增的次數（global / monotonic） | 0 / 0 | 0 / 0 | 0 / 0 | 0 / 0 |
對照：clock_ns8() 重複值 |
4 | 5 | 6 | 3 |
| 對照：monotonic 跨連線重複值 | 0 | 0 | 1 | 1 |
| global 遞增 +1 ns 的次數 / 總呼叫次數 | 1,547 / 3,233,538* | 2,040 / 2,590,012 | 1,946 / 2,494,094 | 1,813 / 2,390,116 |
| 與 identity 順序相反的列數 | 35,196 | 44,878 | 37,520 | 38,152 |
* 第 1 次執行前沒有重設計數器，所以總呼叫次數包含先前回歸測試的呼叫。腳本已改為先重設。
說明：
- clock_ns_global() 保證「呼叫順序」嚴格遞增且不重複，不保證與 commit 順序一致。表中「與 identity 順序相反」的列數就是這個差異：先取到時間的交易，可能較晚取得 identity 值或較晚 commit。需要依 commit 順序排序時，請不要依賴這個時間戳。
- clock_ns_monotonic() 只保證同一連線內遞增，不同連線之間可能重複（PG 18 兩次各出現 1 筆）。
多連線同時呼叫的總呼叫速率（bench/phase3/contended.sh，8 個連線、10 秒、每個交易呼叫 10 萬次）：
| 函數 | PG 17.11 | PG 18.6 |
|---|---|---|
clock_ns8() |
60.76 M 次/秒 | 84.77 M 次/秒 |
clock_ns8_monotonic() |
58.55 M 次/秒 | 84.56 M 次/秒 |
clock_ns8_global() |
17.19 M 次/秒 | 12.23 M 次/秒 |
clock_ns_global() 在多個連線同時大量呼叫時，因為共享同一個 cache line 上的 atomic 變數，總速率只剩一般時鐘的約 14–28%。上面的 INSERT 測試每秒約 16–20 萬筆交易，每筆只呼叫一次，遠低於這個上限。不過我們沒有另外跑「不呼叫 global」的對照組，所以這只是推論，並未實際量測。
時鐘倒退：回歸測試用僅限 superuser 的 nanotime.debug_clock_offset_us 把時鐘往回撥 2 秒來驗證，結果如下：
- monotonic 仍然嚴格遞增，倒退時取上一個值 +1 ns。
- nanotime_clock_status() 記錄 1 次倒退，幅度約 2 秒，lead（領先實際時鐘的量）約 2 秒。
- 一般的 clock_ns() 會跟著時鐘倒退。
- 時鐘往前跳不算倒退。
- 切換 nanotime.clock_source 也不算倒退。
TAI：這台機器的核心 TAI 偏移是 0，因此 nanotime.clock_source = tai 的讀值與 realtime 相同。Linux 預設不會設定 TAI 偏移，通常要由 chrony（leapsectz）或 ptp4l/phc2sys 設定；目前約為 37 秒。
9. 已知限制
- 不支援
infinity/-infinity；輸出固定為 ISO 格式，不受 DateStyle 影響。 AT TIME ZONE和EXTRACT這兩個語法在 PG 中寫死，擴充套件無法支援。請改用timezone(zone, ts)與extract_ns(field, ts)。smallint乘 nanointerval 時要先轉型，否則會出現 operator is not unique。- 不帶型別的字串字面值加在時間上（例如
ts + '1 day'）會被解析成內建 interval，最小單位是 µs。 - TimescaleDB：不能用連續聚合，
time_bucket不接受這些型別。16 B 型別需要分區函數，超出 int64 範圍的值會集中在兩端的 chunk。 - 不支援閏秒。TAI 偏移取決於系統設定。
clock_ns_global()需要shared_preload_libraries。它的計數器存在共享記憶體，伺服器重啟後會重來，所以如果重啟後系統時鐘比重啟前還慢，跨重啟就不保證遞增。- 回歸測試涵蓋 PG 17 與 18；timestamp9 的實測只在 PG 18 上做過。