各種ファイルのレイアウトとアクセス

PostgreSQLのテーブルファイルの実態

• 公式/データレイアウト

• ページ

  • 8192バイトの固定長領域

  • 1GBまで連続して配置される

    • 8192 * 131072ページ

  -rw------- 1 postgres postgres   8192 Feb  6 14:15 112
  -rw------- 1 postgres postgres   8192 Feb  6 14:15 113
  -rw------- 1 postgres postgres  81920 Feb  6 14:15 1247
  -rw------- 1 postgres postgres  24576 Feb  6 14:15 1247_fsm
  -rw------- 1 postgres postgres   8192 Feb  6 14:15 1247_vm
...

• 1GBを超えると分割され、ノード番号.1,ノード番号.2,…と管理される
• PostgreSQLのビルド時のconfigureオプションで変更可能
• ページサイズ: --with-blocksize(kB単位)

#
# Block size
#
AC_MSG_CHECKING([for block size])
PGAC_ARG_REQ(with, blocksize, [BLOCKSIZE], [set table block size in kB [8]],
             [blocksize=$withval],
             [blocksize=8])
case ${blocksize} in
  1) BLCKSZ=1024;;
  2) BLCKSZ=2048;;
  4) BLCKSZ=4096;;
  8) BLCKSZ=8192;;
 16) BLCKSZ=16384;;
 32) BLCKSZ=32768;;
  *) AC_MSG_ERROR([Invalid block size. Allowed values are 1,2,4,8,16,32.])
esac
AC_MSG_RESULT([${blocksize}kB])

• --with-blocksize=4でビルドした場合

  • ページが4096単位

postgresql/data/base/1$ ls -lA

ls -lA
total 6072
-rw------- 1 postgres postgres   4096 Feb  6 14:41 112
-rw------- 1 postgres postgres   4096 Feb  6 14:41 113
-rw------- 1 postgres postgres  77824 Feb  6 14:41 1247
-rw------- 1 postgres postgres  12288 Feb  6 14:41 1247_fsm
-rw------- 1 postgres postgres   4096 Feb  6 14:41 1247_vm
-rw------- 1 postgres postgres 450560 Feb  6 14:41 1249
-rw------- 1 postgres postgres  12288 Feb  6 14:41 1249_fsm
-rw------- 1 postgres postgres   4096 Feb  6 14:41 1249_vm
...

• セグメントサイズ: --with-segsize(GB単位)

#
# Relation segment size
#
AC_MSG_CHECKING([for segment size])
PGAC_ARG_REQ(with, segsize, [SEGSIZE], [set table segment size in GB [1]],
             [segsize=$withval],
             [segsize=1])
# this expression is set up to avoid unnecessary integer overflow
# blocksize is already guaranteed to be a factor of 1024
RELSEG_SIZE=`expr '(' 1024 / ${blocksize} ')' '*' ${segsize} '*' 1024`
test $? -eq 0 || exit 1
AC_MSG_RESULT([${segsize}GB])

• relation(テーブルやインデックス)は最大32TBまで

  • 公式
  • パーティショニングで限界突破できる

テーブルファイル

• 公式

• ページファイルのレイアウト

  • PageHeaderData
  • ItemId
  • Free Space
  • Item
  • Special

PageHeaderData

• ページ先頭24バイト

• このページに対する更新のWALについての情報

  • LSN: Log Sequence Number

    • このページに対して行われた最後の更新に関する、WALレコード最終バイトの次のバイト位置

      • 【所感】「要するに、次はここからWALを書き出すよ」ってことかな

• ページ内の管理情報

  • Free Spaceの位置(start,end)
  • Special領域の位置

ItemId

• Itemすなわちtupleを指すやつ。32ビット = 4バイト

  • tuple開始オフセット (15ビット)
  • tuple状態 (2ビット)
  • tuple長 (15ビット)

Free Space

• ItemIdの後ろ、およびItemの後ろの使用されていない領域

• 1 - (Free Space領域の比率)が充填率

  • データ挿入時に充填率がテーブルのFILLFACTORを超える場合、充填せずに新しいページを払い出す

    • 更新用にFree Spaceをとっておく(HOT更新用のチューニング)

Item

• タプル実体

• ページと同じような構造

  • 先頭に固定長ヘッダがある(23バイト)
  • nullビットマップ(オプショナル)
  • oid(オプショナル)
  • ユーザーデータ

HeapTupleHeaderData

• タプルのヘッダ(固定長23バイト)

• xmin: XID周回問題の解消のために、VACUUMでFrozenTransactionId (2)を入れるやつ

  • 新しいバージョンでは入れずにフラグを立てるだけ

Special

• インデックス特有の情報を格納

  • テーブルデータのページファイルでは使用されない
• 例: B-Tree構造の両隣ページへのリンクとか

ページ数見積もり

• 行の想定平均サイズ(TS)

  • ItemIdや、varchar等の可変長列のヘッダ含む
• 想定レコード数(RN)
• FILLFACTOR(FF)
• テーブルサイズ概算(バイト)

(RN * TS)

• 1ページあたりのデータサイズ(バイト/page)

  • PageHeaderDataの24バイトを除いている

((8192 * FF) - 24)

• ページ数概算

(RN * TS) / ((8192 * FF) - 24)

増加するタイミング

• データ挿入
• データ更新時に新規ページが払い出された

減少するタイミング

• まるごと消す系

  • DROP TABLE
  • TRUNCATE TABLE
• CLUSTERによる、インデックス順でのタプル再構成
• VACUUM FULL

インデックスファイル

• テーブルファイルとほぼ同じ構成

• Special領域がある

  • B-Treeインデックスの場合、4種類のページで木構造を構成する

    • meta page
    • root page
    • internal page
    • leaf page
  • leaf pageは、Special領域で左右の兄弟leafへのリンクを保持している

増加するタイミング

• データ挿入
• データ更新時に新規ページが払い出された

減少するタイミング

• まるごと消す系

  • DROP INDEX
  • TRUNCATE TABLE
• REINDEXによる再構成

テーブルファイルに対するアクセス

シーケンシャルアクセス

• テーブルファイルのページを順々に全部見るやつ
• O(n)

インデックスアクセス

• インデックスのリーフに設定されたテーブルファイルへのポインタからテーブルファイルの特定のページを取得する
• 検索がO(n)ではスケールしなくなる
• 代わりにランダムアクセス頻発

WALファイルとアーカイブファイル

• 公式
• Reliability (信頼性)

• コミットされたトランザクションが保持されること

  • 停電時
  • OS障害
  • H/W障害

WALファイル

postgres@7edc466e9109:~/data$ ls -lA pg_wal

total 16388
-rw------- 1 postgres postgres 16777216 Feb  6 15:14 000000010000000000000001
drwx------ 2 postgres postgres     4096 Feb  6 14:15 archive_status

の、これ

-rw------- 1 postgres postgres 16777216 Feb  6 15:14 000000010000000000000001

• 16MB

アーカイブファイル

• 公式/PITR
• アーカイブモードを有効化してあると、WALファイルのコピーとして生成されるやつ

postgres=# SELECT name,setting,context FROM pg_settings WHERE name LIKE '%archive%';
           name            |  setting   |  context   
---------------------------+------------+------------
 archive_cleanup_command   |            | sighup
 archive_command           | (disabled) | sighup
 archive_mode              | off        | postmaster
 archive_timeout           | 0          | sighup
 max_standby_archive_delay | 30000      | sighup
(5 rows)

• アーカイブの有効/無効

# - Archiving -

#archive_mode = off		# enables archiving; off, on, or always
				# (change requires restart)

• onまたはalwaysだと、archive_commandのコマンドでバックアップが実施される
• 古いものが溜まっていく
• 最新のベースバックアップ以降にしかロールフォワードしないならば、古いアーカイブファイルは不要
• 適宜削除しよう

HOTとFILLFACTOR

HOT

• 公式

• HOT: Heap-Only Tuple

  • 冗長なインデックスを排除

  • テーブル単位のVACUUMを待つことなく不要領域を再利用可能にする

    • ページ単位でVACUUM

• Heap-Only: テーブル領域だけ

  • インデックスエントリの追加処理をスキップする

    • 線形リストみたいな感じ
• ながれ

For example:

	Index points to 1
	lp [1]	[2]

	[111111111]->[2222222222]

• インデックスはlp[1]を指す

  • インデックス->lp[1]->[11111111]と検索

• tuple2 [22222222]はHOT tuple

  • lp[2]を指すインデックスは無い

• が、tuple2 [22222222]をインデックスで検索できる

  • tuple1がHEAP_HOT_UPDATEDでマークされている
  • HEAP_HOT_UPDATEDでマークされている限り、子孫tupleをtraverseする
  • 上図ではインデックス->lp[1]->tuple1->tuple2という流れでtuple2を得る

	Index points to 1
	lp [1]->[2]

	[2222222222]

• tuple1がどのトランザクションからも見えなくなったら

• tuple1 [1111111]領域を回収する

  • テーブル全体のVACUUMを待つことなく回収

• インデックスサーチでtuple2を取得できないといけないので、lp[1]をlp[2]へリダイレクトする

  • ItemIdデータのlp_flagsで管理(2)
  • インデックス->lp[1]->lp[2]->tuple2という流れでtuple2を得る

	Index points to 1
	lp [1]->[2]	 [3]

	[2222222222]->[3333333333]

• 新たにHOT tuple3 [33333333]が追加される

  • インデックス->lp[1]->lp[2]->tuple2->tuple3 と到達する

	Index points to 1
	lp [1]------>[3]

	[3333333333]

• tuple2がどのトランザクションからも不可視になったら

  • tuple2の領域を回収

  • lp1をlp3に向ける

    • インデックス->lp[1]->lp[3]->tuple3と到達する

• 条件

  • インデックスを持たない列への更新

    • インデックスを持つ列を更新すると、普通にインデックスエントリが追加される

  • 更新対象行と同じページ内に空きがある

    • ないと普通に新しいページを払い出す
    • ページ内に空きを設けるためのパラメータとして、FILLFACTORがある

• HOT更新が働かないケース

  • インデックス列をUPDATEで更新する

  • DELETE/INSERTで更新

    • TOAST対象列の更新は内部的にこれ
    • インデックスエントリの追加処理はスキップされない

  • 一度に大量の行を更新

    • 1ページに収まらない

FILLFACTOR

• ページ内の空き領域をデータ挿入用に利用する割合
• 小さくすると、データ挿入時に使用する領域を減らし、更新用に確保する
• CREATE TABLE, ALTER TABLEで指定できる

postgres=# CREATE TABLE tbl2 (col int) WITH (fillfactor=70);
CREATE TABLE

postgres=# \d+ tbl2
                                   Table "public.tbl2"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 col    | integer |           |          |         | plain   |              | 
Access method: heap
Options: fillfactor=70

• \dでは確認できない

  • インデックスのものは\d+でも確認できない
• pg_classシステムビューでも確認できる

postgres=# SELECT relname,reloptions FROM pg_class WHERE relname='tbl2';
 relname |   reloptions    
---------+-----------------
 tbl2    | {fillfactor=70}
(1 row)

• デフォルト値

  • テーブル: 100%
  • インデックス: 90%

• 値の決定指針

  • テーブルに対してINSERT/DELETEしかしないなら100%のままでいい

  • UPDATEする場合、ページあたり平均的なレコードの2倍程度の空き領域を確保する

    • 新しいページの払い出しが起こる可能性が減少する
    • そのページへ同時に更新が発生しない限り、空き領域が交互に使用される可能性が高い