RubyKaigiスケジュールアプリのPostgreSQLからSQLiteへの移行

久しぶり、プログラマーのkinoppydです。前回の記事では、RubyKaigi公式スケジュールアプリ「Schedule.select」のデザイン刷新に関してお届けしました。

tech.smarthr.jp

Schedule.selectの今年の大きなトピックは以下の5つです。

デザインの刷新
PostgreSQLからSQLite3への移行
Solid三兄弟の導入（cableはまだ使うかどうかわかりませんが……）
Rails 8へのアップグレードとPWA対応
Kamalによるデプロイ

今回は第2回目として、Schedule.selectのデータベースをPostgreSQLからSQLiteへ移行したお話しをします。

Why SQLite

Rails 8 から、DHHはSolidプロダクトと同時にSQLiteの利用を促進しています。実際、37Signalsのプロダクト群であるONCEはすべてSQLiteで動いており、パフォーマンスに関しても問題ないという話をしています。

Rails 8.0: #NOBUILD, #NOPAAS, all-in on SQLite as a production database option with jobs, cache, and cable, new authentication generator, and so much more! Final release is out 🎉 https://t.co/qom10worLW
— DHH (@dhh) 2024年11月7日

The rise of sqlite as a viable production database for smaller Rails apps is the epitome of compressing complexity. ONCE/Campfire is running on it, and it's made distribution, debugging, and backups much simpler. Love to see the work going into speeding it up further! 😍 https://t.co/ffoXz2EWws
— DHH (@dhh) 2024年4月21日

また、こちらの記事にもあるように、SQLiteはすでにProduction readyな選択肢の一つになっています。

note.com

これらの状況を鑑みつつ、RailsConfの動画を観てDHHのフィーリングを感じ取ったり、Rails側のSQLite対応Pull Requestを確認したり、冷静沈着に状況を眺めながら、コーヒー片手に慎重に思考を巡らせていたところ、ちょっとやってみてえなというバイブスがぶち上がってきたのでいっちょノリでやってみたろかという感じになりました。

実際、今年はレイテンシの関係でHerokuを離れる必要があり、Google Cloudで稼働させることを考えていました。しかし、CloudSQLは高い、高すぎる！　なんで最小構成で月65ドルもすんだよ、教えはどうなってんだ教えは！　ってずっと思っていたので、SQLiteで済むならSQLiteで良いじゃんと思ったのも大きいです。SmartHRはスケールアップ企業でソリッドスケーリングというものを標榜しているので、無駄な出費はそぎ落として行きたいですねというところです。これもソリッドじゃん。やったね。

note.com

PostgreSQLからSQLiteへのデータ移行

今回の記事は、基本的には以下のPullRequestの内容を補足しながら追うものです。

github.com

実際の移行には、以下の手順を経る必要があります

pg gem を削除し、sqlite3 gemを入れる
config/database.ymlを編集する
（オプショナル）SolidQueueのテーブルを分離する
- これはSolidQueue 0.8以前からのユーザー特有の問題です
（オプショナル）PK用のUUID生成コードをRails側に導入する
- これもPKにbigintを使っていれば不要です、SmartHRでは伝統的にUUIDを使っているため、対応が必要でした
すべてのmigrationファイルから、PostgreSQL固有の記述を削除する
DBをセットアップする
テストの実行と修正を行う
PostgreSQLのデータをダンプする
ダンプファイルを加工する
ダンプファイルからSQLiteへのリストアする
（あれば）docker-composeやActionsなどの、開発やテストのためのPostgreSQLリソースを削除する

移行の際には停止メンテは避けられません。さすがにオンラインでPostgreSQLからSQLiteにつなぎ替える方法は思いつきませんでした。また、SQLiteに切り替えることによっていくらかのデメリットも生じます。

それでは、順を追って振り返ってみましょう。

pg gem を削除し、sqlite3 gemを入れる

これはそんなに難しいことはありません。Gemfileを編集してください。

@@ -9,8 +9,8 @@ gem 'rails', '~> 8.0'
 
 gem 'propshaft'
 
-# Use postgresql as the database for Active Record
-gem 'pg', '~> 1.5'
+gem "sqlite3", "~> 2.5"
+
 # Use Puma as the app server
 gem 'puma', '~> 5.0'
 # Use Active Model has_secure_password

config/database.ymlを編集する

これもそんなに難しいことはなく、database.ymlからPostgreSQL特有の設定を消して、adapterをsqlite3に変更するだけです。

@@ -15,22 +15,19 @@
 # gem 'pg'
 #
 default: &default
-  adapter: postgresql
-  encoding: unicode
+  adapter: sqlite3
   # For details on connection pooling, see Rails configuration guide
   # https://guides.rubyonrails.org/configuring.html#database-pooling
   pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
+  timeout: 5000
 
 development:
   primary: &primary_development
     <<: *default
-    database: mie_development
-    host: "127.0.0.1"
-    user: postgres
-    port: 25432
+    database: storage/development.sqlite3
   cache:
     <<: *primary_development
-    database: mie_development_cache
+    database: storage/development_cache.sqlite3
     migrations_paths: db/cache_migrate
 
   # The specified database role being used to connect to postgres.

（オプショナル）SolidQueueのテーブルを分離する

SolidQueueのバージョン0.8以前から使用しているユーザーは、キュー用のDBがprimaryのDBと一体化した状態です。ですが、0.8以降からはキュー用のDBが専用に分離されるようになりました。0.8以前から使っているユーザーは同じDBのまま問題なく使えるのですが、SQLiteを使うしあまり1つのDBに負荷をかけたくないなと思うので、まあ分離した方が良いだろうという感じです。ちょうど良いので、このタイミングで分離しちゃいました。

といっても、やることは簡単な2ステップで、SolidQueue関連のマイグレーションファイルを専用のディレクトリに移動した後、database.ymlでDB定義を増やすだけです。

mkdir -p db/queue_migrate
mv db/migrate/*solid_queue* db/queue_migrate

@@ -92,3 +96,7 @@production:
     <<: *primary_production
     database: storage/production_cache.sqlite3
     migrations_paths: db/cache_migrate
+  queue:
+    <<: *primary_development
+    database: storage/production_queue.sqlite3
+    migrations_paths: db/queue_migrate

移行前に rails db:migrate の実行タイミングがあるので、そのときに全部勝手に修正される感じです。PostgreSQLからのデータのダンプ時に、混ざり込まないようにしましょう。

PK用のUUID生成コードをRails側に導入する

これは通常のBigint PKなアプリケーションではあまり問題にならないですが、SmartHRのアプリは伝統的にPKにUUIDを使っているため必要な手順です。UUIDの生成をPostgreSQLのpgcryptモジュールに頼っていたため、これをRails側のコードでどうにかする必要があります。

次のようなModel concernを作成し、PKがUUIDのModelにincludeしていきます。

module UuidPrimaryKey
  def self.included(klass)
    klass.before_create :generate_id
  end

  def generate_id
    self.id = loop do
      uuid = SecureRandom.uuid
      break uuid unless self.class.exists?(id: uuid)
    end
  end
end

SecureRandomで生成されるUUIDはUUIDv4なので、もしUUIDv7が使いたければ専用にGemを入れるか、そんなに難しいコードではないので自分で書いてしまうというのもアリだと思います。

私は面倒だったのでRailsで解決しちゃいましたが、SQLite3の拡張で対応しても良いと思います。その場合は、マイグレーションの変更時にちょっと違う手順を踏む必要があるので、各自対応してください。

すべてのmigrationファイルから、PostgreSQL固有の記述を削除

過去に遡り、すべてのマイグレーションファイルが対象です。そうしないと、新規に開発に参加する人がDBをマイグレーションできなくなってしまうからです。

Schedule.selectでは、以下の点を修正する必要がありました。

PostgreSQL特有の拡張記述の削除
- UUIDの為に使っていたpgcryptと、gen_random_uuid() 関数です
PKの型をuuid型からstring型に変更
- 速度を重視するのであれば、string型ではなくblob型という手もあります
- が、途中で変換が必要なので扱いづらくなると思います
jsonb型をjson型に置き換え
- SQLite3でもjsonb型はあるのですが、なぜかテスト通らなかったのと、そもそもそんなにDB側でJSONをどうこうしないのでbである必要はなかったです
db/schema.rbの削除
- 新しく作り直すために、念のため消してしまいます

Schedule.selectでは以上でしたが、ここのセクションはアプリケーションの都合による差が大きいと思います。なので、ゴリゴリとsedとかを駆使して頑張るか、数が少なければ手でなおしてしまうと良いと思います。

DBセットアップ

schema.rbを再生成します。ここで通らなかったら、一つ前に戻ってマイグレーションの修正をやり直しましょう。

bin/rails db:create
bin/rails db:migrate

テスト実行と修正

マイグレーションが実行でき、DBがSQLiteに切り替わったら、テストを実行しましょう。

Schedule.selectでは、orderを付けていないにもかかわらず、テストでソート順をチェックしてしまっていた箇所で失敗するということがありました。これはテストの内容がそもそも間違っており、かつPostgreSQLとSQLiteの実装の違いで出てきてしまう差分なので、テストを正しく修正してグリーンにしました。

PostgreSQLのデータをダンプ

pg_dumpを使ってダンプしていきます。その際に、 schema_migrations テーブル、 ar_internal_metadata テーブルは -T オプションで除外します。すでに手元のDBにもそのデータは入っているため、リストアに失敗してしまう原因になります。また、--inserts でPostgreSQL用のリストア用データ構造を避け、 --data-only でスキーマ情報は含まないようにします。

この例では、テストのためにローカル環境のPostgreSQLで開発用のデータをダンプしているため、ローカルホストを指しています。

pg_dump postgres://postgres:postgres@127.0.0.1:5432/mie_development --inserts --data-only -T schema_migrations -T ar_internal_metadata -T '*solid_queue*' > tmp/database_backup.sql

また、上記のダンプコマンドはSolidQueueのテーブルも読み込まないようにしています。SolidQueueのDBを分割するためなので、その場合はSolidQueueのテーブルは別でダンプする必要があります。

pg_dump postgres://postgres:postgres@127.0.0.1:5432/mie_development --inserts --data-only -t '*solid_queue*' > tmp/database_solid_queue_backup.sql

ダンプファイルを加工

ダンプしたデータは、SQLのINSERT文です。ですが、PostgreSQL専用コマンドなども含まれているので、消したり変更していきます。Schedule.select では以下の変更を行いました。

SET系の、PostgreSQLを制御する命令を削除
テーブルスキーマ（デフォルトだとpublic）の削除
pgcatalog.setvalの削除

SET系の命令は、基本的にPostgreSQLにリストアする際に必要なものなのですべて消してしまいます。Schedule.selectでは問題になりませんでしたが、もしかしたらデータの持ち方によっては同様の命令をSQLite側で再現する必要があるかもしれないので、念のために確認はしておいてください。

次にテーブルスキーマを削除します。これはデフォルトだとpublicが付く、PostgreSQLのテーブルの名前空間です。SQLiteには存在しないので消してしまいます。

sed -i '' -e 's/public\.//' tmp/database_backup.sql

最後に、pgcatalog.setvalの削除です。これは、PKにUUIDを使用しているSchedule.selectでは不要のため削除しました。通常のBigintを使っているシステムでも、もしかしたらSQLiteのsqlite_sequenceに変換する必要があるかもしれませんが、基本的にSQLiteはテーブルに入っているPKの最大値を復元するので、消してしまうでも問題ないと思います。

note.kiriukun.com

sed -id '' '/pg_catalog.setval/d' tmp/database_backup.sql

ダンプファイルからSQLiteへのリストア

リストアは非常に簡単です。

sqlite3 storage/development.sqlite3 < tmp/database_backup.sql

レコード数にもよりますが、INSERTベースのリストアなのでかなーり時間はかかります。その間、エラーが出ないことをひたすら祈ります。

（あれば）docker-composeやActionsなどの、開発やテストのためのPostgreSQLリソース削除

特にActionsなどのサイドカーとして残っていることが多いので、消してしまいましょう。docker-composeの場合はきちんとdownしてコンテナ自体を削除することを忘れずに、volumeをマウントしている場合などはそちらも削除するようにしましょう。

まとめ

試行錯誤を経てはいますが、全体としては思っていたよりもすっきりとPostgreSQLからSQLiteへの乗り換えができました。もちろんこれは、Schedule.selectが非常に軽量なアプリケーションで、DBに依存する処理などが入っていなかったという理由が大きいです。ですが、過度にPostgreSQL（やMySQL）固有の機能に依存した作りになっていなければ、あまり変わらない手間で移行できそうです。

自分は常々、一度動き出したアプリケーションのDBそのものを変更するときは、そのアプリケーションを作り直した方が良いタイミングだと思っていましたが、こういうポジティブな気持ちでDB移行をすると思ってなかったのでまあまあ面白かったです。

いつもの

やあ（´・ω・｀)

ようこそ、We are hiringへ。

このテキーラはサービスだから、まず飲んで落ち着いて欲しい。

うん、「また」なんだ。済まない。

仏の顔もって言うしね、謝って許してもらおうとも思っていない。

でも、この求人を見たとき、君は、きっと言葉では言い表せない「ときめき」みたいなものを感じてくれたと思う。

殺伐としたSaaS業界のなかで、そういう気持ちを忘れないで欲しい

そう思って、この項目を作ったんだ。

じゃあ、カジュアル面談に行こうか。

hello-world.smarthr.co.jp

SmartHR Tech Blog

SmartHR 開発者ブログ