Rails+PostgreSQLのパーティショニングを制覇する(翻訳)

概要 原著者の許諾を得て翻訳・公開いたします。 英語記事: Partition and conquer 原文公開日: 2017/11/07 著者: Sergey Dolganov、Denis Lifanov サイト: https://evilmartians.com/ 原文タイトルはおそらくCommand&Conquerのもじりと思われます。 Rails+PostgreSQLのパーティショニングを制覇する(翻訳) 前書き 本記事は実際の出来事をヒントにしたデータベースパーティショニングについて書いたものです。productionアプリ、すなわちRuby on RailsとPostgreSQLの速度を低下させる巨大なテーブルを分割する方法について手順を追って学びます。 これ以上大きくなっては困るとき データベースは肥大化する傾向があります。データベースのサイズはある時点から負債と化しますが、主キー数が上限に達するような極端な状況をなかなか想定できません(そしてこれは実際に起きます)。本記事は、私達の一顧客であるGettでの経験を元にしています。このときはデータベーステーブルが危険水域に達するほど肥大化し続けていました。 行数が数百万行に達すると、クエリによっては完了に数時間を要することもあります。これによって生じた技術的な困難をデータベースパーティショニングによって解決しました。 1個の巨大なテーブルを多数の小さなテーブルに分割するというのは標準的な技法ですが、特に本番稼働中のデータが危機にひんしている場合は注意深く行う必要があります。本記事では、よくある落とし穴を回避してデータロスなしで移行できるようにする方法を解説します。何らかのハンズオンをやってみるのが学習法として最善なので、最初にフェイクデータで巨大なテーブルを作成して問題を作り出します。続いて、PostgreSQLのマジックを武器としてこの問題を皆さんと一緒に解決します。 実際のフェイクテーブル まずデータが、それも大量のデータが必要です。改善前のテーブルが含むordersには、通常のビジネスロジックを模したカラムがあります。 訳注: 上の一文目は、おそらく映画「マトリックス」のセリフ(Guns, Lots of Guns)のもじりです。 CREATE TABLE orders ( id SERIAL, country VARCHAR(2) NOT NULL, — 国コード type VARCHAR NOT NULL DEFAULT ‘delivery’, — orderの種類 scheduled_at TIMESTAMP WITHOUT TIME ZONE NOT NULL, — orderの作成時刻 cost NUMERIC(10,2) NOT NULL DEFAULT 0, — orderのコスト data JSONB NOT NULL DEFAULT ‘{}’ — 追加データ ); 注: リファクタリングでは主にPostgreSQLを考慮するため、以後クエリは純粋なSQLで、関数はPL/pgSQLでそれぞれ表記します。作業が終わった後は、ActiveRecord経由でデータをRailsで扱えるようになります。 最初に、実行頻度が最も高いクエリを次のように決めます。 idでorder_byしてorderを1件取得 ある期間(精度は分単位)の特定の国についてのordersをすべて取得する orderのcostや関連付けられたデータを変更する ordersテーブルを検討すれば、最も多いクエリの速度を向上させるにはcountryとscheduled_atをインデックス化するのが妥当であることが即座にわかります。 CREATE INDEX index_orders_on_country_and_scheduled_at ON orders (country, scheduled_at); 準備が整ったので、以下のようにランダムな値を用いてgenerate_seriesでテーブルの値を埋めます。 INSERT INTO orders (country, type, scheduled_at, cost) SELECT (‘{RU,RU,RU,RU,US,GB,GB,IL}’::text[])[trunc(random() * 8) + 1], (‘{delivery,taxi}’::text[])[trunc(random() * 2) + 1], CURRENT_DATE – (interval ‘1 day’ * (n / 100000)) + (interval ‘1 second’ * (random() * 86400)), round((100 + random() * 200)::numeric, 2) FROM generate_series(1,30 * 1000000) s(n); 分割 目標はストレートに設定しなければなりません。ここではordersを分割して次のようにしたいと考えています。 生成されるテーブルには特定の月の特定の国のordersがすべて含まれること アプリのロジックがほぼ変わらないようにすること 最も達成しやすいのは、子テーブルを作成し、対応するトリガを作成し、テーブル全体にレコードを分散させるトリガ関数を作成する方法です。 しかしこの方法でActiveRecordでデータベースにクエリをかけたい場合、ひとつ面倒な点があります。純粋なSQLでは、同じレコードを2回INSERTする(マスターテーブルで1回、子テーブルで1回)のを避けるため、トリガプロシージャはNULLを返す必要があります。しかしこれはActiveRecordとの相性がよくありません。ActiveRecordはINSERT文でRETURNING文を使った場合に新規レコードの主キーを1つ返すことを期待するからです。解決方法はいくつか考えられます。 レガシーなスキーマで(データベース)ビューを使う方法の例はRailsガイド(英語)をご覧ください。 ActiveRecordにおまかせする: NULLの代わりに新規レコードを返します。新規レコードをマスターテーブルと子テーブルにそれぞれ配置したら、マスターテーブルから即座に削除します。つまり1つの操作を3つに分けて行うことになります。この方法を選んでもパフォーマンスが必然的に著しく低下するため、ほとんどのRails開発者はパーティショニング自体を諦めざるを得なくなるでしょう。 ActiveRecord PostgreSQLアダプタで切り抜ける: Rails 4.0.2以降なら設定ファイルでinsert_returningをfalseに設定すればよいので、これは難しくありません。これはうまくいきますが、その代わりアプリの全テーブルの振る舞いが変わってしまいます。また、(主キーの現在の値を取得するため)INSERT操作ごとにリクエストを1つ余分に受け取ることになります。 (データベース)ビューを使う: これならデータベースレベルのリファクタリングだけでできるようになります。しかも、ビューは「普通の」テーブルであるかのように扱えるため、ActiveRecordはビューと自然に協調動作でき、既存アプリのロジック変更は最小限で済みます。 第3の方法を使うことにします。最初に、テーブルを複製する必要があります。テーブルを複製するメリットは次のとおりです。 既存データの完全性を保ち、他のモデルからの参照が安全に保たれる パーティショニングのデプロイ中(マイグレーション後からリスタートまでの間)に既存アプリを生かしておくことができる 作業中に問題が発生しても元のテーブルにフォールバックできる 複製は次の方法で行います。 CREATE TABLE orders_partitioned (LIKE orders INCLUDING ALL); クローンされたテーブルの主キーは、元のテーブルと同じorders_id_seqシーケンスを参照します。これにより、古いテーブルから新しいテーブルにデータを移動するときに衝突を回避できます。 データベースビューはマジでいいやつ 今度は新しいテーブルでビューを作成する必要があります。新しいテーブルはまだ空ですが、変更をデプロイするとすべての新規レコードがそこに配置され、対応する複数の子テーブルにも直ちに同じレコードが配置されます。 CREATE OR REPLACE VIEW orders_partitioned_view AS SELECT * FROM orders_partitioned; まだ何か足りないようです。デフォルト値はどうすればよいでしょうか。明らかに主キーのデフォルト値が必要ですし、デフォルト値がないとActiveRecordでINSERTが効かなくなってしまいます(orders_id_seqにご注目ください)。 ALTER … Continue reading Rails+PostgreSQLのパーティショニングを制覇する(翻訳)