PostgreSQL记录更新后按日期排序失效问题排查

• SQL 本身的语义规则决定：如果 ORDER BY 子句指定的排序字段组合无法唯一确定所有行的先后顺序，那么排序键值相同的行的返回顺序是没有任何保障的，会随表的物理存储结构、查询执行计划、行版本变化而变动，不属于数据库Bug。
• PostgreSQL 基于 MVCC 机制实现事务隔离，执行 UPDATE 操作时不会在原位置修改数据，而是将旧行标记为待清理的死元组，同时写入一个全新的行版本。你更新 reply_id=3 的记录后，该行的新行版本被分配到了表数据文件的末尾物理位置。
• 你展示的结果里 created_at 只显示到日期粒度，但实际字段是微秒精度的 TIMESTAMPTZ 类型：要么是测试时部分记录的 created_at 实际值完全相同（比如同一事务内批量插入的记录，NOW() 取事务启动时间，所有插入行的时间值完全一致），要么是你展示时截断了时间精度，才会导致物理位置靠后的新行版本在排序时被排到末尾。你看到的日期差异是截断显示带来的错觉，只有排序键值相等时，行顺序才会受物理存储位置影响。

• 给排序规则增加一个唯一的第二排序键，直接使用表的自增主键即可，主键全局唯一，可以保证排序结果完全稳定，不会随数据更新变动：

SELECT topic_replies.*, users.avatar as author_avatar, users.user_id as author_id,
users.role as author_role, users.name as author_name FROM topic_replies
LEFT JOIN users ON users.username = topic_replies.author
WHERE topic_id = $1
ORDER BY topic_replies.created_at DESC, topic_replies.reply_id DESC;

• 可选排查：检查表上是否存在未感知到的触发器、重写规则，在更新 body 字段时意外修改了 created_at 的值，可以在更新后单独查询对应 reply_id 的 created_at 值，确认和更新前完全一致。
• 性能优化：可以为 (topic_id, created_at DESC, reply_id DESC) 建立联合索引，后续查询可以直接通过索引拿到有序结果，不需要执行额外的排序操作，查询速度会更快。

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