PostgreSQLの、テーブル・インデックスのメンテナンスについて

テーブルやインデックスで必要となるメンテナンス処理についての備忘。

調べた結論だけまとめておくと、下記といった感じでした。

  • auto vacuumを有効にしておく(デフォルトで有効)
    • かつ、適当な頻度でauto vacuumされていることを、定期的に確認する
  • indexの断片化状態を定期的に確認する
    • 必要であれば、indexを再構築してあげる

テーブルのvaccum

PostgreSQLデータベースはバキューム処理として知られている定期的な保守を必要とします。

PostgreSQL - 定常的なバキューム作業

PostgreSQLのvacuumコマンドは以下の理由により定期的にそれぞれのテーブルを処理しなければなりません。
1. 更新、あるいは削除された行によって占められたディスク領域の復旧または再利用。
2. PostgreSQL問い合わせプランナによって使用されるデータ統計情報の更新。
3. 可視性マップの更新。 これによりインデックスオンリースキャンが高速化される。
4. トランザクションIDの周回またはマルチトランザクションIDの周回による非常に古いデータの損失を防止。

vaccumにより何か実行されているのか、順におさらい・確認していきます。

ディスク領域の復旧または再利用

これはポスグレにおける有名なはなし。PostgreSQLは追記型アーキテクチャのため、updateやdeleteとなったレコードを削除することなく、削除フラグを付与する形となる。そのフラグされた領域を再利用可能としてくれる処理。

今のポスグレでは、いい感じで自動的にvacuumしてくれる機能が用意されており、これに任せてしまうのが推奨。デフォルトで有効となっています。

auto vacuumが実行される条件はこちら。

autovacuum_vacuum_threshold + (テーブル行数 * autovacuum_vacuum_scale_factor) < dead tuple数

  • autovacuum_vacuum_threshold
    • autovaccum実行のしきい値となる、dead tuple(updateやdeleteされた行数)の最小数
    • デフォルトは 50
  • autovacuum_vacuum_scale_factor
    • autovaccum実行のしきい値となる、テーブル内でdead tupleとなっているレコード数の割合
    • デフォルトは 0.2

PostgreSQL - autovacuumのパラメータ

いずれのパラメータも、 postgresql.conf またはテーブル作成時に指定可能です。

全体の1/5がdead tuple化しないとvacuumされないため、大きいテーブルでは要注意となります。

統計情報の更新

auto vacuum処理では、同時に統計情報の更新も行ってくれるらしいです。別途 analyze コマンドも用意されていますが、こちらも自動処理に任せてしまうのが推奨とのこと。

auto analyzeが実行される条件はこちら。

autovacuum_analyze_threshold + (テーブル行数 * autovacuum_analyze_scale_factor) < 更新レコード数

  • autovacuum_analyze_threshold
    • autoanalyzeのしきい値となる、更新レコードの最小数
    • デフォルトは 50
  • autovacuum_analyze_scale_factor
    • autoanalyzeのしきい値となる、テーブル内で更新されたレコード数の割合
    • デフォルトは 0.1

PostgreSQL - autovacuumのパラメータ

いずれのパラメータも、 postgresql.conf またはテーブル作成時に指定可能です。

可視性マップ(Visibility Map)の更新

可視性マップ(Visibility Map)とは、

Each heap relation has a Visibility Map (VM) to keep track of which pages contain only tuples that are known to be visible to all active transactions; it also keeps track of which pages contain only frozen tuples.

PostgreSQL - Visibility Map

つまり、各ページ内でfrozen tupleが含まれているかいないかを、管理してくれているマップファイルです。 XXXXX_vm というファイル名にて、各ヒープリレーション(テーブルファイル)ごとに作成されています。こんな感じ。

$ sudo ls -l /var/lib/pgsql/10/data/base/13807
-rw-------. 1 postgres postgres  73728  55 01:01 1247
-rw-------. 1 postgres postgres  24576  55 01:01 1247_fsm
-rw-------. 1 postgres postgres   8192  55 01:01 1247_vm
-rw-------. 1 postgres postgres 393216  55 01:01 1249
-rw-------. 1 postgres postgres  24576  55 01:01 1249_fsm
-rw-------. 1 postgres postgres   8192  55 01:01 1249_vm

Vacuum処理では、このVisibility Mapも一緒に更新することで、Vacuum対象のページを管理できるようなり、今後のVacuum処理の効率化を図っている訳ですね。

Visibility Mapの役割としては、上記のVacuum処理効率化のほか、Index Only Scanでも利用されています。

PostgreSQLが、背後にあるテーブルを参照することなく、インデックスのみを使用して一部の問い合わせに応えることができるようになります。 PostgreSQLのインデックスにはタプルの可視性に関する情報を持ちませんので、通常のインデックススキャンは合致したインデックス項目のヒープタプルを取り込み、現在のトランザクションから可視であるべきかどうか検査します。 一方でインデックスオンリースキャンはまず可視性マップを検査します。 そのページのタプルがすべて可視であることが分かれば、ヒープの取り出しを省くことができます。

トランザクションID(XID)周回問題の回避

XID周回問題というのは、ポスグレ界では有名な話なのですね。

PostgreSQLのMVCCトランザクションのセマンティックは、トランザクションID(XID)番号の比較が可能であることに依存しています。 現在のトランザクションのXIDよりも新しい挿入時のXIDを持ったバージョンの行は、「未来のもの」であり、現在のトランザクションから可視であってはなりません。 しかし、トランザクションIDのサイズには制限(32ビット)があり、長時間(40億トランザクション)稼働しているクラスタトランザクションの周回を経験します。 XIDのカウンタが一周して0に戻り、そして、突然に、過去になされたトランザクションが将来のものと見えるように、つまり、その出力が不可視になります。 端的に言うと、破滅的なデータの損失です。 (実際はデータは保持されていますが、それを入手することができなければ、慰めにならないでしょう。) これを防ぐためには、すべてのデータベースにあるすべてのテーブルを少なくとも20億トランザクションごとにバキュームする必要があります。

で、Vaccum処理では、この 破滅的なデータの損失 を防ぐため、以下のことをしてくれています。

The reason that periodic vacuuming solves the problem is that VACUUM will mark rows as frozen, indicating that they were inserted by a transaction that committed sufficiently far in the past that the effects of the inserting transaction are certain to be visible to all current and future transactions.

PostgreSQL - Preventing Transaction ID Wraparound Failures

現在のXID値と比較して、充分に古いとされるXIDのタプル(レコード)に、Freeze処理が実行されます。Freeze処理が実行されたタプルは、XIDの値が充分に古いとされる特殊なXID値により更新がなされ、XIDの周回が起きたとしても、すべてのトランザクションより可視化できるようなります。充分に古いとされる条件は、postgresql.conf内のパラメータにより定義されています。

vacuumとvacuum Fullの違い

  • vacuum
    • Auto vacuumの処理はこっち
    • 再利用可能領域をOSに返却しない
    • DDL系のコマンドは並列可能
  • vacuum Full
    • 明示的に実行する必要がある
    • 再利用可能領域をOSに返却する
      • 再利用可能領域を圧縮することで、テーブルファイルの容量を最小化していく
      • 処理対象のテーブルをコピーして実行するため、コピー用のDISK領域が必要となる
      • 処理が長い
      • DISK I/Oへの負荷が高い
    • テーブルの排他ロックを取得する

インデックスの肥大化

PostgreSQLでは、インデックスファイルも追記型アーキテクチャであるため、テーブル同様、何もしない限りファイルサイズが肥大化していきます。auto vacuumの処理では、テーブルファイル同様、インデックスファイルに対しても、再利用可能領域とする処理を実行してくれています。

インデックスの断片化

B-treeインデックスのリーフ部分が一杯になった場合、該当のリーフは分割されることになります。この分割を繰り返すことで、インデックスの断片化が発生し、効率的なインデックスの利用(DISK I/Oの増加)が起こることとなります。

インデックス断片化の確認

インデックスの断片化状況を確認するには、 pgstattuple モジュール内の pgstatindex 関数を利用します。

PostgreSQL - pgstattuple

pgstattuple は、 postgresql10-contrib パッケージ内に含まれています。make&installすることになるのですが、yumでもinstallできるので、初期構築時にインストールしておくと良いです。DBを利用開始したあとに yum install すると、DBサーバのマイナーバージョンも一緒に上がってしまったりします。

PostgreSQL - postgresql-contribパッケージに含まれるモジュール

利用したいDBに、 create extension コマンドで有効化できます。

> select * from pg_available_extensions where name = 'pgstattuple';
    name     | default_version | installed_version |           comment
-------------+-----------------+-------------------+-----------------------------
 pgstattuple | 1.5             |                   | show tuple-level statistics
(1 行)
>
> create extension pgstattuple;
CREATE EXTENSION
>
> select * from pg_available_extensions where name = 'pgstattuple';
    name     | default_version | installed_version |           comment
-------------+-----------------+-------------------+-----------------------------
 pgstattuple | 1.5             | 1.5               | show tuple-level statistics
(1 行)

確認はこのコマンド。pgstatindexの引数に、確認したいindexを入れます。

# select * from pgstatindex('idx_actor_last_name');
 version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
       2 |          0 |      16384 |             1 |              0 |          1 |           0 |             0 |       52.75 |                  0
(1 行)

leaf_fragmentation の値が大きい場合、断片化が発生しています。

インデックス断片化の解消

断片化の解消は REINDEX コマンドを用います。

REINDEXは、インデックスのテーブルに保存されたデータを使用してインデックスを再構築し、古いインデックスのコピーと置き換えます。

PostgreSQL - Reindex

こんな感じですね。

> reindex index idx_actor_last_name;
REINDEX

注意点として、REINDEXコマンド実行時には、テーブルの排他ロックが取得されるようです。

REINDEXはすべての状況で安全に簡単に使うことができます。 しかし、このコマンドはテーブルの排他ロックを要求しますので、生成と置き換えの処理を続けて行なうことでインデックスの再構築を実行する方が好ましい場合がしばしばあります。

PostgreSQL - 定常的なインデックスの再作成

テーブルのCluster性

このCluster性とは、いわゆるCluster Index等で使われる時のClusterです。テーブルファイル内カラムの物理的配置が、インデックス内の順番(ソートされた並び順)と、どれだけ保たれているか、という話です。ポスグレでは、テーブルのクラスタ性を保つような機能はないので、Cluster性はどんどん崩れていきます。

Cluster性の確認

確認コマンドは下記。 pg_stats テーブルから確認できます。

> select tablename, attname, correlation from pg_stats where tablename = 'actor';
 tablename |   attname   | correlation
-----------+-------------+-------------
 actor     | actor_id    |     0.99752
 actor     | first_name  |  -0.0175804
 actor     | last_name   |  -0.0245916
 actor     | last_update |           1
(4 行)

correlation の値が0に近いほど、Cluster性が崩れています。

物理的な[訳注:ディスク上の]行の並び順と論理的な列の値の並び順に関する統計的相関です。 この値は-1から+1の範囲です。 値が-1もしくは+1の近辺にある時、ディスクにランダムアクセスする必要が少なくなるためこの列に対してのインデックススキャンは0近辺にある場合に比較して安価であると推定されます。

PostgreSQL - pg_stats

Cluster性の回復

cluster コマンドでクラスタ性を回復できます。

> cluster actor using idx_actor_last_name;
CLUSTER

物理的なタプルを並び替えているので、該当テーブルへのアクセスは禁止されます。

クラスタ化を行っているテーブルでは、ACCESS EXCLUSIVEロックが獲得されています。 これにより、CLUSTERが終わるまで、そのテーブルに対するデータベース操作(読み書き両方)はできません。

PostgreSQL - cluster