PostgreSQL18連載の4本目の記事です。

この度、PosgreSQLメジャーバージョンアップに伴い、pg_dumpに統計情報のバックアップ・リストアが追加されました。

PostgreSQLのpg_dumpは、データ削除前のバックアップや他環境へのデータ移行などで広く利用されている機能だと思います。しかし、PosgreSQL18以前のpg_dumpには統計情報が含まれないため、あくまでオブジェクト・データのバックアップとして利用されるが多かったのではないかと考えます。

今回、PosgreSQLメジャーバージョンアップで、バックアップ・リストアに統計情報が含まれたことにより、より高い精度で本番環境等の実環境を再現できるようになり、pg_dumpを活用できるシーンも増えるのではないかと考えます。

本記事では統計情報を含むpg_dumpの有用性について検証してまいります。

検証①：pg_dumpに統計情報を含むことによる影響は？

pg_dumpに統計情報が含まれることは活用の幅も広がり、メリットではありますが、実行時間やバックアップファイルサイズが著しく増加すれば、有用性に欠けると考えます。

PosgreSQL18とPosgreSQL18以前（今回はPosgreSQL 16）でpg_dumpによるバックアップ・リストアを実施し、実行時間・ファイルサイズの比較検証を行います。

検証条件

シナリオ

バージョン間の条件を近づけるため、バージョンごとに以下のシナリオで検証を行います。
また、各バージョンごとに「a.10テーブル」「b.50テーブル」「c.100テーブル」の3パターンのテーブル数で検証を行います。

【PosgreSQL18】

1. pg_dump：スキーマレベル論理バックアップ（対象：スキーマ・オブジェクト・データ・統計情報）
2. restore：バックアップファイルによるスキーマリストア

【PosgreSQL18以前】

1. pg_dump：論理バックアップ（対象：スキーマ・オブジェクト・データ）
2. restore：バックアップファイルによるスキーマリストア
3. analyze：統計情報取得

テスト用オブジェクト

バックアップ・リストア対象として用意するオブジェクトは以下とします。
なお、データは1テーブルあたり100万件とします。

テーブル

create database {データベース名};

create schema {スキーマ名};

create table {テーブル名} (id01 bigint not null , id02 text not null , id03 text not null , id04 text not null , id05 text not null) with(autovacuum_enabled = false, toast.autovacuum_enabled = false);
alter table table01 add constraint pk_table01 primary key (id01);

insert into table01 (select generate_series(1,1000000),generate_series(1,1000000)%2,generate_series(1,1000000)%3,generate_series(1,1000000)%4,generate_series(1,1000000)%5);

analyze {対象オブジェクト全てを指定};

powershell -Command "Measure-Command { pg_dump -f {バックアップファイル名} -h {ホスト名} -p {ポート} -U {ユーザ名} -n {スキーマ名} -d {データベース名} -W --format=p -E "UTF8" --verbose --statistics }"

powershell -Command "Measure-Command { pg_dump -f {バックアップファイル名} -h {ホスト名} -p {ポート} -U {ユーザ名} -n {スキーマ名} -d {データベース名} -W --format=p -E "UTF8" --verbose }"

Days              : 0
Hours             : 0
Minutes           : 0
Seconds           : 5
Milliseconds      : 676
Ticks             : 56764202
TotalDays         : 6.56993078703704E-05
TotalHours        : 0.00157678338888889
TotalMinutes      : 0.0946070033333333
TotalSeconds      : 5.6764202
TotalMilliseconds : 5676.4202

drop schema {スキーマ名} cascade;

psql -h {ホスト名} -p {ポート} -U {ユーザ名} -d {データベース名}
\timing
\i {バックアップファイル名};

（略）
CREATE TABLE
時間: 3.321 ミリ秒
ALTER TABLE
時間: 0.479 ミリ秒
CREATE TABLE
時間: 4.801 ミリ秒
ALTER TABLE
時間: 0.641 ミリ秒
COPY 1000000
時間: 816.210 ミリ秒
COPY 1000000
時間: 721.280 ミリ秒
COPY 1000000
（略）

analyze {対象オブジェクト全てを指定};

検証①結果

実行時間

• pg_dumpの実行時間はPostgres18が優位である。
• restore（+analyze）の実行時間にも致命的な差はない。

バックアップファイルサイズ

• 統計情報データの分ファイルサイズとしては純増していると思われるが、軽微な範囲であると考える。

反省点（まずは反省から。今後、アップデートしていきます）

• 純粋なバージョンアップによる比較を行うためには、Postgres17を採用すべきであった。これではPostgres17アップデートによる影響なのか、Postgres18アップデートによる影響なのか判断ができない
• 値の比較を行うには、試行回数が足りていない

pg_dump考察

Postgres18バックアップファイルを確認すると、以下のように統計情報が含まれていることがわかる。

また、テーブルレベルの統計情報はpg_restore_relation_stats、カラムレベルの統計情報はpg_restore_attribute_statsにより、統計情報が更新されている。

（略）
SELECT * FROM pg_catalog.pg_restore_relation_stats(
	'version', '180000'::integer,
	'schemaname', 'test',
	'relname', 'table01',
	'relpages', '5406'::integer,
	'reltuples', '1e+06'::real,
	'relallvisible', '0'::integer,
	'relallfrozen', '0'::integer
);
SELECT * FROM pg_catalog.pg_restore_attribute_stats(
	'version', '180000'::integer,
	'schemaname', 'test',
	'relname', 'table01',
	'attname', 'id01',
	'inherited', 'f'::boolean,
	'null_frac', '0'::real,
	'avg_width', '8'::integer,
	'n_distinct', '-1'::real,
	'histogram_bounds', '{56,10524,21232,30718,41153,50377,60264,70120,80019,89398,99574,109434,119523,129696,139684,149877,161659,171036,181245,191695,201052,211025,220695,231533,241040,251121,261035,272801,282385,291622,302144,311922,320997,331276,341649,352215,362449,372253,383367,393098,402659,412876,422222,431857,441934,452435,462050,471877,481111,491169,502147,512482,522629,533297,542697,552015,562928,573165,583118,592605,602734,612603,622786,632162,641168,651381,660752,671539,681549,691029,701074,711518,721946,732664,743058,752998,763483,773288,783463,793519,802897,812524,822721,832245,841333,851908,860870,870866,880738,889926,898894,909459,919903,929828,939844,949636,960208,970440,980545,989514,999967}'::text,
	'correlation', '1'::real
);
（略）

テーブルレベル統計情報（pg_class）

参考：52.11. pg_class

カラムレベル統計情報（pg_stats）

参考：53.29. pg_stats

データベースオブジェクト統計操作関数

参考：9.28. System Administration Functions

restore考察

リストアフローを分解し、各所の実行時間を洗い出す。※環境変数設定・スキーマ作成は含まない

• テーブル・カラム統計情報更新、インデックス統計情報更新は純増している。
• インデックス作成時にも実行時間の増加傾向が見受けられる。★今後の検証課題とする。

検証①結論

• pg_dumpに統計情報を含む形でも実行時間・バックアップファイルサイズが著しく増加することはないため、今後問題なく活用できると考える

検証②：オブジェクト・データ・統計情報をリストアすれば、バックアップ元の実行計画は再現される？

オブジェクト・データ・統計情報が再現されれば、理論上プランナは同じ実行計画を生成すると想定できる。
しかし、あくまで理論上であるため、検証を行います。

検証条件

シナリオ

以下シナリオの②と⑤の実行計画を比較する。

①オブジェクト作成・データ作成・統計情報を取得
②実行計画を取得
③論理バックアップ（対象：スキーマ・オブジェクト・データ） を取得
④スキーマを削除・バックアップファイルをリストア
⑤実行計画を取得

テスト用オブジェクト

バックアップ・リストア対象として用意するオブジェクトは以下とします。
なお、データは1テーブルあたり100万件とします。

テーブル（table01）

インデックス

クエリ
table01_idx1を利用するようなクエリとします。

select * from table01 where id02 = '1';

create database {データベース名};

create schema {スキーマ名};

create table {テーブル名} (id01 bigint not null , id02 text not null , id03 text not null , id04 text not null , id05 text not null) with(autovacuum_enabled = false, toast.autovacuum_enabled = false);
alter table table01 add constraint pk_table01 primary key (id01);

insert into table01 (select generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000));

analyze {対象オブジェクト全てを指定};

explain analyze select * from table01 where id02 = '1';

powershell -Command "Measure-Command { pg_dump -f {バックアップファイル名} -h {ホスト名} -p {ポート} -U {ユーザ名} -n {スキーマ名} -d {データベース名} -W --format=p -E "UTF8" --verbose --statistics }"

drop schema {スキーマ名} cascade;

psql -h {ホスト名} -p {ポート} -U {ユーザ名} -d {データベース名}
\i {バックアップファイル名};

explain analyze select * from table01 where id02 = '1';

結果

実行計画は概ね同様であると考える。

ただ、同一のオブジェクト・データ・統計情報であるため、再現したわけではなく、ただ同じ条件下で、同じ実行計画を生成したとも考えられる。
よって、バックアップ元の実行計画が再現されているとは言い切れないと判断する。

②バックアップ元実行計画

Index Scan using table01_idx1 on table01  (cost=0.42..8.44 rows=1 width=32) (actual time=0.038..0.039 rows=1.00 loops=1)
  Index Cond: (id02 = '1'::text)
  Index Searches: 1
  Buffers: shared hit=4
Planning:
  Buffers: shared hit=34
Planning Time: 0.107 ms
Execution Time: 0.060 ms

⑤リストア先実行計画

Index Scan using table01_idx1 on table01  (cost=0.42..8.44 rows=1 width=32) (actual time=0.080..0.081 rows=1.00 loops=1)
  Index Cond: (id02 = '1'::text)
  Index Searches: 1
  Buffers: shared read=4
Planning:
  Buffers: shared hit=27 read=1
Planning Time: 1.288 ms
Execution Time: 0.109 ms

検証②追加検証：データ状況の異なるテーブルに統計情報のみリストアすれば、バックアップ元の実行計画は再現される？

バックアップ元のデータ状況とは異なるテーブルに統計情報のみをリストアした場合、理論上はバックアップ元の統計情報を基に実行計画を生成する。

検証条件

シナリオ

以下シナリオの②と⑤と⑦の実行計画を比較する。

①オブジェクト作成・データ作成・統計情報を取得
②実行計画を取得
③論理バックアップ（対象：スキーマ・オブジェクト・データ） を取得
④スキーマを削除・バックアップファイルをリストア
⑤実行計画を取得
⑥統計情報を生成
⑦実行計画を取得

テスト用オブジェクト

今回は統計情報のみバックアップ・リストアするため、バックアップ元・リストア先のテーブル・データそれぞれ用意します。

バックアップ元のテーブル（table01）
データ：2件

リストア元のテーブル（table01）
データ：100万件

インデックス

クエリ
table01_idx1を利用するようなクエリとします。

select * from table01 where id02 = '1';

create database {データベース名};

create schema {スキーマ名};

create table {テーブル名} (id01 bigint not null , id02 text not null , id03 text not null , id04 text not null , id05 text not null) with(autovacuum_enabled = false, toast.autovacuum_enabled = false);
alter table table01 add constraint pk_table01 primary key (id01);

insert into table01 (select generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000));

analyze {対象オブジェクト全てを指定};

explain analyze select * from table01 where id02 = '1';

powershell -Command "Measure-Command { pg_dump -f {バックアップファイル名} -h {ホスト名} -p {ポート} -U {ユーザ名} -n {スキーマ名} -d {データベース名} -W --format=p -E "UTF8" --verbose --statistics-only }"

drop schema {スキーマ名} cascade;

psql -h {ホスト名} -p {ポート} -U {ユーザ名} -d {データベース名}
\i {バックアップファイル名};

explain analyze select * from table01 where id02 = '1';

analyze {対象オブジェクト全てを指定};

explain analyze select * from table01 where id02 = '1';

結果

• ⑤リストア先実行計画（統計情報取得前）を見る限り、プランナは取得されるのは1件であると想定している。しかし、それに対して実際は999,999件取得できているため、indexscanに切り替えて実行している。
  想定通り、バックアップ元の統計情報に基づいて、実行計画を生成するしている様子がうかがえる。

②バックアップ元実行計画

Seq Scan on table01  (cost=0.00..1.02 rows=1 width=16) (actual time=0.022..0.023 rows=1.00 loops=1)
  Filter: (id02 = '1'::text)
  Rows Removed by Filter: 1
  Buffers: shared hit=1
Planning:
  Buffers: shared hit=27 read=1
Planning Time: 1.491 ms
Execution Time: 0.039 ms

⑤リストア先実行計画（統計情報取得前）

Index Scan using table01_idx1 on table01  (cost=0.41..8.43 rows=1 width=16) (actual time=0.678..181.715 rows=999999.00 loops=1)
  Index Cond: (id02 = '1'::text)
  Index Searches: 1
  Buffers: shared hit=7352 read=844
Planning:
  Buffers: shared hit=36 read=1
Planning Time: 0.794 ms
Execution Time: 215.249 ms

⑦リストア先実行計画（統計情報取得後）

Seq Scan on table01  (cost=0.00..19852.00 rows=1000000 width=28) (actual time=0.019..93.264 rows=999999.00 loops=1)
  Filter: (id02 = '1'::text)
  Rows Removed by Filter: 1
  Buffers: shared hit=7352
Planning:
  Buffers: shared hit=34 dirtied=2
Planning Time: 1.653 ms
Execution Time: 125.505 ms

検証②結論

• 追加検証を見ても、バックアップ元の統計情報がリストア先に反映されていると判断できる

結論

• pg_dumpに統計情報を含むことによる劣化はなく、問題なく活用できる
• 統計情報のバックアップ・リストア自体も問題なく機能している
• あくまで論理バックアップであり、デッドタプル等の物理的な部分まで再現できるわけではないため、リストア先で問題がなかったからバックアップ元でも問題ないと言い切るべきではない
• ただし、従来のpg_dumpによるバックアップ・リストアと比較して、より高い精度でバックアップ元環境を再現できるようになっているため、”検証” を行う上ではより有用性の高い機能になっていると考える