PostgreSQL已填充表新增serial列，其值是否保证与插入顺序一致？

给已填充数据的存量表新增serial类型列时，生成的序列值无法保证和数据实际插入顺序完全匹配，你观测到的部分顺序错位是符合序列设计逻辑的正常现象，不属于异常。

• 存量数据的serial值分配不按插入/时间序
  为已有数据的表追加serial列时，数据库不会按照原始插入顺序、也不会参考created_at列的时间顺序给存量行分配序列值，默认是按照全表扫描时读取到的行物理顺序依次调用nextval()赋值。而全表扫描的行顺序受表的聚簇状态、VACUUM操作导致的页移动、行更新后的物理位置偏移、存储碎片影响，和原始插入顺序、created_at时间顺序没有强绑定关系，小范围观测到的顺序对齐只是巧合。
• 即使是加列之后新写入的数据，serial值也无法100%对齐实际插入可见顺序
  • 序列取值时机在事务提交之前：serial的本质是调用序列对象的nextval()函数取值，只要调用发生就会占用序列值，和对应事务最终是否提交、提交的先后顺序无关。比如事务A先拿到序列值100但长时间未提交，事务B后拿到值101且先完成提交，最终表里值101对应的行对外可见的时间反而早于值100的行，自然会出现顺序错位。
  • 序列值不会因事务回滚复用：如果某条插入操作拿到serial值后事务回滚，这个值会直接被丢弃不会重复使用，会造成可见行的serial值断层，进一步打乱和实际插入顺序的对应关系。
• 你发现的created_at和serial值不匹配的记录，除了主机时间源不可靠的因素，上述两个序列本身的机制也会导致错位，不需要把所有错位都归因为时间源故障。

如果你需要一个能严格对齐数据先后顺序的标识，不要依赖serial列的隐式顺序：

• 针对存量数据，可以在新增serial列后，显式按照ORDER BY created_at, ctid的顺序重新为存量行分配序列值，能最大程度对齐现有数据的时间先后
• 针对后续新写入的数据，如果需要严格的全局顺序，不要依赖跨事务的serial序列，可以考虑使用数据库日志LSN、配置了严格单调时钟源的时间戳、独立发号器生成的顺序ID作为顺序标识

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