Postgres数据库流式输出交易数据的最优实现方案咨询

方案C的查询合并方法 #

可以合并为单个查询，无需对22个标的分别发起请求，具体写法如下：

SELECT * FROM trades 
WHERE instrument IN ('标的1', '标的2', ..., '标的22')
ORDER BY instrument, ts, id;

这个查询会先按交易标的分组，同一标的内先按时间戳排序，时间戳相同的按主键id排序，刚好匹配你需要的每个标的独立按顺序处理的需求，客户端拿到结果后可以直接按instrument字段拆分到对应队列即可。

更优的整体解决方案 #

前置准备：创建联合索引 #

先给表加适配查询场景的联合索引，所有过滤、排序逻辑都可以直接走索引，无需回表和文件排序，效率提升非常明显：

CREATE INDEX idx_trades_inst_ts_id ON trades (instrument, ts, id);

1. 历史数据加载逻辑 #

启动时直接查询最近1小时的所有数据，按如下写法拉取后直接拆分到各标的队列即可：

SELECT * FROM trades
WHERE ts >= NOW() - INTERVAL '1 hour'
ORDER BY instrument, ts, id;

排序规则优先按时间戳，同时间戳按主键id排序，完全解决你提到的id和时间戳不匹配的顺序问题。

2. 流式增量拉取逻辑 #

不需要回退时间窗口查重复数据，也不需要只按id拉取，只需要每次拉取后记录两个游标：

• 当前已处理的所有记录的最大时间戳last_max_ts
• 该最大时间戳下已处理记录的最大idlast_max_id

下一次拉取的查询条件为：

SELECT * FROM trades
WHERE 
  (ts > %s) OR (ts = %s AND id > %s)
ORDER BY instrument, ts, id
LIMIT 1000; -- 可根据实际吞吐量调整批次大小

参数依次传入last_max_ts、last_max_ts、last_max_id即可。

该逻辑的优势：

• 完全避免多线程写入导致的id和ts不匹配问题，返回的所有记录都是未处理的新记录，不会漏也不会重复
• 每次查询都走联合索引，没有多余的重复数据拉取，也不需要客户端做大量去重、排序操作，性能远高于方案B
• 批次大小可灵活调整，适配不同流量峰值，就算同一毫秒有大量记录也可以分批拉取不会卡住

特殊场景兜底 #

如果业务允许极小的延迟（比如100毫秒），可以在每次拉取的WHERE条件里额外加ts <= NOW() - INTERVAL '100 ms'，避免未提交的并发事务导致的临时记录缺失，100毫秒的延迟对于绝大多数交易场景都可以接受。

内容的提问来源于stack exchange，提问作者Thomas