Future Tech Blog
フューチャー技術ブログ
DBカテゴリ

pg_dumpによる統計情報ダンプ検証

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

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

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

サードパーティ系の拡張モジュールを利用することで、PosgreSQL18以前のバージョンにおいても統計情報のバックアップできます。

今回、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万件とします。

テーブル

カラム名 (列名) データ型 PK NULL許容 カーディナリティ
id01 bigint 〇 NO 1,000,000
id02 text NO 2
id03 text NO 3
id04 text NO 4
id04 text NO 5
検証手順
  1. データベース作成
create database {データベース名};
  1. スキーマ作成
create schema {スキーマ名};
  1. テーブル・PK作成

意図しないタイミングで統計情報が更新されるのを避けるため、テーブルレベルでautovacuumを無効化しています。

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);
  1. データ作成
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);
  1. 統計情報取得※【PosgreSQL18】のみ

autovacuumを無効化しているため、バックアップ対象となる統計情報を取得させます。

analyze {対象オブジェクト全てを指定};
  1. pg_dump
    windows上にインスタンスを立てているため、Powershellよりpg_dumpを実施しています。
    今回は論理バックアップの内容がわかるよう平文にてバックアップを取得します。

【PosgreSQL18】

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

【PosgreSQL18以前】

--statisticsは付与できない。

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

今回は実行時間を取得したいのでMeasure-Commandを使用しています。

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
  1. スキーマ削除
drop schema {スキーマ名} cascade;
  1. リストア

windows上にインスタンスを立てているため、PowershellよりDBにログインし、リストアを実施しています。

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

今回は実行時間を取得したいので\timingを使用しています。

（略）
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
（略）
  1. 統計情報取得※【PosgreSQL18以前】のみ
analyze {対象オブジェクト全てを指定};

検証①結果

実行時間

  • pg_dumpの実行時間はPostgres18が優位である。
  • restore（+analyze）の実行時間にも致命的な差はない。
バージョン パターン
(総データ量)		 pg_dump
(ms)		 restore
(ms)		 analyze
(ms)		 total
(ms)
PosgreSQL16 10テーブル
(1,000万件)		 10,292.248 19,134.755 1,179.920 30,606.923
PosgreSQL16 50テーブル
(5,000万件)		 38,700.804 113,306.861 7,801.671 159,809.336
PosgreSQL16 100テーブル
(10,000万件)		 147,478.958 318,141.068 19,085.712 484,705.738
PosgreSQL18 10テーブル
(1,000万件)		 5,676.420 19,507.097 - 25,183.517
PosgreSQL18 50テーブル
(5,000万件)		 24,826.907 195,836.231 - 220,663.138
PosgreSQL18 100テーブル
(10,000万件)		 69,300.5253 341,310.349 - 410,610.8743

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

  • 統計情報データの分ファイルサイズとしては純増していると思われるが、軽微な範囲であると考える。
バージョン パターン
(総データ量)		 ファイルサイズ(byte)
Postgres16 10テーブル
(1,000万件)		 148,897,705
Postgres16 50テーブル
(5,000万件)		 744,485,345
Postgres16 100テーブル
(10,000万件)		 1,488,969,904
Postgres18 10テーブル
(1,000万件)		 148,951,114
Postgres18 50テーブル
(5,000万件)		 744,657,089
Postgres18 100テーブル
(10,000万件)		 1,489,313,091

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

  • 純粋なバージョンアップによる比較を行うためには、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）

列 説明
relpages テーブルのディスク上のページ表現のサイズ
reltuples テーブル内の有効な行数
relallvisible テーブルの可視性マップですべて可視とマークされているページの数
relallfrozen テーブルの可視性マップで「すべて凍結」とマークされているページの数

参考：52.11. pg_class

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

列 説明
null_frac NULLの列エントリの割合
avg_width 列のエントリの平均幅（バイト単位）
n_distinct 列内の固有値の推定数
histogram_bounds 列の値をほぼ均等な母集団のグループに分割する値のリスト
correlation 物理的な行順序と列値の論理的な順序との間の統計的な相関関係

参考：53.29. pg_stats

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

関数名 説明
pg_restore_relation_stats テーブルレベルの統計情報を更新
pg_restore_attribute_stats 列レベルの統計情報を作成または更新

参考：9.28. System Administration Functions

restore考察

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

  • テーブル・カラム統計情報更新、インデックス統計情報更新は純増している。
  • インデックス作成時にも実行時間の増加傾向が見受けられる。★今後の検証課題とする。
バージョン
パターン		 TABLE
作成
(ms)		 データ
作成
(ms)		 統計情報
更新
TABLE
(ms)		 INDEX
作成
(ms)		 統計情報
更新
INDEX
(ms)
PosgreSQL16
10テーブル		 82.982 9950.638 - 9092.244 -
PosgreSQL16
50テーブル		 316.995 58,569.127 - 54,412.186 -
PosgreSQL16
100テーブル		 746.560 133,915.813 - 183,468.043 -
PosgreSQL16
10テーブル		 58.679 8059.094 527.813 10846.512 5.288
PosgreSQL16
50テーブル		 286.436 63196.114 989.952 131313.686 39.404
PosgreSQL16
100テーブル		 640.463 134743.767 1298.477 204548.495 63.719

検証①結論

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

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

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

検証条件

シナリオ

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

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

テスト用オブジェクト

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

テーブル（table01）

カラム名 (列名) データ型 PK NULL許容 カーディナリティ
id01 bigint 〇 NO 1,000,000
id02 text NO 1,000,000
id03 text NO 1,000,000
id04 text NO 1,000,000
id04 text NO 1,000,000

インデックス

インデックス名 データ型
table01_idx1 id02

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

select * from table01 where id02 = '1';
検証手順
  1. データベース作成
create database {データベース名};
  1. スキーマ作成
create schema {スキーマ名};
  1. テーブル・PK作成
    意図しないタイミングで統計情報が更新されるのを避けるため、テーブルレベルでautovacuumを無効化しています。
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);
  1. データ作成
insert into table01 (select generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000));
  1. 統計情報取得
    autovacuumを無効化しているため、バックアップ対象となる統計情報を取得させます。
analyze {対象オブジェクト全てを指定};
  1. 実行計画取得
explain analyze select * from table01 where id02 = '1';
  1. pg_dump
    windows上にインスタンスを立てているため、Powershellよりpg_dumpを実施しています。
    今回は論理バックアップの内容がわかるよう平文にてバックアップを取得します。
powershell -Command "Measure-Command { pg_dump -f {バックアップファイル名} -h {ホスト名} -p {ポート} -U {ユーザ名} -n {スキーマ名} -d {データベース名} -W --format=p -E "UTF8" --verbose --statistics }"
  1. スキーマ削除
drop schema {スキーマ名} cascade;
  1. リストア
    windows上にインスタンスを立てているため、PowershellよりDBにログインし、リストアを実施しています。
psql -h {ホスト名} -p {ポート} -U {ユーザ名} -d {データベース名}
\i {バックアップファイル名};
  1. 実行計画取得
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件

カラム名 (列名) データ型 PK NULL許容 カーディナリティ
id01 bigint 〇 NO 2
id02 text NO 2
id03 text NO 2
id04 text NO 2
id04 text NO 2

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

カラム名 (列名) データ型 PK NULL許容 カーディナリティ
id01 bigint 〇 NO 2
id02 text NO 2 (内、999,999件の値が1）
id03 text NO 2
id04 text NO 2
id04 text NO 2

インデックス

インデックス名 データ型
table01_idx1 id02

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

select * from table01 where id02 = '1';
検証手順
  1. データベース作成
create database {データベース名};
  1. スキーマ作成
create schema {スキーマ名};
  1. テーブル・PK作成
    意図しないタイミングで統計情報が更新されるのを避けるため、テーブルレベルでautovacuumを無効化しています。
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);
  1. データ作成
insert into table01 (select generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000));
  1. 統計情報取得
    autovacuumを無効化しているため、バックアップ対象となる統計情報を取得させます。
analyze {対象オブジェクト全てを指定};
  1. 実行計画取得
explain analyze select * from table01 where id02 = '1';
  1. pg_dump
    windows上にインスタンスを立てているため、Powershellよりpg_dumpを実施しています。
    今回は論理バックアップの内容がわかるよう平文にてバックアップを取得します。
powershell -Command "Measure-Command { pg_dump -f {バックアップファイル名} -h {ホスト名} -p {ポート} -U {ユーザ名} -n {スキーマ名} -d {データベース名} -W --format=p -E "UTF8" --verbose --statistics-only }"
  1. スキーマ削除
drop schema {スキーマ名} cascade;
  1. リストア
    windows上にインスタンスを立てているため、PowershellよりDBにログインし、リストアを実施しています。
psql -h {ホスト名} -p {ポート} -U {ユーザ名} -d {データベース名}
\i {バックアップファイル名};
  1. 実行計画取得
explain analyze select * from table01 where id02 = '1';
  1. 統計情報取得
analyze {対象オブジェクト全てを指定};
  1. 実行計画取得
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によるバックアップ・リストアと比較して、より高い精度でバックアップ元環境を再現できるようになっているため、”検証” を行う上ではより有用性の高い機能になっていると考える