- ストレージ容量不足アラートをきっかけに PostgreSQL の整理を進め、インデックスやデータを削除することなく 70GB超 の非最適化・未使用領域を確保した
- 当初は 未使用インデックス、テーブル・インデックスの bloat、
REINDEX CONCURRENTLY、pg_repack、PostgreSQL 13 の B-Tree deduplication といった一般的な回収手法を点検した
- 決定的な違いは、PostgreSQL は Oracle と異なり NULL 値もインデックス化する点で、NULL が大半を占める
cancelled_by_user_id の全面インデックスを partial index に変えたところ、769MB が 5MB 未満に縮小した
pg_stats.null_frac が高い単一カラムインデックスを探し、WHERE column IS NOT NULL 条件の partial index 候補を選別し、統計カウンタのリセット後に pg_stat_all_indexes で実際の使用有無を確認した- Django では nullable な
ForeignKey がデフォルトの B-Tree インデックスを作ることがあるため、db_index=False を明示し、models.Index(..., condition=Q(...__isnull=False)) で部分インデックスを直接定義する必要がある
ストレージ容量不足アラートから始まった整理
- 数か月ごとにデータベース監視でストレージ容量不足アラートが発生し、普段はストレージを追加して対応していた
- 当時は隔離期間でシステム負荷が低く、普段より負担の大きい 整理作業 を試すのに適した状況だった
- 結果として、インデックスを削除したりデータを消したりせずに 70GB超 の非最適化・未使用領域を確保した
- 一般的なインデックス・テーブル再構成だけでも多くの容量を削減でき、その後の予想外の発見によって 約20GB の未使用インデックス値を追加で除去した
まず疑った原因
-
未使用インデックス
- 未使用インデックスは検索性能のために作られるが、容量を消費し、
INSERT と UPDATE を遅くすることがある
pg_stat_all_indexes で idx_scan = 0, idx_tup_read = 0, idx_tup_fetch = 0 のインデックスを探し、最後の統計リセット以降 スキャンまたは fetch されていないインデックス を確認した- 見た目には未使用でも削除してはいけない場合がある
- PostgreSQL オプティマイザがインデックス自体ではなく メタデータ を利用する場合
- 長期間更新されていないテーブルの unique または primary key 制約を保証するインデックス
- 実際に削除可能なインデックスかどうかは一覧を1つずつ確認する必要があり、統計カウンタは点検後にリセットする方式で管理した
- この事例では定期的に同じ作業をしていたため、削除すべき 未使用インデックス はなかった
-
インデックスとテーブルの bloat
- PostgreSQL は行を更新すると既存 tuple を dead としてマークし、新しい tuple を別の領域に追加するため bloat が発生しうる
- bloat はテーブルだけでなくインデックスにも影響し、実際に必要なサイズより多くのストレージを使うことになる
- bloat の推定は単純ではないため、PostgreSQL Wiki と
pgsql-bloat-estimation のクエリでテーブルおよび B-Tree インデックスの bloat を見積もった
インデックス bloat を減らす方法
-
インデックス再生成・再構成
- インデックス bloat を除去するには、インデックスを作り直す必要がある
- 直接 drop してから create すれば、最適化された形で新しいインデックスを作成できる
- PostgreSQL の
REINDEX は既存インデックスをその場で再構成する
- 一般的な再生成方式はテーブルに lock をかけて変更を防ぐため、本番環境では
REINDEX INDEX CONCURRENTLY index_name のほうが適している
REINDEX CONCURRENTLY は _ccnew 接尾辞を持つ新しいインデックスを作成し、変更分を同期した後、完了時に既存インデックスと入れ替えて旧インデックスを削除する- 途中で再構成が中断されると、新しいインデックスが invalid 状態のまま残って容量を消費することがあるため、
_ccnew かつ indisvalid ではないインデックスを探して削除する必要がある
-
PostgreSQL 13 の B-Tree deduplication
- PostgreSQL 13 は B-Tree インデックスで重複値をより効率的に保存する B-Tree Deduplication を導入した
- PostgreSQL 12 までは、重複値が多くても各重複値がインデックス leaf に繰り返し保存されるため、多くの容量を使うことがあった
- PostgreSQL 13 で deduplication が有効になると、重複値を1回だけ保存するため、重複の多いインデックスサイズに大きく影響しうる
- PostgreSQL 13 では
deduplicate_items = ON がデフォルトで、旧バージョンからアップグレードした場合、効果を最大限得るには REINDEX でインデックスを再構成する必要がある
- 100万行の例では、重複の多い非一意カラムのインデックスは deduplication 適用時に 21MB から 6840kB へ縮小し、一意カラムのインデックスは 21MB のままで変化がなかった
- この事例では PostgreSQL 13 がまだ新しく、クラウドプロバイダも対応していなかったため、deduplication では容量を削減できなかった
テーブル bloat と pg_repack
-
テーブル bloat 除去の制約
- テーブルでも dead tuple によって bloat や断片化が発生しうる
- インデックスと違ってテーブルは単純に作り直しにくく、新しいテーブルの作成、データ移行、同期、インデックス・制約・参照制約の再作成が必要になる
VACUUM FULL table_name は bloat や dead tuple が占める容量を回収できるが、テーブル lock を必要とする- テーブル再生成と
VACUUM FULL は、それぞれ大きな開発作業またはダウンタイムにつながりうる
-
pg_repack の使用
- pg_repack はダウンタイムなしでテーブルとインデックスを再構成するために使われる PostgreSQL 拡張機能である
- 利用するには
CREATE EXTENSION pg_repack; で拡張を作成し、コンソールで pg_repack -k --table table_name db_name の形で実行する
pg_repack は新しいテーブルを作成し、元データを読み込みつつ新規データと同期し、その後インデックスも再構成して最後に2つのテーブルを入れ替える- 本番に適用する際にはいくつか制約がある
- 再構成対象のテーブルとインデックスのサイズに見合う 追加ストレージ容量 が概ね必要
- repack が失敗したり手動で中断したりすると、中間オブジェクトが残り 手動クリーンアップ が必要になることがある
- すでにストレージ容量が不足している状態では、追加容量の要求のため不向きな場合があるので、空き容量を監視して事前に計画する必要がある
予想外の発見: PostgreSQL は NULL もインデックス化する
- 一般的な手法で未使用インデックス除去とテーブル・インデックス bloat の整理を終えても、まだ削減できる余地が残っていた
- 最も大きいテーブルの1つは取引データを保存しており、決済後にユーザーがキャンセルして返金を受けるケースも記録していた
- 取引テーブルには購入ユーザーとキャンセルユーザーの両方に対する foreign key があり、各フィールドに B-Tree インデックスが定義されていた
- 購入ユーザーには
NOT NULL 制約があり、すべての行に値がある
- キャンセルユーザーは nullable で、キャンセルがまれなため大半の値が NULL だった
- キャンセルユーザーのインデックスは購入ユーザーのインデックスよりはるかに小さいと予想していたが、実際のサイズは同じだった
- Oracle では NULLs are not indexed と学んでいたが、PostgreSQL では NULL 値もインデックス化される
- 既存の全面インデックスは次のとおりだった
CREATE INDEX transaction_cancelled_by_ix
ON transactions(cancelled_by_user_id);
- NULL 値を除外する partial index に変えて仮説を確認した
DROP INDEX transaction_cancelled_by_ix;
CREATE INDEX transaction_cancelled_by_part_ix
ON transactions(cancelled_by_user_id)
WHERE cancelled_by_user_id IS NOT NULL;
- 再インデックス後、全面インデックスのサイズは 769MB で、値の 99% 以上が NULL だった
- NULL を除いた partial index は 5MB 未満 で、インデックスの 99% 以上を削減した結果となった
- テーブル統計をリセットして一定時間観察した結果、新しいインデックスは既存インデックスと同様に使われ、性能低下なく 760MB超 の未使用 indexed tuple を除去できた
partial index 候補を探す
- 1つの partial index で良い結果を得た後、似たインデックスを探すため
pg_stats.null_frac が高いカラムのインデックスを検索した
null_frac は PostgreSQL が推定した、そのカラム値に占める NULL の比率 である- 候補検索クエリでは次の条件を適用した
- primary key は partial にできないため除外
- すでに partial index の場合は除外
- composite index は除外
- サイズが 10MB超 のインデックスだけを含める
- 結果例では
tx_cancelled_by_ix が 1418MB、null_frac が 96.15% で、約 1363MB 削減できる可能性のある候補として現れた
- すべての NULL をインデックスから除外する方法が常に有利とは限らない
IS NULL 条件で NULL を探すクエリは、NULL 値に対するインデックスの利点を受けられる場合がある- NULL でない値だけでなく、ほとんど検索されない他の値も partial index で除外できる
- タイトルの 20GB は、プライマリデータベースで約 10GB 削減できれば replica 側でも同程度のストレージが一緒に減る構成だったため可能になった
Django ORM で partial index にマイグレーションする
-
nullable ForeignKey と暗黙のインデックス
- Django は
db_index=False を明示しない限り、models.ForeignKey フィールドに B-Tree インデックスを暗黙に作成する
- キャンセルユーザーを保存する nullable な
ForeignKey に何も設定しないと、Django が全面インデックスを作成する
- partial index を作るには、FK フィールドに
db_index=False を指定し、Meta.indexes に条件付き models.Index を追加する
class Transaction(models.Model):
cancelled_by_user = models.ForeignKey(
to=User,
null=True,
on_delete=models.CASCADE,
db_index=False,
)
class Meta:
indexes = (
models.Index(
fields=('cancelled_by_user_id', ),
name='%(class_name)s_cancelled_by_part_ix',
condition=Q(cancelled_by_user_id__isnull=False),
),
)
- nullable foreign key は partial index 候補 になりやすい
- 暗黙機能が気づかないうちにインデックスを作らないよう、Django check を作成して foreign key では常に
db_index を明示するよう強制した
-
ダウンタイムなしで全面インデックスを partial index に置き換える
- 全面インデックスを partial index に置き換える際、ダウンタイムや性能低下を避けるには段階的に進める必要がある
- 関連する Django モデルを修正して全面インデックスを partial index に変更するが、Django が生成したマイグレーションはそのまま実行しない
- そのマイグレーションは FK 制約を無効化し、既存の全面インデックスを drop してから新しい partial index を作成する可能性があり、ダウンタイムや性能低下を招きうる
./manage.py sqlmigrate で SQL を作成し、CREATE INDEX だけを抽出して CONCURRENTLY に書き換えたうえで、データベース上で手動実行する- 既存の全面インデックスがまだ残っているため、partial index の作成中もクエリは既存インデックスを引き続き利用できる
- 新しい partial index が使われているか確認するため、全面インデックスの統計カウンタを
pg_stat_reset_single_table_counters(<full index oid>) でリセットする
- その後
pg_stat_all_indexes の idx_scan, idx_tup_read, idx_tup_fetch を観察し、全面インデックスと partial index の使用状況を比較する
- partial index が使われていると判断できたら、全面インデックスを drop し、実際に確保できたストレージ容量を確認する
- データベース状態がモデル状態と一致した後、
./manage.py migrate --fake で Django マイグレーションを実行済みとして登録する
- dev、QA、staging のようにダウンタイムの考慮が小さい環境では、Django マイグレーションを通常実行して全面インデックスを partial index に置き換えた
最終整理
- ディスク、ストレージパラメータ、設定の最適化だけでは、性能と容量改善に限界がある
- 最後の改善余地はデータベースオブジェクト自体、この事例では インデックス定義 を変えることにあった
- 実施した流れは3段階に要約できる
- 未使用インデックスの除去
- テーブルとインデックスの repack、可能なら B-Tree deduplication の有効化
- 必要な値だけをインデックス化する partial index の活用
- この種の整理は、追加ストレージをプロビジョニングする前に数日の余裕を作る現実的な選択肢になりうる
1件のコメント
Hacker News のコメント
ストレージ容量が多くても、インデックスを小さくすることは良いことです。そうすれば、より多くのデータをホットセットに入れられます。
ただし、TB単位のデータベースを運用する立場では、「単にストレージを増やせばよい」というのも常に有効な選択肢です。特にクラウド外ならなおさらで、自前のハードウェアではエンタープライズ向け NVMe SSD が約 $80/TB、DDR4 RAM は約 $1.20/GB です。エンジニアリング4時間分のコストをおおよそ $1000 と見積もると、RAM 800GB かストレージ 12TB を買えます。
1年分の週次バックアップだけでも 1TB が 50TB になり、オフラインストレージでも同じです。私たちの会社でも、ライブDBにストレージを追加するのは簡単だったので何年もそうしてきましたが、マルチTBのバックアップを移動するのがだんだん面倒になり、エンジニアリングコストをかけてでも本番データを減らす必要が出ています。
一度きりの場合、既存インフラが空きラックスペースや余分な PCIe スロットのようなコストを吸収できることもありますが、この種の償却は小規模でしかうまく機能しません。また、システムが大きくなるほど管理コストとレイテンシ増加のため、機器1台あたりの性能は必然的に落ちます。システムを拡大し続けて解決しようとすると、システム全体は次第に鈍重になり、運用不能になることもあります。逆に、必要なシステムリソースを減らす解決策は、単位リソースあたりの性能を高め、それ自体がより質の高い資産になります。
ある最適化で今日 1TB を節約できるなら、数年後には 2TB を節約できるかもしれず、こうした判断がいくつかあるだけで一桁以上の差が出ることもあります。より大きな規模では特にそうで、結局は常にバランスを取る必要があります。
「ハードウェアを投入する」選択が時には賢明なこともありますが、ほとんどの場合、今日 128GB RAM のサーバーを明日 $1K で 1TB RAM に変えるのは難しいと思います。
ただし一度きりの支出ではなく月額費用なので、計算は少し複雑になります。もちろん、データベースが一定の速度で成長するなら、オンプレミスでの一回限りのプロビジョニングも実質的には月額費用に近いものです。
記事では、PostgreSQL 13 にはあるが使用中の 12 にはない B-tree の重複排除に触れつつ、一部の外部キーインデックス値の大半が NULL だと述べています。
この場合、B-tree の重複排除が役に立ったのか気になります。PostgreSQL 13 ドキュメントの 63.4.2 を見ると、そうであるように見えます: “B-Tree deduplication is just as effective with ‘duplicates’ that contain a NULL value...” もちろん、記事で適用した部分インデックスほど効果的ではないでしょうが、興味深いです。
https://www.postgresql.org/docs/13/btree-implementation.html
https://news.ycombinator.com/item?id=25989467
この記事は、核心である部分インデックスの「発見」だけでなく、PostgreSQL でスペースを非効率に使っているのが心配なときに知っておくとよい手法を全般的にまとめてくれていて有用でした。
ただし、スタートアップや初期段階の会社なら、こうしたストレージ問題ではサイズ最適化に気を配るより、ディスク容量を増やす選択のほうがほぼ常に優れている、という点は覚えておく価値があります。開発者は高く、ディスクは安いです。
以前、急成長していたスタートアップで働いていたとき、創業者の一人がサーバー1台だけをアップグレードし続けることにこだわっていましたが、冗長性とバックアップは備えつつ、アーキテクチャがシンプルだったので管理、デバッグ、復旧が容易でした。会社の最初の5年間、システム全体が1台のサーバーで動きながらも指数関数的に成長し、世界中の数百万ユーザーを処理していました。それを見て以来、必要なときだけ、最もシンプルでまっすぐな方法でアップグレードすべきだと確信しています。
値の大半が NULL である場合のように、ユースケースに明確に合った部分インデックスを使うのは正しいモデリングに近く、時期尚早な最適化や開発者時間の浪費と見なすべきではありません。
似た文脈で、これらのスクリプトはかなり役に立ちました。
https://github.com/NikolayS/postgres_dba
大きなテーブルのカラム順序を再構成することで、ストレージの約10%、つまり100GBほどを確保できました。
先週、
reindexとpg_repackだけで 600GB の DB から 200GB を確保しました。最もひどいテーブルはデータが 17GB なのにインデックスが 142GB あり、再インデックス後に 21GB まで減りました。このテーブルはインデックスが過剰で、複数のカラム組み合わせに対するインデックスがいくつもありました。巨大なインデックスの原因の一つはデータ分布だったように思います。2015年から挿入、更新、削除が続いており、古いデータほど削除される可能性が高いため最近の年のデータのほうが多いものの、それでも約 0.1% は 2015年のデータです。このような長いテールを持つ偏った分布のため、vacuum がインデックス肥大に対処しにくかったのかもしれないと思います。
当時も議論されていた: An unexpected find that freed 20GB of unused index space in PostgreSQL - https://news.ycombinator.com/item?id=25988871 - 2021年2月、コメント78件
使われていないインデックス、最適化の機会、レイテンシの大きいクエリを見つけるには pganalyze.com を強くおすすめする
本当に「奇妙なトリック1つで20GB節約」という言い方が誇張ではなかった。古いOracle / SQL Serverの現場ではほとんど聞いたことがない PostgreSQL特有のインデックス形態を使いたいという要望をよく受けるが、NULL値もインデックス化されるとは知らなかった
初めて参加した大規模プロジェクトで、データセットが大きくなるにつれてなぜ処理が遅くなったのか、チームは把握できていなかった
インデックスはレコードごとの挿入時間が log(n) になる。テストDBのレコードが1000件から65k近くになると、挿入時間は60%増える（2^10対2^16）。成功がすべてを遅くし、サーバーをアップグレードしても限界がある。ビジネス側が求めた曖昧な機能のためにインデックスをいくつか追加すると、今度は2倍になる
数億レコードで、テーブル/コレクションあたり40個以上のインデックスがあるDBもたくさん管理している
B-treeページ内の二分探索は100サイクル未満だ。1億レコードに対するB-tree探索でもまだマイクロ秒単位のはずで、その上の二分探索も、数百ナノ秒ではないにしてもマイクロ秒単位だろう
1つまたはいくつかの値が非常に多い場合にも、この原理は当てはまるのだろうか？ たとえば整数カラムの 90%が0 の場合
where val = 0が行の90%を残すと分かっていれば、インデックスではなくテーブルスキャンを選べる