PostgreSQLのカラム順序がテーブルサイズに与える影響

カラム順序がテーブルサイズに与える影響

テーブルのカラム順序を最適化することで、ストレージの利用効率を向上させられるというお話です(使い古されたネタです)。

主に次の記事を元に記載しています。

事前知識

テーブルサイズ取得には次の関数を使用します

関数	説明
pg_column_size	個々のデータ値を格納するのに使用されるバイト数を表示します。
pg_column_compression	個々の可変長値で使われた圧縮アルゴリズムを表示します。 値が圧縮されていなければ、NULLを返します。
pg_size_pretty	バイトサイズを、サイズ単位(バイト、kB、MB、GB、TB、PBのうちの適切なもの)を使った、より人間が読みやすい形式に変換します。
pg_total_relation_size	指定テーブルが使用している、インデックスとTOASTデータを含む全ディスクスペースを計算します。 結果はpg_table_size + pg_indexes_sizeと等価です。

実行環境

postgres=# select version();
                                                                version                                                                 
----------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.10 (Ubuntu 14.10-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)

データベースの準備

postgres@masami-L ~> createdb -U postgres sample
postgres@masami-L ~> psql -U postgres -d sample
psql (14.10 (Ubuntu 14.10-0ubuntu0.22.04.1))
Type "help" for help.

カラム順序整列前テーブルのサイズ計測

様々な型のカラムを持つテーブルを定義します

postgres=# CREATE TABLE diverse_types_table (
     id SERIAL PRIMARY KEY,
     name VARCHAR(255),
     description TEXT,
     age INTEGER,
     salary NUMERIC(10, 2),
     created_at TIMESTAMP WITH TIME ZONE,
     updated_at TIMESTAMP WITH TIME ZONE,
     is_active BOOLEAN
 );
CREATE TABLE

データを挿入します

sample=# DO $$
BEGIN
    FOR i IN 1..100000 LOOP
        INSERT INTO diverse_types_table (name, description, age, salary, created_at, updated_at, is_active)
        VALUES (
            'Name ' || i,
            'Description ' || i,
            i % 100,
            (i % 100)::numeric + 0.99,
            NOW() - ((i % 365) || ' days')::interval,
            NOW(),
            i % 2 = 0
        );
    END LOOP;
END $$;
DO

リレーションサイズを確認します

sample=# vacuum analyze diverse_types_table;
VACUUM
sample=# select pg_size_pretty(pg_total_relation_size('diverse_types_table'::regclass));
 pg_size_pretty 
----------------
 12 MB
(1 row)

カラム順序整列後テーブルのサイズ計測

カラムの順番を求めます

sample=# SELECT a.attname, t.typname, t.typalign, t.typlen
   FROM pg_class c
   JOIN pg_attribute a ON (a.attrelid = c.oid)
   JOIN pg_type t ON (t.oid = a.atttypid)
  WHERE c.relname = 'diverse_types_table'
    AND a.attnum >= 0
  ORDER BY t.typlen DESC;
   attname   |   typname   | typalign | typlen 
-------------+-------------+----------+--------
 created_at  | timestamptz | d        |      8
 updated_at  | timestamptz | d        |      8
 id          | int4        | i        |      4
 age         | int4        | i        |      4
 is_active   | bool        | c        |      1
 salary      | numeric     | i        |     -1
 description | text        | i        |     -1
 name        | varchar     | i        |     -1
(8 rows)

この順番でカラムを並び替えて、テーブルを再定義してみます

sample=# CREATE TABLE sorted_diverse_types_table (
    created_at TIMESTAMP WITH TIME ZONE,
    updated_at TIMESTAMP WITH TIME ZONE,
    id SERIAL PRIMARY KEY,
    age INTEGER,
    name VARCHAR(255),
    salary NUMERIC(10, 2),
    is_active BOOLEAN,
    description TEXT
 );
CREATE TABLE

カラム順序整列前のテーブルからデータをコピーします

sample=# INSERT INTO
 sorted_diverse_types_table
SELECT
 created_at,
 updated_at,
 id,
 age,
 name,
 salary,
 is_active,
 description
FROM
 diverse_types_table
; 
INSERT 0 100000

カラム順序整列後のテーブルのリレーションサイズを確認します

sample=# vacuum analyze sorted_diverse_types_table;
VACUUM
postgres=# sample=# select pg_size_pretty(pg_total_relation_size('sorted_diverse_types_table'::regclass));
 pg_size_pretty 
----------------
 11 MB
(1 row)

比較

２つのテーブルを作成し、テーブルサイズを比較した結果です

テーブル	サイズ
カラム順序整列前	12 MB
カラム順序整列後	11 MB

※例は可変型のカラムが良い感じに補完しあっていて、削減効果が弱いです。
実際には2~3割削れることも多いです。

各テーブルのカラムサイズを確認します
カラム整列後の方が4バイト小さくなっています

postgres=# select pg_column_size(diverse_types_table.*) FROM diverse_types_table limit 1;
 pg_column_size 
----------------
             81
(1 row)

postgres=# select pg_column_size(sorted_diverse_types_table.*) FROM sorted_diverse_types_table limit 1;
 pg_column_size 
----------------
             77
(1 row)

カラムは8バイト区切りでアライメントされて、ディスクに記録されるため、8バイト区切りでデータを格納できない場合はパティングされる可能性があります。

簡単な例です。

sample=# SELECT pg_column_size(row()) AS empty,
       pg_column_size(row(0::SMALLINT)) AS byte2,
       pg_column_size(row(0::BIGINT)) AS byte8,
       pg_column_size(row(0::SMALLINT, 0::BIGINT)) AS byte16_padding,
       pg_column_size(row(0::BIGINT, 0::SMALLINT)) byte16_nopadding;
 empty | byte2 | byte8 | byte16_padding | byte16_nopadding 
-------+-------+-------+----------------+------------------
    24 |    26 |    32 |             40 |               34
(1 row)

byte16_paddingとbyte16_nopaddingのタプルを構成する型は同じですが、6バイトの差が発生しています。
※タプルには24バイトのヘッダーが必要なので、それを除いて考える必要があります

結論

• カラム順序は8バイトアライメントの影響を受けないように並び替えるとテーブルサイズが削減できる(ストレージ効率の向上、パフォーマンス向上)

備考

• 関連性の強いカラムの位置を近づけた方が保守性の高いテーブルになるので、トレードオフが発生する可能性もある
• Postgres はカラムの並べ替えをネイティブにサポートしていないので、作成済みのテーブルのカラムの入れ替えはできません。そのため、自前でコピーする必要がある
• この記事では詳しく書きませんが、カラム順序整列はカラムアクセス速度に対する恩恵もあります