Postgres Advent Calendar 2017の8日目を担当させていただきます。
Postgres 10とかナウい話やテクい話は他のみなさまにお任せして、地味なネタ狙いで物理構造を意識したチューニングについて書こうと思っていた。最近、お仕事で触れたものの中からこれを選んだ。タイトルがキャッチーでかっこいいから。モテたいから。本当に。
TL;DR
- Postgresのテーブルのカラムは、
CREATE TABLEに書いた順がそのまま物理配置に反映される - その並べ方によって格納効率が変わるので、一考の余地がある
Postgresのデータ構造
本題の前にPostgresにおいてデータがどのように格納されているか、単位の大きいものから順に「データベース」「テーブル」「ブロック」「ヒープタプル」について説明する。細かい話や正確性は捨てて、なんとなく雰囲気がわかるように。
データベースの構造
Postgresのデータは、 PGDATA という環境変数で参照されるディレクトリに格納される*1。PGDATAディレクトリにはサブディレクトリ*2と制御ファイルがあり、サブディレクトリの中に、テーブル毎に1つのファイルを確保する。
ちなみにこの構造は、その他のRDBMSとはだいぶ異なる。OracleやMySQLでポピュラーなInnoDBストレージエンジンは、データ領域を巨大なファイルとして確保し、その中身を分割して個々のテーブルなどを管理する。
テーブルの構造
テーブル（の実体となるファイル）は、ブロックという単位で分割される。各ブロックには、実際のデータとそのブロックのメタ情報を持つヘッダが保存される。ブロックのサイズはデフォルトで8KBとなっている。変更したければ、ビルド時にconfigureのオプションで指定してあげればよい*3。SHOW block_size;とすれば確認もできる。自分の環境では8KB。
./configure --prefix=/usr/local/pgsql --with-blocksize=32
単一のファイルで保持することが非効率なケースを想定して、ファイル外にデータを持たせる仕組みもある。ひとつはセグメント分割で、これはテーブルが1GBを超える*4とセグメントという単位に分割される。もうひとつはTOASTで、これはデータ量が大きくなりそうなカラム*5はTOASTテーブルという別のテーブルに格納する。
ブロックの構造
ブロックに含まれる実際のデータは「ヒープタプル」と呼ばれる。各ヒープタプルが、それぞれレコードに対応することになる。8KBの中にヒープタプルを詰め込めるだけ詰め込んで、もう入らなくなったら次のブロック領域を確保してそこに詰め込む、というかんじでデータを格納していく。
ヒープタプルの構造
各ヒープタプルには、実際の属性データ（＝レコード）と、そのヒープタプルのメタ情報を持つヘッダが含まれる。ヘッダのあとに各属性データが順番に格納される。ここで重要なのが、Postgresにおいてメモリの確保は8 bytes単位になっており、属性データもこの単位ごとに格納される。従って、例えば、smallint型はデータのサイズとしては2 bytesだが、これを単体で格納する場合、8 bytesの領域を占有することになる。余った6 bytesの領域にはパディングが入る。
COLUMN TETRISとは
そういうわけで、同じデータを保持するにしても、カラムの配置順によって無駄にディスク領域を専有してしまう。そして、Postgresにおおいてカラムの物理的な配置順は、 CREATE TABLE に書いた順になる。そこで、CREATE TABLE するときに、保持したいデータの型に応じてカラム順を組み立てましょう、というお話。これをCOLUMN TETRISと呼ぶらしい。キャッチー。
COLUMN TETRISの検証
カラムの並び順を最適化したテーブルとそうでないテーブルを用意し、データの格納効率に差があることを確認する。環境としては、Postgreのバージョンは9.6.2、Mac OS X 10.11.6。
お題
例として、smallint(2 bytes)、bigint(8 bytes)、integer(4 bytes)、bigint(8 bytes)の順でカラムが定義されたテーブルでは、データは以下のように格納される。「o」が実データ、「|」がヒープタプルの区切り（前述のとおり8 bytes単位）、「x」がパディング。
|ooxxxxxx|oooooooo|ooooxxxx|ooooooooo|
データ格納の流れとしては、まず、ひだりからsmallintをいれる。次にbigintをいれたいんだけど、残りの属性データ領域が6 bytesしかなくて入らないので、次の属性データ領域にいれる。というようなかんじ。
ここで、カラム順を、bigint、bigint、integer、smallintにしてみる。
|oooooooo|ooooooooo|ooooooxx|
まずbigintは8 bytesの属性データ領域を目一杯使う。次に、integerとsmallintは合計でも6 bytesなため、ひとつの属性データ領域に収まる。そのため、必要な属性データ領域が、テトリスしない場合に比べ、1つ削減できる。
実験
テトリスされていない場合とされている場合の違いをみるために、以下の実験用のテーブルを用意する。わかりやすくするために、カラム名の末尾にバイト数をつけている。
CREATE TABLE test ( a_2 smallint, b_8 bigint, c_4 integer, d_8 bigint ); CREATE TABLE test_tetrised ( b_8 bigint, d_8 bigint, c_4 integer, a_2 smallint ); INSERT INTO test VALUES (1,1,1,1),(1,1,1,1),(1,1,1,1),(1,1,1,1),.../* 合計で1000行になるまでデータを入れる */; INSERT INTO test_tetrised VALUES (1,1,1,1),(1,1,1,1),(1,1,1,1),(1,1,1,1),.../* 合計で1000行になるまでデータを入れる */;
この状態で比較してみる。
| relname | reltuples | relpages | tablesize |
|---|---|---|---|
| test | 10000 | 74 | 592 KB |
| test_tetrised | 10000 | 64 | 512 KB |
reltuplesとrelpagesはpg_classから、tablesizeは pg_size_pretty(pg_relation_size(relname)) で算出した。
考察
保持しているデータ量は10000レコードと同じだが、ページ数、テーブルサイズからもわかるように、テーブルサイズは削減される。前項では属性データ領域が3/4になるとしていたが、これはかなり簡略化した例であり、実際にはヘッダやその他付随情報も含まれるため、単純にテーブルサイズが3/4になるわけではない。
データ量が削減されることのメリットはわざわざ語るほどのこともなく自明。ディスク容量の節約になるし、Postgresの場合だとshared_buffersにのるデータ量も節約できるので、データベース全体でみるとパフォーマンスの改善にもつながる。
ただし、今回のようにテトリスして意味がある場合ばかりではない。例えば、textなど可変長の型は8KBという制約にとらわれないため、どの順においても変わりはない*6。
RDBを本来の使い方をしていればアプリケーションからみてカラムに順序性はないはずなので、新規にテーブルを作成するだけでなく、既存のテーブルのサイズを削減したい場合などは、リファクタリングしてみてもいいかもしれない。
終わりに
本当の小ネタになってしまったし、ところどころ知識が怪しくて、申し訳がない。
SQL Serverなどはテーブル作成時に自動的に最適な配置になるようにしてくれると、風の噂で聞いた。かっこいい。モテそう。上記実験はPostgres 9.6で行ったものだが、10でもまだ同じっぽい。とはいえ、そこは昨今の発展目覚ましいPostgres、超スーパーイケてるRDBへと進化してくれることでしょう。