PostgreSQL データベース設計の勘所

仕事でPostgreSQL 9.6 を使っており、インフラを担当している。

PostgreSQLは「追記型アーキテクチャ」を採用しており、
他のデータベースにはない、PostgreSQLの特徴になっている。

PostgreSQLを利用する場合、「追記型アーキテクチャ」を考慮して設計しないと、
大きな問題に発展する可能性がある。

設計に関わるため、後工程になるほど変更が困難になる。

PostgreSQLは、設計の技量が顕著に表れやすい、と言っても過言ではない。

PostgreSQLのアクセスは、ブロック（ページとも言う。8KB）単位である。

例えば、shared_buffersに「1MB」と設定すると、
「128」「UNIT:8KB」と、pg_settings.shared_buffersに「128」と設定される。
これは、8KB単位だからだ。

shared_buffersの中身は、pg_buffercacheで確認できるが、
INDEXも8KB単位で格納されている。

8KB単位でアクセスするからだ。

データを更新すると、元のデータを残しておき、新たにデータを登録する。

これは、MVCC(Multi Version Concurrency Control)を実現するためである。
MVCCでは、あるトランザクションが参照中の行を、
別のトランザクションが更新できる。

PostgreSQLは、ORACLEで言う ROLLBACK(UNDO)セグメントを持っておらず、
テーブルと同じ領域を使用しているおり、
COMMITかROLLBACKすると、片方のデータが不要領域になる。

不要領域が増えていくとテーブルが肥大化していき、
不要領域の回収が必要となる。

不要領域を回収する仕組みに VACUUMがあるが、負荷が高い。

業務が日中しか動かないのであれば、デフォルトのautovacuumを止め、
夜間バッチでvacuumさせる、
業務が24H動くのであれば、vacuumをいかに減らすか、設計が重要になる。

２．HOTを利用する（VACUUMが不要に。FILLFACTORを設定する）

PostgreSQLには、VACUUM以外に HOT(Heap Only Tuple)という機能がある。

以下の条件を満たすと、SELECT,UPDATE 等の処理の中で、
不要領域が自動的に回収される。（VACUUMが不要になる）
※該当ページへアクセスした際に、不要領域を回収する。

・ページの空き領域を確保（FILLFACTORを設定する）
・インデックスキーの更新がない

不要領域を回収するのは、ページへのスキャンが発生した際、
以下の条件に合致した時。
・ページ内の空き領域が、FILLFACTORの設定値か、ブロックサイズの
　1/10以下になった場合
・直近の更新処理がページ内に空き領域を見つけられなかった場合

３．FILLFACTOR

ORACLEで言うPCTFREEで、予備領域の事。
これを設定しないと、HOTが効かない。

UPDATEの割合によって設定するため、処理を把握しておかないと設計できない。

トランザクションかマスターでも、変わってくる。
UPDATEが少なければ90位を設定する。

処理を動作させ、以下で確認できる。

pg_stat_user_tablesビューの
・n_tup_upd：更新された行
・n_tup_hot_upd：HOT更新された行

予備領域のデフォルト値は以下。
　　　　　PostgreSQL　ORACLE
テーブル　　　　　0%　　 10%
INDEX 　　　　　 10%　　 10%

HOT更新された行の割合が100%だったら、予備領域が多過ぎ、
INSERT時、空きを多く作り過ぎていることになる。

HOT更新された行が0件だったら、FILLFACTORの設定を100に戻す。

全件、UPDATEを1回するのであれば、66位に設定すれば、
HOT更新された行の割合が50%位になると思う。

以下の２点を確認することが重要！

・pg_stat_user_tables.n_tup_hot_updの割合
・pg_stat_user_tablesで、autovacuum_countが0にも関わらず、
　n_dead_tup（不要行）が減った

※pg_stat_user_tablesの数値は、殆どが累計値（一部、最新値）

着眼点の構想