Rails+PostgreSQL场景下删除行后如何上移行保证ID无间隙？

前提说明 #

一般不建议修改主键ID的连续性，主键ID的核心作用是唯一标识记录，修改ID会导致关联表外键失效、缓存失效、历史日志匹配错误等问题。如果只是需要连续的序号用于展示，完全可以加单独的序数字段或者查询时动态计算，不需要修改主键ID。如果业务确实强制要求主键连续，再参考以下方案操作。

方案1：删除操作后批量重置ID（仅适用于无关联表的小表） #

该方案直接修改主键ID，执行期间会锁表，大表操作会影响线上业务性能，且有外键关联的表禁止使用：

• 操作前先备份全表数据，避免操作失误丢失数据
• 删除目标记录后，执行SQL重置ID序列和主键自增起始值

纯PostgreSQL的实现SQL：

-- 假设操作的表名为items
-- 临时关闭约束检查（无外键关联时可省略）
ALTER TABLE items DISABLE TRIGGER ALL;
-- 重新生成连续ID
WITH new_ids AS (
  SELECT id AS old_id, ROW_NUMBER() OVER (ORDER BY id) AS new_id FROM items
)
UPDATE items SET id = new_ids.new_id FROM new_ids WHERE items.id = new_ids.old_id;
-- 重置主键自增序列的起始值为当前最大ID+1
SELECT setval(pg_get_serial_sequence('items', 'id'), COALESCE(MAX(id), 1)) FROM items;
-- 恢复约束检查
ALTER TABLE items ENABLE TRIGGER ALL;

Rails中集成的写法，可以写在模型的删除回调里：

class Item < ApplicationRecord
  after_destroy :reset_continuous_ids

  private

  def reset_continuous_ids
    ActiveRecord::Base.transaction do
      connection.execute <<~SQL
        WITH new_ids AS (
          SELECT id AS old_id, ROW_NUMBER() OVER (ORDER BY id) AS new_id FROM items
        )
        UPDATE items SET id = new_ids.new_id FROM new_ids WHERE items.id = new_ids.old_id;
        SELECT setval(pg_get_serial_sequence('items', 'id'), COALESCE(MAX(id), 1)) FROM items;
      SQL
    end
  end
end

方案2：使用额外序号字段替代修改主键（推荐方案） #

不需要修改主键ID，新增独立的sort_order字段存储连续序号，完全不影响主键和外键关联，性能也更高：

1. 先新增字段的迁移文件：

# 执行命令生成迁移：rails g migration AddSortOrderToItems sort_order:integer
class AddSortOrderToItems < ActiveRecord::Migration[7.0]
  def change
    add_column :items, :sort_order, :integer
    add_index :items, :sort_order, unique: true
    # 初始化已有数据的序号
    Item.order(:id).each_with_index do |item, idx|
      item.update!(sort_order: idx + 1)
    end
  end
end

2. 模型中添加删除后的序号重置逻辑：

class Item < ApplicationRecord
  after_destroy :reset_sort_order

  private

  def reset_sort_order
    # 仅更新比当前删除记录序号大的记录，不需要全表遍历，性能更高
    Item.where("sort_order > ?", self.sort_order).update_all("sort_order = sort_order - 1")
  end
end

注意：如果你的表有外键关联其他表，绝对不要使用修改主键的方案，直接用方案2的额外序数字段即可。

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