トーストが更新されないなら掃除をすればいいじゃない
概要
PostgreSQLでTOASTableなデータ型を使っているときに、 pg_classでtoastテーブルの relpagesを確認した時に推計値が実態と結構ずれている？ということで推計値を最新化する時に調べたメモです。
TL;DR
analyzeはtoastテーブルをスキップするためpg_class.relpagesに格納されている推計値が更新されない。vacuumをかけると最新化される。
-- can execute analyze but it's skipped
analyze verbose pg_toast.pg_toast_16387;
postgres.public> analyze verbose pg_toast.pg_toast_16387
[01000] skipping "pg_toast_16387" --- cannot analyze > non-tables or special system tables
completed in 4 ms
セットアップ
DBセットアップ
環境は検証できればなんでもいいが今回はコンテナを利用。
version: '3'
services:
postgresql:
image: postgres:17
hostname: postgres
container_name: postgres
ports:
- 55432:5432
restart: always
networks:
- compose-network
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: <my_awesome_super_password>
POSTGRES_DB: postgres
volumes:
- ./db/pg/data:/var/lib/postgresql/data
networks:
compose-network:
driver: bridge
$ docker compose up -d
テストテーブル作成
TOASTableなデータ型として、今回はbytea型を利用。
create table test_table (
id serial primary key,
blob bytea
);
検証
データ追加前の状態
truncateをtest_tableにかけた後のテーブルサイズをチェック。
まず pg_total_relation_size() でディスク上のサイズをチェック。
select pg_size_pretty(pg_total_relation_size('test_table')) as total_rel_size
, pg_size_pretty(pg_relation_size('test_table')) as rel_size
, pg_size_pretty(pg_table_size('test_table')) as table_size
;
total_rel_size | rel_size | table_size
----------------+----------+------------
16 kB | 0 bytes | 8192 bytes
(1 row)
block_sizeが8192 bytesなので、total_rel_size は 2 page, table_sizeとしては 1 page あるらしい。
各 size の違いはこちらがわかりやすかった。
pg_class で relpages もチェック。今回 test_table に紐づく toast テーブルはpg_toast_16387だったため、そちらも併せて確認。
select c.oid
, n.nspname
, relname
, relpages
, relpages * current_setting('block_size')::integer as approx_size_byte
, relpages * current_setting('block_size')::double precision / 1024 as approx_size_kb
, relpages * current_setting('block_size')::double precision / 1024 / 1024 as approx_size_mb
, reltuples
, reltoastrelid
from pg_class c
join pg_namespace n
on c.relnamespace = n.oid
where c.relname = 'test_table'
or c.relname = 'pg_toast_16387';
oid | nspname | relname | relpages | approx_size_byte | approx_size_kb | approx_size_mb | reltuples | reltoastrelid
-------+----------+----------------+----------+------------------+----------------+----------------+-----------+---------------
16391 | pg_toast | pg_toast_16387 | 0 | 0 | 0 | 0 | -1 | 0
16387 | public | test_table | 0 | 0 | 0 | 0 | -1 | 16391
(2 rows)
とりあえずデータは入っていないという状態からスタート。
データ追加
要点だけ記載。python でファイルを読み込んでそのままinsert。
conn = psycopg2.connect(**config)
conn.autocommit = False
cur = conn.cursor()
with open(filename, 'rb') as file:
data = file.read()
cur.execute("INSERT INTO test_table(blob) " +
"VALUES(%s)",
(data,))
conn.commit()
cur.close()
追加するデータはTOAST領域に保存される、圧縮後2KBを超えるデータならなんでも良いので今回はおよそ144MBのnytaxiのファイルを利用。
同じファイルを5回保存した。
データ追加後の状態
pg_total_relation_size()で確認すると、
total_rel_size | rel_size | table_size
----------------+------------+------------
715 MB | 8192 bytes | 715 MB
144MB * 5 -> 720MB のため、想定よりやや小さいがこんなもんであろう。
この記事での本題はpg_classでrelpagesを確認するとき。
oid | nspname | relname | relpages | approx_size_byte | approx_size_kb | approx_size_mb | reltuples | reltoastrelid
-------+----------+----------------+----------+------------------+----------------+----------------+-----------+---------------
16391 | pg_toast | pg_toast_16387 | 52071 | 426565632 | 416568 | 406.8046875 | 208284 | 0
16387 | public | test_table | 0 | 0 | 0 | 0 | -1 | 16391
(2 rows)
approx_size_mb が page 数と block_size から算出した概算データサイズになるが、これが406MBと実際に挿入したデータ数、pg_total_relation_size()などで確認した700MB以上の値と比べてかなり小さい。これは relpages の値が実際より小さく推計されていると想定される。
autovacuumの設定状況や動作によってはデータ追加直後に推計値が同期されていて問題ないケースもあると想像しているが、今回そこは細かく調査していない。
推計値の更新
ということでまずはanalyzeを発行。
analyze verbose test_table;
analyze verbose pg_toast.pg_toast_16387;
再度、pg_classでrelpagesと推計サイズを確認する。
oid | nspname | relname | relpages | approx_size_byte | approx_size_kb | approx_size_mb | reltuples | reltoastrelid
-------+----------+----------------+----------+------------------+----------------+----------------+-----------+---------------
16391 | pg_toast | pg_toast_16387 | 52071 | 426565632 | 416568 | 406.8046875 | 208284 | 0
16387 | public | test_table | 1 | 8192 | 8 | 0.0078125 | 5 | 16391
(2 rows)
test_table側は推計値が更新されているが、pg_toast_16387側は更新されていない。
analyzeのログをよく見てみると、toastテーブルはanalyzeがスキップされている。（時刻情報は削除している）
リファレンスを見つけられなかったため想像に留まっているが、toastテーブルはシステムテーブル扱いの上、元テーブルを利用するときにTOASTポインタを介した定型的なアクセスを実施することから統計情報を取得する意味がないという判断なのだろう。(読まれた方、もしリファレンスあったら教えていただきたいです...)
postgres.public> analyze verbose test_table
analyzing "public.test_table"
"test_table": scanned 1 of 1 pages, containing 5 live rows and 0 dead rows; 5 rows in sample, 5 estimated total rows
completed in 45 ms
postgres.public> analyze verbose pg_toast.pg_toast_16387
[01000] skipping "pg_toast_16387" --- cannot analyze non-tables or special system tables
completed in 3 ms
pg_classのドキュメント上ではanalyze以外にvacuumなどによっても更新されるということで、今度はvacuumをかける。
vacuum (analyze false , verbose) pg_toast.pg_toast_16387;
これでrelpagesの値が更新された。
oid | nspname | relname | relpages | approx_size_byte | approx_size_kb | approx_size_mb | reltuples | reltoastrelid
-------+----------+----------------+----------+------------------+----------------+----------------+-----------+---------------
16391 | pg_toast | pg_toast_16387 | 90543 | 741728256 | 724344 | 707.3671875 | 362170 | 0
16387 | public | test_table | 1 | 8192 | 8 | 0.0078125 | 5 | 16391
(2 rows)
先人の知恵
調査していたら、コミュニティにも同じような投稿があった。
結論
肝心なところはanalyzeでスキップされたメッセージを読み飛ばさないことかもしれない。pg_class.relpagesのドキュメントではvacuum以外にもcreate indexなどで更新されると記載があるが、toastテーブルに構成変更をかける場面はまずないと考えられるため、今すぐに推計値を最新化したい時にはvacuumが第一の選択肢だろうか。
Discussion