はじめに
pg_filedumpはテーブル・インデックスのファイルを人が見ることができるように編集して表示するためのツールです。
最新版ではPostgreSQL11まで対応しているため、PostgreSQL11を使用して使ってみたいと思います。（PostgreSQL12にもインストールしようとしたのですが、コンパイルでエラーになったので諦めました）
※[2020/1/24追記]PostgreSQL12版もリリースされていました。
インストール
まずはpg_filedumpをインストールします。モジュールは以下のページからダウンロードします。
ダウンロード後、以下のようにコンパイルとインストールを実施します。
$ tar xvzf pg_filedump-REL_11_0-02b1aaa.tar.gz
$ make
$ sudo make install
これでpg_filedumpコマンドを利用できるようになります。
pg_filedumpを実行する
pg_filedumpコマンドでpgbenchコマンドで生成されたpgbench_accountsテーブルをdumpしてみます。
まずは物理ファイルを見つけるために、oid2nameコマンドでOIDを確認します。
$ oid2name
All databases:
Oid Database Name Tablespace
----------------------------------
13878 postgres pg_default
13877 template0 pg_default
1 template1 pg_default
16385 testdb pg_default
$ oid2name -d testdb
From database "testdb":
Filenode Table Name
----------------------------
16398 pgbench_accounts
16399 pgbench_branches
16400 pgbench_history
16401 pgbench_tellers
上のように実行したことで、データベース（testdb）は16385、テーブルは16398がOIDであることが分かりました。
OIDを用いてpg_filedumpコマンドを以下のように実行します。
"-R 1 1"は1から1までのブロックを出力するということを意味しています。
$ pg_filedump -R 1 1 /data/base/16385/16398
上のコマンドを実行した結果は以下のようになります。
$ pg_filedump -R 1 1 /data/base/16385/16398
*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 11.0
*
* File: /data/base/16385/16398
* Options used: -R 1 1
*
* Dump created on: Fri Nov 1 12:17:55 2019
*******************************************************************
Block 1 ********************************************************
<Header> -----
Block Offset: 0x00002000 Offsets: Lower 268 (0x010c)
Block: Size 8192 Version 4 Upper 384 (0x0180)
LSN: logid 0 recoff 0x016cfe68 Special 8192 (0x2000)
Items: 61 Free Space: 116
Checksum: 0x0000 Prune XID: 0x00000000 Flags: 0x0004 (ALL_VISIBLE)
Length (including item array): 268
<Data> ------
Item 1 -- Length: 121 Offset: 8064 (0x1f80) Flags: NORMAL
Item 2 -- Length: 121 Offset: 7936 (0x1f00) Flags: NORMAL
Item 3 -- Length: 121 Offset: 7808 (0x1e80) Flags: NORMAL
Item 4 -- Length: 121 Offset: 7680 (0x1e00) Flags: NORMAL
Item 5 -- Length: 121 Offset: 7552 (0x1d80) Flags: NORMAL
Item 6 -- Length: 121 Offset: 7424 (0x1d00) Flags: NORMAL
Item 7 -- Length: 121 Offset: 7296 (0x1c80) Flags: NORMAL
Item 8 -- Length: 121 Offset: 7168 (0x1c00) Flags: NORMAL
Item 9 -- Length: 121 Offset: 7040 (0x1b80) Flags: NORMAL
Item 10 -- Length: 121 Offset: 6912 (0x1b00) Flags: NORMAL
Item 11 -- Length: 121 Offset: 6784 (0x1a80) Flags: NORMAL
Item 12 -- Length: 121 Offset: 6656 (0x1a00) Flags: NORMAL
Item 13 -- Length: 121 Offset: 6528 (0x1980) Flags: NORMAL
Item 14 -- Length: 121 Offset: 6400 (0x1900) Flags: NORMAL
Item 15 -- Length: 121 Offset: 6272 (0x1880) Flags: NORMAL
Item 16 -- Length: 121 Offset: 6144 (0x1800) Flags: NORMAL
Item 17 -- Length: 121 Offset: 6016 (0x1780) Flags: NORMAL
Item 18 -- Length: 121 Offset: 5888 (0x1700) Flags: NORMAL
Item 19 -- Length: 121 Offset: 5760 (0x1680) Flags: NORMAL
Item 20 -- Length: 121 Offset: 5632 (0x1600) Flags: NORMAL
Item 21 -- Length: 121 Offset: 5504 (0x1580) Flags: NORMAL
Item 22 -- Length: 121 Offset: 5376 (0x1500) Flags: NORMAL
Item 23 -- Length: 121 Offset: 5248 (0x1480) Flags: NORMAL
Item 24 -- Length: 121 Offset: 5120 (0x1400) Flags: NORMAL
Item 25 -- Length: 121 Offset: 4992 (0x1380) Flags: NORMAL
Item 26 -- Length: 121 Offset: 4864 (0x1300) Flags: NORMAL
Item 27 -- Length: 121 Offset: 4736 (0x1280) Flags: NORMAL
Item 28 -- Length: 121 Offset: 4608 (0x1200) Flags: NORMAL
Item 29 -- Length: 121 Offset: 4480 (0x1180) Flags: NORMAL
Item 30 -- Length: 121 Offset: 4352 (0x1100) Flags: NORMAL
Item 31 -- Length: 121 Offset: 4224 (0x1080) Flags: NORMAL
Item 32 -- Length: 121 Offset: 4096 (0x1000) Flags: NORMAL
Item 33 -- Length: 121 Offset: 3968 (0x0f80) Flags: NORMAL
Item 34 -- Length: 121 Offset: 3840 (0x0f00) Flags: NORMAL
Item 35 -- Length: 121 Offset: 3712 (0x0e80) Flags: NORMAL
Item 36 -- Length: 121 Offset: 3584 (0x0e00) Flags: NORMAL
Item 37 -- Length: 121 Offset: 3456 (0x0d80) Flags: NORMAL
Item 38 -- Length: 121 Offset: 3328 (0x0d00) Flags: NORMAL
Item 39 -- Length: 121 Offset: 3200 (0x0c80) Flags: NORMAL
Item 40 -- Length: 121 Offset: 3072 (0x0c00) Flags: NORMAL
Item 41 -- Length: 121 Offset: 2944 (0x0b80) Flags: NORMAL
Item 42 -- Length: 121 Offset: 2816 (0x0b00) Flags: NORMAL
Item 43 -- Length: 121 Offset: 2688 (0x0a80) Flags: NORMAL
Item 44 -- Length: 121 Offset: 2560 (0x0a00) Flags: NORMAL
Item 45 -- Length: 121 Offset: 2432 (0x0980) Flags: NORMAL
Item 46 -- Length: 121 Offset: 2304 (0x0900) Flags: NORMAL
Item 47 -- Length: 121 Offset: 2176 (0x0880) Flags: NORMAL
Item 48 -- Length: 121 Offset: 2048 (0x0800) Flags: NORMAL
Item 49 -- Length: 121 Offset: 1920 (0x0780) Flags: NORMAL
Item 50 -- Length: 121 Offset: 1792 (0x0700) Flags: NORMAL
Item 51 -- Length: 121 Offset: 1664 (0x0680) Flags: NORMAL
Item 52 -- Length: 121 Offset: 1536 (0x0600) Flags: NORMAL
Item 53 -- Length: 121 Offset: 1408 (0x0580) Flags: NORMAL
Item 54 -- Length: 121 Offset: 1280 (0x0500) Flags: NORMAL
Item 55 -- Length: 121 Offset: 1152 (0x0480) Flags: NORMAL
Item 56 -- Length: 121 Offset: 1024 (0x0400) Flags: NORMAL
Item 57 -- Length: 121 Offset: 896 (0x0380) Flags: NORMAL
Item 58 -- Length: 121 Offset: 768 (0x0300) Flags: NORMAL
Item 59 -- Length: 121 Offset: 640 (0x0280) Flags: NORMAL
Item 60 -- Length: 121 Offset: 512 (0x0200) Flags: NORMAL
Item 61 -- Length: 121 Offset: 384 (0x0180) Flags: NORMAL
*** End of Requested Range Encountered. Last Block Read: 1 ***
一番最初に表示されるのはページのヘッダーです。
<Header> -----
Block Offset: 0x00002000 Offsets: Lower 268 (0x010c)
Block: Size 8192 Version 4 Upper 384 (0x0180)
LSN: logid 0 recoff 0x016cfe68 Special 8192 (0x2000)
Items: 61 Free Space: 116
Checksum: 0x0000 Prune XID: 0x00000000 Flags: 0x0004 (ALL_VISIBLE)
Length (including item array): 268
- ページ8192bytesの内、Upper:384bytesまでがヘッダのエリアになります。
- Lower:268bytesからUpper:384bytesまでのFree Space:116bytesが空きと思われます。
- Items: 61からページ中のタプルは61ということが分かります。
- 空き領域以外は Length (including item array): 268bytesであり、アイテムIDデータが4bytes固定なので、61 * 4bytes = 244bytesがタプル数によって変動しています。268bytes - 244bytes=24bytesは固定領域（だったはず）
次のデータ部分は以下のように出力されています。
<Data> ------
Item 1 -- Length: 121 Offset: 8064 (0x1f80) Flags: NORMAL
Item 2 -- Length: 121 Offset: 7936 (0x1f00) Flags: NORMAL
Item 3 -- Length: 121 Offset: 7808 (0x1e80) Flags: NORMAL
- "Item 1"が先頭のタプルのようです。ページの後ろからデータが入っていくので、Offset: 8064 -> 7936 -> 7808とオフセットが埋まっているのが分かります。
- また、Length: 121で、1タプルのサイズが121bytesであることが分かります。
次にインデックスをdumpしています。
最初にテーブルの時と同じようにOIDを確認します。
$ oid2name -d testdb -i
From database "testdb":
Filenode Table Name
---------------------------------
16398 pgbench_accounts
16406 pgbench_accounts_pkey
16399 pgbench_branches
16402 pgbench_branches_pkey
16400 pgbench_history
16401 pgbench_tellers
16404 pgbench_tellers_pkey
インデックスpgbench_accounts_pkey(16406)を出力するコマンドは以下のようになります。
$ pg_filedump -R 1 1 /data/base/16385/16406
インデックスをdumpした結果、以下のように出力されました。
*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 11.0
*
* File: /data/base/16385/16406
* Options used: -R 1 1
*
* Dump created on: Fri Nov 1 14:18:24 2019
*******************************************************************
Block 1 ********************************************************
<Header> -----
Block Offset: 0x00002000 Offsets: Lower 1492 (0x05d4)
Block: Size 8192 Version 4 Upper 2304 (0x0900)
LSN: logid 0 recoff 0x02141ed8 Special 8176 (0x1ff0)
Items: 367 Free Space: 812
Checksum: 0x0000 Prune XID: 0x00000000 Flags: 0x0000 ()
Length (including item array): 1492
<Data> ------
Item 1 -- Length: 16 Offset: 2304 (0x0900) Flags: NORMAL
Item 2 -- Length: 16 Offset: 8160 (0x1fe0) Flags: NORMAL
Item 3 -- Length: 16 Offset: 8144 (0x1fd0) Flags: NORMAL
～省略～
Item 365 -- Length: 16 Offset: 2352 (0x0930) Flags: NORMAL
Item 366 -- Length: 16 Offset: 2336 (0x0920) Flags: NORMAL
Item 367 -- Length: 16 Offset: 2320 (0x0910) Flags: NORMAL
<Special Section> -----
BTree Index Section:
Flags: 0x0001 (LEAF)
Blocks: Previous (0) Next (2) Level (0) CycleId (0)
テーブルのFILLFACTORを設定してみる
テーブルのFILLFACTORを90%に設定してページの内容がどのように変わるかを確認してみます。
$ pgbench -i -F 90 testdb
*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 11.0
*
* File: /data/base/16385/25072
* Options used: -R 1 1
*
* Dump created on: Fri Nov 1 23:52:56 2019
*******************************************************************
Block 1 ********************************************************
<Header> -----
Block Offset: 0x00002000 Offsets: Lower 244 (0x00f4)
Block: Size 8192 Version 4 Upper 1152 (0x0480)
LSN: logid 0 recoff 0x06982178 Special 8192 (0x2000)
Items: 55 Free Space: 908
Checksum: 0x0000 Prune XID: 0x00000000 Flags: 0x0004 (ALL_VISIBLE)
Length (including item array): 244
<Data> ------
Item 1 -- Length: 121 Offset: 8064 (0x1f80) Flags: NORMAL
Item 2 -- Length: 121 Offset: 7936 (0x1f00) Flags: NORMAL
Item 3 -- Length: 121 Offset: 7808 (0x1e80) Flags: NORMAL
～省略～
Item 53 -- Length: 121 Offset: 1408 (0x0580) Flags: NORMAL
Item 54 -- Length: 121 Offset: 1280 (0x0500) Flags: NORMAL
Item 55 -- Length: 121 Offset: 1152 (0x0480) Flags: NORMAL
*** End of Requested Range Encountered. Last Block Read: 1 ***
FILFACTOR=100の場合とは、タプルの数（Items）と空き容量で以下のような差が発生します。
FILLFACTOR=100
Items: 61 Free Space: 116
FILLFACTOR=90
Items: 55 Free Space: 908
FILLFACTOR=90の場合、ページ内の空き容量は以下のようになります。
- 8192bytes * ( 1 - 0.9 ) = 819 bytes
実際の空き容量は908 bytesとなっており、10%分の空きが確保されていることが分かります。