MoTLab -GO Inc. Engineering Blog-MoTLab -GO Inc. Engineering Blog-

PostgreSQL大量レコードの更新する作業でやったこと

SQL
December 08, 2023

バックオフィス基盤第2グループの名嘉眞です。今回は業務で合計で1800万件程のレコードを更新する際にやったことについて書こうと思います。 最終的に本番環境に対してどう更新したのかは、1度に更新するレコード数を制限できるように更新用のバッチ処理を作成し実行しましたが、そこに至るまでにやったことを記事にしようと思います。あまり大量のレコードを更新することはないかも知れないですが誰かの参考になれば幸いです。

大量のレコードを更新することになったきっかけ

バックオフィス基盤第2グループでは主に支払い請求基盤を開発しています。 支払い請求基盤は名前の通り、タクシー事業者様や、経理などに向けた支払いと請求の金額や取引の件数を集計し、その結果を管理画面向けのAPIとして提供したり、 会計システムに連携したりしています。 そのAPIで検索結果への項目追加があり、対象のテーブルにカラムを追加してその値をAPIのレスポンスに含めたいというタスクがきっかけでした。(よくありそうな改修内容ですよね)

更新用SQLを考える

追加したカラムに設定したい値は既存の別のテーブルに保持していまして、更新作業としてはSELECTした結果で対象のテーブルのレコードを更新するというSQLで実現できる状況でした。 更新対象のテーブルは細かいところは割愛しますが、取引データ関する情報を保持しているテーブルでして、対象のテーブルは年月でパーティション分割されております。また更新用の値を保持している別のテーブルも年月でパーティションされています。 この年月というのは締日の年月になっています(つまり締日がパーティションのキー)。支払い請求基盤ではタクシー事業者様に締日単位でのお支払いや請求のやり取りをしていることもあり締日(15日と末日)を取引データに保持しています。 そのため更新対象の合計は1800万件程になりますが、締日で分けると以下のような感じで分けることができました。(実際の件数ではないです)

  • 2023/10/15 ... 250万件
  • 2023/10/末 ... 750万件
  • 2023/11/15 ... 250万件
  • 2023/11/末 ... 550万件

この締日ごとに更新した方が良さそうではありますが、それでも件数が多いのでこの時点でバッチ処理を作成することを考え始めました。 ちなみにPostgreSQLで大量のレコードを一括更新した場合以下のようなことが発生する可能性があります。

- 大量のレコードを更新するため時間がかかり、更新操作中は更新対象の行がロックされ、他のトランザクションから更新することができない
- 1度に大量のレコードを更新するとVACUUM処理が追いつかずDBの性能を低下させるおそれがある
- 更新対象のレコードがメモリに一時的に保存されることによるメモリ使用量の増加

※ PostgreSQLのVACUUMについて


PostgreSQLでは、行の更新や削除が行われると、古いデータはすぐに削除されずに、新しいバージョンの行が作成されます。
VACUUMはこれらの古いデータ(デッドタプル)を削除し、ディスクスペースを再利用可能にします。(詳しくはこちら)

開発環境での確認について

更新SQLをレコード件数を制限して実行した方が安全なのはわかるのですが、実際に1回のSQLで更新するレコードが何件の時更新SQLはどのくらい時間がかかるのか確認したくなりました。 大量のレコードのテストデータを用意する方法は色々ありますが、今回は generate_series を利用してSQLだけでテストデータを用意しました。 COPY コマンドも検討しましたがテストデータとなるCSVファイルを作成することが少し面倒なのと、 generate_series の場合SQLだけで準備できる手軽さがあります。 以下のようなテストデータ登録SQLを作成しました。(実際のSQLとは異なります)

-- generate_series(1, 1000000) で指定した件数のレコードを作成する
INSERT INTO "settlements" ("transaction_id", "event_id", "record_date", "closing_date", "store_id", "item_id", "method_id", "type_id", "amount", "order_id", "description", "created_at", "updated_at")
SELECT
  gen_random_uuid (), -- transaction_id
  gen_random_uuid (), -- event_id
  TO_DATE('2023-11-01', 'YYYY-MM-DD') + CAST(format('%s days', TO_CHAR(i % 15, '99')) AS INTERVAL), -- record_date
  '2023-11-15', -- closing_date
  i % 1000, -- store_id
  101, -- item_id
  101, -- method_id
  1, -- transaction_type_id
  1000, -- amount
  gen_random_uuid (), -- order_id
  'description', -- description
  TO_TIMESTAMP('2023-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + CAST(format('%s days', TO_CHAR(i % 15, '99')) AS INTERVAL) + CAST(format('%s seconds', TO_CHAR(i % 60, '99')) AS INTERVAL), -- created_at
  TO_TIMESTAMP('2023-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + CAST(format('%s days', TO_CHAR(i % 15, '99')) AS INTERVAL) + CAST(format('%s seconds', TO_CHAR(i % 60, '99')) AS INTERVAL) -- updated_at
FROM
  generate_series(1, 1000000) AS i;

generate_series で指定した件数(上記の場合1000000件)のレコードを作成します。 DATE型やTIMESTAMP型のカラムに関してはテストデータの内容として締めの期間(上記だと11/1 〜 11/15)に合致するように少し工夫していますがあとは単純SQLです。

今回のタスクでは問題になりませんが、例えば日付に関する値で、日付ごとのレコード件数が同じぐらいの件数になるためデータの分布に違いを持たせたい場合は少し変更が必要です。 また固定値にしている箇所も本来は複数の値が設定される可能性がありますが今回は問題にならないので固定値にしています。 15分ぐらいで1000万件のレコードが登録できました。(手元の環境によります)

テストデータ登録後に、実験的に1000万件を1回で更新しようとすると、2589秒かかりました。 その後、件数を10万件などに絞って動作確認を行い問題なさそうなことを確認しました。

更新用バッチ処理について

更新用バッチ処理はコマンド引数で以下の値を受け取り、この記事の冒頭でも記載したような更新SQLを実行するような処理にしました。

$ ./update-payments-batch --help                                                                                                                                                  [develop]

OPTIONS:
   --closing-date value          締め日, ex) '2023-01-15'
   --fetch-rows-limit value      1回の更新処理で取得する行数, ex) 20000 (default: 0)
   --exec-limit value            1回の更新処理で実行する数, ex) 200 (default: 0)
   --base-wait-second value      1回の更新処理で待機する基本時間(sec), ex) 2 (default: 0)
   --add-wait-millisecond value  回数ごとに等間隔で増加する分の待機時間(msec), ex) 200 (default: 0)
   --cursor value                更新対象を絞り込むためのカーソル(id), ex) 1111 (default: 0)
   --help, -h                    show help

締日と1回更新SQLの更新対象件数、1度のバッチ処理実行で何回更新SQLを実行するかをを指定できるようにしています。 バッチ処理でやりたいことは、指定された締日の、指定された件数ずつ更新できれば良いのですが、指定された件数ずつ更新する回数も指定できるようにすることで、少しずつ確認しながら実行できることを考慮しました。 例えば最初の1回目は10レコードだけを更新して実行し問題ないことを確認した後、2回目の実行はidを指定することで11件目から更新できるようにしました。 その他、1回の更新SQL実行後にすぐ2回目の更新SQLを実行するのではなく念の為少し待機時間を設定できるようにしました。

最後に

実際の更新作業としては問題なく作業ができまして、更新作業による本番環境への影響もありませんでした。

タスクとしては単純でも、既に利用されている機能の改修の場合は特にそのデータ量などで難易度や、やる事が変わり難しさもありますが、とても面白いなと感じました。 この記事が誰かの参考になると幸いです。

We're Hiring!

📢
GO株式会社ではともに働くエンジニアを募集しています。

興味のある方は 採用ページ も見ていただけると嬉しいです。

Twitter @goinc_techtalk のフォローもよろしくお願いします！