Railsのid列は必ずしもきれいな連番にはならないんですよ、という話

## はじめに
Railsでモデルを作ると、テーブル定義上のid列（プライマリーキー）はデフォルトで自動的に連番が付与されるカラム（auto increment値）になります。

とはいえ、idにしたからといって必ずしも連番になるとは限りません。場合によっては「歯抜け（ギャップ）」が発生こともあります。
そのため、「idの最大値＝そのテーブルの件数」になるとは限りません。

つまり、こういうデータが作成されることもありうる、ということです（たとえ一度もDELETEされなかったとしても）。

| id | name  |
|----|-------|
| 1  | Alice |
| 2  | Bob   |
| 5  | Carol |

そこでこの記事ではRDBMS別にidの連番が歯抜けになるケースを見ていきます。

## この記事で使用するサンプルアプリ
この記事で使用するのは以下のようなモデルを1つだけ持つ、単純なRailsアプリです。

```ruby 
class Item < ApplicationRecord
end
```

```ruby:db/schema.rb
ActiveRecord::Schema[7.0].define(version: 2022_02_09_225353) do
  create_table "items", force: :cascade do |t|
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end
end
```

コードはGitHubに置いています。

https://github.com/JunichiIto/id-count-sandbox

## PostgreSQLの場合
以下のようなテストを書いた場合、テストが落ちます。

```ruby 
require "test_helper"

class ItemTest < ActiveSupport::TestCase
  test "id count" do
    Item.transaction do
      Item.create!
      raise ActiveRecord::Rollback
    end
    Item.create!
    assert_equal 1, Item.count
    assert_equal Item.count, Item.last.id
  end
end
```

```
# Running:

F

Failure:
ItemTest#test_id_count [/Users/jnito/dev/sandbox/id-count-sandbox/test/models/item_test.rb:11]:
Expected: 1
  Actual: 2

rails test test/models/item_test.rb:4

Finished in 0.022535s, 44.3754 runs/s, 88.7508 assertions/s.
1 runs, 2 assertions, 1 failures, 0 errors, 0 skips
```

### 何が起きているのか？

- `Item.create!`でDBへのINSERTが走ります。このとき、idは1になります。
- `raise ActiveRecord::Rollback`で更新がロールバックされるため、上のINSERT処理はキャンセルされます。
  - ここでは明示的に`raise ActiveRecord::Rollback`していますが、実際のRailsアプリケーションでは他のレコードの更新に失敗したものと考えてください。
- その次の`Item.create!`でDBへのINSERTが走ります。このとき、idは2になります。
- その結果、idの最大値が2で、itemsテーブルの件数が1となるため、「idの最大値＝そのテーブルの件数」が成り立たず`assert_equal Item.count, Item.last.id`が失敗します。

### マニュアルではどう説明されているか？
PostgreSQLの場合、id列はbigserial型になります。この型ではギャップが発生するケースがあることがマニュアルに明記されています。

> smallserial、 serialおよびbigserialはシーケンスを使って実装されているため、行の削除が行われていなくとも、列に"穴"や連番の抜けが発生するかもしれません。また、テーブルへ正常に挿入されていないにも関わらず、シーケンスの値を"消費してしまう"こともあります。
>
> [8\.1\. 数値データ型](https://www.postgresql.jp/document/13/html/datatype-numeric.html)

上でテストした内容は、ここで言う「テーブルへ正常に挿入されていないにも関わらず、シーケンスの値を"消費してしまう"」ケースに該当します。

**2025-04-21追記**
この他にも、DBを強制終了したときに、事前にキャッシュしていたIDが失われて連番が一気に飛ぶケースがあるようです。
詳しくは以下の記事をご覧ください。

https://zenn.dev/saran/articles/aa85fc4b92f250

## MySQLの場合

MySQLもPostgreSQLと同じ挙動になり、前述のテストが失敗します。

### マニュアルではどう説明されているか？
MySQLの場合、id列は`auto_increment`が指定された`bigint`型になります。こちらもやはりギャップが発生する可能性がマニュアルに明記されています。

> 「失われた」自動インクリメント値とシーケンスギャップ
> すべてのロックモード (0、1、および 2) では、自動インクリメント値を生成したトランザクションがロールバックされると、これらの自動インクリメント値が「失われます」。 「INSERT のような」ステートメントが完了したかどうか、およびそれを含むトランザクションがロールバックされたかどうかに関係なく、自動インクリメントカラムの値は一度生成されたら、ロールバックできません。 このような失われた値は再使用されません。 したがって、テーブルの AUTO_INCREMENT カラムに格納されている値にはギャップが存在する可能性があります。
>
> [MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.6.1.6 InnoDB での AUTO_INCREMENT 処理](https://dev.mysql.com/doc/refman/8.0/ja/innodb-auto-increment-handling.html#innodb-auto-increment-lock-mode-usage-implications)

## SQLite3の場合
SQLite3の場合はPostgreSQLやMySQLとは異なり、先ほどのテストはパスします。

```
# Running:

.

Finished in 0.012709s, 78.6844 runs/s, 157.3688 assertions/s.
1 runs, 2 assertions, 0 failures, 0 errors, 0 skips
```

SQLite3の場合、id列は`autoincrement`が指定された`integer`型になります。SQLite3では`sqlite_sequence`というテーブルで連番を制御しているので、トランザクションがロールバックされると`sqlite_sequence`テーブルで管理している連番もロールバックされるんだと思います（想像）。

### マニュアルではどう説明されているか？

ただし、SQLite3のマニュアルにはギャップが発生するケースがあることも書かれています。

> Note that "monotonically increasing" does not imply that the ROWID always increases by exactly one. One is the usual increment. However, if an insert fails due to (for example) a uniqueness constraint, the ROWID of the failed insertion attempt might not be reused on subsequent inserts, resulting in gaps in the ROWID sequence. AUTOINCREMENT guarantees that automatically chosen ROWIDs will be increasing but not that they will be sequential.
>
> [SQLite Autoincrement](https://www.sqlite.org/autoinc.html)

DeepL翻訳の結果も併記しておきます（一部手修正）。

> なお、「単調増加」は、ROWIDが常に正確に1ずつ増加することを意味するものではありません。1は通常の増加分です。しかし、(例えば)一意性制約のために挿入に失敗した場合、失敗した挿入のROWIDはその後の挿入で再利用されない可能性があり、結果としてROWIDシーケンスにギャップが発生します。AUTOINCREMENTは、自動的に選択されたROWIDが増加することは保証しますが、連続的であることは保証しません。

### SQLite3でもこんなコードを書くとギャップが発生する

では試しに、SQLite3でギャップが発生するコードを書いてみましょう。

たとえば先ほどのItemテーブルに`name`カラムを追加し、ユニーク制約を付けます。

```diff_ruby:db/schema.rb
 ActiveRecord::Schema[7.0].define(version: 2022_02_09_235257) do
   create_table "items", force: :cascade do |t|
     t.datetime "created_at", null: false
     t.datetime "updated_at", null: false
+    t.string "name"
+    t.index ["name"], name: "index_items_on_name", unique: true
   end
 end
```

さらにテストコードを次のように変更します。

```ruby 
require "test_helper"

class ItemTest < ActiveSupport::TestCase
  test "id count" do
    Item.create!(name: 'foo')
    ApplicationRecord.connection.exec_query(<<~SQL)
      INSERT OR IGNORE INTO items
      (name, created_at, updated_at)
      VALUES
      ('foo', 0, 0)
    SQL
    Item.create!(name: 'bar')
    assert_equal 2, Item.count
    assert_equal Item.count, Item.last.id
  end
end
```

こうするとテストが失敗します。

```
# Running:

F

Failure:
ItemTest#test_id_count [/Users/jnito/dev/sandbox/id-count-sandbox/test/models/item_test.rb:14]:
Expected: 2
  Actual: 3

rails test test/models/item_test.rb:4

Finished in 0.008400s, 119.0476 runs/s, 238.0952 assertions/s.
1 runs, 2 assertions, 1 failures, 0 errors, 0 skips
```

### このテストが失敗する理由

SQLite3で`INSERT OR IGNORE INTO`を使ってINSERTすると、ユニーク制約違反が発生してもINSERTには失敗するものの、エラーにはなりません。

一方、idはINSERTされなくても1つインクリメントされたままになります。

そのため、最終的にidの最大値が3で、itemsテーブルの件数が2となり、「idの最大値＝そのテーブルの件数」が成り立たず、`assert_equal Item.count, Item.last.id`が失敗します。

Railsアプリでわざわざ`INSERT OR IGNORE INTO`を使うのはレアケースだと思いますが、必ずしも「idの最大値＝そのテーブルの件数」にはならないことを示す例としてあえて使ってみました。

## まとめ
というわけで、この記事ではRailsのid列がきれいに連番にならずギャップが発生する事例を示してみました。

RDBMSは並列で大量のアクセスをさばく必要があるため、パフォーマンスを重視してauto increment列でもギャップの発生を許容しています。「なんでidが連番にならないの！？」と思ったときは、この記事を読み直してみてください。