PostgreSQLを再起動するとシーケンスの値が33増える謎

ある日アプリケーションのDEBUGログを眺めていると、レコードのidカラムの値が実際のレコード数よりも極端に大きくなっていることに気付きました。このカラムの値は、シーケンスを使って採番しているので、1つずつ増えていくことが期待です。「まさかバグか？」と焦りましたが、リソース節約のため毎晩DBを停止していることが原因でした。

再現確認

手っ取り早く PostgreSQL 12.3 の Docker公式イメージ を使って検証してみます。

1. PostgreSQL を起動する

   $ docker run --name testdb -e POSTGRES_PASSWORD=mysecretpassword -d postgres:12.3
   

2. psql で接続する

   $ docker exec -it testdb psql -U postgres
   psql (12.3 (Debian 12.3-1.pgdg100+1))
   Type "help" for help.
   
   postgres=# 
   

3. シーケンスを作成して、値を取得する

   postgres=# create sequence myseq;
   CREATE SEQUENCE
   postgres=# select nextval('myseq');
    nextval
   ---------
          1
   (1 row)
   

4. DBを再起動する

   $ docker restart testdb
   

5. シーケンスの値を再び取得する → 2じゃない！

   postgres=# select nextval('myseq');
    nextval
   ---------
         34
   (1 row)
   

Sequence Caching: Oracle vs. PostgreSQL によると、再起動したときに 32（固定サイズ）+ 1（シーケンスのキャッシュサイズ。デフォルト値から変えている場合はさらに大きくなる）だけ番号が進んでしまうようです。

補足

参考ページを見てみると「4. DBを再起動する」でDBをきちんと停止していないのがまずそうです。DBを安全に停止する手順でやり直してみます。

1. シーケンスを作成して、値を取得する

   postgres=# create sequence myseq;
   CREATE SEQUENCE
   postgres=# select nextval('myseq');
    nextval
   ---------
          1
   (1 row)
   

2. DBを再起動する

   $ docker exec -it testdb su postgres -c 'pg_ctl stop -m fast'
   
   waiting for server to shut down....
   $ docker start testdb
   

3. シーケンスの値を再び取得する

   postgres=# select nextval('myseq');
    nextval
   ---------
          2
   (1 row)
   

今度は期待通り 2 になりました。DBを無理やり停止するなということですね。

補足2

なお、今回問題が起きた実際の環境では、PostgreSQL ではなく Amazon Aurora PostgreSQL を使っていて、AWS CLIを使って停止させていました。無理やり停止させているつもりはないのですが、なぜ…

$ aws rds stop-db-cluster --db-cluster-identifier mydbcluster

まとめ

シーケンスを使ってカラムの値を採番していると、ときどき欠番が発生することがあります。トランザクションがロールバックされた場合に欠番が発生することは理解していましたが、DBを停止した場合にも欠番が（32も！）発生することは知りませんでした。勉強になりました。

ただ、Aurora の挙動については情報源を見つけられず、Aurora を停止したときにもシーケンスが飛ぶのが期待なのかは分からないままでした。

参考

• Sequence Caching: Oracle vs. PostgreSQL
• postgresql sequence jump 30 or 33 number with cache equals 1 - Stack Overflow