Postgres中为记录设置任意排序规则有没有更优实现方案？

原有方案的问题 #

• 用float类型做排序键存在精度上限：双精度浮点数仅支持53位有效数字，连续在两个值之间插入中间值最多53次就会出现精度溢出，导致排序键重复，顺序混乱。
• 仅用固定的±1做头尾插入的初始值，多次插入头尾后会快速耗尽两端的可用间隙。

不推荐直接使用sequence作为排序键的原因 #

sequence是单调自增的全局序列，仅能生成递增的连续整数，无法满足中间插入的需求：如果要在两条记录中间插入新数据，必须批量更新后续所有记录的排序键，数据量较大时会锁表、性能急剧下降，完全不适合频繁调整顺序的场景。

最优实现方案：numeric类型排序键 + sequence做默认值 #

这个方案改动最小，完美匹配所有需求，性能足够支撑绝大多数场景：

核心逻辑 #

用Postgres支持任意精度的numeric类型替换原有float类型作为排序键，同时绑定sequence仅用于生成默认追加到尾部的排序键，插入头尾/中间时按需计算排序键即可。

实现步骤 #

1. 先创建用于生成尾部默认排序键的sequence

CREATE SEQUENCE list_item_sort_seq START WITH 1 INCREMENT BY 1;

2. 建表（如果是已有表直接修改字段类型即可）

CREATE TABLE list_items (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    -- 存储业务数据，按需调整类型
    payload JSONB NOT NULL,
    sort_key NUMERIC NOT NULL DEFAULT nextval('list_item_sort_seq'::regclass),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- 排序字段建索引，保证查询性能
CREATE INDEX idx_list_items_sort ON list_items (sort_key ASC);

各需求对应操作 #

• 未指定位置默认追加尾部：插入时不指定sort_key，直接用sequence生成的默认值即可，天然排在所有已有记录后面。
• 插入头部：取当前最小排序键减1作为新记录的sort_key

INSERT INTO list_items (payload, sort_key)
VALUES ('{"your_data": "test"}', (SELECT COALESCE(MIN(sort_key), 0) - 1 FROM list_items));

• 插入两已知记录（id为a_id和b_id，a排在b前面）中间：取两个记录sort_key的平均值作为新键

INSERT INTO list_items (payload, sort_key)
SELECT '{"your_data": "test"}', (a.sort_key + b.sort_key) / 2
FROM list_items a, list_items b
WHERE a.id = $a_id AND b.id = $b_id;

• 删除任意位置记录：直接删除对应记录即可，不需要修改其他记录的sort_key，剩余记录的排序顺序完全不受影响，天然稳定。
• 返回有序列表：查询时直接按sort_key排序即可

SELECT payload FROM list_items ORDER BY sort_key ASC;

极端情况优化 #

如果业务存在超高频率的中间插入操作，担心sort_key位数过多，只需要极低频率（通常数月甚至数年一次）做一次全表重排，重置为连续整数即可：

WITH reordered AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY sort_key ASC) AS new_sort
    FROM list_items
)
UPDATE list_items l
SET sort_key = r.new_sort
FROM reordered r
WHERE l.id = r.id;

可选极端场景方案：分数排序法 #

如果你的业务需要超高频次插入中间，完全不想做重排，可以用两个整数字段sort_num和sort_den分别存储排序分数的分子和分母，插入中间时新的分数为(a.sort_num + b.sort_num)/(a.sort_den + b.sort_den)，理论上可以无限插入不会出现精度问题，仅需要在查询时按sort_num::numeric / sort_den排序即可，性能略低于numeric方案。

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