Contents
pg_nanotime 實例集
以下每段 SQL 與輸出都是在 PostgreSQL 17.11 與 18.6 上實際執行的結果（兩版輸出完全相同），直接取自回歸測試 test/sql/examples.sql 與 test/expected/examples.out。每次 make installcheck 都會重跑，另外 make check-docs（tools/check_examples.py）與 CI 會逐行比對本文件的程式碼區塊與測試檔，不一致就失敗，所以文件與實際行為保持一致。
共同設定：SET timezone = 'UTC';。與現在時間有關的值都改用固定字面值，或只顯示計數，不顯示時間本身。
1. 交易 tick 資料與延遲統計
交易所在 exch_ts 蓋上時間，我們收到時在 recv_ts 記下時間（正式環境用 DEFAULT clock_ns8() 自動填入；這裡為了輸出固定，直接寫入時間字面值）。兩者相減就是延遲，以奈秒精確計算。
CREATE TABLE ticks (
symbol text NOT NULL,
price numeric(12,4) NOT NULL,
exch_ts nanotimestamptz8 NOT NULL, -- time stamped by the exchange
recv_ts nanotimestamptz8 NOT NULL DEFAULT clock_ns8() -- time we received it
);
INSERT INTO ticks VALUES
('2330', 1025.0000, '2026-10-23 01:30:00.000000100+00', '2026-10-23 01:30:00.000041350+00'),
('2330', 1025.5000, '2026-10-23 01:30:00.000250000+00', '2026-10-23 01:30:00.000289007+00'),
('2330', 1026.0000, '2026-10-23 01:30:00.001000000+00', '2026-10-23 01:30:00.001102500+00'),
('2317', 212.5000, '2026-10-23 01:30:00.000000500+00', '2026-10-23 01:30:00.000038900+00'),
('2317', 213.0000, '2026-10-23 01:30:00.000900000+00', '2026-10-23 01:30:00.000961234+00');
SELECT symbol, exch_ts, recv_ts - exch_ts AS latency FROM ticks ORDER BY exch_ts;
symbol | exch_ts | latency
--------+----------------------------------+--------------------
2330 | 2026-10-23 01:30:00.000000100+00 | 00:00:00.000041250
2317 | 2026-10-23 01:30:00.000000500+00 | 00:00:00.000038400
2330 | 2026-10-23 01:30:00.000250000+00 | 00:00:00.000039007
2317 | 2026-10-23 01:30:00.000900000+00 | 00:00:00.000061234
2330 | 2026-10-23 01:30:00.001000000+00 | 00:00:00.000102500
(5 rows)
SELECT symbol,
count(*) AS n,
min((recv_ts - exch_ts)::bigint) AS min_ns,
round(avg((recv_ts - exch_ts)::bigint)) AS avg_ns,
percentile_disc(0.5) WITHIN GROUP (ORDER BY (recv_ts - exch_ts)::bigint) AS p50_ns,
max((recv_ts - exch_ts)::bigint) AS max_ns
FROM ticks GROUP BY symbol ORDER BY symbol;
symbol | n | min_ns | avg_ns | p50_ns | max_ns
--------+---+--------+--------+--------+--------
2317 | 2 | 38400 | 49817 | 38400 | 61234
2330 | 3 | 39007 | 60919 | 41250 | 102500
(2 rows)
-- latency of anything slower than 50 microseconds
SELECT symbol, price, recv_ts - exch_ts AS latency
FROM ticks WHERE recv_ts - exch_ts > '50 microseconds'::nanointerval ORDER BY exch_ts;
symbol | price | latency
--------+-----------+--------------------
2317 | 213.0000 | 00:00:00.000061234
2330 | 1026.0000 | 00:00:00.000102500
(2 rows)
2. 撮合引擎：先到先處理（FIFO）
clock_ns8_global() 在所有連線之間保證嚴格遞增、不重複，可以直接當主鍵與排隊順序。需要 shared_preload_libraries = 'pg_nanotime'。時間值每次執行都不同，所以這裡只顯示計數與排序結果，不顯示時間本身。
CREATE TABLE orders (
seq_ts nanotimestamptz8 NOT NULL DEFAULT clock_ns8_global() PRIMARY KEY,
side char(1) NOT NULL,
price numeric(12,4) NOT NULL,
qty int NOT NULL
);
-- 10000 orders in one statement: every default is a distinct, increasing time
INSERT INTO orders(side, price, qty)
SELECT CASE WHEN g % 2 = 0 THEN 'B' ELSE 'S' END, 100 + g % 7, g % 10 + 1
FROM generate_series(1, 10000) g;
SELECT count(*) AS orders, count(DISTINCT seq_ts) AS distinct_ts FROM orders;
orders | distinct_ts
--------+-------------
10000 | 10000
(1 row)
SELECT count(*) AS not_increasing
FROM (SELECT seq_ts, lag(seq_ts) OVER (ORDER BY ctid) AS prev FROM orders) s
WHERE seq_ts <= prev;
not_increasing
----------------
0
(1 row)
-- FIFO: the best-priced buy orders, oldest first
SELECT side, price, qty FROM orders
WHERE side = 'B' ORDER BY price DESC, seq_ts LIMIT 3;
side | price | qty
------+----------+-----
B | 106.0000 | 7
B | 106.0000 | 1
B | 106.0000 | 5
(3 rows)
3. 網路封包：匯入 bigint 奈秒
pcap、PTP 等工具常輸出「1970 年起的奈秒整數」。::nanotimestamptz8 直接轉換（不需運算），也能轉回 bigint 匯出。
CREATE TABLE packets_raw (ts_ns bigint, src inet, len int);
INSERT INTO packets_raw VALUES
(1792719000000000000, '10.0.0.1', 64),
(1792719000000001250, '10.0.0.2', 1500),
(1792719000000001300, '10.0.0.1', 64),
(1792719000000250000, '10.0.0.3', 576);
CREATE TABLE packets AS
SELECT ts_ns::nanotimestamptz8 AS ts, src, len FROM packets_raw;
SELECT ts, src, len, ts - lag(ts) OVER (ORDER BY ts) AS gap FROM packets ORDER BY ts;
ts | src | len | gap
----------------------------------+----------+------+--------------------
2026-10-23 01:30:00.000000000+00 | 10.0.0.1 | 64 |
2026-10-23 01:30:00.000001250+00 | 10.0.0.2 | 1500 | 00:00:00.000001250
2026-10-23 01:30:00.000001300+00 | 10.0.0.1 | 64 | 00:00:00.000000050
2026-10-23 01:30:00.000250000+00 | 10.0.0.3 | 576 | 00:00:00.000248700
(4 rows)
-- and back to bigint for export
SELECT ts::bigint AS ts_ns FROM packets ORDER BY ts LIMIT 1;
ts_ns
---------------------
1792719000000000000
(1 row)
4. IoT 感測器：每秒分桶統計
每 250 ms 一筆讀數，用 time_bucket_ns 依 1 秒分桶。
CREATE TABLE readings (sensor int, ts nanotimestamptz, value double precision);
INSERT INTO readings
SELECT s, '2026-10-23 00:00:00+00'::nanotimestamptz + (g * 250000000::bigint)::nanointerval,
s * 10 + g % 4
FROM generate_series(1, 2) s, generate_series(0, 11) g;
SELECT sensor, time_bucket_ns('1 second'::nanointerval, ts) AS bucket,
count(*) AS n, avg(value) AS avg_value
FROM readings GROUP BY 1, 2 ORDER BY 1, 2;
sensor | bucket | n | avg_value
--------+----------------------------------+---+-----------
1 | 2026-10-23 00:00:00.000000000+00 | 4 | 11.5
1 | 2026-10-23 00:00:01.000000000+00 | 4 | 11.5
1 | 2026-10-23 00:00:02.000000000+00 | 4 | 11.5
2 | 2026-10-23 00:00:00.000000000+00 | 4 | 21.5
2 | 2026-10-23 00:00:01.000000000+00 | 4 | 21.5
2 | 2026-10-23 00:00:02.000000000+00 | 4 | 21.5
(6 rows)
5. 從 timestamp9 遷移
這裡用 bigint domain 模擬 timestamp9（數值相同：1970 年起的奈秒），讓測試不依賴 timestamp9；真實 timestamp9 欄位的呼叫方式完全相同，實測紀錄見 TESTING.md。先 dry_run 看要執行的 SQL，再正式執行。
-- timestamp9 here; with the real extension the same calls work.
CREATE DOMAIN timestamp9 AS bigint;
CREATE TABLE events (id int PRIMARY KEY, t timestamp9 NOT NULL);
INSERT INTO events VALUES (1, 1792719000000000001), (2, -1), (3, 1792719000123456789);
CREATE INDEX events_t ON events(t);
CALL nanotime_migrate_column('events', 't', dry_run => true);
NOTICE: index events_t will be rebuilt
NOTICE: ALTER TABLE events ALTER COLUMN t TYPE nanotimestamptz USING (t)::bigint::nanotimestamptz
CALL nanotime_migrate_column('events', 't');
NOTICE: index events_t will be rebuilt
SELECT id, t FROM events ORDER BY t;
id | t
----+----------------------------------
2 | 1969-12-31 23:59:59.999999999+00
1 | 2026-10-23 01:30:00.000000001+00
3 | 2026-10-23 01:30:00.123456789+00
(3 rows)
SELECT pg_typeof(t) FROM events LIMIT 1;
pg_typeof
-----------------
nanotimestamptz
(1 row)
6. 交易內固定時間與即時時鐘
now_ns() 在同一交易內不變；clock_ns_monotonic() 每次呼叫都會前進。
BEGIN;
SELECT now_ns() = now_ns() AS same_in_txn, clock_ns_monotonic() < clock_ns_monotonic() AS clock_moves;
same_in_txn | clock_moves
-------------+-------------
t | t
(1 row)
COMMIT;