PostgreSQL のテーブルとテーブルファイル (ページ) の関係と、テーブルファイル (ページ) の構成は下図のようになっています。実際のページの中身を確認しながら、構成を読み解いていきます。
ページの中身を確認するには、pageinspectモジュールのget_raw_page関数を使用します。
数値型と文字型の 2つの列を持つ適当なテーブルを作成し、データを 1件 INSERT (123, foo) してページの中身を確認します。
postgres=#
postgres=# create table testtab (col1 integer, col2 varchar(10));
CREATE TABLE
postgres=# insert into testtab values (123, 'foo');
INSERT 0 1
postgres=# select * from get_raw_page('public.testtab',0);
get_raw_page
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
\x0000000028016201000000001c00e01f0020042000000000e09f40000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
(snip)
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000fb02000000000000000000000000000001000200020818007b00000009666f6f
(1 row)
postgres=#
postgres=#
先述の構成要素に対応する箇所を抽出すると下表の通りです。
| 構成要素 | 対応箇所 |
|---|---|
| PageHeaderData (24B) | 0000000028016201000000001c00e01f0020042000000000 |
| ItemId (4B) | e09f4000 |
| Item | fb02000000000000000000000000000001000200020818007b00000009666f6f |
PageHeaderData の構成情報、対応する箇所、値は下表の通りです。
| 情報 | 対応箇所 | 値 (意味 or 計算式) |
|---|---|---|
| ログシーケンス番号 (8B) | 0000000028016201 | 1620128 |
| チェックサム (2B) | 0000 | 0 (なし) |
| フラグ (2B) | 0000 | 0 (なし) |
| 空き領域の開始位置 (2B) | 1c00 | 28 ($1*16^1+12*16^0$) |
| 空き領域の終了位置 (2B) | e01f | 8160 ($1*16^3+15*16^2+14*16^1$) |
| 特殊領域の開始位置 (2B) | 0020 | 8192 ($2*16^3$) |
| ページサイズとレイアウトバージョン (2B) | 0420 | 8192+4 ($2*16^3+4*16^0$) |
| 最小XID (4B) | 00000000 | 0 (なし) |
Item の構成情報、対応する箇所、値は下表の通りです。
| 情報 | 対応箇所 | 値 |
|---|---|---|
| 挿入XID (4B) | fb020000 | 763 ($2*16^2+15*16^1+11*16^0$) |
| 削除XID (4B) | 00000000 | 0 (なし) |
| コマンドID (4B) | 00000000 | 0 (なし) |
| 最新の場所 (6B) | 000000000100 | block = 0, offset = 1 |
| 列数と追加フラグ (2B) | 0200 | 2 |
| 主要フラグ (2B) | 0208 | 割愛 |
| 行ヘッダ長 (1B) | 18 | 24 ($1*16^1+8*16^0$) |
| パディング (1B) | 00 | － |
| 列情報1 | 7b000000 | 123 ($7*16^1+11*16^0$) |
| 列情報2 | 09666f6f | foo (09は可変長を示す) |
データを 2件 INSERT (456, zoo / 789, bar) してページの中身を再確認します。
postgres=# insert into testtab values (456, 'zoo');
INSERT 0 1
postgres=# select * from get_raw_page('public.testtab',0);
get_raw_page
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
\x00000000c8016201000000002000c01f0020042000000000e09f4000c09f400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
(snip)
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
00000fc0200000000000000000000000000000200020002081800c8010000097a6f6ffb02000000000000000000000000000001000200020818007b00000009666f6f
(1 row)
postgres=#
postgres=# insert into testtab values (789, 'bar');
INSERT 0 1
postgres=# select * from get_raw_page('public.testtab',0);
get_raw_page
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
\x0000000068026201000000002400a01f0020042000000000e09f4000c09f4000a09f4000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
(snip)
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000fd020000000000000000000000000000030002000208180015030000096
26172fc0200000000000000000000000000000200020002081800c8010000097a6f6ffb02000000000000000000000000000001000200020818007b00000009666f6f
(1 row)
postgres=#
PageHeaderData を見ると、ログシーケンス番号以外では空き領域の開始・終了位置が更新されています。
これは、データを INSERT したことで空き領域に ItemId, Item が割り当てられたためです。
| 情報 | 1件目の INSERT 直後 | 2件目 | 3件目 |
|---|---|---|---|
| ログシーケンス番号 | 0000000028016201 | 00000000c8016201 | 0000000068026201 |
| チェックサム | 0000 | 0000 | 0000 |
| フラグ | 0000 | 0000 | 0000 |
| 空き領域の開始位置 | 1c00 (28) | 2000 (32) ★ | 2400 (36) ★ |
| 空き領域の終了位置 | e01f (8160) | c01f (8128) ★ | a01f (8096) ★ |
| 特殊領域の開始位置 | 0020 | 0020 | 0020 |
| ページサイズとレイアウトバージョン | 0420 | 0420 | 0420 |
| 最小XID | 00000000 | 00000000 | 00000000 |
Item を見ると、INSERT したデータに対応する新規Item が確認できます。
| 情報 | 既存Item | 新規Item1 | 新規Item2 |
|---|---|---|---|
| 挿入XID | fb020000 | fc020000 | fd020000 |
| 削除XID | 00000000 | 00000000 | 00000000 |
| コマンドID | 00000000 | 00000000 | 00000000 |
| 最新の場所 | 000000000100 | 000000000200 | 000000000300 |
| 列数と追加フラグ | 0200 | 0200 | 0200 |
| 主要フラグ | 0208 | 0208 | 0208 |
| 行ヘッダ長 | 18 | 18 | 18 |
| パディング | 00 | 00 | 00 |
| 列情報1 | 7b000000 | c8010000 (456) ★ | 15030000 (789) ★ |
| 列情報2 | 09666f6f | 097a6f6f (zoo) ★ | 09626172 (bar) ★ |
col1 が 123 の col2 を foo から baz に UPDATE してページの中身を再確認します。
postgres=# update testtab set col2='baz' where col1=123;
UPDATE 1
postgres=# select * from get_raw_page('public.testtab',0);
get_raw_page
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
\x00000000c0046201000000002800801f00200420fe020000e09f4000c09f4000a09f4000809f40000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
(snip)
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000fe02000000000000000000000000000004000280022818007b0000000962617afd020000000000000000000000000000030002000209180015030000096
26172fc0200000000000000000000000000000200020002091800c8010000097a6f6ffb020000fe020000000000000000000004000240020118007b00000009666f6f
(1 row)
postgres=#
PageHeaderData を見ると、最小トランザクションIDが更新されていました。
また、追記型アーキテクチャのため空き領域が割り当てられ、空き領域の開始・終了位置も更新されていることがわかります。
| 情報 | 1件目の INSERT 直後 | 2件目 | 3件目 | UPDATE 直後 |
|---|---|---|---|---|
| ログシーケンス番号 | 0000000028016201 | 00000000c8016201 | 0000000068026201 | 00000000c0046201 |
| チェックサム | 0000 | 0000 | 0000 | 0000 |
| フラグ | 0000 | 0000 | 0000 | 0000 |
| 空き領域の開始位置 | 1c00 (28) | 2000 (32) | 2400 (36) | 2800 (40) ★ |
| 空き領域の終了位置 | e01f (8160) | c01f (8128) | a01f (8096) | 801f (8064) ★ |
| 特殊領域の開始位置 | 0020 | 0020 | 0020 | 0020 |
| ページサイズとレイアウトバージョン | 0420 | 0420 | 0420 | 0420 |
| 最小XID | 00000000 | 00000000 | 00000000 | fe020000 ★ |
Item を見ると、baz は新規Item として割り当てられ、その挿入トランザクションID が既存Item の削除トランザクションID になっていました。
| 情報 | 既存Item (UPDATE前) | 既存Item (UPDATE後) | 新規Item |
|---|---|---|---|
| 挿入XID | fb020000 | fb020000 | fe020000 ★ |
| 削除XID | 00000000 | fe020000 ★ | 00000000 |
| コマンドID | 00000000 | 00000000 | 00000000 |
| 最新の場所 | 000000000100 | 000000000400 | 000000000400 |
| 列数と追加フラグ | 0200 | 0240 | 0280 |
| 主要フラグ | 0208 | 0201 | 0228 |
| 行ヘッダ長 | 18 | 18 | 18 |
| パディング | 00 | 00 | 00 |
| 列情報1 | 7b000000 | 7b000000 | 7b000000 |
| 列情報2 | 09666f6f | 09666f6f | 0962617a (baz) |
なお、pageinspectモジュールのpage_header関数やheap_page_items関数を使用すれば、PageHeaderData や Item の構成情報と値を直接確認できます。
------1回目の INSERT直後
postgres=# select * from page_header(get_raw_page('public.testtab',0));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid
-----------+----------+-------+-------+-------+---------+----------+---------+-----------
0/1620128 | 0 | 0 | 28 | 8160 | 8192 | 8192 | 4 | 0
(1 row)
postgres=# select * from heap_page_items(get_raw_page('public.testtab',0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------
1 | 8160 | 1 | 32 | 763 | 0 | 0 | (0,1) | 2 | 2050 | 24 | | | \x7b00000009666f6f
(1 row)
------2回目の INSERT直後
postgres=# select * from page_header(get_raw_page('public.testtab',0));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid
-----------+----------+-------+-------+-------+---------+----------+---------+-----------
0/16201C8 | 0 | 0 | 32 | 8128 | 8192 | 8192 | 4 | 0
(1 row)
postgres=# select * from heap_page_items(get_raw_page('public.testtab',0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------
1 | 8160 | 1 | 32 | 763 | 0 | 0 | (0,1) | 2 | 2050 | 24 | | | \x7b00000009666f6f
2 | 8128 | 1 | 32 | 764 | 0 | 0 | (0,2) | 2 | 2050 | 24 | | | \xc8010000097a6f6f
(2 rows)
------3回目の INSERT直後
postgres=# select * from page_header(get_raw_page('public.testtab',0));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid
-----------+----------+-------+-------+-------+---------+----------+---------+-----------
0/1620268 | 0 | 0 | 36 | 8096 | 8192 | 8192 | 4 | 0
(1 row)
postgres=# select * from heap_page_items(get_raw_page('public.testtab',0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------
1 | 8160 | 1 | 32 | 763 | 0 | 0 | (0,1) | 2 | 2050 | 24 | | | \x7b00000009666f6f
2 | 8128 | 1 | 32 | 764 | 0 | 0 | (0,2) | 2 | 2050 | 24 | | | \xc8010000097a6f6f
3 | 8096 | 1 | 32 | 765 | 0 | 0 | (0,3) | 2 | 2050 | 24 | | | \x1503000009626172
(3 rows)
------UPDATE直後
postgres=# select * from page_header(get_raw_page('public.testtab',0));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid
-----------+----------+-------+-------+-------+---------+----------+---------+-----------
0/16204C0 | 0 | 0 | 40 | 8064 | 8192 | 8192 | 4 | 766
(1 row)
postgres=# select * from heap_page_items(get_raw_page('public.testtab',0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------
1 | 8160 | 1 | 32 | 763 | 766 | 0 | (0,4) | 16386 | 258 | 24 | | | \x7b00000009666f6f
2 | 8128 | 1 | 32 | 764 | 0 | 0 | (0,2) | 2 | 2306 | 24 | | | \xc8010000097a6f6f
3 | 8096 | 1 | 32 | 765 | 0 | 0 | (0,3) | 2 | 2306 | 24 | | | \x1503000009626172
4 | 8064 | 1 | 32 | 766 | 0 | 0 | (0,4) | 32770 | 10242 | 24 | | | \x7b0000000962617a
(4 rows)
ページは PostgreSQL の I/O の最小単位ですので、理解することで実行計画や SQLチューニングにも応用できます。
DMLなどの実行による変化の発見も面白いので、是非一度覗いてみてはいかがでしょうか 