Postgres×ストレージ上の物理配置×実際に見てみた

この記事の目的
本題
- ★行データの実体までのみちのり
- ★Postgresの書き込み処理について
まとめ

この記事の目的

この記事では、
postgresのデータが
どのようにストレージ上で管理されているかを
実際に確認すること
を目的としています。

動作環境として、Docker image postgres:11.3を利用しています。
このイメージでは、以下のパスにDBのデータがまとめられています。
/var/lib/postgresql/dataにpostgres

//参考
 docker run -d -v $(pwd):/var/lib/postgresql/data postgres

本題

★行データの実体までのみちのり

データベース「postgres」に作成した「warning_table」の1行目のデータの実体を見に行くための手順をまとめます。
①データベースのOIDを検索します。

oid2name -U postgres
======
All databases:
    Oid  Database Name  Tablespace
----------------------------------
  13067       postgres  pg_default
  13066      template0  pg_default
      1      template1  pg_default

②次に、dataディレクトリ配下にあるbaseディレクトリに移動し、postgresデータベースのOIDと同じ名前がついているディレクトリを探します。
ここで見つけたディレクトリ自体が、postgresデータベースの実体ファイルがまとめられているディレクトリです。

root@248026c85967:/var/lib/postgresql/data/base# pwd
/var/lib/postgresql/data/base

root@248026c85967:/var/lib/postgresql/data/base# ls -l
total 36
drwx------ 2 postgres postgres 12288 Mar  2 05:12 1
drwx------ 2 postgres postgres 12288 Mar  2 05:12 13066
drwx------ 2 postgres postgres 12288 Mar  2 14:40 13067

③次に、対象のテーブル情報を検索します。

root@248026c85967:/var/lib/postgresql/data/base/13067# oid2name -U postgres -d postgres
From database "postgres":
  Filenode     Table Name
-------------------------
     16384  warning_table
root@248026c85967:/var/lib/postgresql/data/base/13067# ls 16384
16384

この16384ファイルが、postgresデータベースのwarning_tableテーブルの実体です。
④psqlから見ると、warning_tableの中はこのようになっています。

root@248026c85967:/var/lib/postgresql/data/base/13067# psql -U postgres
psql (11.1 (Debian 11.1-3.pgdg90+1))
Type "help" for help.

postgres=# SELECT * FROM warning_table;
 id | name | price
----+------+-------
  1 | test |   100
  1 | test |   100
  1 | test |   100
  1 | test |   100
(4 rows)

⑤これに対し、16384ファイルの中はこのようになっています。

root@248026c85967:/var/lib/postgresql/data/base/13067# ls -l 16384
-rw------- 1 postgres postgres 8192 Mar  3 08:59 16384

root@248026c85967:/var/lib/postgresql/data/base/13067# cat 16384
f    ( `       ؟P P P `P                                                                                              =                    test   d   <                         test   d   ;                     test   d   :    test   d

8kbのファイルの中に、レコードっぽい情報が入っていることが確認できました。

★Postgresの書き込み処理について

テーブルの実体がわかったところで、
次に、どのようにして情報が書き出されているかについて確認していきます。
ここでは、単純にwarning_tableにInsertしたときにどうなるか確認します。

INSERT INTO warning_table VALUES(9999,'AAAAAAAAA',9999);

↓はInsertを実行してからすぐにデータベースのファイルを確認したところです。

root@248026c85967:/var/lib/postgresql/data/base/13067# cat 16384
   f    , 0       ؟P P P `P 0X                                                                                            >                    test   d   <                     test  d   ;                           test   d   :                     test   d

AAAAAAAAAは登録されていないようです( ﾟДﾟ)

これは、追加された情報が、WALファイル（トランザクションログ）と共有バッファメモリに書き込まれているためです。
直接ファイルを更新していないということがわかります。
このうち、共有バッファメモリの確認方法がわからないので、いったん放置して、
WALファイルに正しくデータが登録されているか確認してみます。

WALファイルは、/var/lib/postgresql/dataの【pg_wal】フォルダにあります。
バージョンによっては、pg_xlogフォルダかもしれません。

root@248026c85967# od -a  000000010000000000000001 | tail -n 100

	31557300  bs can nul  si   ' nul nul nak   A   A   A   A   A   A   A   A
	31557320   A nul nul  si   ' nul nul nul nul nul nul   > stx nul nul nul
	31557340 nul nul nul nul nul nul nul nul nul nul nul enq nul etx nul stx
	31557360  bs can nul stx nul nul nul dc3   t   e   s   t   t   e   s   t
	31557400 nul nul nul   g etx nul nul nul nul nul nul   = stx nul nul nul
	31557420 nul nul nul nul nul nul nul nul nul nul nul eot nul etx nul stx
	31557440  ht can nul soh nul nul nul  vt   t   e   s   t nul nul nul   d
	31557460 nul nul nul   < stx nul nul nul nul nul nul nul nul nul nul nul
	31557500 nul nul nul etx nul etx nul stx  ht can nul soh nul nul nul  vt
	31557520   t   e   s   t nul nul nul   d nul nul nul   ; stx nul nul nul
	31557540 nul nul nul nul nul nul nul nul nul nul nul stx nul etx nul stx
	31557560  ht can nul soh nul nul nul  vt   t   e   s   t nul nul nul   d
	31557600 nul nul nul   : stx nul nul nul nul nul nul nul nul nul nul nul
	31557620 nul nul nul soh nul etx nul stx  ht can nul soh nul nul nul  vt
	31557640   t   e   s   t nul nul nul   d nul nul nul ack nul nul nul nul
	31557660   " nul nul nul   ? stx nul nul   H   ^   f soh nul nul nul nul
	31557700 nul soh nul nul dc4   R   .   G del  bs   H  si dc3   N   -   &
	31557720 stx nul nul nul nul nul nul nul nul nul nul nul nul nul nul nul
	31557740 nul nul nul nul nul nul nul nul nul nul nul nul nul nul nul nul

見方が正しいかわかりませんが、AAAから始まる情報が登録されていることが確認できました。（↑の1～2行目あたり）

最後に、共有バッファメモリ、WALログに登録されているこのデータをテーブルファイルにも登録するところまで確認します。
テーブルファイルとの同期処理は、チェックポイント実行時や、共有バッファがいっぱいになり追い出されるタイミングで実行されます。
デフォルトで、チェックポイントは5分ごとに実行されます。

postgres=# SHOW checkpoint_timeout;
 checkpoint_timeout
--------------------
 5min
(1 row)

5分後に確認すると、以下のようにテーブルデータが変更されたことを確認できました。

root@248026c85967:/var/lib/postgresql/data/base/13067# cat 16384
    f    0         ؟P P P `P 0X  X                                                                                        ?                 '  
AAAAAAAAA  '      >                    test   d   <                            test   d   ;                     test   d   :                   test   d

3行目あたりにAAAAAAAAA が確認できます！
無事にデータ登録が完了できました。