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

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

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

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

https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/

## 事前知識

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

|関数|説明|
|--|--|
|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と等価です。|

## 実行環境

```sql
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.
```

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

様々な型のカラムを持つテーブルを定義します
```sql
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
```
データを挿入します
```sql
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
```
リレーションサイズを確認します
```Sql
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)
```
## カラム順序整列後テーブルのサイズ計測

カラムの順番を求めます
```sql
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)
```

この順番でカラムを並び替えて、テーブルを再定義してみます
```sql
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
 ```
カラム順序整列前のテーブルからデータをコピーします
 ```Sql
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
```
カラム順序整列後のテーブルのリレーションサイズを確認します
```sql
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バイト小さくなっています
```sql
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バイト区切りでデータを格納できない場合はパティングされる可能性があります。  
  
簡単な例です。

```sql
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 はカラムの並べ替えをネイティブにサポートしていないので、作成済みのテーブルのカラムの入れ替えはできません。そのため、自前でコピーする必要がある
* この記事では詳しく書きませんが、カラム順序整列はカラムアクセス速度に対する恩恵もあります