• データ型のサイズ
• 実際の例

PostgreSQLにおけるテーブル列の順序付け

GitLabでは、新しいテーブルのカラムを最小限のスペースで並べることを求めています。これを行う簡単な方法は、型のサイズに基づいて降順に並べ、最後に変数サイズ (text,varchar, array,json,jsonb, など) を並べることです。

C構造と同様に、テーブルのスペースは列の順序に影響されます。これは、列のサイズが次の列の型によって整列されるためです。例を考えてみましょう：

• id (整数、4バイト)
• name （テキスト、変数）
• user_id (整数、4バイト)

最初の列は 4 バイトの整数。次の列は可変長のテキストです。text データ型は1ワードアライメントを必要とし、64ビットプラットフォームでは1ワードは8バイトです。アライメント要件を満たすために、最初の列の直後に4つのゼロを追加する必要があるため、id が4バイトを占め、次に4バイトのアライメントパディングが行われ、次のname のみが格納されます。したがってこの場合、4バイトの整数を格納するために8バイトが費やされます。

行と行の間のスペースもアライメントパディングの対象となります。user_id 列は4バイトしか必要としませんが、64ビットプラットフォームでは、”clear “ワードから始まる次の行を格納できるように、アラインメントパディングのために4つのゼロが追加されます。

その結果、各列の実際のサイズは（変数長データと24バイトのタプルヘッダを省くと）以下のようになります：8バイト、変数、8バイト。これは、各行が2つの4バイト整数のために少なくとも16バイトを必要とすることを意味します。テーブルの行数が少なければ、これはイシューではありません。しかし、数百万の行を格納するようになると、別の順序を使用することでスペースを節約することができます。上記の例では、理想的なカラムの順序は以下のようになります：

• id (整数、4バイト)
• user_id (整数、4バイト)
• name （テキスト、変数）

あるいは

• name (テキスト, 変数)
• id (整数、4バイト)
• user_id (整数、4バイト)

これらの例では、id とuser_id のカラムが一緒になっています。これは、各行が8バイト少ないスペースで済むことを意味します。

Ruby on Rails 5.1以降、IDのデフォルトデータ型はbigint 、8バイトを使用します。より現実的な並べ替えシナリオを紹介するために、例ではinteger 。

データ型のサイズ

PostgreSQLのドキュメントには多くの情報が記載されていますが、ここでは調べやすいように一般的な型のサイズを列挙します。ここで “word “はワードサイズを意味し、32ビットプラットフォームでは4バイト、64ビットプラットフォームでは8バイトです。

変数 “サイズとは、実際のサイズが格納される値に依存することを意味します。PostgreSQLがこれを行に直接埋め込むことができると判断した場合はそうしますが、非常に大きな値の場合はデータを外部に格納し、列に（1ワードのサイズの）ポインタを格納します。このため、変数サイズの列は常にテーブルの最後になければなりません。

実際の例

events 、現在以下のようなレイアウトになっているテーブルを例にしてみましょう：

列を揃えるためにパディングを追加した後、列は次のように固定サイズのチャンクに分割されます：

つまり、変数サイズのデータとタプルヘッダーを除くと、1行あたり少なくとも8 * 6 = 48バイトが必要です。

これを最適化するには、次のような列順を使用します：

これは以下のチャンクを生成します：

変数サイズのデータと24バイトのタプルヘッダを除けば、1行あたり40バイトで済みます。8バイトの節約は大したことではないと思われるかもしれませんが、events テーブルのような大きなテーブルでは重要なことです。例えば、80,000,000行を格納する場合、これは少なくとも610MBのスペース節約になります。