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

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

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

■はじめに

正直な所私自身がまだ良く理解できていないのですが、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)

■最後に

かなり細かい内容ですが、設計時にカラムの順序を調整することでテーブルサイズを削減することができる可能性もあるようです。大規模なデータを扱う環境で PostgreSQL を採用する場合は、アライメントを気にしてみると少し幸せになれるかもしれません。

ご覧いただきありがとうございます! この投稿は役に立ちましたか? 役に立った 役に立たなかった

Be the first to comment

コメント投稿

Your email address will not be published.


*