【PostgreSQL】TOASTテーブルの仕組みとサイズ確認方法

TOASTとは

PostgreSQLのTOAST（The Oversized-Attribute Storage Technique）は、大きなデータを効率的に格納するための仕組み。PostgreSQLではテーブルの行サイズが約8KBに制限されているため、大きなデータはTOASTテーブルと呼ばれる別のテーブルに自動的に分割して格納される。

TOASTは透過的に動作するため、通常の操作では意識する必要がない。ただし、テーブルサイズの調査や削除時には、TOASTテーブルの存在を理解しておく必要がある。

TOASTテーブルの確認

通常のテーブルを作成すると、自動的にTOASTテーブルが作成される。TOASTテーブル名はpg_toast_[テーブルのoid]という形式。

まず、テストテーブルを作成する。

CREATE TABLE large_data (
  id SERIAL PRIMARY KEY,
  content TEXT
);

テーブルのoidを確認する。

SELECT oid, relname FROM pg_class WHERE relname = 'large_data';
  oid  |  relname
-------+------------
 16385 | large_data
(1 row)

対応するTOASTテーブルを確認する。

SELECT oid, relname FROM pg_class WHERE relname LIKE 'pg_toast_16385%';
  oid  |       relname
-------+----------------------
 16388 | pg_toast_16385
 16389 | pg_toast_16385_index
(2 rows)

TOASTテーブルとそのインデックスが作成されている。

TOAST戦略の設定

TEXT型のデフォルトTOAST戦略はEXTENDED（圧縮してからTOAST）である。同じ文字の繰り返しは圧縮率が高く、TOASTテーブルに格納されない場合がある。

確実にTOASTテーブルに格納するため、TOAST戦略をEXTERNAL（圧縮せずにTOAST）に変更する。
今回は実験のために強制的に設定しており、通常はデフォルトのままで問題ない。

ALTER TABLE large_data ALTER COLUMN content SET STORAGE EXTERNAL;

TOAST戦略を確認する。

SELECT attname, attstorage
FROM pg_attribute
WHERE attrelid = 'large_data'::regclass AND attname = 'content';
 attname | attstorage
---------+------------
 content | e
(1 row)

eはEXTERNALを意味する。

TOAST戦略の種類

PostgreSQLには4つのTOAST戦略がある。

戦略	説明	圧縮	TOAST	attstorage 値
PLAIN	TOASTを使用しない(TOAST化不可能のデータ型)	なし	なし	p
EXTENDED	圧縮してからTOAST（デフォルト）	あり	あり	x
EXTERNAL	圧縮せずにTOAST	なし	あり	e
MAIN	可能な限り圧縮する。TOASTは最後の手段	あり	最小限	m

大きなデータの格納実験

大きなテキストデータを挿入してTOASTの動作を確認する。PostgreSQLは約2KB（2032バイト）を超えるデータをTOASTテーブルに格納する。

INSERT INTO large_data (content)
SELECT repeat('A', 10000) FROM generate_series(1, 100);

10,000文字の文字列を100行挿入した。圧縮を無効化しているため、データはTOASTテーブルに格納される。

テーブルサイズの確認

pg_relation_size関数を使用してテーブルのサイズを確認する。

SELECT pg_size_pretty(pg_relation_size('large_data')) AS table_size;
 table_size
------------
 24 kB
(1 row)

メインテーブルのサイズは24KB程度と小さい。メインテーブルのサイズが小さいのは、大きなデータがTOASTテーブルに格納されているためである。

TOASTテーブルのサイズを確認する。TOASTテーブル名は先ほど確認したpg_toast_16385を使用する。

SELECT pg_size_pretty(pg_relation_size('pg_toast.pg_toast_16385')) AS toast_size;
 toast_size
------------
 1000 kB
(1 row)

TOASTテーブルに実際のデータが格納されていることが確認できる。

もしサイズが0と表示される場合は、以下を確認する：

TOAST戦略がEXTERNALに設定されているか（EXTENDEDの場合は圧縮されてTOASTに格納されない可能性がある）
データサイズが約2KB以上あるか
正しいTOASTテーブル名を指定しているか（oidが異なる場合はpg_classで確認）

テーブル全体のサイズを確認

メインテーブルとTOASTテーブルを含む全体のサイズを確認するにはpg_total_relation_size関数を使用する。

SELECT
  pg_size_pretty(pg_relation_size('large_data')) AS table_size,
  pg_size_pretty(pg_total_relation_size('large_data')) AS total_size;
 table_size | total_size
------------+------------
 24 kB      | 1032 kB
(1 row)

pg_total_relation_sizeはTOASTテーブルとインデックスを含めたサイズを返す。メインテーブル（24 kB）とTOASTテーブル（約1000 kB）とインデックスを合わせたサイズである。

TOASTテーブルがあるときのテーブル削除

通常のテーブル削除では、TOASTテーブルも自動的に削除される。

DROP TABLE large_data;

このコマンドで、メインテーブルとTOASTテーブルの両方が削除される。

削除後にTOASTテーブルが残っていないことを確認する。

SELECT oid, relname FROM pg_class WHERE relname LIKE 'pg_toast_16385%';
 oid | relname
-----+---------
(0 rows)

TOASTテーブルも正しく削除されている。

TOASTの確認に便利なクエリ

すべてのテーブルとそのTOASTサイズを一覧表示するクエリ。

SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS toast_size,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

このクエリで、各テーブルのメインテーブルサイズ、TOAST+インデックスサイズ、合計サイズを確認できる。