Postgres更新单条数据后查询结果顺序改变的原因及解决方法咨询

问题原因 #

• Postgres 默认的 SELECT 查询如果没有显式加 ORDER BY 子句，返回的结果顺序是不确定的，并不保证按插入顺序或其他固定顺序返回。
• 你更新某条 job 记录时，如果更新操作导致这条记录的存储位置发生变更（比如更新后字段长度变大，原有存储页空间不足，Postgres 会把旧版本记录标记为过期，把新版本记录写到表文件的末尾位置），无排序的查询就会按照物理存储位置返回结果，刚更新的记录自然就出现在结果集的最后。

注意：很多人误以为默认返回的是插入顺序，这是错误认知。只要没有 ORDER BY，数据库不保证任何返回顺序，即使大多数时候看起来符合插入顺序，只要发生更新、vacuum 操作、数据导入导出等场景，顺序都会发生变化。

解决方法 #

你只需要在查询 jobs 列表的 SQL 语句里显式加上 ORDER BY 子句，指定固定的排序规则即可，比如：

• 要保持插入顺序的话可以按自增主键 id 升序排序：SELECT * FROM jobs ORDER BY id ASC;
• 要按最新更新时间排序就加更新时间字段排序：SELECT * FROM jobs ORDER BY updated_at DESC;
• 也可以按业务需要的优先级、创建时间、标题等字段排序，只要 ORDER BY 指定的字段值不会随你当前的更新操作变化，排序结果就不会因为单条记录更新而发生异常重排。
• 如果客户端做分页查询的话，ORDER BY 的排序规则最好是唯一的（比如联合排序 ORDER BY updated_at DESC, id ASC），避免出现同一时间更新的记录每次查询顺序不一致，导致分页重复或者遗漏的问题。

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