こんにちは。サイオステクノロジー 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 を採用する場合は、アライメントを気にしてみると少し幸せになれるかもしれません。
