PostgreSQL のアライメントについて検証してみた

◆ Live配信スケジュール ◆
サイオステクノロジーでは、Microsoft MVPの武井による「わかりみの深いシリーズ」など、定期的なLive配信を行っています。
⇒ 詳細スケジュールはこちらから
⇒ 見逃してしまった方はYoutubeチャンネルをご覧ください

【5/21開催】Azure OpenAI ServiceによるRAG実装ガイドを公開しました
生成AIを活用したユースケースで最も一番熱いと言われているRAGの実装ガイドを公開しました。そのガイドの紹介をおこなうイベントです!!
https://tech-lab.connpass.com/event/315703/

こんにちは。サイオステクノロジー OSS サポート担当 Y です。

今回は PostgreSQL のアライメントについて検証してみました。(※以下の内容は PostgreSQL 10.1 及び CentOS 7.4 にて検証/調査しています。)

1 ■はじめに
2 ■検証
3 ■最後に

■はじめに

正直な所私自身がまだ良く理解できていないのですが、PostgreSQL のテーブルに格納されるレコードのデータは “アライメント” と呼ばれる境界を跨がないように格納されており、この境界を跨がないようにするために、余った領域はパディングされる作りになっているらしいです。

また、このアライメントを意識してテーブル設計を行うと、パディングの量を減らすことでテーブルの容量を節約できる可能性があるらしいです。

上記のようなお話をとあるセミナーで聞かせて頂いたので、実際に自分でも検証してみました。

■検証

まず、アライメントの値はプラットフォームに依存しているらしいので、以下のように pg_controldata コマンドにて当該環境のアライメントの値を確認します。

[postgres@localhost ~]$ pg_controldata | grep alignment
Maximum data alignment:               8

上記 pg_controldata コマンドの出力によると、今回検証した環境の場合アライメントの値は “8” になっているようなので、データの格納領域のイメージとしては以下ような 8 byte 区切りの領域になります。

+--------+--------+--------+--------+--------
|        |        |        |        |        
+--------+--------+--------+--------+-------- ...
  8 byte   8 byte   8 byte   8 byte   8 byte   ...

例えば int 型 (4 byte) のデータを格納した場合は以下のようなイメージになります。

+--------+--------+--------+--------+--------
|XXXXPPPP|        |        |        |        
+--------+--------+--------+--------+-------- ...
  8 byte   8 byte   8 byte   8 byte   8 byte   ...

凡例)
XXXX -> int 型のデータ
PPPP -> パディングされた領域

また、int 型 (4 byte) のデータを 2つ格納した場合は以下のようなイメージになります。

+--------+--------+--------+--------+--------
|XXXXYYYY|        |        |        |        
+--------+--------+--------+--------+-------- ...
  8 byte   8 byte   8 byte   8 byte   8 byte   ...
凡例)
XXXX -> int 型のデータ
YYYY -> int 型のデータ

上記イメージだけだとわかり辛いので、実際に検証を行いテーブルのサイズ等を確認してみます。

検証用に int 型 (4 byte) 2つ, bigint 型 (8 byte) 2つの計 4つのカラムを持つテーブルを 2つ用意します。

postgres=# CREATE TABLE hoge (a int, b int, c bigint, d bigint);
CREATE TABLE
postgres=# 
postgres=# CREATE TABLE fuga (a int, c bigint, b int, d bigint);
CREATE TABLE
postgres=#

この 2つのテーブルの違いは “カラムの順序” です。

テーブル “hoge” は int, int, bigint, bigint の順でカラムを定義しています。

また、テーブル “fuga” は int, bigint, int, bigint の順でカラムを定義しています。

postgres=# \d hoge
                Table "public.hoge"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 
 c      | bigint  |           |          | 
 d      | bigint  |           |          | 

postgres=# 
postgres=# \d fuga
                Table "public.fuga"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 c      | bigint  |           |          | 
 b      | integer |           |          | 
 d      | bigint  |           |          |

これらのテーブルにレコードが格納された際のイメージは以下のようになり、”hoge” と “fuga” で 1レコードで利用される領域の大きさが異なる状況になります。

[hoge のイメージ]
+--------+--------+--------+
|AAAABBBB|CCCCCCCC|DDDDDDDD|
+--------+--------+--------+
  8 byte   8 byte   8 byte

凡例)
AAAA     -> int 型 "a" のデータ
BBBB     -> int 型 "b" のデータ
CCCCCCCC -> bigint 型 "c" のデータ
DDDDDDDD -> bigint 型 "d" のデータ

[fuga のイメージ]
+--------+--------+--------+--------+
|AAAAPPPP|CCCCCCCC|BBBBPPPP|DDDDDDDD|
+--------+--------+--------+--------+
  8 byte   8 byte   8 byte   8 byte

凡例)
AAAA     -> int 型 "a" のデータ
BBBB     -> int 型 "b" のデータ
CCCCCCCC -> bigint 型 "c" のデータ
DDDDDDDD -> bigint 型 "d" のデータ
PPPP     -> パディングされた領域

実際に内容が全く同じレコードを 100万レコードずつ INSERT し、各テーブルのサイズを確認してみると、”fuga” の方がテーブルのサイズが大きくなる結果となりました。

postgres=# INSERT INTO hoge SELECT 0, 0, 0, 0 FROM generate_series(1,1000000);
INSERT 0 1000000
postgres=# 
postgres=# INSERT INTO fuga SELECT 0, 0, 0, 0 FROM generate_series(1,1000000);
INSERT 0 1000000
postgres=# 
postgres=# SELECT pg_relation_size('hoge');
 pg_relation_size 
------------------
         52183040
(1 row)

postgres=# 
postgres=# SELECT pg_relation_size('fuga');
 pg_relation_size 
------------------
         60235776
(1 row)

次に、以下のようにカラム数が違うテーブルでの検証も行なってみました。

postgres=# CREATE TABLE foo (a int, b int, c bigint);
CREATE TABLE
postgres=# 
postgres=# CREATE TABLE bar (a int, c bigint);
CREATE TABLE
postgres=# 
postgres=# \d foo
                Table "public.foo"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 
 c      | bigint  |           |          | 

postgres=# 
postgres=# \d bar
                Table "public.bar"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 c      | bigint  |           |

これらのテーブルにレコードが格納された際のイメージは以下のようになり、”foo” と “bar” で 1レコードで利用される領域の大きさは同じになっています。

[foo のイメージ]
+--------+--------+
|AAAABBBB|CCCCCCCC|
+--------+--------+
  8 byte   8 byte

凡例)
AAAA     -> int 型 "a" のデータ
BBBB     -> int 型 "b" のデータ
CCCCCCCC -> bigint 型 "c" のデータ

[bar のイメージ]
+--------+--------+
|AAAAPPPP|CCCCCCCC|
+--------+--------+
  8 byte   8 byte

凡例)
AAAA     -> int 型 "a" のデータ
CCCCCCCC -> bigint 型 "c" のデータ
PPPP     -> パディングされた領域

実際に内容が全く同じレコードを 100万レコードずつ INSERT し、各テーブルのサイズを確認してみると、カラム数が異なるテーブルであるにもかかわらず 2つのテーブルのサイズは同じになっていました。

postgres=# INSERT INTO foo SELECT 0, 0, 0 FROM generate_series(1,1000000);
INSERT 0 1000000
postgres=# 
postgres=# INSERT INTO bar SELECT 0, 0 FROM generate_series(1,1000000);
INSERT 0 1000000
postgres=# 
postgres=# SELECT pg_relation_size('foo');
 pg_relation_size 
------------------
         44285952
(1 row)

postgres=# 
postgres=# SELECT pg_relation_size('bar');
 pg_relation_size 
------------------
         44285952
(1 row)

上記の通り、同じカラムを定義していても順序によってテーブルのサイズが変わる場合や、異なるカラムを定義していても順序によってテーブルのサイズが同じになることがあるようです。

また、各データ型のアライメントについては、システムカタログ pg_type の typalign 列で確認できるようです。

(https://www.postgresql.org/docs/10/static/catalog-pg-type.html)