釣りキチプログラマー翔平の備忘録 
はじめに
この記事では
- PostgreSQLで主要なファイルの中身はどうなっている？？
- ファイルへのアクセスとデータの増減タイミングは？？
についてまとめました。
データベースのディスク容量を見積もる際、
ディスクI/Oを減らして性能改善する方法を考える際に
必要な内容です。
テーブルファイルを知る
テーブルに登録するレコードの実体は各ページの最後尾からアイテムとして登録されます。
アイテムデータの方には該当するアイテムのオフセット、長さ、有効無効などの
メタデータが登録されています。
テーブルファイルの拡張とデータの増減
テーブルファイルは、
ページと呼ばれる固定長(8,192バイト)領域が連続して配置されたものになります。
テーブルデータが増えていってページがいっぱいになると、
固定長領域が拡張され次のページへ登録していき、
最大1GBまで拡張します。
それを超えると、
テーブルファイルが新しく作成され、そちらに登録していきます。
1テーブルあたり最大で32TBまでの拡張が可能です。
(ただし、パーティショニングテーブルを利用すればさらに大きなテーブル管理も可能)
テーブルファイルのサイズは以下のタイミングで増加し、
- データの挿入時
- データ更新時に既存の再利用可能領域が使用できず、新規ページ追加時
以下のタイミングで減少します。
- DROP TABLE時
- TRUNCATE TABLE時
- CLUSTER時(有効なレコードだけをコピーして作り直す)
- VACUUM時(有効なレコードだけをコピーして作り直す)
FILLFACTOR
ページ内の空き領域をデータ挿入時にどの程度利用するかの割合を示すパラメータです。
例えば、90%ならアイテムを空き領域の90%が埋まるまで挿入することになります。
PostgreSQLは追記型であるため、
更新時にデータが追加され、ページ拡張のオーバーヘッドが発生する可能性があります。
そのため、
更新が想定されるテーブルに
更新用の領域をあらかじめ確保しておくことで、
更新時のページ拡張オーバーヘッドを回避できます。
FILLFACTOR値はテーブルは100%、インデックスは90%です。
FILLFACTER値のチューニング基本方針は以下です。
- 該当テーブルに更新や削除がなければ(検索、登録のみ)100%のまま
- 該当テーブルに更新があれば該当テーブル平均レコード長の2倍の空き領域を確保(同時に該当ページ2レコード更新は可能性が低いため)
- FILLFACTORを小さくしすぎるとディスクに無駄が生じるので注意(FILLFACTORの加減は70%程度)
総ページの概算値 ＝ (レコードの想定平均サイズ(TS) * 想定レコード数(RN)) / (ページサイズ(8,192) * FILL FACTOR)インデックスファイルを知る
インデックスファイルにはCREATE INDEXで作成される
インデックスの実体が格納されます。
各ページの構成はテーブルファイルとほぼ同じです。
行の代わりにインデックスがエントリされ、
インデックスはメタ、ルート、インターナル、リーフの4ページから構成され、
全体として1つのTree構造をを構成します。
インデックスファイルの拡張とデータの増減
基本的にはインデックスファイルの拡張もテーブルファイルと同じですが、
例えばリーフページが一杯になったらSplitが発生し、断片化します。
断片化とその対策についての詳細は、
こちらの記事(インデックスファイル断片化とその対策)をご覧ください。
インデックスをメンテナンスする〜PostgreSQLのきほん〜
インデックスファイルは以下のタイミングで増加し、
- データの挿入時
- データ更新時に既存の再利用可能領域が使用できず、新規ページ追加時
以下のタイミングで減少します。
- DROP INDEX時
- TRUNCATE TABLE時
- REINDEX時
今回のテーマについては以上です。
現場で役立つデータベースの基礎知識という
まとめ記事を作ったので是非ご覧ください。
初めてデータベースを触る方に向けて〜新人プログラマー時代の自分に伝えたいこと〜
参考文献
第6章 物理設計
［改訂3版］内部構造から学ぶPostgreSQL―設計・運用計画の鉄則
